Export to MySQL fails: ERROR 1071 Specified key was too long


we are running CP2 on a cluster. We used SQLite so far but are now switching to MySQL, but we are experiencing problems with exporting our measurements to MySQL. The error happens when we create the batch file for the cluster and says OperationalError: (1071, ‘Specified key was too long; max key length is 1000 bytes’). Command and detailed output follows:

(The same command worked with a pipeline that had no difference except for an export to SQLite instead of MySQL. I can connect to the MySQL server fine and create tables etc as the user specified in the pipeline.)

According to this discussion, this means that a PK is created which spans columns with a total size of greater than 1000 bytes, i.e., about 330 UTF-8 characters.

I think is due to CellProfiler creating PKs over columns of type varchar, maybe in the table Experiment_Properties.

1.) Did anybody else (maybe running our system configuration, see below) ever get this error?
2.) Any suggestions on howto fix / circumvent it?
3.) If you think my guess on what causes the error is wrong: what may cause it?

**Additional info: **
We are running MySQL 5.1 under SuSE 11.4 on x86_64:

I did a quick and dirty fix by manually editing the CellProfiler source code in the file CellProfiler/cellprofiler/modules/exporttodatabase.py and changing the line


constraint %(T_EXPERIMENT_PROPERTIES)s_pk primary key (experiment_id, object_name(100), field(100))).

This circumvents the MySQL error and allows CP to successfully create the database structure.

(For CP version 2012-05-23T10:09:43 SVN:9661 / 20120523100943, this is line 1602.)


Glad you got it working, and posted the workaround. I have not seen this error before, but I filed a bug report on GitHub for this: github.com/CellProfiler/CellProfiler/issues/346.