Migrating a SQLite database to MySQL

From WhyNotWiki

Jump to: navigation, search
sqlite3 db/development.sqlite '.dump'

sqlite3 db/development.sqlite '.dump' | mysql -u tyler -p... tyler

You'll probably need to tweak the dump first before importing into MySQL, so it would be better to dump to a file first, like this:

sqlite3 db/development.sqlite '.dump' > dump.sql
vim dump.sql  # Make any necessary changes
mysql -u tyler -p... tyler < dump.sql

Contents

[edit] Problem: If your MySQL database doesn't support transactions

The dump that it generates will be wrapped in a transaction, so if your target database doesn't support transactions, you'll have to remove those lines:

BEGIN TRANSACTION;
COMMIT;

[edit] Problem: MySQL doesn't like column names to be in double quotes

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"schema_info" VALUES(0)' at line 1

It would prefer the column names to be wrapped in backticks (`).

So how do we reliably convert the dump file to that? (Without inadvertently changing a " to a ` that shouldn't be changed.)

Well, I wish there were a better way, but I ended up using the venerable [sed (category)] to do this magic. (It's just a regular expression search and replace.)

To fix the inserts:

cat dump.sql | sed 's#^INSERT INTO "\(.*\)"#INSERT INTO `\1`#' > dump2.sql

To fix the "create table"s: Who am I kidding? I can't do that in sed.

http://svn.tylerrick.com/public/ruby/bin/sqlite_dump_to_mysql.rb

cat dump2.sql | ./sqlite_dump_to_mysql.rb > dump3.sql


[edit] Problem: BLOB/TEXT column 'name' used in key specification without a key length

That happened for this line:

CREATE INDEX `index_nested_set_menu_items_on_name` ON menu_items (`name`);

CREATE TABLE menu_items (..., `name` text DEFAULT NULL, ...);

Solution was easy enough: Just changed that line to:

CREATE TABLE menu_items (..., `name` varchar(255) DEFAULT NULL, ...);

[edit] Finish

mysql -u tyler -p... tyler < dump3.sql

[edit] Problem: sqlite doesn't have auto_increment??

So you may have to manually add auto_increment to each of your primary key / id columns:

mysql> alter table table_name modify id int(11) not null auto_increment;

if you don't want to see errors like this:

Mysql::Error: Duplicate entry '0' for key 1: INSERT INTO foo_table (`url`, `user_id`) VALUES('http://example.com', 0)


[edit] References

Retrieved from "http://whynotwiki.com/Migrating_a_SQLite_database_to_MySQL"
Personal tools