MySQL connection error after scheduled task completes

[Andre Kozaczka]

I'm getting the following error after my scheduled task completes:

Traceback (most recent call last): File "/home/wdis/web2py/gluon/shell.py", line 273, in

   run exec(python_code, _env) 

File "<string>", line 1, in <module> 

File "/home/wdis/web2py/gluon/scheduler.py", line 717, in loop 

   self.wrapped_report_task(task, self.async(task)) 

File "/home/wdis/web2py/gluon/scheduler.py", line 874, in wrapped_report_task 

   db.rollback() 

File "/home/wdis/web2py/gluon/packages/dal/pydal/base.py", line 956, in rollback 

   self._adapter.rollback() 

File "/home/wdis/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1342, in rollback return self.connection.rollback()

OperationalError: (2006, 'MySQL server has gone away')

It appears that the db connection created by the scheduler (to update the task status) gets stale and MySQL is closing it. I've tried adding dummy reads to the database in my scheduled task code but it seems to have no bearing on the db connection used by the scheduler.

Any advice? I'm using PythonAnywhere so I can not change the timeout setting for MySQL.

-Andre

[Niphlod]

how many seconds does it take to be executed ?

[Andre Kozaczka]

In the "real" world, the task can take anywhere from 1 minute up to 30 minutes.

In debugging my problem, I created a dummy task that would loop (sleep 15 seconds then do a db write) and end after a set period of time. I'm seeing the problem when the dummy task takes over 300 seconds - anything less that that the scheduler is able to update the task status after completion with no errors.

[Niphlod]

I'm really not sure on how to tackle this mysql-specific shortcoming but I get the point. 

What does happen if you queue the task with sync_output=200 ? Functionaly you don't need it, but practically - but still in theory - it would short-circuit the issue keeping the connection alive every 200 seconds ...

[Andre Kozaczka]

 
Well..... adding "sync_output=200" ended up working for tasks that were just over the 300 second mark (where previously I was failing). I changed the task length to 600 seconds and I'm back to failing. Ah... so close!!!

I tried changing sync_output to 100 but still failed. Unless you have another trick up your sleeve, I may have to wave the white flag and ditch MySQL.... and maybe give Postgres a try?

-Andre

[Niphlod]

is the exception raised in the same lines ?

[Andre Kozaczka]

Yes, same lines.

[Niphlod]

uhm, then it'll be quite impossible to fix it given the timeout is seemingly random.

[Andre Kozaczka]

Ah bummer! Thank you for looking into this for me.

Through PythonAnywhere, I've added a Postgres database. I'm not sure if I will migrate everything from MySQL to Postgres but I was thinking of just starting off with the scheduler. I created a second DAL for the Postgres and initialized the scheduler with it. All the tables are generated however I'm getting error messages on the console and the task sits in QUEUED status.

I know I'm veering off from the original question, but is it kosher to have a second database just for the scheduler?

Here are the error messages:
2016-06-07 14:54:57,026 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - Error retrieving status
2016-06-07 14:54:57,335 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (0)
2016-06-07 14:54:57,843 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (1)
2016-06-07 14:54:58,353 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (2)
2016-06-07 14:54:58,862 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (3)
2016-06-07 14:54:59,370 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (4)
2016-06-07 14:54:59,878 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (5)
016-06-07 14:55:00,048 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - Error retrieving status
2016-06-07 14:55:00,398 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (6)
2016-06-07 14:55:00,908 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (7)
2016-06-07 14:55:01,417 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (8)
2016-06-07 14:55:01,972 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - TICKER: error assigning tasks (9)
2016-06-07 14:55:03,066 - web2py.scheduler.giles-liveconsole2#12039 - ERROR - Error retrieving status

[Niphlod]

NP. perfectly kosher. For all matter and purposes, unless you need "transactional consistency" a separate db makes perfect sense.

Those messages though indicate that the scheduler isn't finding the tables, or the tables have an incorrect structure.

[Andre]

I was not able to get the scheduler to work with Postgres so I tried using SQLite and it seems to work! 

So moving forward I'll try using two databases: SQLite for the scheduler and MySQL for everything else.

As a side note, I'm not quite sure why Postgres did not work. I tried clearing out the tables and then have the scheduler recreate everything but I was still getting the same error. Poking around the forums, it seems a possible solution is updating the postgres drivers however my hands are a bit tied since I'm using PythonAnywhere.

-Andre

[Niphlod]

sqlite is the worst choice for concurrency, which scheduler is quite all about. I'd investigate further on the path of pythonanywhere's postgresql not being viable, mostly because it seems that psycopg2 is a "battery included" library.

[Andre]

Okay, fair enough.

Along with the errors I posted earlier (related to Postgres), I also noticed that when I try to access the scheduler_worker table I get the following error:

 Traceback (most recent call last):

  File "/home/wdis/web2py/applications/wdis/controllers/appadmin.py", line 252, in select
    limitby=(start, stop))
  File "/home/wdis/web2py/gluon/packages/dal/pydal/objects.py", line 2020, in select
    return adapter.select(self.query, fields, attributes)
  File "/home/wdis/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1296, in select
    return self._select_aux(sql,fields,attributes)
  File "/home/wdis/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1277, in _select_aux
    return processor(rows,fields,self._colnames,cacheable=cacheable)
  File "/home/wdis/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1767, in parse
    for row in rows]
  File "/home/wdis/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1677, in _parse
    value = self.parse_value(value, ft, blob_decode)
  File "/home/wdis/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1527, in parse_value
    return self.parsemap[key](value,field_type)
  File "/home/wdis/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1633, in parse_json
    raise RuntimeError('json data not a string')
RuntimeError: json data not a string

I'm not sure if the errors are related but perhaps it can offer a clue to what's going on. Any thoughts?

-Andre

[Andre]

More information.... just tried initializing the DAL with pg8000 and things appear to be working. This points to an issue with psycopg2 (or at least the version that PythonAnywhere has installed)