View Ticket does not work properly with MySQL

[Martin Burger <mburger@…>]

I have some problems with Trac 0.10b1 and MySQL 4.1.11-Debian_4sarge5-log.

When I try to use a database with collation "utf8_general_ci" with trac-admin, I get the following error:

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

So, using collation "utf8_general_ci" trac-admin cannot create the environment. However, using collation "latin1_general_ci", trac-admin is able to finish it's work.

But: while viewing tickets, I get the following error:

Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 335, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 220, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 287, in process_request
    get_reporter_id(req, 'author'))
  File "/usr/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 600, in _insert_ticket_data
    for change in self.grouped_changelog_entries(ticket, db):
  File "/usr/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 647, in grouped_changelog_entries
    changelog = ticket.get_changelog(when=when, db=db)
  File "/usr/lib/python2.3/site-packages/trac/ticket/model.py", line 297, in get_changelog
    "ORDER BY time", (self.id,  str(self.id), self.id))
  File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 47, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  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: (1267, "Illegal mix of collations (latin1_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'UNION'")

To summarize:

• latin1_general_ci: I can create the environment but I cannot view tickets
• utf8_general_ci: I cannot create the environment

[Martin Burger <mburger@…>]

Quick fix:

--- mysql_backend.py.BAK        2006-09-03 20:43:48.580402627 +0200
+++ mysql_backend.py    2006-09-03 20:53:32.394345764 +0200
@@ -141,7 +141,7 @@
         else:
             cnx = MySQLdb.connect(db=path, user=user, passwd=password,
                                   host=host, port=port, use_unicode=True)
-            self._set_character_set(cnx, 'utf8')
+            self._set_character_set(cnx, 'latin1')
         ConnectionWrapper.__init__(self, cnx)

     def cast(self, column, type):

[Martin Burger <mburger@…>]

I just created another project. Using latin1 I was not able to create the project, I had to use utf8. But afterwards, I needed to switch to latin1 in order to view the tickets. Very strange… (?)

[Martin Burger <mburger@…>]

See #3673

[mrbrush]

Try to set MySQL to use the InnoDB backend instead of the default MyISAM. With InnoDB storage engine Trac currently works without problems for me with international characters etc, which were previously not saved correctly with MyISAM, even with UTF-8 as the default encoding.

Here are some recommended options to set in the my.ini/my.cnf for integration with Trac. Put these in to the [mysqld] section on linux, and [mysql] section on windows.

default-character-set=utf8
default-storage-engine=INNODB
innodb_file_per_table

The last option will save you a lot of headaches because without that, every database table using InnoDB in your MySQL instance will have it's data stored in a single file called ibdata1, and that file only grows, never shrinks, even if you drop your large databases. Whith this option, you will have your InnoDB database content separated into different files in different directories based on the database/table they are part of.

I think it would help everybody if this information would be included in the Trac/MySQL documentation.

[Martin Burger <mburger@…>]

I changed /etc/mysql/my.cnf - without success. Trac creates InnoDB tables, but it still throws that exception:

OperationalError: (1071, 'Specified key was too long; max key length is 1024 bytes')
Failed to initialize environment. 1
Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/trac/scripts/admin.py", line 617, in do_initenv
    sys.exit(1)
SystemExit: 1

[Martin Burger <mburger@…>]

Are you using MySQL 4.0.x (4.0.20)? I'm using 4.1.11. With 4.0.x it may work, with 4.1.x it will not: ​http://jira.atlassian.com/browse/CONF-2783#action_31418

Starting with 4.1.1 (?) the global max key length is 1024 bytes, independent of the used table engine.

[mrbrush]

Replying to Martin Burger <mburger@netbeyond.de>:

Are you using MySQL 4.0.x (4.0.20)? I'm using 4.1.11. With 4.0.x it may work, with 4.1.x it will not: ​http://jira.atlassian.com/browse/CONF-2783#action_31418

Starting with 4.1.1 (?) the global max key length is 1024 bytes, independent of the used table engine.

Sorry, forgot to mention that I've been using MySQL 5 (5.0.22).

[Christian Boos]

"utf8_general_ci" should be the collation used, see MySqlDb.

The error obtained while creating a db using this collation is also reported in #3673 and discussed there.

[Thijs Triemstra <info@…>]

Replying to cboos:

"utf8_general_ci" should be the collation used, see MySqlDb.

I understand that this is a very old comment but for people arriving at this page using Google, realize that:

"In short, up to 0.11 use utf8_general_ci collation type, but starting with 0.11.1, use utf_bin"

as described on the MySqlDb page.