'MySQL server has gone away' is raised when connection goes stale while adding to 'apscheduler_jobs'

[pranav93]

When connection goes stale either because of non usage or MySQL restart, following error is raised,

1805 2017-09-26 13:39:03,396 [ERROR] sm_lib [__init__.py:56] [write_error]: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: u'INSERT INTO apscheduler_jobs (id, next_run_time, job_state) VALUES (%s, %s, %s)'] [parameters: ('f01b7658d8ae499fb86b9038f9ffc3c6', 1506450600.0, "\x80\x02}q\x01(U\x04argsq\x02\x8a\x01\x19\x85q\x03U\x08executorq\x04U\x07defaultq\x05U\rmax_instancesq\x06K\x01U\x04funcq\x07U:aps_scheduler.scheduler.executer.campaign:campaign_triggerq\x08U\x02idq\tU f01b7658d8ae499fb86b9038f9ffc3c6q\nU\rnext_run_timeq\x0bcdatetime\ndatetime\nq\x0cU\n\x07\xe1\t\x1a\x12\x1e\x00\x00\x00\x00cpytz\n_UTC\nq\r)Rq\x0e\x86Rq\x0fU\x04nameq\x10U\x10campaign_triggerq\x11U\x12misfire_grace_timeq\x12M\x84\x03U\x07triggerq\x13capscheduler.triggers.date\nDateTrigger\nq\x14)\x81q\x15}q\x16(U\x07versionq\x17K\x01U\x08run_dateq\x18h\x0cU\n\x07\xe1\t\x1a\r'\x03\x05\x94mcpytz\n_p\nq\x19U\x07Etc/UTCq\x1a\x85Rq\x1b\x86Rq\x1cubU\x08coalesceq\x1d\x89h\x17K\x01U\x06kwargsq\
 x1e}q\x1fu.")]

[pranav93]

For now I've added ping_connection in CustomJobStore which is inheriting from SQLAlchemyJobStore.

class MySQLAlchemyJobStore(SQLAlchemyJobStore):

    def __init__(self, *args, **kwargs):
        pool_pre_ping = kwargs.pop('pool_pre_ping', False)
        super(MySQLAlchemyJobStore, self).__init__(*args, **kwargs)
        if pool_pre_ping:
            event.listen(self.engine, "engine_connect", ping_connection)

Refer to http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
And http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic

[jkryanchou]

+1 Is there any good solution on this issue?

[pranav93]

@jkryanchou IMO, we should provide kwargs to SQLAlchemyJobStore to enable pessimistic pinging as well as pool_recycle for optimistic approach.

[agronholm]

For now you can just create your own engine with those options and pass that to the job store.

[jkryanchou]

@pranav93 I have inherit my own MySqlalchemyJobStore Class which provide the pool_size and pool_cycle to customize without using the original one. It Could work well.

[pranav93]

@jkryanchou +1 to that. I'm using it like that. Or as @agronholm said you can pass session to store while making sure you do event.listen(engine, "engine_connect", ping_connection) for sqlalchemy version that does not support pool_pre_ping.

[pranav93]

@agronholm, do you think there is need to apply patch or library user should take care of it by customizing like MySQLAlchemyJobStore?

[jkryanchou]

@pranav93 Yeah. It works well for me.

[agronholm]

If any customizations to the engine are needed, library users should create their own Engine and pass it to the job store. That said, I could add a new argument for doing that as a shortcut.

[pranav93]

@agronholm , I would love to apply little patch for it :)

[agronholm]

I've considered two options:

1. Adding an engine_options argument to SQLAlchemyJobStore
2. Treating engine as keyword arguments for create_engine if it's a dict

[pranav93]

First one is explicit so it seems cool IMO.

[jkryanchou]

It's better to expose the engine options for user to customize.

[agronholm]

It's better to expose the engine options for user to customize.

Wouldn't both suggestions laid out above do exactly that?

[MDMSever]

For now I've added ping_connection in CustomJobStore which is inheriting from SQLAlchemyJobStore.

class MySQLAlchemyJobStore(SQLAlchemyJobStore):

    def __init__(self, *args, **kwargs):
        pool_pre_ping = kwargs.pop('pool_pre_ping', False)
        super(MySQLAlchemyJobStore, self).__init__(*args, **kwargs)
        if pool_pre_ping:
            event.listen(self.engine, "engine_connect", ping_connection)

Refer to http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic
And http://docs.sqlalchemy.org/en/latest/core/pooling.html#disconnect-handling-pessimistic

Hello! I need a bit of help here. I want to use this MySQLAlchemyJobStore instead of the default SQLAlchemyJobStore since I get this '2006' error a lot. I notice function 'ping_connection()' is defined at the first link above. But where do I get "some_engine" from.... seems that self.engine won't work.... I thought the engine gets created inside the SQLAlchemyJobStore function.

I don't know where to start in the SQLAlchemy documentation - a concrete example would be best for me - what am I missing?