[[PageOutline(2-3)]] = MySQL and its MySQLdb Python bindings Trac supports the [http://mysql.com MySQL] database backend, although there are a couple of [#KnownIssues]. MySQL is fully integrated in our test suite. == Requirements === MySQL The recommended versions of MySQL are those from 5.x series. The following MySQL dropin replacements are also known to work: * The [http://askmonty.org/wiki/index.php/MariaDB MariaDB] variant: - tested MariaDB 5.3.3, MySQLdb 1.2.3 and Trac 0.12.3 - tested MariaDB 10.1.10 (x64), MySQLdb 1.2.5 and Trac 1.0.10dev, 1.2dev * The [http://www.percona.com/software/percona-server/ps-5.6 Percona] variant: - tested Percona 5.6 with Trac 1.1.4 In order to avoid Unicode and international characters problems in Trac, eg international characters turning into question marks with mysterious **"Incorrect string value"** errors logged, the Trac database '''must''' be configured with one of the following character set and collation type pairs: * `utf8` / `utf8_bin` * `utf8mb4` / `utf8mb4_bin` The configuration is specified through the `character_set_database` and `collation_database` variables of `my.cnf`. Additionally, all tables must be of type `utf8_bin` or `utf8mb4_bin` collation. Tables '''must''' be created as **InnoDB** or **TokuDB** type tables, because Trac uses a transaction mechanism that is not supported by MyISAM tables, see [comment:ticket:8067:5] and [http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html MySQL docs]. Be aware that the cluster storage engine NDB introduces some limits for Trac (#8567). The `default_storage_engine` and `default_tmp_storage_engine` variables in `my.cnf` must be set to one of the storage engines supported by Trac. A proper database can be created with any MySQL client, like this: {{{#!sql CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; }}} With MySQL version 5.5.3 and up, you can also select the [http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html mb4] variant of utf8, which enables you to store any Unicode character, not only those in the [WikiPedia:Basic_Multilingual_Plane Basic Multilingual Plane]. Note that you'll need at least Trac 0.12.3 for this (#9766). {{{#!sql CREATE DATABASE trac DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; }}} To check character set settings for your database, issue the following commands: {{{#!sql 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 you have for example `latin1` or `utf8` or `utf8_ci` collation, then read on to the [#Conversion] section below on how to change it. If you are setting a new collation on an existing database, make sure that column collations are converted too: {{{#!sql 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: {{{#!sql GRANT ALL ON trac.* TO tracuser@localhost IDENTIFIED BY 'password'; }}} The connection string will then be: {{{#!ini mysql://tracuser:password@localhost/trac }}} === MySQLdb ==== Trac >= 1.3.2 For Trac >= 1.3.2, [pypi:PyMySQL] should be used. ==== Trac < 1.3.2 The recommended version is [pypi:MySQL-python MySQL-python 1.2.2+], which must be compiled in ''thread-safe client'' mode. Earlier versions are known to be problematic. The [pypi:MySQL-python MySQL-python] page on PyPI contains links to the `.tar.gz` source distribution. For Windows, there are several possibilities: - the PyPI page mentioned above also links to pre-built Windows eggs, but for Python 2.7 32-bits only. - Python binaries (32-bits and 64-bits) can be found at codegood.com [http://www.codegood.com/archives/4 for Python 2.6] and [http://www.codegood.com/archives/129 for Python 2.7]; however they have been last updated in 2010, so it only proposes version 1.2.3, which should however work fine - recent Python 2.7 wheels (32-bits and 64-bits) can be found in the [http://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python Unofficial Windows Binaries for Python Extension Packages], kindly provided by Christoph Gohlke, Laboratory for Fluorescence Dynamics, University of California, Irvine. - if you are using ActiveState Python 2.6 on Windows, you can install with `pypm install mysql-python` - last but not least, the [http://conda.pydata.org/docs/ Conda] project also contains recent versions of this package and it's handy for use by continuous integration systems as the package can be installed non-interactively using a simple `conda install -qy mysql-python` command (32-bits and 64-bits) On FreeBSD this package is called {{{py-MySQLdb}}} in [http://www.freshports.org/databases/py-MySQLdb55/ the ports collection]. ==== 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: - [http://mysql-python.svn.sourceforge.net/viewvc/mysql-python/tags/MySQLdb-1.2.3/MySQLdb/README?revision=646 MySQLdb-1.2.3/MySQLdb/README], see the ''Building and installing'' section, - [http://mysql-python.svn.sourceforge.net/viewvc/mysql-python/tags/MySQLdb-1.2.3/MySQLdb/site.cfg?revision=646&content-type=text%2Fplain MySQLdb-1.2.3/MySQLdb/site.cfg] 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, ie 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 == #Conversion ==== Convert from MyISAM to InnoDB storage engine InnoDB is the default storage engine for MySQL since v5.5, but older databases may be defined using MyISAM. Check which tables are not already converted: {{{#!sql SELECT table_name, engine FROM information_schema.tables WHERE table_schema = DATABASE(); }}} For every table in the Trac database which is not InnoDB run the following: {{{#!sql ALTER TABLE `table_name` ENGINE = InnoDB; }}} ==== Convert to "utf8_bin" collation First modify the DB collation to ensure new tables will be created properly: {{{#!sql 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. Run the following for every table: {{{#!sql ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; }}} == Known Issues === Still Opened [[TicketQuery(keywords=~mysql&status=!closed&group=milestone)]] === Troubleshooting If you have an issue, such as `OperationalError: (2006, 'MySQL server has gone away')`, as in #3645, and your web frontend is Apache, you may have conflicting MySQL libraries. Read [mod-wsgi:ApplicationIssues#MySQL_Shared_Library_Conflicts]. Another possibility is that you've hit MySQL bug 36639, as explained in ticket:3645#comment:48. Changing collation to ''utf8'' may fail in MySQL 5.0.x on the MyISAM table type and display the "too big" indexes in some Trac tables. Upgrade to a higher version of MySQL and use InnoDB tables, see [comment:ticket:3659:5]. [[TicketQuery(keywords=~mysql&resolution=worksforme)]] Also noteworthy: [[TicketQuery(id=4378)]] 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` 1. Created user is not yet used by MySQL. Login to the MySQL server as root with `mysql -p` and type: `FLUSH PRIVILEGES;` 1. 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;` ==== Ticket numbers are not incrementing by one The database schema specifies autoincrement for several columns, including the column for ticket IDs. In MySQL, the autoincrement value is configurable and defaults to `1`. If the sequence of ticket IDs is not incrementing by 1, the [http://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html#sysvar_auto_increment_increment auto_increment_increment] value in `my.cnf` probably needs to be changed to `1`.