Problem with threads accessing MySQL database

[kopikopiko]

Hi,

I have an application set up under:
Django 0.91
Windows 2000 Server
MySQL 5.0
which reads scheduled events from a table then spawns threads (using
the Thread module) to process the events and update the table. As soon
as my threads started writing to the database I ran into MySQL error
2013 - 'Lost connection to MySQL server during query'. Googling led me
to Eugene Lazutkin's blogging on the issue and patches 463 and 1442 to
deal with the problems of the conflict over the MySQL connection
between the threads and the original app. I applied patch 1442 as it
seemed more recommended and the problem seemed to go away.
Unfortunately once I started running the application continuously (one
event every 15 seconds or so) I started to get the error back at
irregular intervals (as soon as 1 minute but usually within 15-20
minutes). If I comment out the database update in the thread then the
error doesn't occur.

I'm looking at how to address this by perhaps:
1) applying patch 1539
2) creating a new database connection in the thread (but don't know how
to do this)

Any advice would be appreciated...

[Eugene Lazutkin]

kopikopiko wrote:
> Hi,
>
> I have an application set up under:
> Django 0.91
> Windows 2000 Server
> MySQL 5.0
> which reads scheduled events from a table then spawns threads (using
> the Thread module) to process the events and update the table. As soon
> as my threads started writing to the database I ran into MySQL error
> 2013 - 'Lost connection to MySQL server during query'. Googling led me
> to Eugene Lazutkin's blogging on the issue and patches 463 and 1442 to
> deal with the problems of the conflict over the MySQL connection
> between the threads and the original app. I applied patch 1442 as it
> seemed more recommended and the problem seemed to go away.

#1442 was applied to trunk some time ago. Most probably you are running
an old version of Django.

> I'm looking at how to address this by perhaps:
> 1) applying patch 1539

#1539 was applied to trunk as well.

> 2) creating a new database connection in the thread (but don't know how
> to do this)
>
> Any advice would be appreciated...

svn up your Django installation and test again. Let us know the results.

Thanks,

Eugene

[Andy Dustman]

The patch on #1590 was also recently applied, and it may also be a factor.

--
The Pythonic Principle: Python works the way it does
because if it didn't, it wouldn't be Python.

[kopikopiko]

Thanks very much for your answers.

I svn'ed up to 2654 but am still seeing errors. The errors happen more
frequently after the upgrade. What I'm getting back is:

File "C:\Django\hodie\kron.py", line 109, in ? db_save(this_inst)
File "C:\Django\hodie\kron.py", line 14, in db_save obj.save()
File
"c:\python24\lib\site-packages\Django-0.91-py2.4.egg\django\utils\functional.py",
line 3, in _curried
return args[0](*(args[1:]+moreargs), **dict(kwargs.items() +
morekwargs.items()))
File
"c:\python24\lib\site-packages\Django-0.91-py2.4.egg\django\core\meta\__init__.py",
line 1029, in method_save db.db.commit()
File
"c:\python24\lib\site-packages\Django-0.91-py2.4.egg\django\core\db\backe
nds\mysql.py", line 76, in commit
self.connection.commit()
_mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL
server during query')

I tried using try/except on the database save but the 2013 error killed
my app anyway. I have now managed to work around it by setting a lock
on writing to the database (using thread.allocate_lock() ) and this
doesn't kill the application too much. As soon as I allow the thred to
write to the database I get the errors and I suspect that it is
simultaneous writes from the thread and the main programme that is
triggering the error.

Cheers,
Mike

[Eugene Lazutkin]

kopikopiko wrote:
> Thanks very much for your answers.
>
> I svn'ed up to 2654 but am still seeing errors. The errors happen more
> frequently after the upgrade. What I'm getting back is:
>
> File "C:\Django\hodie\kron.py", line 109, in ? db_save(this_inst)
> File "C:\Django\hodie\kron.py", line 14, in db_save obj.save()
> File
> "c:\python24\lib\site-packages\Django-0.91-py2.4.egg\django\utils\functional.py",
> line 3, in _curried
> return args[0](*(args[1:]+moreargs), **dict(kwargs.items() +
> morekwargs.items()))
> File
> "c:\python24\lib\site-packages\Django-0.91-py2.4.egg\django\core\meta\__init__.py",
> line 1029, in method_save db.db.commit()
> File
> "c:\python24\lib\site-packages\Django-0.91-py2.4.egg\django\core\db\backe
> nds\mysql.py", line 76, in commit
> self.connection.commit()
> _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL
> server during query')

Could you send the Django error page you are getting? Just save the html
and send it to me.

