Trac MySQL migration

[nacin]

We're experimenting with moving Trac to MySQL. I'm going to track progress here.

The goal here is to make Trac's data accessible to outside scripts. This means we can more quickly and openly build features, tools, integration, syncing, and such. Some examples: A PHP script on WP.org can query Trac directly and then use that data on WP.org (like make/core, profiles, stats) or improve Trac directly (like JS on Trac making XHR requests to an API). While most of this is read-only, writes will also be possible, like syncing profile email changes.

Tasks:

• Write a migration script, since the ones out there aren't very good. (Done already, commit to follow.)
• Improve the MySQL schema, since Trac's default is terrible. (Done already, commit to follow.)
• Test this out on a giant Trac DB locally. (Done already.)
• Set up MySQL on the server and for Python (​systems request).
• Test this out on a small Trac in production (probably this one).
• Merge over Core Trac.
• Merge over other Tracs after working out any further kinks and evaluating/comparing performance.

[nacin]

In 155:

Add Trac MySQL schema and migrate script. see #253.

[nacin]

Included at the top of the schema SQL file is an explanation:

---

This Trac MySQL DB schema is modified for WordPress. It may work elsewhere It may work elsewhere but note that some assumptions are made, such as WP core's username length of 60 characters. Revisions are given 40 characters as that is a sha1 hash and IP address fields receive 45 characters to hypothetically handle IPv6.

Why does WordPress need a modified schema?

While Trac properly specifies BIGINT when needed, all non-integer fields are declared to be TEXT, when many need to be VARCHAR and some should be MEDIUMTEXT or LONGTEXT. (core.trac.wordpress.org required more than 64KB a few ticket descriptions.) Using TEXT even when only some words or even a few letters needed to be stored is overkill and has also been reported Trac for being terrible for query and general performance.

InnoDB and utf8_bin are both recommended specifically by Trac.

Some links:

• ​http://trac.edgewall.org/wiki/MySqlDb - includes list of known/old issues
• ​http://trac.edgewall.org/ticket/6986 - Suggested schema changes
• ​http://trac.edgewall.org/ticket/4378 - Why utf8_bin is used
• ​http://trac.edgewall.org/ticket/3673 - Length of primary keys
• ​http://trac.edgewall.org/ticket/6823 - sid field length and PK issues
• ​http://trac-hacks.org/wiki/SqliteToMySqlScript - deprecated

[nacin]

In 159:

Trac MySQL migration: Bump session ID fields to 80 characters.

We found some anonymous session identifiers that were mangled with other cookie parameters, totalling 64 or 65 characters in length. (Trac bug?)

see #253.

[nacin]

Core Trac was moved without incident using these scripts. We'll move future databases as we need them, which probably will be as we start to build more tools.

[SergeyBiryukov]

Follow-up: #257