Hi!
Just want to ask how long this still takes to fix? We like to use stored
procedures for MySQL queries. After an executesql( "CALL... everything
gives this error - even commit() and rollback(). This only happens if the
procedure wants to give back a result. I have tried to fetch it manually
through the cursor but no luck.
Could you point me to a solution or workaround other than write all queries
by hand? Maybe it could be implemented as an execute_multiple or
call_procedure function aside from the normal workings to not mess things
up in the current DAL?
Thanks,
rawbits
2013. január 25., péntek 19:44:06 UTC+1 időpontban Massimo Di Pierro a
következőt írta:
>
> True. For now you need to separate calls to db.executesql if you have
> multiple queries.
>
> On Friday, 25 January 2013 11:54:18 UTC-6, AngeloC wrote:
>>
>> Hi Massimo,
>>
>> I think this is a real problem.
>>
>> In a web2py shell you can do:
>>
>> rows = db.executesql("select * from charts")
>>
>> but you cannot do:
>>
>> rows = db.executesql("select * from charts; select * from charts;")
>>
>> it raises the error above.
>>
>> I think the problem is related to multiple resultsets. That example query
>> return 2 resultset, as well a stored procedure could return multiple
>> resultset. I think web2py mess something when a multiquery or a stored
>> procudere returns more than one resultset.
>>
>>
>> Mysql user guide says here (
>> http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html):
>>
>> If you get Commands out of sync; you can't run this command now in your
>> client code, you are calling client functions in the wrong order.
>>
>> This can happen, for example, if you are using mysql_use_result()
>> <http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html> and try
>> to execute a new query before you have called mysql_free_result()
>> <http://dev.mysql.com/doc/refman/5.0/en/mysql-free-result.html>. It can
>> also happen if you try to execute two queries that return data without
>> calling mysql_use_result()
>> <http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html> or
>> mysql_store_result()
>> <http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html> in
>> between.
>>
>>
>>
>> 2012/9/28 Yarin <[email protected]>
>>
>>> 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.
>>>>>>>
>>>>>>>
>>>>>>> --
>>>
>>>
>>>
>>>
>>
>>
>>
>> --
>> Profile: http://it.linkedin.com/in/compagnucciangelo
>>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
