On Mar 27, 2014, at 3:01 PM, Dustin Oprea <[email protected]> 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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
