[wp-meta] [Making WordPress.org] #253: Trac MySQL migration

[Making WordPress.org]

#253: Trac MySQL migration
--------------------+------------------
  Reporter:  nacin  |      Owner:
      Type:  task   |     Status:  new
  Priority:  high   |  Component:  Trac
Resolution:         |   Keywords:
--------------------+------------------

Comment (by 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

--
Ticket URL: <http://meta.trac.wordpress.org/ticket/253#comment:2>
Making WordPress.org <http://meta.trac.wordpress.org/>
WordPress blogging software