Version 50 (modified by 14 years ago) ( diff ) | ,
---|
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. Some users reported that MariaDB variant of MySQL works as well, but again, no guarantees.
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. 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).
A proper database can be created with any MySQL client, like this:
CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_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 you are setting new collation on old database, make sure that columns 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.2, which must be compiled in thread-safe client mode. Earlier versions are known to be problematic. The 1.2.3 pre-versions seem to work fine (did somewhat limited testing for 1.2.3c1).
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:
- 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)
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:
- MySQLdb-1.2.2/MySQLdb/README, see the Building and installing section,
- MySQLdb-1.2.2/MySQLdb/site.cfg
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 DB collation to ensure new tables will be created properly:
ALTER DATABASE `trac_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
Then modify collation for tables. The command below changes default collation for new columns and converts old columns as well. Issue it 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-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:
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 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)
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:
- Try first to login on the command-line using the user tracuser (mysql -p -h localhost -u tracuser)
- Created user is not yet used by MySQL (Login to the MySQL server(as root): mysql -p and type FLUSH PRIVILEGES;)
- 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;)