MySQL default scheme for Trac

[Dmitry Shurupov]

Hi all!

After converting SQLite Trac database to MySQL I've got a lot of
troubles. Googling showed me that it hadn't been just my problems (with
primary keys and so)... So, I came out with a solution for this stuff.
I've created a simple MySQL database scheme for Trac with more accurate
data types (the main thing is using MySQL VARCHARs instead of "ugly"
SQLite TEXT that caused a lot of problems).

My MySQL dump is attached and accessible via Web:
http://share.auditory.ru/2009/Dmitry.Shurupov/trac/trac_mysql_scheme.sql

(Warning: it's not a really GOOD scheme -- it's just a quick hack that
really helped me for a few times.)

I think it would be a good idea to include such kind of dump into the
Trac default installation.

--
Dmitry Shurupov,
CJSC TrueOffice (www.trueoffice.ru)

[anatoly techtonik]

Excuse me, could you be more specific about the problems?
It is easier to estimate the changes brought by dump if the are look
like diff with existing structure.

--
--anatoly t.

[Dmitry Shurupov]

4 months have passed, but I still want to answer ;-)

The main problem in the default MySQL scheme lays in primary keys. Trac
gives us something like "PRIMARY KEY
(`type`(111),`id`(111),`filename`(111))" in every table. It means that
we have a primary key combined of table fields' parts -- it's _not_
always unique. I can't recollect which table's primary keys made me to
start all this stuff, but the error was about primary key duplicate.

And the second thing is that creating table with a lot of TEXT fields
(like Trac does) will slow down the performance amazingly. Of course, it
won't appear on little databases, but MySQL isn't SQLite, so it can be
used for something more great...

P.S. After applying this scheme to another few Trac installations I've
updated it a bit. New version is available here:
http://shurupov.ru/pub/dev/trac/trac_mysql_scheme.sql

В Втр, 16/12/2008 в 11:31 +0200, anatoly techtonik пишет:

Dmitry Shurupov,
TrueOffice (www.trueoffice.ru)

[anatoly techtonik]

Another 6 and you haven't got any replies except mine. The reason for
that is that your problem is a little bit obscure. You did not provide
any proof how it is possible to get duplicated keys or some metrics
about DB performance before and after conversion.

MySQL DB format is now "officially supported". At least it made its
way into main distribution. There are no dumps for every database -
all tables are created dynamically, so if you want to improve it -
feel free to submit a patch for trac\db\mysql_backend.py But first
you would need to prove that this code bloat is worthy to be added.

Regards,

--anatoly t.



2009/3/16 Dmitry Shurupov <dmitry....@trueoffice.ru>: