Two mysql / timestamp related fixes
Hi. Just had som troubles when I added timestamp columns to a table. Commnets? my setup: Mysql: 4.1.20-log Zope Version: (Zope 2.7.5-1.fc3, python 2.3.4, linux2) 1 ) First I got a ValueError 'query' when using a very simple zsql method. (consisting of only "<dtml-var sql >") (I am not totally sure this is related to the timestamp column) I appled the fix to customDefaultReport.dtml mentioned in http://mail.zope.org/pipermail/zope-db/2005-June/004219.html < <a href="&dtml-URL;?query_start=&dtml-previous-sequence-start-number;&query=<dtml-var query url_quote>"> ---
<a
href="&dtml-URL;&dtml-sequence-query;query_start=&dtml-previous-sequence-start-number;"> 25d24 < <a href="&dtml-URL;?query_start=&dtml-next-sequence-start-number;&query=<dtml-var query url_quote>"> 26a26
<a
href="&dtml-URL;&dtml-sequence-query;query_start=&dtml-next-sequence-start-number;"> This does not seem to be exactly the same fix as described in: http://www.zope.org/Collectors/Zope/1812 Should I have this problem in Zope 2.7.5-1.fc3? 2) ValueError: invalid literal for int(): 0- When trying to do a "select *" this is definitely connected to the timestamp column, because this change fixed it: diff /usr/lib/zope/lib/python/Products/ZMySQLDA/db.py /usr/lib/zope/lib/python/Products/ZMySQLDA/db.py~ 173d172 < conv[FIELD_TYPE.TIMESTAMP] = DateTime_or_None I have found no mention of this anywhere? Have anyone else had this problem Comments? Regards Gaute Amundsen
Gaute Amundsen schrieb:
Hi.
Just had som troubles when I added timestamp columns to a table. Commnets?
my setup: Mysql: 4.1.20-log Zope Version: (Zope 2.7.5-1.fc3, python 2.3.4, linux2)
1 ) First I got a ValueError 'query' when using a very simple zsql method. (consisting of only "<dtml-var sql >")
Thats a very bad idea. Better make zsql methods for every query you want to issue instead of rebuilding your own (obviously not working) templating for SQL. Quoting is done automatically when you use <dtml-sqlvar ...>
(I am not totally sure this is related to the timestamp column)
I appled the fix to customDefaultReport.dtml mentioned in http://mail.zope.org/pipermail/zope-db/2005-June/004219.html
< <a href="&dtml-URL;?query_start=&dtml-previous-sequence-start-number;&query=<dtml-var query url_quote>"> ---
<a
href="&dtml-URL;&dtml-sequence-query;query_start=&dtml-previous-sequence-start-number;"> 25d24 < <a href="&dtml-URL;?query_start=&dtml-next-sequence-start-number;&query=<dtml-var query url_quote>"> 26a26
<a
href="&dtml-URL;&dtml-sequence-query;query_start=&dtml-next-sequence-start-number;">
This does not seem to be exactly the same fix as described in: http://www.zope.org/Collectors/Zope/1812
Should I have this problem in Zope 2.7.5-1.fc3?
2)
ValueError: invalid literal for int(): 0- When trying to do a "select *"
this is definitely connected to the timestamp column, because this change fixed it:
diff /usr/lib/zope/lib/python/Products/ZMySQLDA/db.py /usr/lib/zope/lib/python/Products/ZMySQLDA/db.py~ 173d172 < conv[FIELD_TYPE.TIMESTAMP] = DateTime_or_None
I have found no mention of this anywhere? Have anyone else had this problem Comments?
Regards
Gaute Amundsen
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
On Thursday 08 March 2007 17:27, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
Hi. <snip> 1 ) First I got a ValueError 'query' when using a very simple zsql method. (consisting of only "<dtml-var sql >")
Thats a very bad idea. Better make zsql methods for every query
I know the orthodoxy :-/ I'd rather have a proper DB library, (or model layer, if you will) and build my SQL in python. To build anything complex or generalized in dtml is a PAIN. And a sea of too specific templates is even worse.
instead of rebuilding your own (obviously not working) templating for SQL.
I's not a template, it's a wrapper to get around the whole mess. A hack in fact ;) If a template this simple "<dtml-var sql >" is "obviously not working" then it is ZSQLMethods that have a problem, not me. (it's working fine now.)
Quoting is done automatically when you use <dtml-sqlvar ...>
I can use sql_quote when I need to, thankyou. The only thing that I worry about is transaction support. "insert foo; select last_inserted" works when built inside the template with dtml, but not with the exact same statement passed to this "wrapper". Probably another bug, but I have not taken time to dig properly into it yet. Gaute
Gaute Amundsen schrieb:
On Thursday 08 March 2007 17:27, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
Hi. <snip> 1 ) First I got a ValueError 'query' when using a very simple zsql method. (consisting of only "<dtml-var sql >") Thats a very bad idea. Better make zsql methods for every query
I know the orthodoxy :-/ I'd rather have a proper DB library, (or model layer, if you will) and build my SQL in python.
So you want to use SQLAlchemy? Feel free to write a product as wrapper :-)
To build anything complex or generalized in dtml is a PAIN.
Is it? Can you come up with examples?
And a sea of too specific templates is even worse.
Well from some level of complexity you could also use database functions. But if its so complex, why using MySQL?
instead of rebuilding your own (obviously not working) templating for SQL.
I's not a template, it's a wrapper to get around the whole mess. A hack in fact ;)
If a template this simple "<dtml-var sql >" is "obviously not working" then it is ZSQLMethods that have a problem, not me.
No, the content of your variable sql is wrong :-) <dtml-var sql> would indeed work if it contains valid sql code.
(it's working fine now.)
Quoting is done automatically when you use <dtml-sqlvar ...>
I can use sql_quote when I need to, thankyou.
this does not need to work identical - but current adaptors indeed do their handmade quoting.
The only thing that I worry about is transaction support. "insert foo; select last_inserted" works when built inside the template with dtml, but not with the exact same statement passed to this "wrapper". Probably another bug, but I have not taken time to dig properly into it yet.
Yes, MySQL doesnt want ; as sql-delimiter. It rather wants \0 and sql methods hack around it for you if you put the template there. Transactions are maintained thrughout your session (read: request) so there is no need to squeeze it all into one ZSQL call. Every subsequent ZSQL call inside your request will be in the same database transaction. Regards Tino
On Friday 09 March 2007 09:40, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
On Thursday 08 March 2007 17:27, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
Hi.
<snip>
1 ) First I got a ValueError 'query' when using a very simple zsql method. (consisting of only "<dtml-var sql >")
Thats a very bad idea. Better make zsql methods for every query
I know the orthodoxy :-/ I'd rather have a proper DB library, (or model layer, if you will) and build my SQL in python.
So you want to use SQLAlchemy? Feel free to write a product as wrapper :-)
Hm.. have never quite gotten around to writing my first product. Looks like a very nice candidate, but no promises :)
To build anything complex or generalized in dtml is a PAIN.
Is it? Can you come up with examples?
tabell:string felter:list INSERT INTO <dtml-var tabell sql_quote> SET <dtml-in expr="felter.keys()"> <dtml-let key=sequence-item value="felter[key]"> <dtml-unless sequence-start>,</dtml-unless> <dtml-var key sql_quote> = <dtml-if expr="same_type(value, 1)"> <dtml-sqlvar value type="int"> <dtml-elif expr="same_type(value, 1.0)"> <dtml-sqlvar value type="float"> <dtml-else> <dtml-sqlvar value type="string"> </dtml-if> </dtml-let> </dtml-in> <dtml-var sql_delimiter> SELECT LAST_INSERT_ID() and that is not _really_ complex is it? There's the whole sqlgroup thing. dtml is after all depreciated, so I can see no reason to learn it as torougly as ZSQLMethods obviously requires when I can use python instead.
And a sea of too specific templates is even worse.
Well from some level of complexity you could also use database functions. But if its so complex, why using MySQL?
Don't see quite what you mean here, but it would be just the same problems with PGsql.
instead of rebuilding your own (obviously not working) templating for SQL.
I's not a template, it's a wrapper to get around the whole mess. A hack in fact ;)
If a template this simple "<dtml-var sql >" is "obviously not working" then it is ZSQLMethods that have a problem, not me.
No, the content of your variable sql is wrong :-) <dtml-var sql> would indeed work if it contains valid sql code.
It did not. That was the whole point of this mail. The method was "<dtml-var sql >" and the sql was "select * from foo" care to spot the invalid code in that?
(it's working fine now.)
Quoting is done automatically when you use <dtml-sqlvar ...>
I can use sql_quote when I need to, thankyou.
this does not need to work identical - but current adaptors indeed do their handmade quoting.
The only thing that I worry about is transaction support. "insert foo; select last_inserted" works when built inside the template with dtml, but not with the exact same statement passed to this "wrapper". Probably another bug, but I have not taken time to dig properly into it yet.
Yes, MySQL doesnt want ; as sql-delimiter. It rather wants \0 and sql methods hack around it for you if you put the template there.
Hm.. interesting, but I looked into it a bit, and I can't find where this happens. I see that ZRDB/DA.py does: argdata['sql_delimiter']='\0' Tried a "query = query.replace(';','\0')" at line 444, but to no effect :) Suggestions? I guess this could be a problem with LAST_INSERT_ID, forcing me to use ZSQLMethods, or dome ugly workaround for inserts, where I need the id afterwards.
Transactions are maintained thrughout your session (read: request) so there is no need to squeeze it all into one ZSQL call. Every subsequent ZSQL call inside your request will be in the same database transaction.
No plans to squeze it all into one huge SQL statement. Now THAT would be ugly! but python errors does not trigger rollback it seems. I guess only sql errors would do that? regards Gaute
Gaute Amundsen schrieb:
On Friday 09 March 2007 09:40, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
On Thursday 08 March 2007 17:27, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
Hi. <snip>
1 ) First I got a ValueError 'query' when using a very simple zsql method. (consisting of only "<dtml-var sql >") Thats a very bad idea. Better make zsql methods for every query I know the orthodoxy :-/ I'd rather have a proper DB library, (or model layer, if you will) and build my SQL in python. So you want to use SQLAlchemy? Feel free to write a product as wrapper :-)
Hm.. have never quite gotten around to writing my first product. Looks like a very nice candidate, but no promises :)
To build anything complex or generalized in dtml is a PAIN. Is it? Can you come up with examples?
tabell:string felter:list INSERT INTO <dtml-var tabell sql_quote> SET <dtml-in expr="felter.keys()"> <dtml-let key=sequence-item value="felter[key]"> <dtml-unless sequence-start>,</dtml-unless> <dtml-var key sql_quote> = <dtml-if expr="same_type(value, 1)"> <dtml-sqlvar value type="int"> <dtml-elif expr="same_type(value, 1.0)"> <dtml-sqlvar value type="float"> <dtml-else> <dtml-sqlvar value type="string"> </dtml-if> </dtml-let> </dtml-in> <dtml-var sql_delimiter> SELECT LAST_INSERT_ID()
and that is not _really_ complex is it?
Well, it looks a bit silly - I mean you make a hand-written ORM? This would be the time to either write a real ORM as product or try some of the available: ZPatterns, Archetypes, ... Usual database interacting zope solutions have only a handful insert places where you need bulk inserts. And even then you can just call one ZSQL method (per table) in a loop to insert your values from a list. More, sql_quote does not really work for tablenames or column names - it may work by accident for MySQL though, but not for standards compliant databases.
There's the whole sqlgroup thing.
Well to be honest I never used it ;)
dtml is after all depreciated, so I can see no reason to learn it as torougly as ZSQLMethods obviously requires when I can use python instead.
You always can but what do you gain? How does your python script code look like for the above problem?
And a sea of too specific templates is even worse. Well from some level of complexity you could also use database functions. But if its so complex, why using MySQL?
Don't see quite what you mean here, but it would be just the same problems with PGsql.
Not direct related but if you have complex queries, e.g. join over many tables, partial indexes, subqueries, ... it may be a bit hard to get reliable performance with MySQL. I think it was related to different views at the word "complex queries"
instead of rebuilding your own (obviously not working) templating for SQL. I's not a template, it's a wrapper to get around the whole mess. A hack in fact ;)
If a template this simple "<dtml-var sql >" is "obviously not working" then it is ZSQLMethods that have a problem, not me. No, the content of your variable sql is wrong :-) <dtml-var sql> would indeed work if it contains valid sql code.
It did not. That was the whole point of this mail.
The method was "<dtml-var sql >" and the sql was "select * from foo" care to spot the invalid code in that?
wait... your variable really is named "sql"? Maybe that is the problem. I did not verify this but it could be. DTML is sometimes weird ;)
(it's working fine now.)
Quoting is done automatically when you use <dtml-sqlvar ...> I can use sql_quote when I need to, thankyou. this does not need to work identical - but current adaptors indeed do their handmade quoting.
The only thing that I worry about is transaction support. "insert foo; select last_inserted" works when built inside the template with dtml, but not with the exact same statement passed to this "wrapper". Probably another bug, but I have not taken time to dig properly into it yet. Yes, MySQL doesnt want ; as sql-delimiter. It rather wants \0 and sql methods hack around it for you if you put the template there.
Hm.. interesting, but I looked into it a bit, and I can't find where this happens. I see that ZRDB/DA.py does: argdata['sql_delimiter']='\0' Tried a "query = query.replace(';','\0')" at line 444, but to no effect :)
Interesting. I must admit the whole ZSQL Code is very old and ugly and I even removed some parts in my private installations ;)
Suggestions? I guess this could be a problem with LAST_INSERT_ID, forcing me to use ZSQLMethods, or dome ugly workaround for inserts, where I need the id afterwards.
Last_insert_id is really part of the uglyness of mysql. But you would work around with distinct ZSQL Methods. See these methods as interface and really methods of your database to interact with your data model.
Transactions are maintained thrughout your session (read: request) so there is no need to squeeze it all into one ZSQL call. Every subsequent ZSQL call inside your request will be in the same database transaction.
No plans to squeze it all into one huge SQL statement. Now THAT would be ugly! but python errors does not trigger rollback it seems. I guess only sql errors would do that?
No, every transaction is rolled back if it bubbles up to zpublisher. (ZODB, external databases, ...) If it does not, then there is a bug in the DB adaptor or the database. This would be risky especially in cases where ZODB conflict handling jumps in and requests are retried. Regards Tino
On Friday 09 March 2007 16:22, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
On Friday 09 March 2007 09:40, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
On Thursday 08 March 2007 17:27, Tino Wildenhain wrote:
Gaute Amundsen schrieb:
Hi.
<snip>
1 ) First I got a ValueError 'query' when using a very simple zsql method. (consisting of only "<dtml-var sql >")
Thats a very bad idea. Better make zsql methods for every query
I know the orthodoxy :-/ I'd rather have a proper DB library, (or model layer, if you will) and build my SQL in python.
So you want to use SQLAlchemy? Feel free to write a product as wrapper :-)
Hm.. have never quite gotten around to writing my first product. Looks like a very nice candidate, but no promises :)
To build anything complex or generalized in dtml is a PAIN.
Is it? Can you come up with examples?
tabell:string felter:list INSERT INTO <dtml-var tabell sql_quote> SET <dtml-in expr="felter.keys()"> <dtml-let key=sequence-item value="felter[key]"> <dtml-unless sequence-start>,</dtml-unless> <dtml-var key sql_quote> = <dtml-if expr="same_type(value, 1)"> <dtml-sqlvar value type="int"> <dtml-elif expr="same_type(value, 1.0)"> <dtml-sqlvar value type="float"> <dtml-else> <dtml-sqlvar value type="string"> </dtml-if> </dtml-let> </dtml-in> <dtml-var sql_delimiter> SELECT LAST_INSERT_ID()
and that is not _really_ complex is it?
Well, it looks a bit silly - I mean you make a hand-written ORM? This would be the time to either write a real ORM as product or try some of the available: ZPatterns, Archetypes, ...
Hm.. not intentionally :-/ Have heard about them of course, but not yet had a project that could defend trying them out properly. I whish there where some way in the zope commulity to get a feeling for what people are actually using, and what has some momentum... but that is another thread, I guess. btw. I found this rather good. http://blogs.nuxeo.com/sections/blogs/florent_guillaume/2005_08_11_object_re... But no clear conclusion there either.
Usual database interacting zope solutions have only a handful insert places where you need bulk inserts. And even then you can just call one ZSQL method (per table) in a loop to insert your values from a list.
More, sql_quote does not really work for tablenames or column names - it may work by accident for MySQL though, but not for standards compliant databases.
There's the whole sqlgroup thing.
Well to be honest I never used it ;)
dtml is after all depreciated, so I can see no reason to learn it as torougly as ZSQLMethods obviously requires when I can use python instead.
You always can but what do you gain? How does your python script code look like for the above problem?
Not found a way to work around the sql_delimiter problem yet, so it's not really done.. but I think you will agree that its is more readable anyway. def insert(tabell,felter): sql = "INSERT INTO %s SET " % ( sql_quote(tabell) ) cols = [] for key, val in felter.items(): if same_type(val, ' '): val = "'%s'" % val cols.append("%s = %s" % (key, val)) sql += ", ".join(cols) sql += '; SELECT LAST_INSERT_ID();' return wrapper( sql=sql )
And a sea of too specific templates is even worse.
Well from some level of complexity you could also use database functions. But if its so complex, why using MySQL?
Don't see quite what you mean here, but it would be just the same problems with PGsql.
Not direct related but if you have complex queries, e.g. join over many tables, partial indexes, subqueries, ... it may be a bit hard to get reliable performance with MySQL. I think it was related to different views at the word "complex queries"
instead of rebuilding your own (obviously not working) templating for SQL.
I's not a template, it's a wrapper to get around the whole mess. A hack in fact ;)
If a template this simple "<dtml-var sql >" is "obviously not working" then it is ZSQLMethods that have a problem, not me.
No, the content of your variable sql is wrong :-) <dtml-var sql> would indeed work if it contains valid sql code.
It did not. That was the whole point of this mail.
The method was "<dtml-var sql >" and the sql was "select * from foo" care to spot the invalid code in that?
wait... your variable really is named "sql"? Maybe that is the problem. I did not verify this but it could be. DTML is sometimes weird ;)
So what you are saying is that you have a mysql table with a column of type timestamp, and experienced no problems?
(it's working fine now.)
Quoting is done automatically when you use <dtml-sqlvar ...>
I can use sql_quote when I need to, thankyou.
this does not need to work identical - but current adaptors indeed do their handmade quoting.
The only thing that I worry about is transaction support. "insert foo; select last_inserted" works when built inside the template with dtml, but not with the exact same statement passed to this "wrapper". Probably another bug, but I have not taken time to dig properly into it yet.
Yes, MySQL doesnt want ; as sql-delimiter. It rather wants \0 and sql methods hack around it for you if you put the template there.
Hm.. interesting, but I looked into it a bit, and I can't find where this happens. I see that ZRDB/DA.py does: argdata['sql_delimiter']='\0' Tried a "query = query.replace(';','\0')" at line 444, but to no effect :)
Interesting. I must admit the whole ZSQL Code is very old and ugly and I even removed some parts in my private installations ;)
Suggestions? I guess this could be a problem with LAST_INSERT_ID, forcing me to use ZSQLMethods, or dome ugly workaround for inserts, where I need the id afterwards.
Last_insert_id is really part of the uglyness of mysql. But you would work around with distinct ZSQL Methods. See these methods as interface and really methods of your database to interact with your data model.
If we dont get around to setting up APE or something, and getting it all into SVN soon, we may be forced to anway. Hopeless to have several people edit the same script through webdav. Today it feels like zope tries to force me to drink the special zope coolaid in any way it can, but does not stop me from sawing my own branch of in a hundred different ways :-(
Transactions are maintained thrughout your session (read: request) so there is no need to squeeze it all into one ZSQL call. Every subsequent ZSQL call inside your request will be in the same database transaction.
No plans to squeze it all into one huge SQL statement. Now THAT would be ugly! but python errors does not trigger rollback it seems. I guess only sql errors would do that?
No, every transaction is rolled back if it bubbles up to zpublisher. (ZODB, external databases, ...) If it does not, then there is a bug in the DB adaptor or the database. This would be risky especially in cases where ZODB conflict handling jumps in and requests are retried.
Hm.. more serious that I tought then.. I will have to investigate. Thanks for the heads-up. Gaute
On Thu, Mar 08, 2007 at 02:07:56PM +0100, Gaute Amundsen wrote:
this is definitely connected to the timestamp column, because this change fixed it:
diff /usr/lib/zope/lib/python/Products/ZMySQLDA/db.py /usr/lib/zope/lib/python/Products/ZMySQLDA/db.py~ 173d172 < conv[FIELD_TYPE.TIMESTAMP] = DateTime_or_None
I have found no mention of this anywhere? Have anyone else had this problem
Yes, I've observed the same thing, and worked around it with a similar patch. -- Paul Winkler http://www.slinkp.com
On Thursday 08 March 2007 17:53, Paul Winkler wrote:
On Thu, Mar 08, 2007 at 02:07:56PM +0100, Gaute Amundsen wrote:
this is definitely connected to the timestamp column, because this change fixed it:
diff /usr/lib/zope/lib/python/Products/ZMySQLDA/db.py /usr/lib/zope/lib/python/Products/ZMySQLDA/db.py~ 173d172 < conv[FIELD_TYPE.TIMESTAMP] = DateTime_or_None
I have found no mention of this anywhere? Have anyone else had this problem
Yes, I've observed the same thing, and worked around it with a similar patch.
Good to know. Safety in numbers :) Not very surprising, come to think of it, when:
Latest Release: 2.0.8 Last Updated: 2001-09-06
Not much use submitting a bugreport either then, I guess? Gaute
participants (3)
-
Gaute Amundsen -
Paul Winkler -
Tino Wildenhain