MySQL and the MySQLdb Python bindings for MySQL

work in progress, only the most critical information is there for now

Starting with 0.10 Trac can use MySQL for its DatabaseBackend, but only starting with 0.10.4 will MySQL be fully supported (at least that's the plan). So stick to 0.10.3 for now.

Requirements

MySQL

In order to avoid several issues with unicode and international characters throughout Trac (e.g., international characters turning into question marks with mysterious "Incorrect string value" errors logged), we strongly advise MySQL users to only use databases having the utf8 character set and the utf8_general_ci collation type. However, changing collation to utf8 makes MySQL fail in 4.1.x and 5.0.x versions on MyISAM table type, because some Trac tables indexes become "too big". 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.

Such databases can be created with the MySQL monitor, like that:

CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

XXX: I think the above advice on collations is wrong - using a case-insensitive collation does not sound right, and when I used the above command, the indexing process failed when it came to insert a revision which included changes to files with names differing only in case. Recreating the db using the utf8_bin collation allowed things to work as expected.

To check character set settings for your database, issue command:

mysql> USE trac;
Database changed
mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+--------------------
| Variable_name            | Value
+--------------------------+--------------------
| character_set_client     | cp1251
| character_set_connection | cp1251
| character_set_database   | utf8
| character_set_filesystem | binary
| character_set_results    | cp1251
| character_set_server     | utf8
| character_set_system     | utf8
| character_sets_dir       | C:\DevServer\Instal
+--------------------------+--------------------
8 rows in set (0.00 sec)
mysql>

See also #3884.

MySQLdb

The recommended version is MySQL-python 1.2.2.

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.

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

• MySQL-python 1.2.1_p2 was the previously recommended version
• MySQL-python 1.2.0-3.2.2, MySQL-python 1.2.2b2) were reported to have issues (see #3645).
• 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.

Known Issues

Still Opened

next-dev-1.7.x milestone tickets:

#13080

Add [trac] mysql_storage_engine option instead of editting my.cnf

next-major-releases milestone tickets:

#6986

MySQL schema changes for performance

#11206

Reduce join in query process

#12390

Attachment table primary key not unique when migrating from SQLite to MySQL

next-stable-1.6.x milestone tickets:

#12097

Add a script to contrib for converting MySQL storage engine

#12363

Support ucs2/utf16/utf32 charset and utf16_bin/utf32_bin collation in MySQL

unscheduled milestone tickets:

#8567

BLOB column can't be used in PRIMARY KEY and maximum ROW size is 8052 on NDB engine

#12143

mysql + InnoDB + auth_cookie == Bad.

Typical Problems

milestone tickets:

#3182

Error when adding or viewing a ticket using MySQL

#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')

#11727

Problem with synchronizing svn commits to MySQL database

#3723

Problems with database encoding

#12409

The $ character must be URL encoded if used as a database password

(corresponding custom query)

Conversion

While a more robust method may be created for 0.10.4 to allow easy conversion from SQLite to MySQL, users have reported success with the following method.