(using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) Hi there, I had this ZSQL method (simplified for clarity) working on 2.1.4 select id, nickname from bboard where id<=50 order by id desc limit 30 Now, I've upgraded to 2.1.6 and I get the following error; Error, _mysql.OperationalError: (1064, "You have an error in your SQL syntax near 'LIMIT 1000' at line 4") -this is wierd because my ZSQL method says "limit 30" The traceback displayed is; Traceback (innermost last): File /usr/local/Zope/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /usr/local/Zope/lib/python/ZPublisher/Publish.py, line 179, in publish File /usr/local/Zope/lib/python/Zope/__init__.py, line 202, in zpublisher_exception_hook (Object: simple-test) File /usr/local/Zope/lib/python/ZPublisher/Publish.py, line 165, in publish File /usr/local/Zope/lib/python/ZPublisher/mapply.py, line 160, in mapply (Object: manage_test) File /usr/local/Zope/lib/python/ZPublisher/Publish.py, line 102, in call_object (Object: manage_test) File /usr/local/Zope/lib/python/Shared/DC/ZRDB/DA.py, line 331, in manage_test (Object: simple-test) File /usr/local/Zope/lib/python/Shared/DC/ZRDB/DA.py, line 312, in manage_test (Object: simple-test) File /usr/local/Zope/lib/python/Shared/DC/ZRDB/DA.py, line 430, in __call__ (Object: simple-test) File /usr/local/Zope/lib/python/Products/ZMySQLDA/db.py, line 221, in query OperationalError: (see above) Note: if I remove the limit constraint as in; select id, nickname from bboard where id<=50 order by id desc the ZSQL method works fine. I do need to limit the number of results returned. Does anyone know a workaround for this? umesh
Umesh Soni wrote:
(using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1)
Hi there,
I had this ZSQL method (simplified for clarity) working on 2.1.4
select id, nickname from bboard where id<=50 order by id desc limit 30
Remove either the limit 30 or the LIMIT 1000 Advanced property of the ZSQL method (advanced tab). To speed things up, the limit 1000 appended to the query to stop MySQL giving Zope lots of lines when it only wants 1000 anyway. Its not too clever though. There should maybe be a check to see if "limit x" exists in the query. Phill
Umesh Soni wrote:
(using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1)
Hi there,
I had this ZSQL method (simplified for clarity) working on 2.1.4
select id, nickname from bboard where id<=50 order by id desc limit 30
Remove either the limit 30 or the LIMIT 1000 Advanced property of the ZSQL method (advanced tab). To speed things up, the limit 1000 appended to the query to stop MySQL giving Zope lots of lines when it only wants 1000 anyway. Its not too clever though. There should maybe be a check to see if "limit x" exists in the query.
Phill
Thanks Phill, that did the trick --strange thing though the 'Maximum number of rows retrieved attribute' was set to 100 not 1000 as the error reported --I changed it to 30. Anyway it's working now --cheers. Umesh Soni.
Umesh Soni wrote:
Umesh Soni wrote:
(using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1)
Hi there,
I had this ZSQL method (simplified for clarity) working on 2.1.4
select id, nickname from bboard where id<=50 order by id desc limit 30
Remove either the limit 30 or the LIMIT 1000 Advanced property of the ZSQL method (advanced tab). To speed things up, the limit 1000 appended to the query to stop MySQL giving Zope lots of lines when it only wants 1000 anyway. Its not too clever though. There should maybe be a check to see if "limit x" exists in the query.
Phill
Thanks Phill, that did the trick --strange thing though the 'Maximum number of rows retrieved attribute' was set to 100 not 1000 as the error reported --I changed it to 30.
I`m interested in your thoughts about ZSQLMethod's advanced property 'Maximum number of rows retrieved'. It`s default value is 1000. Some DA's like PyGreSQLDA takes no notice on this value while PoPyDA pays attention of this property. Assumed there is a table foo on my DB with actually 2000 records some DA's return only 1000 on table scans like 'select * from foo' and 10 records on 'select * from foo limit 10 offset 1100' (if offset is supported by the RDBMS). How can I know how many records will match at max to my select statement? ZSQLMethod's "advanced" property 'Maximum number of rows retrieved' makes no sense to me. As I know so far there is no way to disable this property or to set a value like 'maximum' -- _________________________________________________________ Andreas Heckel andreas@aHIT.de
-----Original Message----- From: root@www.ahit.de [mailto:root@www.ahit.de]On Behalf Of Andreas Sent: Saturday, June 10, 2000 1:59 AM To: Umesh Soni Cc: Phill Hugo; zope@zope.org Subject: Re: [Zope] ZSQL method broken on 2.1.6
(using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1)
As I know so far there is no way to disable this property or to set a value like 'maximum'
This version of ZMySQLDA (and possibly others) doesn't put the LIMIT statement in at all if you set the maximum number of rows to 0 (zero). Like someone else said, it's a "safety net" for queries that may accidently return way too many records. _______________________ Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
participants (4)
-
Andreas -
Phill Hugo -
Ron Bickers -
Umesh Soni