hitting "primaryKeys" for one of my tables truncates field names (and messes up subsequent calls)

[GoogleCodeExporter]

What steps will reproduce the problem?

import pyodbc 
conn = pyodbc.connect("DSN=neuron;user=ccurvey;password=XXXXXX")
cursor = conn.cursor()

cursor.execute("drop table if exists foobar");
cursor.execute("""create table foobar 
                  (aaaaa int not null auto_increment primary key
                  ,bbbbb varchar(80) null);
                  """)

print "version = ", pyodbc.version
print "foobar keys = ", cursor.primaryKeys("foobar").fetchall()
print "auth_group keys = ", cursor.primaryKeys("auth_group").fetchall()
print "foobar keys = ", cursor.primaryKeys("foobar").fetchall()
print "foobar columns = ", cursor.columns("foobar").fetchall()

What is the expected output? What do you see instead?

The listed program gives me this:   (commentary preceeded by "**")

version =  3.0.3
foobar keys =  [(None, None, u'foobar', u'aaaaa', 1, u'PRIMARY')]

** so far, so good.

auth_group keys =  [(None, None, u'auth', u'i', 1, u'P')]

** WTF?  See definition of auth_group below.

foobar keys =  [(None, None, u'foob', u'a', 1, u'P')]

** now primaryKeys is messing up foobar as well

foobar columns =  [(u'\x00\x00\x00\x00\x00\x00\x00\x00fo', None, u'foob', u'a', 
4, u'i', 10, 4, 0, 10, 1, u'', u'0', 4, None, None, 1, u'YES'), 
(u'\x00\x00\x00\x00\x00\x00\x00\x00fo', None, u'foob', u'b', 12, u'v', 80, 80, 
None, None, 1, u'', None, 12, None, 80, 2, u'YES')]

** and now columns() is returning junk as well.  Maybe the charset setting is 
getting clobbered?

Definition of auth_group:

mysql> show create table auth_group
    -> ;
+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------+
| Table      | Create Table                                                     

                                             |
+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------+
| auth_group | CREATE TABLE `auth_group` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------+
1 row in set (0.00 sec)

Driver definition:

[neuron]
Description = MySQL Database Test
Driver = MySQL
Server = neuron01.XXXXXXX.com
Database = testdb
Port = 3306
Charset = latin1

What version of the product are you using? On what operating system?

pyodbc:  3.0.3 (compiled from source)
python 2.7.2
libmyodbc:  5.1.6, installed from synaptic.
OS:  Ubuntu 11.10.  

Please provide any additional information below.

I have the same problem when communicating with MySQL 5.0 and 5.1.  This 
appears to be something with the auth_group table, but I can't figure it out 
for the life of me.

Original issue reported on code.google.com by ccur...@gmail.com on 25 May 2012 at 1:02

[GoogleCodeExporter]

I tested this on OS/X 64-bit with libmyodbc5w.so version 5.02.0002.  Tested 
with both pyodbc 3.0.3 and the latest from GitHub.

Is this a 32-bit or 64-bit Python?

It looks like you are working with the ANSI driver.  Can you try with the 
Unicode driver also?

Original comment by mkleehammer on 29 Sep 2012 at 4:44

• Changed state: Hold

[GoogleCodeExporter]

I forgot to mention, can you try a later mysql odbc driver?  I'm using 5.2.2, 
which is the latest on their website.

Original comment by mkleehammer on 29 Sep 2012 at 4:48

• Added labels: Driver-mysql