InterfaceError truncated column

[n1ywb]

I'm not sure if this is a bug, please bear with me.

I just switched to oursql 0.9.2 from mysqldb 1.2.2. Running Python 2.6 on win32. I am using SQLAlchemy 0.6.6. I didn't change my code, just the SQLalchemy import statements. I am now getting a bunch of exceptions.

Traceback (most recent call last):
  File "c:\Python26\Lib\threading.py", line 532, in __bootstrap_inner
    self.run()
  File "c:\users\jlaughlin\documents\smartview-svn\python\branches\python2.6\smartview\eventd\eventd.py", line 470, in r
un
    self.device.process()
  File "c:\users\jlaughlin\documents\smartview-svn\python\branches\python2.6\smartview\eventd\eventd.py", line 363, in p
rocess
    event_logger.process()
  File "c:\users\jlaughlin\documents\smartview-svn\python\branches\python2.6\smartview\eventd\eventlogger.py", line 69,
in process
    conn = dls.connection()
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\session.py", line 656, in connection
    return self._connection_for_bind(self.get_bind(mapper, clause))
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\session.py", line 660, in _connection_for_bind
    return self.transaction._connection_for_bind(engine)
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\session.py", line 336, in _connection_for_bind
    conn = bind.contextual_connect()
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 1829, in contextual_connect
    self.pool.connect(),
  File "c:\Python26\lib\site-packages\sqlalchemy\pool.py", line 182, in connect
    return _ConnectionFairy(self).checkout()
  File "c:\Python26\lib\site-packages\sqlalchemy\pool.py", line 369, in __init__
    rec = self._connection_record = pool.get()
  File "c:\Python26\lib\site-packages\sqlalchemy\pool.py", line 213, in get
    return self.do_get()
  File "c:\Python26\lib\site-packages\sqlalchemy\pool.py", line 732, in do_get
    con = self.create_connection()
  File "c:\Python26\lib\site-packages\sqlalchemy\pool.py", line 147, in create_connection
    return _ConnectionRecord(self)
  File "c:\Python26\lib\site-packages\sqlalchemy\pool.py", line 258, in __init__
    l.first_connect(self.connection, self)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\strategies.py", line 151, in first_connect
    dialect.initialize(c)
  File "c:\Python26\lib\site-packages\sqlalchemy\dialects\mysql\oursql.py", line 188, in initialize
    connection.execution_options(_oursql_plain_query=True)
  File "c:\Python26\lib\site-packages\sqlalchemy\dialects\mysql\base.py", line 1753, in initialize
    default.DefaultDialect.initialize(self, connection)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\default.py", line 159, in initialize
    self.returns_unicode_strings = self._check_unicode_returns(connection)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\default.py", line 205, in _check_unicode_returns
    unicode_for_varchar = check_unicode(sqltypes.VARCHAR(60))
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\default.py", line 198, in check_unicode
    row = cursor.fetchone()
  File "cursor.pyx", line 185, in oursql.Cursor.fetchone (oursqlx\oursql.c:16162)
  File "statement.pyx", line 541, in oursql._Statement.fetchone (oursqlx\oursql.c:11281)
InterfaceError: (None, 'truncated column 0, type 253', None)

Also this one:

Traceback (most recent call last):
  File "c:\users\jlaughlin\documents\smartview-svn\python\branches\python2.6\smartview\eventd\eventlogger.py", line 91,
in run
    queuedevts = dls.query(model.EventQueue).order_by(model.EventQueue.idx).all()
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 1579, in all
    return list(self)
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 1791, in instances
    fetch = cursor.fetchall()
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 2504, in fetchall
    self.cursor, self.context)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 2498, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 2465, in _fetchall_impl
    return self.cursor.fetchall()
  File "cursor.pyx", line 228, in oursql.Cursor.fetchall (oursqlx\oursql.c:16568)
  File "statement.pyx", line 550, in oursql._Statement.fetchall (oursqlx\oursql.c:11377)
  File "statement.pyx", line 541, in oursql._Statement.fetchone (oursqlx\oursql.c:11281)
InterfaceError: (InterfaceError) (None, 'truncated column 0, type 3', None) None None

[n1ywb (jeff-laughlin)]

Sorry, I changed the sqlalchemy URLs, not the import statements.

FWIW I turned echo on, here's the log leading up to the second exception:

