Stored-procedure commit woes

[Dustin Oprea]

I'm having a problem where I'm doing writes to MySQL that aren't being committed. I'm obviously calling the procedure from an execute(). I'm okay with explicit commits, but since all of my data logic is in procedures (all operations are one step), autocommit is preferable. I'm using 0.9.1 .

Until recently, the application was only doing SELECTs, so I hadn't noticed.

There seem to be dozens of offered solutions online, but none of them work.

I'm testing using the following procedure:

delimiter //

CREATE PROCEDURE test_func()

BEGIN

    SELECT @@AUTOCOMMIT `autocommit`;

END//

delimiter ;

I'm doing my tests from the command-line, in a simple script.

- I've tried setting the autocommit to True at the engine level, but I get the "Commands out of sync" error:

engine = create_engine(DSN, echo=True).execution_options(autocommit=True)

- I've tried setting the autocommit at the session level, but the result indicates that autocommit is "0" (the option was ignored):

Code:

    Session = sessionmaker(bind=engine, autocommit=True)

    session = Session()

    query = 'CALL test_func()'

    r = session.execute(text(query, autocommit=True))

Debug output:

2014-03-27 14:45:36,956 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()

2014-03-27 14:45:36,956 INFO sqlalchemy.engine.base.Engine ()

2014-03-27 14:45:37,293 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'

2014-03-27 14:45:37,293 INFO sqlalchemy.engine.base.Engine ()

2014-03-27 14:45:37,350 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

2014-03-27 14:45:37,350 INFO sqlalchemy.engine.base.Engine ()

2014-03-27 14:45:37,416 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2014-03-27 14:45:37,417 INFO sqlalchemy.engine.base.Engine CALL test_func()

2014-03-27 14:45:37,417 INFO sqlalchemy.engine.base.Engine ()

- I've tried setting the autocommit on a text(), but it's ignored (with the same result as the above):

    r = session.execute(text(query, autocommit=True))

- I've tried using an execute() on the engine, but autoflush is either got ignored or I got the "out of sync" error (like the above). I can't remember.

- I've also tried to do the actual commits, both directly or using the context-manager, but I get the "out of sync" message every time.

This is driving me crazy. Can anyone give me a short, working example of calling a stored procedure with autocommit, as well as with an explicit commit?

Dustin

[Michael Bayer]

On Mar 27, 2014, at 3:01 PM, Dustin Oprea <myself...@gmail.com> wrote:

I'm having a problem where I'm doing writes to MySQL that aren't being committed. I'm obviously calling the procedure from an execute(). I'm okay with explicit commits, but since all of my data logic is in procedures (all operations are one step), autocommit is preferable. I'm using 0.9.1 .

Until recently, the application was only doing SELECTs, so I hadn't noticed.

There seem to be dozens of offered solutions online, but none of them work.

OK, you seem to have found the autocommit flag on text(), this is the correct approach in this case.

I can’t get your stored proc to run as creating a proc on my machine produces some “thread stack overrun” error I don’t have the time to figure out, but a simple test of just a SELECT with autocommit=True illustrates the COMMIT being called in the log:

from sqlalchemy import *

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)

r = e.execute(text('SELECT 1', autocommit=True))

output:

2014-03-27 15:08:26,951 INFO sqlalchemy.engine.base.Engine SELECT 1

2014-03-27 15:08:26,951 INFO sqlalchemy.engine.base.Engine ()

2014-03-27 15:08:26,951 INFO sqlalchemy.engine.base.Engine COMMIT

- I've tried setting the autocommit to True at the engine level, but I get the "Commands out of sync" error:

engine = create_engine(DSN, echo=True).execution_options(autocommit=True)

can’t reproduce - test script:

from sqlalchemy import *

e = create_engine("mysql://scott:tiger@localhost/test", echo=True).execution_options(autocommit=True)

