"Error closing cursor" on MySQL silently traps exception

[Joril]

Hi everyone!
I'm building a Pylons webapp using SA 0.6.4 and MySQL-Python 1.2.3.
Right now I'm writing the first "change script" for sqlalchemy-migrate
(just 3 lines of SQL).
I noticed that if the change script contains some syntax error, SA
catches the exception (in pool.py) and logs a

Error closing cursor: (1064, "You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near 'NOT NULL' at line 1")

and doesn't propagate the exception, so the outer code won't know that
something went wrong.. In my case, this means that migrate commits che
transaction and I end up with a partially-applied change script.

Am I doing something wrong?

[Michael Bayer]

Sometimes native C libraries for Python do this on occasion - they fail to raise the exception at the appropriate time, then mysteriously the next line of Python code trips the actual error. I've seen it on several occasions with different libraries. Generally its a bug in the library, but they are hard to isolate since there's some specific series of steps that must occur.

I haven't ever observed it with MySQL-python however - we have many tests that raise exceptions and they work fine. In your own environment, a script like the following:

from sqlalchemy import *
e = create_engine('mysql://scott:tiger@localhost/test', echo=True)
e.execute("this is crap")

can confirm this. The stack trace needs to originate within the "execute()" chain within SQLA, goes down to default->do_execute(), then cursor.execute() on the MySQLdb side.

Assuming the above raises its error just fine as it does on my system, there must be some unusual pattern in the way the error is being generated. I don't know that there's anything on the SQLAlchemy side that can address this kind of thing in a consistent way, usually the strategy is to narrow down the series of DBAPI calls to a pure MySQL-python script that continues to reproduce. Its tricky though.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Joril]

On Jan 22, 7:01 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> Assuming the above raises its error just fine as it does on my system, there must be some unusual pattern in the way the error is being generated.    I don't know that there's anything on the  SQLAlchemy side that can address this kind of thing in a consistent way, usually the strategy is to narrow down the series of DBAPI calls to a pure MySQL-python script that continues to reproduce.   Its tricky though.

I see... I did a few more tests, and it looks like the problem arises
consistently when executing multiple statements having a syntax error
in the second (or later) one..

Extending your example:

from sqlalchemy import *
e = create_engine('mysql://scott:tiger@localhost/test', echo=True)

e.execute("select * from validtable; this is crap")

This returns a ResultProxy, but on the NEXT execution (even if a
correct one) SQLAlchemy logs a

INFO sqlalchemy.engine.base.Engine.0x...4acL ()
Exception _mysql_exceptions.ProgrammingError: (1064, "You have an

error in your SQL syntax; check the manual that corresponds to your

MySQL server version for the right syntax to use near 'this is crap'
at line 1") in <bound method Cursor.__del__ of <MySQLdb.cursors.Cursor
object at 0xb743238c>> ignored

Maybe I could persuade SA-migrate to execute one statement at a
time...
Thanks for your assistance :)

[Michael Bayer]

On Jan 23, 2011, at 6:21 AM, Joril wrote:

> On Jan 22, 7:01 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> Assuming the above raises its error just fine as it does on my system, there must be some unusual pattern in the way the error is being generated. I don't know that there's anything on the SQLAlchemy side that can address this kind of thing in a consistent way, usually the strategy is to narrow down the series of DBAPI calls to a pure MySQL-python script that continues to reproduce. Its tricky though.
>
> I see... I did a few more tests, and it looks like the problem arises
> consistently when executing multiple statements having a syntax error
> in the second (or later) one..
>
> Extending your example:
>
> from sqlalchemy import *
> e = create_engine('mysql://scott:tiger@localhost/test', echo=True)
> e.execute("select * from validtable; this is crap")

Well that's your problem, that's not legal DBAPI usage. Its a bug IMHO in MySQLdb that it's accepted.