Changes between Version 19 and Version 20 of SqLiteToMySql
- Timestamp:
- Jan 21, 2015, 7:32:01 PM (9 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
SqLiteToMySql
v19 v20 1 = Convert Trac DB, from SQLite to MySQL =1 = Convert Trac DB, from SQLite to MySQL 2 2 3 Th is is a copy from the mailing list ...3 The easiest way to convert a Trac SQLite database to a MySQL database is as follows: 4 4 5 5 {{{ … … 7 7 % mysql tracdb < trac.sql 8 8 }}} 9 There could be syntax errors that would have 10 to be manually fixed in the file trac.sql. 9 There could be syntax errors that have to be manually fixed in the file trac.sql. 11 10 12 1. Use trac-admin initenvto create the database structure13 2. Use mysqldump --no-datato dump the database structure into trac.mysql.sql14 3. Use truncate to get rid of your default data -it will be replaced by the import:11 1. Use `trac-admin initenv` to create the database structure 12 1. Use `mysqldump --no-data` to dump the database structure into trac.mysql.sql 13 1. Use `TRUNCATE` to get rid of your default data, it will be replaced by the import: 15 14 {{{ 16 15 TRUNCATE `attachment` ; … … 34 33 TRUNCATE `wiki` ; 35 34 }}} 36 4. Use sqlite .dump to dump the structure + data from sqlite into trac.sqlite.sql (unfortunately there's no option to only dump the data).37 5. Remove all the structure ('create table' and 'create index') from trac.sqlite.sql38 You could use this simple perl script (or the more complete python script mentioned below):35 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. 36 1. Remove all the structure ('create table' and 'create index') from trac.sqlite.sql 37 You could use this simple Perl script or the more complete Python script mentioned below: 39 38 {{{ 40 39 $ cat cleansql … … 56 55 }}} 57 56 ---- 58 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.57 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. 59 58 ---- 60 One thing to note is that SQLite uses utf8 as default. If you have your server configure with encoding other than utf8 and you can't change that, you are going to get your data bad encoded. 61 62 Despite the fact that you may probably already set your MySQL database to use utf8 as a default character set, you could have problems when you import data. It's very important to keep in mind that your connection encoding counts. So be sure that whatever software you use as client you set your charset as utf8. As an example for the console client: 59 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: 63 60 64 61 {{{ 65 62 mysql trac_db -h host -u user -p --default-character-set=utf8 < trac.sql 66 63 }}} 67 ----68 64 69 An alternative to the cleansql above (that works a bit better):65 An alternative to the cleansql above: 70 66 {{{ 71 67 … … 84 80 }}} 85 81 ---- 86 One gotcha for all this is that the reports stored in the report table might not work as expected. I solvedthis by:87 1. Dropping the report table from my newly migrated trac mysql db88 2. Dumping the report table from the temporary trac mysql db I created to get the schema(in steps 1 and 2 at the top of the page)89 3. Loading the sql dump back in to my newly migrated trac mysql db82 One caveat is that the reports stored in the report table might not work as expected. You can solve this by: 83 1. Dropping the report table from my newly migrated Trac MySQL database 84 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 85 1. Loading the sql dump back in to my newly migrated Trac MySQL database 90 86 91 One catch is that this will only restore the default trac queries. If you had custom queriesyou will still need to re-write them if they don't work right on MySQL.87 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. 92 88 93 89 {{{ … … 96 92 mysql -D tracmysql < report.sql 97 93 }}} 98 You need to get the full report table from the mysql db you created for its schema in to your new mysql trac db. This also assumes that you did not have any custom queries and were just using the defaults.94 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. 99 95 ---- 100 One alternative to dropping the reports is to log into trac with 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 mySQL should be happy. This might be easier than recreating all your custom reports.96 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. 101 97 ---- 102 98 103 I used: 104 http://trac-hacks.org/wiki/SqliteToMySqlScript 99 You can also use [TracHacks:TracMigratePlugin] as a conversion tool. 105 100 106 Th is worked really well... painless. The only issues you might run into are with character collation on your database. With the default latin1 character set and latin1 collation, /path/to/file and /path/to/FILE are identical, potentially causing index collisions. It is highly advised that youcreate your database with UTF8 binary collation '''and''' set the server's default table type to InnoDB:101 The only issues you might run into are with character collation on your database. With the default latin1 character set and latin1 collation, `/path/to/file` and `/path/to/FILE` are identical, potentially causing index collisions. Therefore create your database with UTF8 binary collation '''and''' set the server's default table type to InnoDB: 107 102 108 103 * in my.cnf: … … 121 116 ---- 122 117 123 This page helped me a lot with my migration. I have a summerised procedure here: http://www.pokesoft.net/blog/?p=473118 This page contains a summarised procedure: http://www.pokesoft.net/blog/?p=473 124 119 125 I've also modified the Python {{{cleansql}}} script, which will now take 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 will also try to fix the backslash escaped quotes(!). Not the best script but it worked for me :) 120 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. 126 121 127 122 ---- 128 123 129 None of the scripts here deal with quoting of ' characters caused by sqlite/mysql differences. Here is my perl script for sqlite to mysql migrate, which handles quoting very accurately:124 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: 130 125 {{{ 131 126 print "BEGIN;\n";
