I've got a number of large queries that fail in 1.1.10 because of an extra space (" ") on Fedora Core 2 with mysql.com binaries. The same query works correctly in 1.1.8 on Windows using the 2.3.exe from sourceforge.
The error given when running cursor.execute(query_string, params) is ValueError: invalid literal for long(): Yes
The relevant portion of each query is
WHERE 1 AND (condition1, condition2, etc) --- note the extra space after the digit 1
If I remove the extra space, the query works correctly, in 0.9.2 against MySQL 3.23.x as well as 1.1.8 and 1.1.10. I have not been able to repro this in a useful test case yet :(
Here's one of the full queries...
query = 'SELECT C_Cu1.ID, C_Cu1.VIP, C_Cu1.alert, C_Cu1.anniversary, C_Cu1.birthday, C_Cu1.e_mail, C_Cu1.gender, C_Cu1.last_modified, C_Cu1.name_business, C_Cu1.name_first, C_Cu1.name_last, C_Cu1.name_middle, C_Cu1.notes, C_Cu1.opt_out_mailing, C_Cu1.purged, C_Cu1.tax_id, C_Cu1.taxable, C_Cu1.wishlist_contact, Z_Ad1.ID, Z_Ad1.address1, Z_Ad1.address2, Z_Ad1.city, Z_Ad1.zip, Z_St1.ID, Z_St1.abbrev, Z_St1.name, Z_Co1.ID, Z_Co1.abbrev, Z_Co1.code_size, Z_Co1.code_specs, Z_Co1.mailing_name, Z_Co1.name, Z_Co1.priority, Z_Ph1.ID, Z_Ph1.category, Z_Ph1.number, Z_Ph2.ID, Z_Ph2.category, Z_Ph2.number, Z_Ph3.ID, Z_Ph3.category, Z_Ph3.number, Z_Ph4.ID, Z_Ph4.category, Z_Ph4.number, Z_Ph5.ID, Z_Ph5.category, Z_Ph5.number FROM C_Customer C_Cu1 LEFT JOIN Z_Address Z_Ad1 ON Z_Ad1.ID =C_Cu1.address_ID LEFT JOIN Z_State Z_St1 ON Z_St1.ID = Z_Ad1.state_ID LEFT JOIN Z_Country Z_Co1 ON Z_Co1.ID = Z_St1.country_ID LEFT JOIN Z_Phone Z_Ph1 ON Z_Ph1.ID = C_Cu1.phone1_ID LEFT JOIN Z_Phone Z_Ph2 ON Z_Ph2.ID = C_Cu1.phone2_ID LEFT JOIN Z_Phone Z_Ph3 ON Z_Ph3.ID = C_Cu1.phone3_ID LEFT JOIN Z_Phone Z_Ph4 ON Z_Ph4.ID = C_Cu1.phone4_ID LEFT JOIN Z_Phone Z_Ph5 ON Z_Ph5.ID = C_Cu1.phone5_ID WHERE 1 AND (C_Cu1.purged="No" AND C_Cu1.name_last LIKE %s) ORDER BY C_Cu1.name_last, C_Cu1.name_first'
params = ['karki%']
Anyone using 1.1.10 seen anything similar?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It's not clear what version of MySQL you are using, or if you are using 3.23 in all cases.
I suspect it's some kind of a parser issue on the MySQL side, and all parsing happens in the server.
ValueError:invalidliteralforlong():Yes
This indicates that MySQL says the column is some sort of INTEGER column, but a string actually gets returned; the client interface actually returns all results as strings, and MySQLdb tries to convert them to the correct Python types, based on the indicated column type.
But the most obvious question is: Why do you have "1 AND" in your where clause? 1 AND x is always true if x is true and false if x is false. I find it particularly suspect that in your WHERE you have a condition C_Cu1.purged="No", which implies to me that sometimes C_Cu1.purged sometimes = "Yes", which happens to be the exact value being complained about.
I think it's basically impossible for this to be a MySQLdb problem: It doesn't parse your query, and it uses the column type the MySQL C API tells it to use.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
doh, sorry about that. The problem occured after an upgrade to 4.1.8a-Debian_6-log on the server, and 4.1.9 rpms on Fedora Core 2 on the clients. I haven't tested 1.1.10 on Linux against the 3.x series and don't have a good way to do so at this time.
***This indicates that MySQL says the column is some sort of INTEGER column, but a string actually gets returned; the client interface actually returns all results as strings, and MySQLdb tries to convert them to the correct Python types, based on the indicated column type.
Right, which is why I posted here... It looked like MySQLdb was incorrectly indentifying an ENUM as an INT or MySQL is passing back a string when the spec dictates an INT.
***But the most obvious question is: Why do you have "1 AND" in your where clause? 1 AND x is always true if x is true and false if x is false.
Heh, it's code generated from a data model by a code generator. The code generator is fairly new and that was the easiest (read, quickest) way to handle a generic search query where the user might not specify "any" criteria. It looks weird, and the extra space can easily be fixed in the generator, but it was very strange to see an error on it. For now, I've just used sed on the generated code to strip the extra space..
***I find it particularly suspect that in your WHERE you have a condition C_Cu1.purged="No", which implies to me that sometimes C_Cu1.purged sometimes = "Yes", which happens to be the exact value being complained about.
I agree. I'll see if I can trigger something similar in a smaller query on a nicer set of tables.
***I think it's basically impossible for this to be a MySQLdb problem: It doesn't parse your query, and it uses the column type the MySQL C API tells it to use.
Hmm, so it might be a problem in the C API?
Again, note that the error occurs against 1.1.10 on Linux, but not against 1.1.8 on Windows. I'll do some more testing on my side now that it is clearer to me that it might be only enum cols causing problems.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Well, my apologies... I can't repro it on the suspect machines today even with the original code. Perhaps I had a library cached on Monday that was causing me problems. :( I just saw a similar error on a Windows machine today that hadn't been upgraded from 0.9.2 yet... Silly me.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've got a number of large queries that fail in 1.1.10 because of an extra space (" ") on Fedora Core 2 with mysql.com binaries. The same query works correctly in 1.1.8 on Windows using the 2.3.exe from sourceforge.
The error given when running cursor.execute(query_string, params) is ValueError: invalid literal for long(): Yes
The relevant portion of each query is
WHERE 1 AND (condition1, condition2, etc) --- note the extra space after the digit 1
If I remove the extra space, the query works correctly, in 0.9.2 against MySQL 3.23.x as well as 1.1.8 and 1.1.10. I have not been able to repro this in a useful test case yet :(
Here's one of the full queries...
query = 'SELECT C_Cu1.ID, C_Cu1.VIP, C_Cu1.alert, C_Cu1.anniversary, C_Cu1.birthday, C_Cu1.e_mail, C_Cu1.gender, C_Cu1.last_modified, C_Cu1.name_business, C_Cu1.name_first, C_Cu1.name_last, C_Cu1.name_middle, C_Cu1.notes, C_Cu1.opt_out_mailing, C_Cu1.purged, C_Cu1.tax_id, C_Cu1.taxable, C_Cu1.wishlist_contact, Z_Ad1.ID, Z_Ad1.address1, Z_Ad1.address2, Z_Ad1.city, Z_Ad1.zip, Z_St1.ID, Z_St1.abbrev, Z_St1.name, Z_Co1.ID, Z_Co1.abbrev, Z_Co1.code_size, Z_Co1.code_specs, Z_Co1.mailing_name, Z_Co1.name, Z_Co1.priority, Z_Ph1.ID, Z_Ph1.category, Z_Ph1.number, Z_Ph2.ID, Z_Ph2.category, Z_Ph2.number, Z_Ph3.ID, Z_Ph3.category, Z_Ph3.number, Z_Ph4.ID, Z_Ph4.category, Z_Ph4.number, Z_Ph5.ID, Z_Ph5.category, Z_Ph5.number FROM C_Customer C_Cu1 LEFT JOIN Z_Address Z_Ad1 ON Z_Ad1.ID =C_Cu1.address_ID LEFT JOIN Z_State Z_St1 ON Z_St1.ID = Z_Ad1.state_ID LEFT JOIN Z_Country Z_Co1 ON Z_Co1.ID = Z_St1.country_ID LEFT JOIN Z_Phone Z_Ph1 ON Z_Ph1.ID = C_Cu1.phone1_ID LEFT JOIN Z_Phone Z_Ph2 ON Z_Ph2.ID = C_Cu1.phone2_ID LEFT JOIN Z_Phone Z_Ph3 ON Z_Ph3.ID = C_Cu1.phone3_ID LEFT JOIN Z_Phone Z_Ph4 ON Z_Ph4.ID = C_Cu1.phone4_ID LEFT JOIN Z_Phone Z_Ph5 ON Z_Ph5.ID = C_Cu1.phone5_ID WHERE 1 AND (C_Cu1.purged="No" AND C_Cu1.name_last LIKE %s) ORDER BY C_Cu1.name_last, C_Cu1.name_first'
params = ['karki%']
Anyone using 1.1.10 seen anything similar?
It's not clear what version of MySQL you are using, or if you are using 3.23 in all cases.
I suspect it's some kind of a parser issue on the MySQL side, and all parsing happens in the server.
This indicates that MySQL says the column is some sort of INTEGER column, but a string actually gets returned; the client interface actually returns all results as strings, and MySQLdb tries to convert them to the correct Python types, based on the indicated column type.
But the most obvious question is: Why do you have "1 AND" in your where clause? 1 AND x is always true if x is true and false if x is false. I find it particularly suspect that in your WHERE you have a condition C_Cu1.purged="No", which implies to me that sometimes C_Cu1.purged sometimes = "Yes", which happens to be the exact value being complained about.
I think it's basically impossible for this to be a MySQLdb problem: It doesn't parse your query, and it uses the column type the MySQL C API tells it to use.
doh, sorry about that. The problem occured after an upgrade to 4.1.8a-Debian_6-log on the server, and 4.1.9 rpms on Fedora Core 2 on the clients. I haven't tested 1.1.10 on Linux against the 3.x series and don't have a good way to do so at this time.
***This indicates that MySQL says the column is some sort of INTEGER column, but a string actually gets returned; the client interface actually returns all results as strings, and MySQLdb tries to convert them to the correct Python types, based on the indicated column type.
Right, which is why I posted here... It looked like MySQLdb was incorrectly indentifying an ENUM as an INT or MySQL is passing back a string when the spec dictates an INT.
***But the most obvious question is: Why do you have "1 AND" in your where clause? 1 AND x is always true if x is true and false if x is false.
Heh, it's code generated from a data model by a code generator. The code generator is fairly new and that was the easiest (read, quickest) way to handle a generic search query where the user might not specify "any" criteria. It looks weird, and the extra space can easily be fixed in the generator, but it was very strange to see an error on it. For now, I've just used sed on the generated code to strip the extra space..
***I find it particularly suspect that in your WHERE you have a condition C_Cu1.purged="No", which implies to me that sometimes C_Cu1.purged sometimes = "Yes", which happens to be the exact value being complained about.
I agree. I'll see if I can trigger something similar in a smaller query on a nicer set of tables.
***I think it's basically impossible for this to be a MySQLdb problem: It doesn't parse your query, and it uses the column type the MySQL C API tells it to use.
Hmm, so it might be a problem in the C API?
Again, note that the error occurs against 1.1.10 on Linux, but not against 1.1.8 on Windows. I'll do some more testing on my side now that it is clearer to me that it might be only enum cols causing problems.
Well, my apologies... I can't repro it on the suspect machines today even with the original code. Perhaps I had a library cached on Monday that was causing me problems. :( I just saw a similar error on a Windows machine today that hadn't been upgraded from 0.9.2 yet... Silly me.