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. >> >> >> --
