Edgewall Software

MySQL and its MySQLdb Python bindings

Trac supports the MySQL database backend, provided you comply with the basic requirements exposed below. There are also a couple of #KnownIssues and supporting this database has proven to be painful at times. We recommend that you use it only if you don't have other choice. Some users are nevertheless using MySQL successfully, and this backend is fully integrated in our test suite, so it might work for you.

Requirements

MySQL

Warning: The following guidelines applies to Trac starting with 0.11.1.
For earlier versions of Trac, please see MySqlDb@22#MySQL.

The recommended versions of MySQL are those from 5.0.x series. It might work with 4.1.x or 6.0.x, but no guarantees. The MariaDB variant of MySQL works as well (tested MariaDB 5.3.3, MySQLdb 1.2.3 and Trac 0.12.3).

In order to avoid Unicode and international characters problems in Trac (e.g., international characters turning into question marks with mysterious "Incorrect string value" errors logged), the Trac database MUST be configured with the utf8 character set and the utf8_bin collation type.

Besides, all tables MUST be created as InnoDB or NDB type tables, because Trac uses a transaction mechanism that is not supported by MyISAM tables (see ticket:8067:5 and MySQL docs). Be aware that NDB introduces some annoying limits for Trac (#8567).

A proper database can be created with any MySQL client, like this:

CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; 

With MySQL version 5.5.3 and up, you can also select the mb4 variant of utf8, which enables you to store any Unicode character, not only those in the Basic Multilingual Plane. Note that you'll need at least Trac 0.12.3 for this (#9766).

CREATE DATABASE trac DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; 

To check character set settings for your database, issue the following commands:

mysql> USE trac;
Database changed
mysql> SHOW VARIABLES WHERE variable_name IN ('character_set_database', 'collation_database');
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| character_set_database | utf8     |
| collation_database     | utf8_bin |
+------------------------+----------+
2 rows in set (0.01 sec)

mysql>

If this is not the case (e.g. you have latin1/latin1_swedish_ci or even utf8 but utf8_ci collation), then read on to the #Collationfromwhatevertoutf8_bin section below to see how to change it.

If you are setting new collation on old database, make sure that column collations are converted too:

mysql> SHOW FULL COLUMNS FROM `node_change`;
+-------------+-------------+-----------+-
| Field       | Type        | Collation | 
+-------------+-------------+-----------+-
| rev         | varchar(64) | utf8_bin  | 
| path        | text        | utf8_bin  | 
| node_type   | varchar(32) | utf8_bin  | 
| change_type | varchar(32) | utf8_bin  | 
| base_path   | text        | utf8_bin  | 
| base_rev    | varchar(64) | utf8_bin  | 
+-------------+-------------+-----------+-
6 rows in set (0.00 sec)                  

See #3884 for info about collations.

Usually, you also want to create a user and give this user access to the database created above:

GRANT ALL ON trac.* TO tracuser@localhost IDENTIFIED BY 'password';

The connection string will then be:

mysql://tracuser:password@localhost/trac

MySQLdb

The recommended version is MySQL-python 1.2.3, which must be compiled in thread-safe client mode. The older version 1.2.2 also works but please don't use earlier versions which are known to be problematic.

See http://cheeseshop.python.org/pypi/MySQL-python/1.2.2, which contains links to the .tar.gz source distribution as well as pre-built Windows eggs, but for Python 2.5 only as of this writing. Python 2.6 binaries for windows can be found here (unofficial site).

Older versions can be found at the mysql-python Sourceforge project:

Version Notes
MySQL-python 1.2.1_p2 This was the previously recommended version — unsupported
MySQL-python 1.2.0-3.2.2, MySQL-python 1.2.2b2 Reported to have issues (see #3645) — unsupported
MySQL-python-1.2.2b2 Was reported to solve an AttributeError: 'array.array' object has no attribute 'startswith' error seen when viewing a changeset. These problems ought to be fixed in 1.2.2b3; if they are not, or if you have other issues, please file a bug in the MySQL-python Bug Tracker

On FreeBSD this package is called py-MySQLdb (in ports).

If you are using ActiveState Python 2.6 on Windows, you can install with pypm install mysql-python.

thread-safety is important

You should make sure that you're using a thread-safe version of the bindings. To verify this, you can check the System Information section in the About Trac page, when you are logged with a user having the CONFIG_VIEW or the TRAC_ADMIN permission. You should see a line reading: MySQL server: "...", client: "...", thread-safe: 1. If you're seeing thread-safe: 0 instead, the MySQL-python version you're using was not correctly built. Check the following resources to see what you should do:

Note that even if you build MySQLdb to use the thread-safe libraries, About Trac may still report that it is not thread-safe. If that happens, verify that it is thread-safe by calling MySQLdb.thread_safe() from a standalone Python script (i.e., not under Apache). If the stand-alone test reports that MySQLdb is indeed thread-safe, then the problem may lie with the PHP module. For example, the Red Hat rpm for PHP is built to use the non-thread-safe MySQL libraries, and the Apache PHP module apparently gets loaded first. So when Python and MySQLdb load, they end up using the already loaded MySQL libraries, which are not thread-safe. The circumvention for this problem is to replace the non-thread-safe libs in /usr/lib/mysql with soft links, so as to have the non-thread-safe lib names point to the thread-safe libs; restart Apache, and now Trac should be running in thread-safe mode.

Conversion Procedures

MyISAM to InnoDB

Check that tables are not already converted:

SELECT table_name, engine FROM information_schema.tables WHERE table_schema=DATABASE();

For every table in Trac database issue:

ALTER TABLE `table_name` ENGINE = InnoDB;

Collation from "whatever" to "utf8_bin"

First modify the DB collation to ensure new tables will be created properly:

ALTER DATABASE `trac_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Then modify the collation for tables. The command below changes the default collation for new columns and converts old columns as well. Issue the command for every table:

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

From SQLite

Users have reported success with the following method.

Known Issues

Still Opened

next-major-releases milestone tickets:

#3676
MySQL: Primary keys are not well designed
#6823
trac crashing under mysql, w/ fix
#6986
MySQL schema changes for performance
#8396
[Database schema] Ticket description field might be too short.
#11206
Reduce join in query process
#11285
trac-admin repository sync fails with very long svn commit message

not applicable milestone tickets:

#8067
Stopped sync in 0.11.2.1

unscheduled milestone tickets:

#8567
Issue with MySQL: Maximum ROW size for NDB engine is 8052

Typical Problems

If you have some strange issues (typically OperationalError: (2006, 'MySQL server has gone away'), as in #3645, and your web frontend is Apache, you might have conflicting MySQL libraries. Be sure to read: ApplicationIssues#MySQL_Shared_Library_Conflicts. Another possibility is that you've hit MySQL bug 36639, as explained in ticket:3645#comment:48.

When changing collation to utf8 MySQL may fail in 4.1.x and 5.0.x versions on MyISAM table type. It will complain about "too big" indexes in some Trac tables. This will be (hopefully) addressed in 0.10.4. As a workaround it has been recommended to use InnoDB tables instead of MyISAM where this limitation is absent.

#3182
Error when adding or viewing a ticket using MySQL
#3723
Problems with database encoding
#3857
Encoding problem with mysql
#4017
initenv problem with MySQL - Access Denied
#4437
install error (mysql)
#4528
TypeError: unhashable type when installing trac-0.10.3 with mysql
#5012
'module' object has no attribute 'connect'
#6237
InterfaceError: (0, '') after idle period with mod_wsgi 1.1/Apache 2.2.3
#6554
trac-admin allows to init environment with mysql errors
#6787
Trac 0.10.4 - ticket module provider fails in background
#8279
IntegrityError when not logged in
#8552
Formatted time via SQL command FROM_UNIXTIME fails to replace %*
#9696
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)")
#9983
TypeError: int() argument must be a string or a number
#10287
TypeError: unsupported type for timedelta microseconds component: unicode
#10511
OperationalError: (1030, 'Got error 28 from storage engine')

Also noteworthy:

#4378
MySQL structure case insensitive causes collision with file names

Troubleshooting

If you get an error from Python when using trac-admin like this:

OperationalError: (1045, "Access denied for user 'tracuser'@'localhost' (using password: YES)")

There are a few possibilities:

  1. Try first to login on the command-line using the user tracuser (mysql -p -h localhost -u tracuser)
  2. Created user is not yet used by MySQL (Login to the MySQL server (as root) with mysql -p and type: FLUSH PRIVILEGES;)
  3. The user is added but the host does not match in the MySQL user table (occurred on a FreeBSD setup). Correct this with: mysql -p; use mysql; UPDATE user SET Host="localhost" WHERE User="tracuser"; FLUSH PRIVILEGES;
Last modified 19 months ago Last modified on Dec 23, 2012 11:45:07 PM