r = e.execute(text('SELECT 1’))

output, includes the COMMIT as expected:

2014-03-27 15:11:14,324 INFO sqlalchemy.engine.base.OptionEngine SELECT 1

2014-03-27 15:11:14,324 INFO sqlalchemy.engine.base.OptionEngine ()

2014-03-27 15:11:14,324 INFO sqlalchemy.engine.base.OptionEngine COMMIT

- I've tried setting the autocommit at the session level, but the result indicates that autocommit is "0" (the option was ignored):

session “autocommit” is not the same thing here, it just means that the session won’t implicitly call begin().  it doesn’t imply commit for all statements.

- I've tried setting the autocommit on a text(), but it's ignored (with the same result as the above):

    r = session.execute(text(query, autocommit=True))

can’t reproduce, as above

- I've also tried to do the actual commits, both directly or using the context-manager, but I get the "out of sync" message every time.

can’t reproduce that either, obviously, there should be no “commands out of sync” error.  whatever is going on to cause that, is likely related to what the actual problem is.  In fact it seems like the attempts you’ve made to do the *correct* thing are where you’re getting that issue, meaning, your database or MySQL DBAPI is not able to run your procedure correctly.

I will note that I’m not able to run a MySQL stored procedure here with MySQL-python, but I get a different error.    If I do this:

e.execute("CALL test_func()")

or if I do this (e.g. use MySQL-python directly):

conn = e.connect()

curs = conn.connection.cursor()

curs.callproc("test_func")

I get:

2014-03-27 15:14:03,360 INFO sqlalchemy.engine.base.Engine ()

2014-03-27 15:14:03,361 INFO sqlalchemy.engine.base.Engine COMMIT

Traceback (most recent call last):

  File "test.py", line 17, in <module>

    curs.callproc("test_func")

  File "build/bdist.macosx-10.4-x86_64/egg/MySQLdb/cursors.py", line 272, in callproc

  File "build/bdist.macosx-10.4-x86_64/egg/MySQLdb/cursors.py", line 316, in _query

  File "build/bdist.macosx-10.4-x86_64/egg/MySQLdb/cursors.py", line 280, in _do_query

_mysql_exceptions.OperationalError: (1436, "Thread stack overrun:  11776 bytes used of a 131072 byte stack, and 128000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.")

In your case, you should use a simple MySQL-python script first, get the SP to run and commit, without any “out of sync” messages.   I think that is the actual problem you’re having.

[Dustin Oprea]

Thanks, Mike.

On Thursday, March 27, 2014 3:16:27 PM UTC-4, Michael Bayer wrote:

...

 

OK, you seem to have found the autocommit flag on text(), this is the correct approach in this case.

I can’t get your stored proc to run as creating a proc on my machine produces some “thread stack overrun” error I don’t have the time to figure out, but a simple test of just a SELECT with autocommit=True illustrates the COMMIT being called in the log:

...

 

can’t reproduce - test script:

from sqlalchemy import *

e = create_engine("mysql://scott:tiger@localhost/test", echo=True).execution_options(autocommit=True)

r = e.execute(text('SELECT 1’))

output, includes the COMMIT as expected:

2014-03-27 15:11:14,324 INFO sqlalchemy.engine.base.OptionEngine SELECT 1

2014-03-27 15:11:14,324 INFO sqlalchemy.engine.base.OptionEngine ()

2014-03-27 15:11:14,324 INFO sqlalchemy.engine.base.OptionEngine COMMIT

...

 

In your case, you should use a simple MySQL-python script first, get the SP to run and commit, without any “out of sync” messages.   I think that is the actual problem you’re having.

A "SELECT @@AUTOCOMMIT" had the same result. However, the same query using the standard library, along with a call to autocommit(), worked exactly as expected. I should've mentioned that I'm calling into Amazon [RDS].

Let:

query = 'SELECT @@AUTOCOMMIT'

dsn = '<DSN string>'

SQLAlchemy code:

def alchemy_test():

    e = create_engine(dsn, echo=True)

    Session = sessionmaker()

    Session.configure(bind=e)

    session = Session()

    r = session.execute(text(query, autocommit=True))

    print(list(r))

Result:

2014-03-27 17:47:16,215 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()

2014-03-27 17:47:16,215 INFO sqlalchemy.engine.base.Engine ()

2014-03-27 17:47:16,548 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'

2014-03-27 17:47:16,548 INFO sqlalchemy.engine.base.Engine ()

2014-03-27 17:47:16,604 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

2014-03-27 17:47:16,604 INFO sqlalchemy.engine.base.Engine ()

2014-03-27 17:47:16,670 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2014-03-27 17:47:16,670 INFO sqlalchemy.engine.base.Engine SELECT @@AUTOCOMMIT

2014-03-27 17:47:16,670 INFO sqlalchemy.engine.base.Engine ()

[(0L,)]

Direct example:

def direct_test():

    import MySQLdb

    conn = MySQLdb.connect(host='db.host.com', user='abc', passwd="def", db="ghi", port=3307)

    conn.autocommit(True)

    c = conn.cursor()

    c.execute(query)

    print(c.fetchone())

Result:

(1L,)

Any idea why SA is falling down on this?

Dustin

[Michael Bayer]

On Mar 27, 2014, at 5:51 PM, Dustin Oprea <myself...@gmail.com> wrote:

Thanks, Mike.

Any idea why SA is falling down on this?

this is not a SQLAlchemy issue, it is an issue with the DBAPI you’re using.  If you’re using MySQL-python, you need to work up a test script using only MySQL-python directly, and figure out what’s going on.  you may need to use another DBAPI like mysql-connector-python (which is the official DBAPI of MySQL now).

if you can show me a MySQL-python script that works, then SQLAlchemy should be working too because it doesn’t have any special behavior here.

[Michael Bayer]

oh, you had one at the bottom there, let me look, sorry.

[Michael Bayer]

On Mar 27, 2014, at 5:51 PM, Dustin Oprea <myself...@gmail.com> wrote:

Direct example:

def direct_test():

    import MySQLdb

    conn = MySQLdb.connect(host='db.host.com', user='abc', passwd="def", db="ghi", port=3307)

    conn.autocommit(True)

    c = conn.cursor()

    c.execute(query)

    print(c.fetchone())

Result:

(1L,)

One thing I don’t understand is, does your *actual* stored procedure need to call “SELECT @@AUTOCOMMIT”?  because that may be part of the problem.

Above, SQLAlchemy has no direct support for “conn.autocommit()”, which is not part of the DBAPI - you can enable this if you want using an event, but it would be better if commit() just worked as expected.  

I would try removing the “conn.autocommit()” part and just try calling conn.commit().     That would reveal whatever bugs are in play, and I’d look to see if a DBAPI like mysql-connector-python, which is now the official MySQL DBAPI, has the same problem.

if you really have to turn on this non-standard feature on, it needs to be across the board for that particular engine, and would be like this:

from sqlalchemy import event

@event.listens_for(engine, “connect”)

def conn(conn, rec):

    conn.autocommit(True)

[Michael Bayer]

On Mar 27, 2014, at 6:50 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:

>
>
> if you really have to turn on this non-standard feature on, it needs to be across the board for that particular engine, and would be like this:
>
> from sqlalchemy import event
>
> @event.listens_for(engine, “connect”)
> def conn(conn, rec):
> conn.autocommit(True)

correction, if you want it per connection, then like this:

conn = engine.connect()
conn.connection.autocommit(True)

conn.execute(<whatever>)

conn.connection.autocommit(False)