Getting a lot of errors with near-simultaneous AJAX statements (FreeTDS, UnixODBC, PyODBC, SQL Server 2008)
181 views
Skip to first unread message
csudcy
Apr 21, 2011, 12:51:48 PM4/21/11
to pyodbc
Hi all,
So, I've just written this and realised it's quite long - sorry! Ive
tried to be as detailed as possible but without too much excess.
Anyway, on with the show...
We're trying to move web server from Apache+mod_wsgi on Windows to
Nginx+uWSGI on Linux but we're having some multi threading/processing
problems.
The problems shows on some of our pages which are AJAX heavy - the
browser makes 1 call which loads the page then send off about 10 ajax
calls (pretty much simultaneously) to load different bits of the page.
The problem we have is that half of those AJAX calls get a SQL error.
However, when we reload the page (and again and again...ad infinitum)
everything works fine!
I worked out we can reliably reproduce the error by sending AJAX
requests straight from Chrome's JS console - running "for (var i=0;
i<50; i++) { $.ajax({url:'<insert URL here>'}); }" sends off 50 AJAX
calls at once. If the URL does not require database interaction (for
us, the only page which doesnt talk to the database is the login page
when the user has no session cookie), everything works fine. Restart
the uWSGI server and try it again with a URL which does interact with
the DB & the first #n fail (where uWSGI is run with #n processes e.g. -
p #n).
So, our dataflow is like this:
<SQL Server> --> <FreeTDS> --> <UnixODBC> --> <PyODBC> --> <Django-
PyODBC> --> <Django> --> <uWSGI> --> <Nginx> --> <Browser>
Everything except the first and the last step is on one box (Ubuntu
10). Ive done a lot of testing to try to pin down exactly where the
problem is - I think its in PyODBC (Ill explain why later) though Im
not absolutely certain.
In general, the tests are python scripts which run 20 threading.Thread/
multiprocess.Process to run 5 times through 10 SQL statements of
"SELECT COUNT(*) FROM <table>". The tests Ive done so far are (all on
the Linux system unless otherwise stated, working from closest to the
SQL server end of the flow outwards):
* tSQL test - From Python, run tsql executable using subprocess.Popen
passing it a SQL statement (like 'USE <db>\nSELECT COUNT(*) FROM
<table>\nGO\n'. The tSQL executable takes a servername to connect to.
Testing with Threads and Processes both work perfectly.
* iSQL test - Pretty identical to the tSQL test except it didnt need a
GO after the SELECT and iSQL uses an ODBC DSN rather than directly
inputting the server details. Again, works perfectly with both ways of
testing.
* PyODBC test - I started off testing with threads/processes sharing
connections/cursors but soon found out this isnt the way to work. The
most useful test I performed at this stage was with each thread/
process having its own connection & cursor. When not sharing
connections/cursors, this test works properly for Threads & Processes.
* Django & Django-PyODBC - I couldnt think of a way to test these
seperately (Im not sure if testing them seperately would even make
sense). So, for these, I also did several tests.
** 1. Each thread/process ran 50 "len(<model>.objects.filter(id__lt =
100))" queries. This works.
** 2. Each thread/process ran 50 "len(<model>.objects.filter(id__lt =
1))" queries. This works (though not until I understood more about
what "Threading = 1" in odbcinst.ini meant and realising I shouldnt
have added it).
** 3. Each thread/process ran 50 "<model>.objects.all().count()"
queries. <b>This fails!</b> The failure rate varies widely (from 8 to
nearly 800 calls out of the 1000 calls total (20 threads * 50
selects)). Ive also run this test on Windows & <b>it works</b>.
** 4. Each thread/process ran 50
"<model>.objects.all().aggregate(Min('id'))" queries. This also fails
though not quite so badly - average of 75 fails per 1000 calls (though
again with a fair amount of variation). Again, this test on Windows
works fine.
So, looks like it might be DjangoPyODBC? However, I took a look at the
DjangoPyODBC source code (I havent looked into Django source as I
assume it doesnt care what OS its running on & leaves DB backends to
take care of that). DjangoPyODBC doesnt seem to do anything
significantly different when running with FreeTDS on Linux vs. SQL
server on Windows. To start with, I thought it was sharing connections
between threads (which failed in my PyODBC test) but after rewriting
some of it so it used 1 connection per thread-process pair, I realised
it wasn't actually sharing connections in the first place (I think it
does something with DatabaseWrapper so there's only one instance but
it seems to use different instance variables - I can go into more
detail about that if you think it would help).
So, since:
* Django & DjangoPyODBC are pretty much identical between Windows &
Linux
* I couldnt get the iSQL and tSQL tests to fail
* I could get PyODBC to fail sometimes (though maybe its right that it
failed in the connection sharing tests)
It could maybe (possibly) be something up with PyODBC?
Help me PyODBC, you're (mostly) my only hope!
Thanks for reading, hope someone can give me some help.
Nick
So, I've just written this and realised it's quite long - sorry! Ive
tried to be as detailed as possible but without too much excess.
Anyway, on with the show...
We're trying to move web server from Apache+mod_wsgi on Windows to
Nginx+uWSGI on Linux but we're having some multi threading/processing
problems.
The problems shows on some of our pages which are AJAX heavy - the
browser makes 1 call which loads the page then send off about 10 ajax
calls (pretty much simultaneously) to load different bits of the page.
The problem we have is that half of those AJAX calls get a SQL error.
However, when we reload the page (and again and again...ad infinitum)
everything works fine!
I worked out we can reliably reproduce the error by sending AJAX
requests straight from Chrome's JS console - running "for (var i=0;
i<50; i++) { $.ajax({url:'<insert URL here>'}); }" sends off 50 AJAX
calls at once. If the URL does not require database interaction (for
us, the only page which doesnt talk to the database is the login page
when the user has no session cookie), everything works fine. Restart
the uWSGI server and try it again with a URL which does interact with
the DB & the first #n fail (where uWSGI is run with #n processes e.g. -
p #n).
So, our dataflow is like this:
<SQL Server> --> <FreeTDS> --> <UnixODBC> --> <PyODBC> --> <Django-
PyODBC> --> <Django> --> <uWSGI> --> <Nginx> --> <Browser>
Everything except the first and the last step is on one box (Ubuntu
10). Ive done a lot of testing to try to pin down exactly where the
problem is - I think its in PyODBC (Ill explain why later) though Im
not absolutely certain.
In general, the tests are python scripts which run 20 threading.Thread/
multiprocess.Process to run 5 times through 10 SQL statements of
"SELECT COUNT(*) FROM <table>". The tests Ive done so far are (all on
the Linux system unless otherwise stated, working from closest to the
SQL server end of the flow outwards):
* tSQL test - From Python, run tsql executable using subprocess.Popen
passing it a SQL statement (like 'USE <db>\nSELECT COUNT(*) FROM
<table>\nGO\n'. The tSQL executable takes a servername to connect to.
Testing with Threads and Processes both work perfectly.
* iSQL test - Pretty identical to the tSQL test except it didnt need a
GO after the SELECT and iSQL uses an ODBC DSN rather than directly
inputting the server details. Again, works perfectly with both ways of
testing.
* PyODBC test - I started off testing with threads/processes sharing
connections/cursors but soon found out this isnt the way to work. The
most useful test I performed at this stage was with each thread/
process having its own connection & cursor. When not sharing
connections/cursors, this test works properly for Threads & Processes.
* Django & Django-PyODBC - I couldnt think of a way to test these
seperately (Im not sure if testing them seperately would even make
sense). So, for these, I also did several tests.
** 1. Each thread/process ran 50 "len(<model>.objects.filter(id__lt =
100))" queries. This works.
** 2. Each thread/process ran 50 "len(<model>.objects.filter(id__lt =
1))" queries. This works (though not until I understood more about
what "Threading = 1" in odbcinst.ini meant and realising I shouldnt
have added it).
** 3. Each thread/process ran 50 "<model>.objects.all().count()"
queries. <b>This fails!</b> The failure rate varies widely (from 8 to
nearly 800 calls out of the 1000 calls total (20 threads * 50
selects)). Ive also run this test on Windows & <b>it works</b>.
** 4. Each thread/process ran 50
"<model>.objects.all().aggregate(Min('id'))" queries. This also fails
though not quite so badly - average of 75 fails per 1000 calls (though
again with a fair amount of variation). Again, this test on Windows
works fine.
So, looks like it might be DjangoPyODBC? However, I took a look at the
DjangoPyODBC source code (I havent looked into Django source as I
assume it doesnt care what OS its running on & leaves DB backends to
take care of that). DjangoPyODBC doesnt seem to do anything
significantly different when running with FreeTDS on Linux vs. SQL
server on Windows. To start with, I thought it was sharing connections
between threads (which failed in my PyODBC test) but after rewriting
some of it so it used 1 connection per thread-process pair, I realised
it wasn't actually sharing connections in the first place (I think it
does something with DatabaseWrapper so there's only one instance but
it seems to use different instance variables - I can go into more
detail about that if you think it would help).
So, since:
* Django & DjangoPyODBC are pretty much identical between Windows &
Linux
* I couldnt get the iSQL and tSQL tests to fail
* I could get PyODBC to fail sometimes (though maybe its right that it
failed in the connection sharing tests)
It could maybe (possibly) be something up with PyODBC?
Help me PyODBC, you're (mostly) my only hope!
Thanks for reading, hope someone can give me some help.
Nick
ashaner
Apr 21, 2011, 1:41:36 PM4/21/11
to pyodbc
I feel your pain, however it might help to state exactly which version
freetds, unixodbc, etc. you are using. I probably can't give you a
definitive answer, however I will say that I've experienced a lot of
problems with python threads and/or pyodbc, freetds and unixodbc
(especially the 0.82 versions of freetds). Most of my pain was when
unixODBC jumped to 2.3.0 on my distro, but freetds is still 0.82.
Ultimately I ended up getting a nightly build of freetds (0.83 series)
working with unix ODBC 2.3.0 that seemed to resolve the threading
issues, however I've also rewritten my big threaded apps to multi
process instead since the python GIL makes threading kind of a pain in
some cases.
Though not necessarily thread/process issue, you may want to try this
simple test in a python shell:
conn = pyodbc.connect(dsn)
cursor1 = conn.cursor()
cursor1.execute('select null')
cursor2 = conn.cursor()
cursor2.execute('select null')
When you stated you where sharing connections/cursors between
threads/processes, I believe you would definitely have an issue if you
try to use 2 cursors on the same connection unless each cursor is
explicitly closed before the next one is used. In my example above, I
get an error on the last line, but not if I call cursor1.close()
before doing the cursor2 work.
Unless I missed it, can you provide the exact error when your calls
fail? Maybe it's in your nginx error log?
freetds, unixodbc, etc. you are using. I probably can't give you a
definitive answer, however I will say that I've experienced a lot of
problems with python threads and/or pyodbc, freetds and unixodbc
(especially the 0.82 versions of freetds). Most of my pain was when
unixODBC jumped to 2.3.0 on my distro, but freetds is still 0.82.
Ultimately I ended up getting a nightly build of freetds (0.83 series)
working with unix ODBC 2.3.0 that seemed to resolve the threading
issues, however I've also rewritten my big threaded apps to multi
process instead since the python GIL makes threading kind of a pain in
some cases.
Though not necessarily thread/process issue, you may want to try this
simple test in a python shell:
conn = pyodbc.connect(dsn)
cursor1 = conn.cursor()
cursor1.execute('select null')
cursor2 = conn.cursor()
cursor2.execute('select null')
When you stated you where sharing connections/cursors between
threads/processes, I believe you would definitely have an issue if you
try to use 2 cursors on the same connection unless each cursor is
explicitly closed before the next one is used. In my example above, I
get an error on the last line, but not if I call cursor1.close()
before doing the cursor2 work.
Unless I missed it, can you provide the exact error when your calls
fail? Maybe it's in your nginx error log?
csudcy
May 3, 2011, 7:36:32 AM5/3/11
to pyodbc
Hey ashaner,
Sorry for the delay, Ive been off work for a while (taking 3 days
holiday to get 11 days off is awesome :)).
Anyway, Im looking at it again now and any help is much appreciated.
On Apr 21, 6:41 pm, ashaner <asha...@chumpland.org> wrote:
> I feel your pain, however it might help to state exactly which version
> freetds, unixodbc, etc. you are using. I probably can't give you a
Woops, didnt think to include those! I went through and got all the
versions I could find:
SQL Server - 2005/2008 (results were identical)
Ubuntu - 10.04.2 LTS
FreeTDS - 0.82 (tried 0.91.dev.20110415RC2 but it was seg-faulting)
UnixODBC - 2.2.11-21
PyODBC - 2.1.8
Django PyODBC - ? (see below)
Django - 1.3.0
uWSGI - 0.9.7.2
Nginx - 0.8.54
I couldnt find a version for Django PyODBC so compared the latest svn
trunk to the version I used for the tests and there were no
significant changes.
> definitive answer, however I will say that I've experienced a lot of
> problems with python threads and/or pyodbc, freetds and unixodbc
> (especially the 0.82 versions of freetds). Most of my pain was when
> unixODBC jumped to 2.3.0 on my distro, but freetds is still 0.82.
> Ultimately I ended up getting a nightly build of freetds (0.83 series)
> working with unix ODBC 2.3.0 that seemed to resolve the threading
> issues, however I've also rewritten my big threaded apps to multi
> process instead since the python GIL makes threading kind of a pain in
> some cases.
Yeah, I tried compiling a newer version of FreeTDS but either I went
for a version that was too new or I did something wrong. I didnt see
any of the errors I was getting previously but it started seg-faulting
after a random amount of DB hits. Ill see if I can get source for an
earlier version (probably start with 0.83 and work up) - even though
my UnixODBC is still 2.2, it could still work.
> Though not necessarily thread/process issue, you may want to try this
> simple test in a python shell:
>
> conn = pyodbc.connect(dsn)
> cursor1 = conn.cursor()
> cursor1.execute('select null')
> cursor2 = conn.cursor()
> cursor2.execute('select null')
>
> When you stated you where sharing connections/cursors between
> threads/processes, I believe you would definitely have an issue if you
> try to use 2 cursors on the same connection unless each cursor is
> explicitly closed before the next one is used. In my example above, I
> get an error on the last line, but not if I call cursor1.close()
> before doing the cursor2 work.
Yeah, after I ran my tests with sharing connections/cursors, I realise
this doesnt (& probably shouldnt) work.
> Unless I missed it, can you provide the exact error when your calls
> fail? Maybe it's in your nginx error log?
Woops, forgot that too!
The nginx errors probably arent very useful but they are:
~64%: connect() failed (111: Connection refused) while connecting to
upstream, client: 172.17.125.153, server: localhost, request: "POST /
common/ajax/set_component_state/ HTTP/1.1", upstream: "uwsgi://
127.0.0.1:3035", host: "aam-webtest-1", referrer: "http://aam-
webtest-1/complex/553/hub/"
~32%: upstream prematurely closed connection while reading response
header from upstream, client: 172.17.125.153, server: localhost,
request: "GET /alert/ajax/get_hub_alerts_datatable/complex/153/?
_=1303232433932 HTTP/1.1", upstream: "uwsgi://127.0.0.1:3031", host:
"aam-webtest-1", referrer: "http://aam-webtest-1/complex/153/hub/"
~4%: recv() failed (104: Connection reset by peer) while reading
response header from upstream, client: 172.17.125.153, server:
localhost, request: "POST /login/ HTTP/1.1", upstream: "uwsgi://
127.0.0.1:3031", host: "aam-webtest-1", referrer: "http://aam-
webtest-1/login/?next=/complex/553/hub/"
I think the connect() and upstream errors are actually the same but it
shows the upstream error when I configure it with an upstream cluster.
The SQL errors only get printed to the uWSGI shell at the moment so
Ive lost them from my extensive testing before. So, Ive just done some
more testing (using the js console way) and the results are:
~53%: pyodbc.Error: ('08S01', '[08S01] [FreeTDS][SQL Server]Write to
the server failed (20006) (SQLPrepare)')
~44%: pyodbc.Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected
EOF from the server (20017) (SQLPrepare)')
~3%: pyodbc.Error: ('HY000', 'The driver did not supply an error!')
I also remember getting one about 'could not find prepared statement
0' before. I think they all happen pretty randomly.
On the SQL server, there's nothing in event viewer or the logs in the
management section in management studio. Not sure if there are any
other logs I can look at?
Ill try compiling various FreeTDS versions after lunch & see if that
helps.
Thanks again,
Nick
Sorry for the delay, Ive been off work for a while (taking 3 days
holiday to get 11 days off is awesome :)).
Anyway, Im looking at it again now and any help is much appreciated.
On Apr 21, 6:41 pm, ashaner <asha...@chumpland.org> wrote:
> I feel your pain, however it might help to state exactly which version
> freetds, unixodbc, etc. you are using. I probably can't give you a
versions I could find:
SQL Server - 2005/2008 (results were identical)
Ubuntu - 10.04.2 LTS
FreeTDS - 0.82 (tried 0.91.dev.20110415RC2 but it was seg-faulting)
UnixODBC - 2.2.11-21
PyODBC - 2.1.8
Django PyODBC - ? (see below)
Django - 1.3.0
uWSGI - 0.9.7.2
Nginx - 0.8.54
I couldnt find a version for Django PyODBC so compared the latest svn
trunk to the version I used for the tests and there were no
significant changes.
> definitive answer, however I will say that I've experienced a lot of
> problems with python threads and/or pyodbc, freetds and unixodbc
> (especially the 0.82 versions of freetds). Most of my pain was when
> unixODBC jumped to 2.3.0 on my distro, but freetds is still 0.82.
> Ultimately I ended up getting a nightly build of freetds (0.83 series)
> working with unix ODBC 2.3.0 that seemed to resolve the threading
> issues, however I've also rewritten my big threaded apps to multi
> process instead since the python GIL makes threading kind of a pain in
> some cases.
for a version that was too new or I did something wrong. I didnt see
any of the errors I was getting previously but it started seg-faulting
after a random amount of DB hits. Ill see if I can get source for an
earlier version (probably start with 0.83 and work up) - even though
my UnixODBC is still 2.2, it could still work.
> Though not necessarily thread/process issue, you may want to try this
> simple test in a python shell:
>
> conn = pyodbc.connect(dsn)
> cursor1 = conn.cursor()
> cursor1.execute('select null')
> cursor2 = conn.cursor()
> cursor2.execute('select null')
>
> When you stated you where sharing connections/cursors between
> threads/processes, I believe you would definitely have an issue if you
> try to use 2 cursors on the same connection unless each cursor is
> explicitly closed before the next one is used. In my example above, I
> get an error on the last line, but not if I call cursor1.close()
> before doing the cursor2 work.
this doesnt (& probably shouldnt) work.
> Unless I missed it, can you provide the exact error when your calls
> fail? Maybe it's in your nginx error log?
The nginx errors probably arent very useful but they are:
~64%: connect() failed (111: Connection refused) while connecting to
upstream, client: 172.17.125.153, server: localhost, request: "POST /
common/ajax/set_component_state/ HTTP/1.1", upstream: "uwsgi://
127.0.0.1:3035", host: "aam-webtest-1", referrer: "http://aam-
webtest-1/complex/553/hub/"
~32%: upstream prematurely closed connection while reading response
header from upstream, client: 172.17.125.153, server: localhost,
request: "GET /alert/ajax/get_hub_alerts_datatable/complex/153/?
_=1303232433932 HTTP/1.1", upstream: "uwsgi://127.0.0.1:3031", host:
"aam-webtest-1", referrer: "http://aam-webtest-1/complex/153/hub/"
~4%: recv() failed (104: Connection reset by peer) while reading
response header from upstream, client: 172.17.125.153, server:
localhost, request: "POST /login/ HTTP/1.1", upstream: "uwsgi://
127.0.0.1:3031", host: "aam-webtest-1", referrer: "http://aam-
webtest-1/login/?next=/complex/553/hub/"
I think the connect() and upstream errors are actually the same but it
shows the upstream error when I configure it with an upstream cluster.
The SQL errors only get printed to the uWSGI shell at the moment so
Ive lost them from my extensive testing before. So, Ive just done some
more testing (using the js console way) and the results are:
~53%: pyodbc.Error: ('08S01', '[08S01] [FreeTDS][SQL Server]Write to
the server failed (20006) (SQLPrepare)')
~44%: pyodbc.Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected
EOF from the server (20017) (SQLPrepare)')
~3%: pyodbc.Error: ('HY000', 'The driver did not supply an error!')
I also remember getting one about 'could not find prepared statement
0' before. I think they all happen pretty randomly.
On the SQL server, there's nothing in event viewer or the logs in the
management section in management studio. Not sure if there are any
other logs I can look at?
Ill try compiling various FreeTDS versions after lunch & see if that
helps.
Thanks again,
Nick
ashaner
May 3, 2011, 10:09:03 AM5/3/11
to pyodbc
On May 3, 7:36 am, csudcy <csu...@gmail.com> wrote:
> SQL Server - 2005/2008 (results were identical)
> Ubuntu - 10.04.2 LTS
> FreeTDS - 0.82 (tried 0.91.dev.20110415RC2 but it was seg-faulting)
> UnixODBC - 2.2.11-21
> PyODBC - 2.1.8
> Django PyODBC - ? (see below)
> Django - 1.3.0
> uWSGI - 0.9.7.2
> Nginx - 0.8.54
>
All of my django apps use nginx / apache / mod_wsgi / psycopg2 /
> SQL Server - 2005/2008 (results were identical)
> Ubuntu - 10.04.2 LTS
> FreeTDS - 0.82 (tried 0.91.dev.20110415RC2 but it was seg-faulting)
> UnixODBC - 2.2.11-21
> PyODBC - 2.1.8
> Django PyODBC - ? (see below)
> Django - 1.3.0
> uWSGI - 0.9.7.2
> Nginx - 0.8.54
>
postgresql, so I'm unfamiliar with Django PyODBC and uWSGI. However I
do have some cherrypy apps that use pyodbc to connect to Sql Server
2008. From recollection, unixodbc 2.2.x and freetds 0.82 had no issues
for me. But if you are going to try newer versions of freetds (0.83
and higher), then definitely try to use unixodbc 2.3.0 as well.
> The SQL errors only get printed to the uWSGI shell at the moment so
> Ive lost them from my extensive testing before. So, Ive just done some
> more testing (using the js console way) and the results are:
> ~53%: pyodbc.Error: ('08S01', '[08S01] [FreeTDS][SQL Server]Write to
> the server failed (20006) (SQLPrepare)')
> ~44%: pyodbc.Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected
> EOF from the server (20017) (SQLPrepare)')
> ~3%: pyodbc.Error: ('HY000', 'The driver did not supply an error!')
> I also remember getting one about 'could not find prepared statement
> 0' before. I think they all happen pretty randomly.
>
many times. At this point my only suggestion is to try the newer
unixodbc (2.3.0) with a newer freetds (>=0.83) just to compare. I also
distinctly remember getting segfaults on one of my servers while
messing around with different versions of these two packages.
Reply all
Reply to author
Forward
0 new messages