= MySQL/MariaDB and the MySQLdb Python bindings for MySQL/MariaDB =

 ''The primary supported backends are PostgreSQL and SQLite. MySQL/MariaDB is supported by Trac 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/MariaDB successfully, so it ''might'' work for you. ''

== Requirements ==
=== MySQL/MariaDB ===

 '' '''Warning:''' 
 The following applies to Trac starting with [milestone:0.11.1]. [[br]]
 For earlier versions of Trac, please see MySqlDb@22#MySQL.

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 [comment:ticket:8067:5] and [http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html MySQL docs]).

Proper database can be created with the MySQL monitor, like that:
{{{
CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; 
}}}

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 [https://sourceforge.net/projects/mysql-python/ 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 [googlegroups:trac-users:e291b3ecb4a990a1 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 [https://sourceforge.net/tracker/?group_id=22307&atid=374932 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)
 1. Created user is not yet used by MySQL (Login to the MySQL server(as root): mysql -p and type FLUSH PRIVILEGES;)
 1. 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 ===
[[TicketQuery(keywords=~mysql&status=!closed&group=milestone)]]

=== 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: mod_wsgi: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 [milestone:0.10.4]. As a workaround it has been [comment:ticket:3659:5 recommended] to use InnoDB tables instead of MyISAM where this limitation is absent.

[[TicketQuery(keywords=~mysql&resolution=worksforme)]]

(corresponding [query:keywords=~mysql&status=!closed 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 ====
Users have reported success with the [wiki:SqLiteToMySql following method].