Done: http://code.google.com/p/web2py/issues/detail?id=1037
On Thursday, September 27, 2012 4:33:37 PM UTC-4, Massimo Di Pierro wrote:
>
> 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.
>>>>
>>>>
>>>>
--
