Re: [web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
Solution found:
db._adapter.cursor.callproc(..)
res = db._adapter.cursor.nextset()
while res != None:
print result
res = db._adapter.cursor.nextset()
In a nutshell you have data waiting to be fetched and DAL skrews it up..
2015. november 21., szombat 18:32:14 UTC+1 időpontban Dániel Finta a
következőt írta:
>
> 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()
>>> and try
>>> to execute a new query before you have called mysql_free_result()
>>> . It can
>>> also happen if you try to execute two queries that return data without
>>> calling mysql_use_result()
>>> or
>>> mysql_store_result()
>>> in
>>> between.
>>>
>>>
>>>
>>> 2012/9/28 Yarin
>>>
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,
Re: [web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
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()
>> and try
>> to execute a new query before you have called mysql_free_result()
>> . It can
>> also happen if you try to execute two queries that return data without
>> calling mysql_use_result()
>> or
>> mysql_store_result()
>> in
>> between.
>>
>>
>>
>> 2012/9/28 Yarin
>>
>>> 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
Re: [web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
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 ykess...@gmail.com
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',Fiel**d('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.Di**ctCursor)
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
reportedhttps://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJhow
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
--
Re: [web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
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 ykes...@gmail.com javascript:
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',Fiel**d('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.Di**ctCursor)
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
reportedhttps://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJhow
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
[web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
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
reportedhttps://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJhow
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.
--
[web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
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
reportedhttps://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJhow
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.
--
[web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
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
reportedhttps://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJhow
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.
--
[web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
me neither (regarding web2py changes), however, I found this
*Compatibility note:* It appears that the mere act of executing the CALL
statement produces an empty result set, which appears after any result sets
which might be generated by the stored procedure. Thus, you will always
need to use nextset() to advance result sets.
on http://mysql-python.sourceforge.net/MySQLdb.html
On Monday, September 24, 2012 7:10:14 PM UTC+2, 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
reportedhttps://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJhow
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.
--
[web2py] Re: web2py 2.0 DAL no longer usable for mysql stored procedures
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
reportedhttps://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJhow
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.
--
