MySQL error #1071 when creating tables in UTF-8 database

[Martin Burger <mburger@…>]

Problem

When trac-admin tries to create the tables in an UTF-8 database

CREATE DATABASE `trac-test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL error 1071 is raised:

Creating and Initializing Project
Failed to create environment. (1071, 'Specified key was too long; max key length is 1000 bytes')
Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/trac/scripts/admin.py", line 611, in do_initenv
    options=options)
  File "/usr/lib/python2.3/site-packages/trac/env.py", line 124, in __init__
    self.create(options)
  File "/usr/lib/python2.3/site-packages/trac/env.py", line 228, in create
    DatabaseManager(self).init_db()
  File "/usr/lib/python2.3/site-packages/trac/db/api.py", line 65, in init_db
    connector.init_db(**args)
  File "/usr/lib/python2.3/site-packages/trac/db/mysql_backend.py", line 50, in init_db
    cursor.execute(stmt)
  File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 48, in execute
    return self.cursor.execute(sql)
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 137, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler    raise errorclass, errorvalue
OperationalError: (1071, 'Specified key was too long; max key length is 1000 bytes')
Failed to initialize environment. 1
Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/trac/scripts/admin.py", line 615, in do_initenv
    sys.exit(1)
SystemExit: 1

Cause

This is because max key length is 1000 bytes, thus 333 characters in UTF-8. But mysql_backend.py defines 500 chars as maximum value. Thus, the table attachment would have a primary key longer than 1000 bytes:

PRIMARY KEY  (`type`(166),`id`(166),`filename`(166)

Quick fix

Change following lines in mysql_backend.py (Trac 0.10b1):

61,62c61
<         # In an UTF8 database the limit is 333 characters
<         limit = 333 / len(columns)
---
>         limit = 500 / len(columns)

The new primary key is

PRIMARY KEY  (`type`(111),`id`(111),`filename`(111)

However, some keys ar shorter than necessary. For example table ticket_change:

PRIMARY KEY  (`ticket`,`time`,`field`(111)

This is because the first two keys are of type int(11) and mysql_backend.py just counts the number of keys without checking their type.

Note

You could change the character set of the connection to latin1 and use latin1 database and tables. However, there is an other issue: #3659

[mrbrush]

see comment:ticket:3659:5 for possible solution

[cboos]

See #3676.

[anonymous]

Hi there,

I think the quick fix is wrong, should it not introduce the 333 number, rather than remove it?

61,62c61
<         limit = 500 / len(columns)
---
>         # In an UTF8 database the limit is 333 characters
>         limit = 333 / len(columns)

[cboos]

If you follow the duplicate track, you'll end up to the applied fix r4755 which effectively introduces the 333 number, so either use trunk or 0.10-stable and all is fine.