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