mysql bigint id field with unsigned attribute causes failure for mapper.

[Former user]

My table definition: CREATE TABLE users ( id bigint(20) unsigned NOT NULL, username varchar(64) collate utf8_bin NOT NULL, firstName varchar(128) collate utf8_bin NOT NULL default 'n/a', lastName varchar(128) collate utf8_bin NOT NULL default 'n/a', email varchar(64) collate utf8_bin NOT NULL, password varchar(32) collate utf8_bin NOT NULL, url varchar(128) collate utf8_bin NOT NULL default ' n/a', homedirUrl varchar(128) collate utf8_bin NOT NULL default ' n/a', admin tinyint(1) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY email (email), UNIQUE KEY username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

The error I when SA tries to do reflection to determine the table structure:

Traceback (most recent call last): File "<stdin>", line 56, in ? File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/schema.py", line 138, in call metadata.engine.reflecttable(table) File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/engine/base.py", line 491, in refl\ ecttable self.dialect.reflecttable(conn, table) File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/databases/mysql.py", line 358, in \ reflecttable coltype = coltype(for a in argslist, *kw) TypeError: init() keywords must be strings

Removing the 'unsigned' attribute goes on to an error in a different part of the code, unrelated to this (I think).

[Former user]

Ok. Maybe I'm wrong, this is still related to the reflection code for doing the mapping to a User object. Removing the 'unsigned' attribute works up until this error (same point in my code):

Traceback (most recent call last): File "<stdin>", line 56, in ? File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/schema.py", line 138, in call metadata.engine.reflecttable(table) File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/engine/base.py", line 491, in refl\ ecttable self.dialect.reflecttable(conn, table) File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/databases/mysql.py", line 368, in \ reflecttable tabletype = self.moretableinfo(connection, table=table) File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/databases/mysql.py", line 388, in \ moretableinfo desc_fetched = desc_fetched.tostring() AttributeError: 'unicode' object has no attribute 'tostring'

So it appears that SA is using some generic ascii string method while parsing the table description.

[Former user]

sorry, I'm lame for not reading the wikiformattting instructions. here's the table definition:

mysql> desc users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | bigint(20)   | NO   | PRI | NULL    |       |
| username   | varchar(64)  | NO   | UNI | NULL    |       |
| firstName  | varchar(128) | NO   |     | n/a     |       |
| lastName   | varchar(128) | NO   |     | n/a     |       |
| email      | varchar(64)  | NO   | UNI | NULL    |       |
| password   | varchar(32)  | NO   |     | NULL    |       |
| url        | varchar(128) | NO   |     |  n/a    |       |
| homedirUrl | varchar(128) | NO   |     |  n/a    |       |
| admin      | tinyint(1)   | NO   |     | 0       |       |
+------------+--------------+------+-----+---------+-------+

Here's the 'show create table':

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `username` varchar(64) collate utf8_bin NOT NULL,
  `firstName` varchar(128) collate utf8_bin NOT NULL default 'n/a',
  `lastName` varchar(128) collate utf8_bin NOT NULL default 'n/a',
  `email` varchar(64) collate utf8_bin NOT NULL,
  `password` varchar(32) collate utf8_bin NOT NULL,
  `url` varchar(128) collate utf8_bin NOT NULL default ' n/a',
  `homedirUrl` varchar(128) collate utf8_bin NOT NULL default ' n/a',
  `admin` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

And the error message:

Traceback (most recent call last):
  File "<stdin>", line 56, in ?
  File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/schema.py", line 138, in __call__
    metadata.engine.reflecttable(table)
  File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/engine/base.py", line 491, in refl\
ecttable
    self.dialect.reflecttable(conn, table)
  File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/databases/mysql.py", line 368, in \
reflecttable
    tabletype = self.moretableinfo(connection, table=table)
  File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.2.8-py2.4.egg/sqlalchemy/databases/mysql.py", line 388, in \
moretableinfo
    desc_fetched = desc_fetched.tostring()
AttributeError: 'unicode' object has no attribute 'tostring'

[Mike Bayer]

it seems like your mysql database is returning result sets as unicode objects. do you have some mysql-config or mysqldb setting that is causing this ?

[Mike Bayer]

i cant test this since I dont know how you got your "DESCRIBE" statements to return unicode result sets, but I have added unicode checks/string conversions (changeset:1923 changeset:1924) to the strings that were causing the problem in your two cases. try and reopen if needed (or figure out why your mysql database is acting all unicode-y....)

[Mike Bayer]

oh duh, this reveals it:

python test/engine/reflection.py --dburi mysql://scott:tiger@localhost/test?use_unicode=1

so i get your errors if i disable my checks. so yes, its fixed since the tests pass with the latest changes.

you might want to not have your connection configured for unicode (i.e. dont use use_unicode, or unset this type of thing: http://dev.mysql.com/doc/refman/5.1/en/charset-metadata.html), and instead use the more generic "convert_unicode" keyword argument to create_engine(), which will allow conversion of all string types to unicode within SA (this is what the linked mysql doc means when it says "Alternatively, a client program can perform the conversion after receiving the result from the server. It is more efficient for the client perform the conversion, but this option is not always available for all clients.")