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

MySQLdb-1.1
closed
Andy Dustman
MySQLdb (285)
5
2012-09-19
2004-08-10
Eugene Sizikov
No

$ 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-

Discussion

  • Andy Dustman
    Andy Dustman
    2004-08-12

    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
    Eugene Sizikov
    2004-08-12

    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
    Eugene Sizikov
    2004-08-12

    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
    Andy Dustman
    2004-08-13

    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.

     
  • 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
    Andy Dustman
    2004-09-06

    Logged In: YES
    user_id=71372

    Should be fixed in 1.1.2.

     
  • Andy Dustman
    Andy Dustman
    2004-09-06

    Logged In: YES
    user_id=71372

    This has been fixed in the current CVS tree.