Edgewall Software
Modify

Opened 14 years ago

Closed 14 years ago

Last modified 14 years ago

#3673 closed defect (duplicate)

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

Reported by: Martin Burger <mburger@…> Owned by: Christian Boos
Priority: normal Milestone:
Component: general Version: 0.10b1
Severity: normal Keywords: mysql utf8 primary key
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

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

Attachments (0)

Change History (6)

comment:1 by Martin Burger <mburger@…>, 14 years ago

Keywords: mysql utf8 primary key added

comment:2 by Christian Boos, 14 years ago

Milestone: 0.10.1
Owner: changed from Jonas Borgström to Christian Boos

comment:3 by mrbrush, 14 years ago

see comment:ticket:3659:5 for possible solution

comment:4 by Christian Boos, 14 years ago

Milestone: 0.10.4
Resolution: duplicate
Status: newclosed

See #3676.

comment:5 by anonymous, 14 years ago

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)

comment:6 by Christian Boos, 14 years ago

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.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Christian Boos.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Christian Boos to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.