#97 failed executing simple query; MySQL-5.0.1, Python-2.3.3

[Eugene Sizikov]

$ mysql test

mysql> create table news (id int not null
auto_increment primary key, ts timestamp, head
varchar(100), body mediumtext not null, index(ts),
index(head));

mysql> insert into news (head, body) values ('test1',
'this is a test news message1'), ('test2', 'this is a
test news message2');

mysql> describe news;
+-------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default
| Extra |
+-------+--------------+------+-----+-------------------+----------------+
| id | int(11) | | PRI | NULL
| auto_increment |
| ts | timestamp | YES | MUL | CURRENT_TIMESTAMP
| |
| head | varchar(100) | YES | MUL | NULL
| |
| body | mediumtext | | |
| |
+-------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

$ cat news.py

/usr/bin/env python

-- coding: UTF-8 --

import MySQLdb as db
con = db.connect(db='test')
cur = con.cursor()
res = cur.execute('select * from news order by ts desc
limit 10')

Traceback (most recent call last):
File "news.py", line 36, in ?
news.show_last_n(10)
File "news.py", line 9, in show_last_n
return self._query('select * from news order by ts
desc limit %u' % int(n))
File "news.py", line 18, in _query
res = cur.execute(query)
File
"/usr/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 95, in execute
return self._execute(query, args)
File
"/usr/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 114, in _execute
self.errorhandler(self, exc, value)
File
"/usr/lib/python2.3/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
ValueError: invalid literal for int(): 8-

[Andy Dustman]

Logged In: YES
user_id=71372

I am nowhere close to testing against or support MySQL-5.

Can you retry your test without id and without ts? Most
likely one of those columns is the culprit (I tend to think
the timestamp, I bet the timestamp format has changed) but I
can't tell for sure which.

Also, which date implementation are you using? Since you
have Python-2.3, it's either the standard datetime or
mx.DateTime (if you have that installed).

[Eugene Sizikov]

Logged In: YES
user_id=126917

There is both standard datetime and mx.DateTime available, I
dunno wich
of them is used within MySQLdb internals.

Ok, I'll try to use withou TIMESTAMP. If it'll work, what
shoud I look
at the MySQL-python sources (procedure or something) to fix
it? Just a
quick guess if you'll be so kind. ;)

[Eugene Sizikov]

Logged In: YES
user_id=126917

Problem solved, patch follows:

diff -u converters.py.orig converters.py

--- converters.py.orig 2004-08-12 14:16:48.742753224 +0900
+++ converters.py 2004-08-12 14:17:05.270240664 +0900
@@ -127,7 +127,7 @@
FIELD_TYPE.INT24: int,
FIELD_TYPE.YEAR: int,
FIELD_TYPE.SET: Str2Set,
- FIELD_TYPE.TIMESTAMP: mysql_timestamp_converter,
+ FIELD_TYPE.TIMESTAMP: DateTime_or_None,
FIELD_TYPE.DATETIME: DateTime_or_None,
FIELD_TYPE.TIME: Time_or_None,
FIELD_TYPE.DATE: Date_or_None,

[Andy Dustman]

Logged In: YES
user_id=71372

Ah yes, they changed TIMESTAMP to return an ISO value like
DATETIME. Well... You fix would unfortunately break with
MySQL<5, so I can't accept it. You can, however, do
something like this in your code:

from MySQLdb.converters import DateTime_or_None
from MySQLdb.constants import FIELD_TYPE
db=MySQLdb.connect(...)
db.conv[FIELD_TYPE.TIMESTAMP] = DateTime_or_None

Or you can fix DateTime_or_None to check for the old-style
TIMESTAMP and then use your fix.

[Nobody/Anonymous]

Logged In: NO

this patch is from the debian bts
(http://bugs.debian.org/185462)

it somehow fixes the new way of handling timestamp, mysql
does since 4.1 for MySQL-python 1.0.0 ...

the problem is, that is seems to break site-compile at
install with mysql libs older than 4.1.

don't know if it helps ...

--- MySQLdb/times.py.orig
+++ MySQLdb/times.py
@@ -26,8 +26,13 @@

def mysql_timestamp_converter(s):
"""Convert a MySQL TIMESTAMP to a Timestamp object."""
- s = s + "0"(14-len(s)) # padding
- parts = map(int, filter(None, (s[:4],s[4:6],s[6:8],
- s[8:10],s[10:12],s[12:14])))
+ if s[4] == '-': ## MySQL 4.1
+ s = s + "0"(19-len(s)) # padding
+ parts = map(int, filter(None, (s[:4],s[5:7],s[8:10],
+
s[11:13],s[14:16],s[17:19])))
+ else:
+ s = s + "0"*(14-len(s)) # padding
+ parts = map(int, filter(None, (s[:4],s[4:6],s[6:8],
+
s[8:10],s[10:12],s[12:14])))
try: return apply(Timestamp, tuple(parts))
except: return None

[Andy Dustman]

Logged In: YES
user_id=71372

Should be fixed in 1.1.2.

[Andy Dustman]

Logged In: YES
user_id=71372

This has been fixed in the current CVS tree.