| 3 | | **Note:** You can also use [TracHacks:TracMigratePlugin] as a conversion tool. |
| 4 | | |
| 5 | | You can convert a Trac SQLite database to a MySQL database as follows: |
| 6 | | |
| 7 | | {{{ |
| 8 | | % echo ".dump" | sqlite3 trac.db > trac.sql |
| 9 | | % mysql tracdb < trac.sql |
| 10 | | }}} |
| 11 | | There could be syntax errors that have to be manually fixed in the file trac.sql. |
| 12 | | |
| 13 | | 1. Use `trac-admin initenv` to create the database structure |
| 14 | | 1. Use `mysqldump --no-data` to dump the database structure into trac.mysql.sql |
| 15 | | 1. Use `TRUNCATE` to get rid of your default data, it will be replaced by the import: |
| 16 | | {{{ |
| 17 | | TRUNCATE `attachment` ; |
| 18 | | TRUNCATE `auth_cookie` ; |
| 19 | | TRUNCATE `component` ; |
| 20 | | TRUNCATE `cache` ; |
| 21 | | TRUNCATE `enum` ; |
| 22 | | TRUNCATE `milestone` ; |
| 23 | | TRUNCATE `node_change` ; |
| 24 | | TRUNCATE `permission` ; |
| 25 | | TRUNCATE `report` ; |
| 26 | | TRUNCATE `repository` ; |
| 27 | | TRUNCATE `revision` ; |
| 28 | | TRUNCATE `session` ; |
| 29 | | TRUNCATE `session_attribute` ; |
| 30 | | TRUNCATE `system` ; |
| 31 | | TRUNCATE `ticket` ; |
| 32 | | TRUNCATE `ticket_change` ; |
| 33 | | TRUNCATE `ticket_custom` ; |
| 34 | | TRUNCATE `version` ; |
| 35 | | TRUNCATE `wiki` ; |
| 36 | | }}} |
| 37 | | 1. Use sqlite `.dump` to dump the structure + data from sqlite into trac.sqlite.sql, unfortunately there's no option to only dump the data. |
| 38 | | 1. Remove all the structure ('create table' and 'create index') from trac.sqlite.sql |
| 39 | | You could use this simple Perl script or the more complete Python script mentioned below: |
| 40 | | {{{ |
| 41 | | $ cat cleansql |
| 42 | | #!/usr/bin/env perl |
| 43 | | |
| 44 | | $_ = join("", <>); |
| 45 | | |
| 46 | | $_ =~ s/^CREATE.*?\);$//smgi; |
| 47 | | |
| 48 | | print $_; |
| 49 | | |
| 50 | | # NEOF |
| 51 | | |
| 52 | | $ ./cleansql trac.sqlite.sql > trac.sqlite.sql.dataonly |
| 53 | | }}} |
| 54 | | 6. Concatenate trac.mysql.sql and trac.sqlite.sql.dataonly into trac.sql |
| 55 | | {{{ |
| 56 | | cat trac.mysql.sql trac.sqlite.sql.dataonly > trac.sql |
| 57 | | }}} |
| 58 | | ---- |
| 59 | | You should open the trac.sql file in a text editor of choice and replace all {{{\}}} with {{{\\}}} as otherwise, these will be interpreted as escape symbols by MySQL. |
| 60 | | ---- |
| 61 | | SQLite uses utf8 as default. If you have your server configured with encoding other than utf8 and you can't change that, you are going to get your data badly encoded. While you may have already set your MySQL database to use utf8 as the default character set, you could still have problems when importing data. As an example for the console client: |
| 62 | | |
| 63 | | {{{ |
| 64 | | mysql trac_db -h host -u user -p --default-character-set=utf8 < trac.sql |
| 65 | | }}} |
| 66 | | |
| 67 | | An alternative to the cleansql above: |
| 68 | | {{{ |
| 69 | | |
| 70 | | $ cat cleansql.py |
| 71 | | #!/usr/bin/env python |
| 72 | | |
| 73 | | import sys |
| 74 | | import re |
| 75 | | |
| 76 | | file = sys.stdin.read() |
| 77 | | file = re.sub(r'(CREATE (TABLE|INDEX)[^;]*|COMMIT|BEGIN TRANSACTION);', '', file) |
| 78 | | file = re.sub(r'INSERT INTO "([^"]+)"', lambda m: 'INSERT INTO `%s`' % m.groups(1), file) |
| 79 | | sys.stdout.write(file) |
| 80 | | |
| 81 | | $ ./cleansql.py < trac.sqlite.sql > trac.sqlite.sql.dataonly |
| 82 | | }}} |
| 83 | | ---- |
| 84 | | One caveat is that the reports stored in the report table might not work as expected. You can solve this by: |
| 85 | | 1. Dropping the report table from my newly migrated Trac MySQL database |
| 86 | | 1. Dumping the report table from the temporary Trac MySQL database to get the schema in steps 1 and 2 at the top of the page |
| 87 | | 1. Loading the sql dump back in to my newly migrated Trac MySQL database |
| 88 | | |
| 89 | | This will only restore the default Trac queries. If you had custom queries, you will still need to re-write them if they don't work right on MySQL. |
| 90 | | |
| 91 | | {{{ |
| 92 | | mysql -D tracmysql -e "drop table report" |
| 93 | | mysqldump -D temptracdbforschema report > report.sql |
| 94 | | mysql -D tracmysql < report.sql |
| 95 | | }}} |
| 96 | | You need to get the full report table from the MySQL database you created for its schema in to your new Trac MySQL database. This also assumes that you did not have any custom queries and were just using the defaults. |
| 97 | | ---- |
| 98 | | One alternative to dropping the reports is to log into trac as a privileged user that can edit the reports, call up one report after the other, click on EDIT and look out for {{{CAST(p.value AS int)}}}. Change this to {{{CAST(p.value AS unsigned)}}} and it should work. This might be easier than recreating all your custom reports. |
| 99 | | |
| 100 | | This page contains a summarised procedure: http://www.pokesoft.net/blog/?p=473 |
| 101 | | |
| 102 | | This is a modified Python {{{cleansql}}} script, which takes in both the MySQL table structure SQL and the SQLite SQL. It will remove the {{{PRAGMA}}} statement and convert {{{CAST(p.value AS int)}}} to {{{CAST(p.value AS signed)}}}. It also fixes the backslash escaped quotes. |
| 103 | | |
| 104 | | ---- |
| 105 | | |
| 106 | | None of the scripts here deal with quoting of ' characters caused by sqlite/mysql differences. Here is a Perl script for SQLite to MySQL migration, which handles quoting very accurately: |
| 107 | | {{{ |
| 108 | | print "BEGIN;\n"; |
| 109 | | print "TRUNCATE `$_`;\n" for (qw(attachment auth_cookie cache component enum milestone node_change permission report |
| 110 | | repository revision session session_attribute system ticket ticket_change ticket_custom version wiki)); |
| 111 | | my ($inq,$del); |
| 112 | | sub qut { local $_=shift; ($inq ? (/'/ ? "\\" : ($inq=0,"'")) : "'".(/'/ ? '' : ($inq=1,''))).$_ } |
| 113 | | while (<>) { |
| 114 | | $del=!/;/, next if ($del); |
| 115 | | s/^(COMMIT|BEGIN TRANSACTION);//mg; |
| 116 | | $del=1, next if (/^CREATE (TABLE|INDEX)/); |
| 117 | | s/INSERT INTO "([^"]+)"/INSERT INTO `$1`/g; |
| 118 | | s/\\/\\\\/g; |
| 119 | | s/'(.|$)/qut($1)/ge; |
| 120 | | s/(INSERT INTO `milestone` .*),0,NULL/$1,NULL/g; |
| 121 | | print; |
| 122 | | } |
| 123 | | print "COMMIT;\n" |
| 124 | | }}} |
| | 3 | [th:TracMigratePlugin] is suggested for converting a SQLite database to MySQL. |
