WordPress.org

Making WordPress.org

Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#253 closed task (fixed)

Trac MySQL migration

Reported by: nacin Owned by:
Milestone: Priority: high
Component: Trac Keywords:
Cc:

Description

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.

Change History (6)

#1 @nacin
6 years ago

In 155:

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

#2 @nacin
6 years ago

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:

#3 @nacin
6 years ago

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.

#4 @nacin
6 years ago

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.

#5 @nacin
6 years ago

  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.