Is this issue still open? If so, can you open a ticket about it? Massimo
On Monday, 24 September 2012 14:38:14 UTC-5, Yarin wrote: > > > > db.py: > db.define_table('mytable',Field('myfield','string')) > > sproc: > DELIMITER // > CREATE PROCEDURE TestSproc() > BEGIN > SELECT COUNT(*) FROM mytable; > END // > DELIMITER; > > tests.py > """NOTE: Tests 1-3 will fail every time in wsgi/linux environments. > From the web2py command line, they will work once, until another operation > is called on the db, INCLUDING commit(). > > In web2py 1, you can run these once successfully, but subsequent sql > operations on the current connection return None results. > """ > > def test_1(): > ''' RESULT: ProgrammingError: (2014, "Commands out of sync; you cant run > this command now") ''' > > sql = '''CALL TestSproc();''' > results = db.executesql(sql, as_dict=True) > return str(results) > > def test_2(): > ''' RESULT: ProgrammingError: (2014, "Commands out of sync; you cant run > this command now") ''' > > sql = """CALL TestSproc();""" > results = db.executesql(sql, as_dict=True) > db.commit() > return str(results) > > def test_3(): > ''' RESULT: ProgrammingError: (2014, "Commands out of sync; you cant run > this command now") ''' > > sql = """CALL TestSproc();""" > db._adapter.execute(sql) > results = db._adapter.cursor.fetchall() > return str(results) > > def test_4(): > ''' RESULT: Works ''' > > import contrib.pymysql as pymysql > sql = """CALL TestSproc();""" > conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd= > 'root', db='test') > cur = conn.cursor(pymysql.cursors.DictCursor) > cur.execute(sql) > results = cur.fetchall() > cur.close() > conn.close() > return str(results) > > > > > > On Monday, September 24, 2012 1:10:14 PM UTC-4, Massimo Di Pierro wrote: >> >> can you show us some of your code. This may be a concurrency issues. I >> cannot thing anything we change that would affect the behavior. >> >> On Sunday, 23 September 2012 22:43:28 UTC-5, Yarin wrote: >>> >>> After upgrading to web2py 2.0, we can no longer use the DAL or mysql >>> adapters to make MySQL stored procedure calls. >>> >>> In web2py 1.x I had >>> reported<https://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJ>how >>> mysql stored procedures weren't working for us in certain >>> circumstances- however we were able to circumvent those issues by simply >>> creating a new DAL instance everytime we had to call a sproc. With the new >>> DAL, that's no longer an option- indeed, any call to a stored procedure >>> seems to fail in any attempt through the DAL or mysql adapter. >>> >>> - Fails with both pymysql and mysqldb drivers >>> - Fails when calling commit() immediately after >>> - We did side-by-side comparisons with web2py 1.x on same server, >>> pointing to same db, and confirmed this fails only in 2.0 >>> - You can make sproc calls using the DAL straight from the web2py >>> shell, but only if you don't call commit. >>> >>> >>> Stack trace: >>> >>> Traceback (most recent call last): >>> File "/opt/web-apps/web2py/gluon/main.py", line 580, in wsgibase >>> BaseAdapter.close_all_instances('rollback') >>> File "/opt/web-apps/web2py/gluon/dal.py", line 511, in close_all_instances >>> getattr(instance, action)() >>> File "/opt/web-apps/web2py/gluon/dal.py", line 1633, in rollback >>> return self.connection.rollback() >>> ProgrammingError: (2014, "Commands out of sync; you can't run this command >>> now") >>> >>> >>> >>> Right now the only option we've arrive at is to make sproc calls >>> directly through the pymysql driver API, one per connection. >>> >>> >>> --