[web2py] Re: ProgrammingError: (2014, "Commands out of sync; you can't run this command now")
Here is a the problem. If you put queries in try... except you must
follow this pattern:
try:
... any db io ...
except:
db.rollback()
If you do not rollback any subsequent attempt to commit or access the
db again results in an error.
On Nov 10, 2:07 pm, David Zejda wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> This one is also quite common:
>
> Traceback (most recent call last):
> File "/opt/web2py/gluon/main.py", line 475, in wsgibase
> BaseAdapter.close_all_instances(BaseAdapter.rollback)
> File "/opt/web2py/gluon/sql.py", line 810, in close_all_instances
> action(instance)
> File "/opt/web2py/gluon/sql.py", line 1393, in rollback
> self._connection.rollback()
> ProgrammingError: (2014, "Commands out of sync; you can't run this
> command now")
>
> Yes, I have many queries also in try..except blocks. But it is really
> hard to find what exactly is the root of problem because I did not
> reveal any meaningful shape in the error occurencies. At least not yet. :(
>
> D.
>
>
>
> mdipierro wrote:
> > Has anybody else here had a similar problem?
>
> > massimo
>
> > On Nov 9, 11:29 am, David Zejda wrote:
> > Hi :)
>
> > E.g. now the exception was raised by the web2py internal db
> > initialization routine:
>
> > 28: db = SQLDB('mysql://myus:myp...@localhost:330h6/mydb', pool_size=10)
>
> > Traceback (most recent call last):
> > File "/opt/web2py/gluon/restricted.py", line 188, in restricted
> > exec ccode in environment
> > File "/opt/web2py/applications/myapp/compiled/models_db.py", line 28,
> > in
> > File "/opt/web2py/gluon/sql.py", line 978, in __init__
> > self._execute('SET FOREIGN_KEY_CHECKS=1;')
> > File "/opt/web2py/gluon/sql.py", line 977, in
> > self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
> > File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
> > in execute
> > self.errorhandler(self, exc, value)
> > File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
> > 35, in defaulterrorhandler
> > raise errorclass, errorvalue
> > ProgrammingError: (2014, "Commands out of sync; you can't run this
> > command now")
>
> > I'm thinking about possibility to raise the size of my poll. But in the
> > past with higher poll I think I was receiving more OperationalError:
> > (2006, 'MySQL server has gone away'). Because I am referring to a live
> > server, I would prefer to avoid experiments which could make frequency
> > of db errors even worse :)
>
> > thanks & wishing you a nice day..
> > David
>
> > mdipierro wrote:
> I cannot say without looking at the code. MySQL has lots of
> undocumented quirks about what you can do and what you cannot do
> within one transaction.
> Try add a db.commit() after each insert/unpdate/form.accepts/
> crud.update/crud.select IF you do a select after that.
> Do you have any try:...except in your controllers and db queries
> inside?
> On Nov 8, 2:57 am, David Zejda wrote:
> The MySQL error occurs quite often, several times every day. The app has
> about 2 page views daily and heavily communicates with db (tens of
> queries per request, in db I have about 100 tables). Currently I have
> pool with size for 10 connections.
> Meaning of the error message is described here:
> http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html
> It seems that with MySQL statements on the same db connection have to be
> exhausted one-by-one, never in parallel. Two statements returning data
> to the process must be using separate DBConnections (possibly to the
> same host/db). Multiple statements on the same connection are supported,
> but only 1 may be in a state to 'fetch' data.
> Traces for the errors look like this:
> File "/opt/web2py/gluon/sql.py", line 3378, in count
> return self.select('count(*)')[0]._extra['count(*)']
> File "/opt/web2py/gluon/sql.py", line 3237, in select
> rows = response(query)
> File "/opt/web2py/gluon/sql.py", line 3232, in response
> db._execute(query)
> File "/opt/web2py/gluon/sql.py", line 977, in
> self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
> File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
> in execute
> self.errorhandler(self, exc, value)
> File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
> 35, in defaulterrorhandler
> raise errorclass, errorvalue
> ProgrammingError: (2014, "Commands out of sync; you can't run this
> command now")
> Or e.g.:
> Traceback (most recent call last):
> File "/opt/web2py/gluon/main.py", line 475, in wsgibase
> BaseAdapter.close_all_instances(BaseAdapter.rollback)
> File "/opt/web2py/gluon/sql.py", line 810, in close_all_instances
> action(instance)
> File "/opt/web2py/gluon/sql
Re: [web2py] Re: ProgrammingError: (2014, "Commands out of sync; you can't run this command now")
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
This one is also quite common:
Traceback (most recent call last):
File "/opt/web2py/gluon/main.py", line 475, in wsgibase
BaseAdapter.close_all_instances(BaseAdapter.rollback)
File "/opt/web2py/gluon/sql.py", line 810, in close_all_instances
action(instance)
File "/opt/web2py/gluon/sql.py", line 1393, in rollback
self._connection.rollback()
ProgrammingError: (2014, "Commands out of sync; you can't run this
command now")
Yes, I have many queries also in try..except blocks. But it is really
hard to find what exactly is the root of problem because I did not
reveal any meaningful shape in the error occurencies. At least not yet. :(
D.
mdipierro wrote:
> Has anybody else here had a similar problem?
>
> massimo
>
> On Nov 9, 11:29 am, David Zejda wrote:
> Hi :)
>
> E.g. now the exception was raised by the web2py internal db
> initialization routine:
>
> 28: db = SQLDB('mysql://myus:myp...@localhost:330h6/mydb', pool_size=10)
>
> Traceback (most recent call last):
> File "/opt/web2py/gluon/restricted.py", line 188, in restricted
> exec ccode in environment
> File "/opt/web2py/applications/myapp/compiled/models_db.py", line 28,
> in
> File "/opt/web2py/gluon/sql.py", line 978, in __init__
> self._execute('SET FOREIGN_KEY_CHECKS=1;')
> File "/opt/web2py/gluon/sql.py", line 977, in
> self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
> File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
> in execute
> self.errorhandler(self, exc, value)
> File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
> 35, in defaulterrorhandler
> raise errorclass, errorvalue
> ProgrammingError: (2014, "Commands out of sync; you can't run this
> command now")
>
> I'm thinking about possibility to raise the size of my poll. But in the
> past with higher poll I think I was receiving more OperationalError:
> (2006, 'MySQL server has gone away'). Because I am referring to a live
> server, I would prefer to avoid experiments which could make frequency
> of db errors even worse :)
>
> thanks & wishing you a nice day..
> David
>
>
>
> mdipierro wrote:
I cannot say without looking at the code. MySQL has lots of
undocumented quirks about what you can do and what you cannot do
within one transaction.
Try add a db.commit() after each insert/unpdate/form.accepts/
crud.update/crud.select IF you do a select after that.
Do you have any try:...except in your controllers and db queries
inside?
On Nov 8, 2:57 am, David Zejda wrote:
The MySQL error occurs quite often, several times every day. The app has
about 2 page views daily and heavily communicates with db (tens of
queries per request, in db I have about 100 tables). Currently I have
pool with size for 10 connections.
Meaning of the error message is described here:
http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html
It seems that with MySQL statements on the same db connection have to be
exhausted one-by-one, never in parallel. Two statements returning data
to the process must be using separate DBConnections (possibly to the
same host/db). Multiple statements on the same connection are supported,
but only 1 may be in a state to 'fetch' data.
Traces for the errors look like this:
File "/opt/web2py/gluon/sql.py", line 3378, in count
return self.select('count(*)')[0]._extra['count(*)']
File "/opt/web2py/gluon/sql.py", line 3237, in select
rows = response(query)
File "/opt/web2py/gluon/sql.py", line 3232, in response
db._execute(query)
File "/opt/web2py/gluon/sql.py", line 977, in
self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
in execute
self.errorhandler(self, exc, value)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
35, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (2014, "Commands out of sync; you can't run this
command now")
Or e.g.:
Traceback (most recent call last):
File "/opt/web2py/gluon/main.py", line 475, in wsgibase
BaseAdapter.close_all_instances(BaseAdapter.rollback)
File "/opt/web2py/gluon/sql.py", line 810, in close_all_instances
action(instance)
File "/opt/web2py/gluon/sql.py", line 1393, in rollback
self._connection.rollback()
ProgrammingError: (2014, "Commands out of sync; you can't run this
command now")
Do you have any tips how to aviod these errors?
Thanks!
- --
David Zejda, Open-IT cz
web development & services
http://www.o-it.info
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkza+54ACgkQ3oCkkciamV
[web2py] Re: ProgrammingError: (2014, "Commands out of sync; you can't run this command now")
Has anybody else here had a similar problem?
massimo
On Nov 9, 11:29 am, David Zejda wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi :)
>
> E.g. now the exception was raised by the web2py internal db
> initialization routine:
>
> 28: db = SQLDB('mysql://myus:myp...@localhost:330h6/mydb', pool_size=10)
>
> Traceback (most recent call last):
> File "/opt/web2py/gluon/restricted.py", line 188, in restricted
> exec ccode in environment
> File "/opt/web2py/applications/myapp/compiled/models_db.py", line 28,
> in
> File "/opt/web2py/gluon/sql.py", line 978, in __init__
> self._execute('SET FOREIGN_KEY_CHECKS=1;')
> File "/opt/web2py/gluon/sql.py", line 977, in
> self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
> File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
> in execute
> self.errorhandler(self, exc, value)
> File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
> 35, in defaulterrorhandler
> raise errorclass, errorvalue
> ProgrammingError: (2014, "Commands out of sync; you can't run this
> command now")
>
> I'm thinking about possibility to raise the size of my poll. But in the
> past with higher poll I think I was receiving more OperationalError:
> (2006, 'MySQL server has gone away'). Because I am referring to a live
> server, I would prefer to avoid experiments which could make frequency
> of db errors even worse :)
>
> thanks & wishing you a nice day..
> David
>
>
>
> mdipierro wrote:
> > I cannot say without looking at the code. MySQL has lots of
> > undocumented quirks about what you can do and what you cannot do
> > within one transaction.
>
> > Try add a db.commit() after each insert/unpdate/form.accepts/
> > crud.update/crud.select IF you do a select after that.
>
> > Do you have any try:...except in your controllers and db queries
> > inside?
>
> > On Nov 8, 2:57 am, David Zejda wrote:
> > The MySQL error occurs quite often, several times every day. The app has
> > about 2 page views daily and heavily communicates with db (tens of
> > queries per request, in db I have about 100 tables). Currently I have
> > pool with size for 10 connections.
>
> > Meaning of the error message is described here:
>
> >http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html
>
> > It seems that with MySQL statements on the same db connection have to be
> > exhausted one-by-one, never in parallel. Two statements returning data
> > to the process must be using separate DBConnections (possibly to the
> > same host/db). Multiple statements on the same connection are supported,
> > but only 1 may be in a state to 'fetch' data.
>
> > Traces for the errors look like this:
>
> > File "/opt/web2py/gluon/sql.py", line 3378, in count
> > return self.select('count(*)')[0]._extra['count(*)']
> > File "/opt/web2py/gluon/sql.py", line 3237, in select
> > rows = response(query)
> > File "/opt/web2py/gluon/sql.py", line 3232, in response
> > db._execute(query)
> > File "/opt/web2py/gluon/sql.py", line 977, in
> > self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
> > File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
> > in execute
> > self.errorhandler(self, exc, value)
> > File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
> > 35, in defaulterrorhandler
> > raise errorclass, errorvalue
> > ProgrammingError: (2014, "Commands out of sync; you can't run this
> > command now")
>
> > Or e.g.:
>
> > Traceback (most recent call last):
> > File "/opt/web2py/gluon/main.py", line 475, in wsgibase
> > BaseAdapter.close_all_instances(BaseAdapter.rollback)
> > File "/opt/web2py/gluon/sql.py", line 810, in close_all_instances
> > action(instance)
> > File "/opt/web2py/gluon/sql.py", line 1393, in rollback
> > self._connection.rollback()
> > ProgrammingError: (2014, "Commands out of sync; you can't run this
> > command now")
>
> > Do you have any tips how to aviod these errors?
>
> > Thanks!
>
> - --
> David Zejda, Open-IT cz
> web development & serviceshttp://www.o-it.info
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org
>
> iEYEARECAAYFAkzZhPcACgkQ3oCkkciamVGyjwCfc0HsoJyvD8DuRWDjT02UJwae
> U0cAmgKsRs7vhz2j5MTljR6TQDulBvt9
> =HFwI
> -END PGP SIGNATURE-
Re: [web2py] Re: ProgrammingError: (2014, "Commands out of sync; you can't run this command now")
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi :)
E.g. now the exception was raised by the web2py internal db
initialization routine:
28: db = SQLDB('mysql://myus:myp...@localhost:330h6/mydb', pool_size=10)
Traceback (most recent call last):
File "/opt/web2py/gluon/restricted.py", line 188, in restricted
exec ccode in environment
File "/opt/web2py/applications/myapp/compiled/models_db.py", line 28,
in
File "/opt/web2py/gluon/sql.py", line 978, in __init__
self._execute('SET FOREIGN_KEY_CHECKS=1;')
File "/opt/web2py/gluon/sql.py", line 977, in
self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
in execute
self.errorhandler(self, exc, value)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
35, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (2014, "Commands out of sync; you can't run this
command now")
I'm thinking about possibility to raise the size of my poll. But in the
past with higher poll I think I was receiving more OperationalError:
(2006, 'MySQL server has gone away'). Because I am referring to a live
server, I would prefer to avoid experiments which could make frequency
of db errors even worse :)
thanks & wishing you a nice day..
David
mdipierro wrote:
> I cannot say without looking at the code. MySQL has lots of
> undocumented quirks about what you can do and what you cannot do
> within one transaction.
>
> Try add a db.commit() after each insert/unpdate/form.accepts/
> crud.update/crud.select IF you do a select after that.
>
> Do you have any try:...except in your controllers and db queries
> inside?
>
>
> On Nov 8, 2:57 am, David Zejda wrote:
> The MySQL error occurs quite often, several times every day. The app has
> about 2 page views daily and heavily communicates with db (tens of
> queries per request, in db I have about 100 tables). Currently I have
> pool with size for 10 connections.
>
> Meaning of the error message is described here:
>
> http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html
>
> It seems that with MySQL statements on the same db connection have to be
> exhausted one-by-one, never in parallel. Two statements returning data
> to the process must be using separate DBConnections (possibly to the
> same host/db). Multiple statements on the same connection are supported,
> but only 1 may be in a state to 'fetch' data.
>
> Traces for the errors look like this:
>
> File "/opt/web2py/gluon/sql.py", line 3378, in count
> return self.select('count(*)')[0]._extra['count(*)']
> File "/opt/web2py/gluon/sql.py", line 3237, in select
> rows = response(query)
> File "/opt/web2py/gluon/sql.py", line 3232, in response
> db._execute(query)
> File "/opt/web2py/gluon/sql.py", line 977, in
> self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
> File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
> in execute
> self.errorhandler(self, exc, value)
> File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
> 35, in defaulterrorhandler
> raise errorclass, errorvalue
> ProgrammingError: (2014, "Commands out of sync; you can't run this
> command now")
>
> Or e.g.:
>
> Traceback (most recent call last):
> File "/opt/web2py/gluon/main.py", line 475, in wsgibase
> BaseAdapter.close_all_instances(BaseAdapter.rollback)
> File "/opt/web2py/gluon/sql.py", line 810, in close_all_instances
> action(instance)
> File "/opt/web2py/gluon/sql.py", line 1393, in rollback
> self._connection.rollback()
> ProgrammingError: (2014, "Commands out of sync; you can't run this
> command now")
>
> Do you have any tips how to aviod these errors?
>
> Thanks!
>
- --
David Zejda, Open-IT cz
web development & services
http://www.o-it.info
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkzZhPcACgkQ3oCkkciamVGyjwCfc0HsoJyvD8DuRWDjT02UJwae
U0cAmgKsRs7vhz2j5MTljR6TQDulBvt9
=HFwI
-END PGP SIGNATURE-
[web2py] Re: ProgrammingError: (2014, "Commands out of sync; you can't run this command now")
I cannot say without looking at the code. MySQL has lots of
undocumented quirks about what you can do and what you cannot do
within one transaction.
Try add a db.commit() after each insert/unpdate/form.accepts/
crud.update/crud.select IF you do a select after that.
Do you have any try:...except in your controllers and db queries
inside?
On Nov 8, 2:57 am, David Zejda wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> The MySQL error occurs quite often, several times every day. The app has
> about 2 page views daily and heavily communicates with db (tens of
> queries per request, in db I have about 100 tables). Currently I have
> pool with size for 10 connections.
>
> Meaning of the error message is described here:
>
> http://dev.mysql.com/doc/refman/4.1/en/commands-out-of-sync.html
>
> It seems that with MySQL statements on the same db connection have to be
> exhausted one-by-one, never in parallel. Two statements returning data
> to the process must be using separate DBConnections (possibly to the
> same host/db). Multiple statements on the same connection are supported,
> but only 1 may be in a state to 'fetch' data.
>
> Traces for the errors look like this:
>
> File "/opt/web2py/gluon/sql.py", line 3378, in count
> return self.select('count(*)')[0]._extra['count(*)']
> File "/opt/web2py/gluon/sql.py", line 3237, in select
> rows = response(query)
> File "/opt/web2py/gluon/sql.py", line 3232, in response
> db._execute(query)
> File "/opt/web2py/gluon/sql.py", line 977, in
> self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
> File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 166,
> in execute
> self.errorhandler(self, exc, value)
> File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line
> 35, in defaulterrorhandler
> raise errorclass, errorvalue
> ProgrammingError: (2014, "Commands out of sync; you can't run this
> command now")
>
> Or e.g.:
>
> Traceback (most recent call last):
> File "/opt/web2py/gluon/main.py", line 475, in wsgibase
> BaseAdapter.close_all_instances(BaseAdapter.rollback)
> File "/opt/web2py/gluon/sql.py", line 810, in close_all_instances
> action(instance)
> File "/opt/web2py/gluon/sql.py", line 1393, in rollback
> self._connection.rollback()
> ProgrammingError: (2014, "Commands out of sync; you can't run this
> command now")
>
> Do you have any tips how to aviod these errors?
>
> Thanks!
>
> - --
> David Zejda
> Sb rka na operaci pro p tiletou Vanessuhttp://www.tabang.eu
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org
>
> iEYEARECAAYFAkzXu3oACgkQ3oCkkciamVEXdACeJOUPZt8d3yDvgNNNY6V8HDmX
> TvEAnid88S+V2I7OV9XLR1Kq/etu92kM
> =iP7t
> -END PGP SIGNATURE-