2011-02-04 11:40:41,437 INFO sqlalchemy.engine.base.Engine.0x...8490 BEGIN (implicit)
2011-02-04 11:40:41,443 INFO sqlalchemy.engine.base.Engine.0x...8490 SELECT event_queue.idx AS event_queue_idx
FROM event_queue ORDER BY event_queue.idx
2011-02-04 11:40:41,444 INFO sqlalchemy.engine.base.Engine.0x...8490 ()
2011-02-04 11:40:41,446 DEBUG sqlalchemy.engine.base.Engine.0x...8490 Col ('event_queue_idx',)
2011-02-04T11:40:41|eventd|ERROR |Exception in RemoteDBThread
Traceback (most recent call last):
  File "c:\users\jlaughlin\documents\smartview-svn\python\branches\python2.6\smartview\eventd\eventlogger.py", line 91,
in run
    queuedevts = dls.query(model.EventQueue).order_by(model.EventQueue.idx).all()
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 1579, in all
    return list(self)
  File "c:\Python26\lib\site-packages\sqlalchemy\orm\query.py", line 1791, in instances
    fetch = cursor.fetchall()
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 2504, in fetchall
    self.cursor, self.context)
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 2498, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "c:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line 2465, in _fetchall_impl
    return self.cursor.fetchall()
  File "cursor.pyx", line 228, in oursql.Cursor.fetchall (oursqlx\oursql.c:16568)
  File "statement.pyx", line 550, in oursql._Statement.fetchall (oursqlx\oursql.c:11377)
  File "statement.pyx", line 541, in oursql._Statement.fetchone (oursqlx\oursql.c:11281)
InterfaceError: (InterfaceError) (None, 'truncated column 0, type 3', None) None None

This table is trivial:
CREATE TABLE `rtu_prod`.`event_queue` (
  `idx` int(11) NOT NULL,
  PRIMARY KEY (`idx`),
  CONSTRAINT `event_queue_ibfk_1` FOREIGN KEY (`idx`) REFERENCES `events2` (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

every record appears to be populated with a normal int.

[n1ywb (jeff-laughlin)]

run this sql script, then run the python script

[n1ywb (jeff-laughlin)]

output from those scripts on my machine:

>>> ================================ RESTART ================================
>>>

Traceback (most recent call last):
  File "C:/Users/jlaughlin/Documents/tmp/oursqltest.py", line 6, in <module>
    print curs.fetchall()
  File "cursor.pyx", line 228, in oursql.Cursor.fetchall (oursqlx\oursql.c:16568)
  File "statement.pyx", line 550, in oursql._Statement.fetchall (oursqlx\oursql.c:11377)
  File "statement.pyx", line 541, in oursql._Statement.fetchone (oursqlx\oursql.c:11281)
InterfaceError: (None, 'truncated column 0, type 3', None)
>>>

[n1ywb (jeff-laughlin)]

I got the oursql build from habnabit.

C:\Users\jlaughlin\Documents\smartview-svn\python\branches\python2.6>python --version
Python 2.6.6

C:\Users\jlaughlin\Documents\smartview-svn\python\branches\python2.6>mysql --version
mysql Ver 14.14 Distrib 5.1.53, for Win32 (ia32)

C:\Users\jlaughlin\Documents\smartview-svn\python\branches\python2.6>pip freeze
Fabric==0.9.3
PyYAML==3.09
SQLAlchemy==0.6.6
boto==2.0b3
distribute==0.6.14
mock==0.7.0b4
oursql==0.9.2
paramiko==1.7.6
pycrypto==2.0.1
pytz==2010o
simplejson==2.1.3
virtualenv==1.5.1

[n1ywb (jeff-laughlin)]

windows 7 FWIW

[n1ywb (jeff-laughlin)]

Also FWIW if I use the same example to connect to my MySQL 4.1 server I get a different exception

>>> ================================ RESTART ================================
>>>

Traceback (most recent call last):
  File "C:/Users/jlaughlin/Documents/tmp/oursqltest.py", line 6, in <module>
    print curs.fetchall()
  File "cursor.pyx", line 228, in oursql.Cursor.fetchall (oursqlx\oursql.c:16568)
  File "statement.pyx", line 549, in oursql._Statement.fetchall (oursqlx\oursql.c:11364)
  File "statement.pyx", line 556, in oursql._Statement.buffer_results (oursqlx\oursql.c:11447)
  File "statement.pyx", line 126, in oursql._Statement._raise_error (oursqlx\oursql.c:6997)
InterfaceError: (2014, "Commands out of sync; you can't run this command now", None)
>>>