> I tried using try/except on the database save but the 2013 error killed
> my app anyway. I have now managed to work around it by setting a lock
> on writing to the database (using thread.allocate_lock() ) and this
> doesn't kill the application too much. As soon as I allow the thred to
> write to the database I get the errors and I suspect that it is
> simultaneous writes from the thread and the main programme that is
> triggering the error.

Please explain what you actually do. Now I am getting the impression
that you have your own multithreaded program, which uses Django ORM. Am
I right? Are we talking about a web site running on multithreaded WSGI
host or a stand alone local program?

Thanks,

Eugene

[kopikopiko]

Hi Eugene,

I'm not getting the error on a web page as such. The project is to
create a scheduling engine in python which uses Django for its
interface. The error comes out of the python programme run from DOS.
Essentially I'm using the Django web interface to maintain a list of
events:

class Ec_event(meta.Model):
ec_event_date_time = meta.DateTimeField('date/time')
ec_event_type = meta.CharField(maxlength=10)
ec_event_text = meta.CharField(maxlength=60)
ec_event_processed = meta.BooleanField()
ec_event_completed = meta.BooleanField()
ec_event_succeeded = meta.BooleanField()
ec_event_cancelled = meta.BooleanField()

I run a standalone python programme (called kron) which looks for new
events in the table and spawns a thread to process them:

new_event_list = ec_events.get_list(
ec_event_processed__exact = False,
ec_event_cancelled__exact = False,)
...
for this_inst in new_event_list:
this_inst.ec_event_processed = True
this_inst.save()
thread.start_new_thread(thread_test,(this_inst,))

Inside the thread I process the event's tasks and then write a status
back to the events table:

def thread_test(inst):
...
inst.ec_event_completed = True
db_save(inst)

Processing of the new events list takes place every 30 seconds while
the thread will process the event tasks at an arbitrary time. What
seems to happen is that the thread writing back to the database
conflicts at random intervals with the main programme changing other
event attributes and throws the 2013 error. The error can be avoided
manually by disabling the database write in the thread or by keeping a
lock variable on the database so saves never coincide.

Thanks again for your help,
Mike

[Eugene Lazutkin]

kopikopiko wrote:
>
> I run a standalone python programme (called kron) which looks for new
> events in the table and spawns a thread to process them:

This is exactly why you have the problem. It looks like you have a
connection, which is shared across several threads. It's a no-no. This
behavior is not related to Django. It's how databases work including MySQL.

Django itself creates one connection per thread. I know it works fine
when threads are spawned by FastCGI or any other WSGI host --- it forces
Django to re-initialize creating a connection in the process. I don't
know how it is done outside of the web processing context.

If somebody knows how Django's ORM behaves on its own, please chime in.
Otherwise we have to trace the connection's creation mechanism, and
possibly fix it.

Thanks,

Eugene

[DavidA]

Hi kopikopiko,

I thought I'd chime in on how I'm solving a similar problem. I also
have a task scheduling app written in Django, but I actually run the
tasks through Django rather than outside of it: I have a special view
(/tasks/trigger) which remembers the last time the view ran and checks
for any tasks that should have run since the last trigger time. It then
simply loads and runs each task in sequence.

Outside of Django I have a schedule NT/Windows task that runs every 5
minutes and just does an HTTP get of the trigger view url.

I can get away with this since my tasks are simple and usually take a
few seconds to run so I don't mind running them synchronously. I did
think about your approach, though, and I was going to load all the DB
data that defines the task, then fire off the task on a background
thread, then have those tasks update the DB with their stats directly
(each making their own MySQL connection and doing direct SQL). I was
going to do it that way since I didn't want my task scripts to be
dependent on Django's ORM.

But like I said, I never went down the multi-threaded route because I
wanted to keep it simple and at this point, I don't need to.
-Dave

[kopikopiko]

Hi Eugene,

Thank you for your advice - that makes sense. At the level I'm working
at (first project) and given that locking the single connection doesn't
kill my application (actions can still be performed in real time and
the only delay is to writing logs and statuses back to the database)
I'm happy with Django's capabilities.

What amazes me (beyond how useful Django is to me) is how responsive
people involved in the project are to uninformed queries like mine.
Thank you for your time and effort.

Regards,
Mike

[kopikopiko]

Hi Dave,

Thanks for your comment. That is an ingenious way of getting your tasks
executed by views. Django is so new to me that I'm clinging to my first
conception of how task timing might work lest I become completely lost
:-)

I'm working on a system to automate interactive TV and synchronise
components with external triggers so having the ability to run threads
that kick off frame-accurately is pretty mandatory. What I find amazing
is how easy it is to make an application like that in python (less than
100 lines of code) and how useful Django is in providing a
reconfigurable data structure and web front end.

Cheers,
Mike

[kopikopiko]