error on 'trac-admin upgrade': "BLOB/TEXT column 'milestone' used in key specification without a key length"

[emparq@…]

Hello, I've just recently tried to install this plugin on a trac 11.5, and the 'trac-admin <path> upgrade' reported this error:

[mike@localhost:~/temp/extendedversionplugin/0.11]$ sudo trac-admin
/var/www/trac/dev_projects upgrade
Traceback (most recent call last):
 File "/usr/bin/trac-admin", line 7, in ?
   sys.exit(
 File "/usr/lib/python2.4/site-packages/Trac-0.11.5-py2.4.egg/trac/admin/console.py",
line 1314, in run
   return admin.onecmd(command)
 File "/usr/lib/python2.4/site-packages/Trac-0.11.5-py2.4.egg/trac/admin/console.py",
line 133, in onecmd
   rv = cmd.Cmd.onecmd(self, line) or 0
 File "/usr/lib64/python2.4/cmd.py", line 219, in onecmd
   return func(arg)
 File "/usr/lib/python2.4/site-packages/Trac-0.11.5-py2.4.egg/trac/admin/console.py",
line 1154, in do_upgrade
   self.__env.upgrade(backup=do_backup)
 File "/usr/lib/python2.4/site-packages/Trac-0.11.5-py2.4.egg/trac/env.py",
line 457, in upgrade
   participant.upgrade_environment(db)
 File "/usr/lib/python2.4/site-packages/ExtendedVersionTracPlugin-0.0-py2.4.egg/extendedversion/environment.py",
line 47, in upgrade_environment
   cursor.execute(sql)
 File "/usr/lib/python2.4/site-packages/Trac-0.11.5-py2.4.egg/trac/db/util.py",
line 60, in execute
   return self.cursor.execute(sql)
 File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 173, in execute
 File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36,
in defaulterrorhandler
_mysql_exceptions.OperationalError: (1170, "BLOB/TEXT column
'milestone' used in key specification without a key length")

I'm still trying to decide where to begin troubleshooting, so I thought I'd ask first. Is this a known issue?

---

System Information:

Trac:	0.11.5
Python:	2.4.3 (#1, Sep 3 2009, 15:37:37) [GCC 4.1.2 20080704 (Red Hat 4.1.2-46)]
setuptools:	0.6c11
MySQL:	server: "5.0.77-log", client: "5.0.77", thread-safe: 0
MySQLdb:	1.2.3c1
Genshi:	0.5.1
mod_python:	3.2.8
Subversion:	1.4.2 (r22196)
jQuery:	1.2.6

[Malcolm Studd]

I'm guessing you're the first person to try the plugin with MySQL. It looks like MySQL is breaking on 'name text PRIMARY KEY' because it expects some sort of length specifier for the primary key.

I guess the code needs to figure out the type of database and change the SQL. Ugh.

For now, try changing the table definition in environment.py to

        """CREATE TABLE milestone_version (
            milestone    text,
            version      text,
            PRIMARY KEY milestone(100)
        );""",

Hopefully that's valid syntax for MySQL.

[emparq@…]

Yes, that last line did the trick- thanks!

[jonathan.pletschke@…]

Hello,
The above SQL change did not work for me. I resolved the issue by using the following on my MySQL installation:

create table milestone_version ( milestone text, PRIMARY KEY (milestone(100)), version text ) ENGINE=InnoDB;

This was done in the MySQL command interpreter. I'm using the InnoDB engine on the Trac tables hence the last table option. The environment.py create table statement would then have caused a problem so I simply commented it out altogether.

---

Made an entry in my Trac wiki to reflect this hack. Just so future maintainers know what happens :-)

[Ryan J Ollos]

I believe that if we use the Table, Column and Index classes from trac.db.schema, then the Trac database API will handle the cross-database compatibility issues.

[Ryan J Ollos]

In 13318:

Improve cross-db compatibility (Fixes #7731).

Use Table and Column from the trac.db.schema to define the database schema, thereby letting the Trac API handle cross-DB compatibility issues.