Edgewall Software

Version 31 (modified by anatoly techtonik <techtonik@…>, 15 years ago) ( diff )

+ query to get engine type

MySQL and the MySQLdb Python bindings for MySQL

The primary supported backends are PostgreSQL and SQLite. MySQL is supported by Trac since 0.10 with limitations (see #KnownIssues). Use this only if you don't have other choice, and be warned that there can be issues unicode support and the repository cache. Some users are nevertheless using MySQL successfully, so it might work for you.

Recommended Trac for using with MySQL DatabaseBackend is 0.10.5 or, much better, 0.11.1.

Requirements

MySQL

Warning: The following applies to Trac starting with 0.11.1.
For earlier versions of Trac, please see MySqlDb@22#MySQL.
In short, up to 0.11 use
utf8_general_ci collation type, but starting with 0.11.1, use utf_bin as explained below.

To avoid unicode and international characters problems in Trac (e.g., international characters turning into question marks with mysterious "Incorrect string value" errors logged), we strongly advise MySQL users to use only databases with the utf8 character set and the utf8_bin collation type. All tables should be created as InnoDB or NDB type tables, because Trac uses transaction mechanism that not supported by MyISAM tables (see ticket:8067:5 and MySQL docs).

Proper database can be created with the MySQL monitor, like that:

CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; 

Some unicode related MySQL-python bugs were worked around in r7286 so if you're experiencing problems with non-ascii text you should consider upgrading to Trac 0.11.1.

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.

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

CREATE USER tracuser IDENTIFIED BY 'password';
GRANT ALL ON trac.* TO tracuser;

The connection string will then be:

mysql://tracuser:password@localhost/trac

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.
  • NOTE: On FreeBSD this package is called py-MySQLdb (in ports)

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): mysql -p and type FLUSH PRIVILEGES;)
  3. The user is added but the host does not match in the mysql user table (I had this on my FreeBSD setup). mysql -p; use mysql; UPDATE user SET Host="localhost" WHERE User="tracuser"; FLUSH PRIVILEGES;)

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

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: http://code.google.com/p/modwsgi/wiki/ApplicationIssues#MySQL_Shared_Library_Conflicts

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')
#11727
Problem with synchronizing svn commits to MySQL database
#12409
The $ character must be URL encoded if used as a database password

(corresponding custom query)

Conversion

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 DB collation:

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

Then modify tables - issue the following for every table:

ALTER TABLE `table_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

From SQLite

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.

Note: See TracWiki for help on using the wiki.