My misadventures while migrating a rails database from Sqlite3 to Mysql

Last week I needed to migrate a database from sqlite to mysql. This is a legacy rails project that uses 2 databases. One of them was completely managed by our customer (data, schema and server machine) on the Oracle platform. Recently we migrated this database from Oracle to sqlite, so at the moment the project is using two sqlite3 databases. We now needed to use mysql.

No schema was provided for the second db. My first option was to use the taps gem for the migration, I had used this gem on heroku in the past to import remote databases to my local machine and vice versa, but while looking at it I discovered that the migration could be even easier using the sequel gem, which by the way taps relies on. So I created the empty mysql db and entered this command on the terminal:

sequel -C sqlite://db/oracle_dump.sqlite3 mysql://root:@localhost/oracle_dump

Unfortunately, the process failed.

I spent some time trying to figure out what went wrong with no luck. Eventually, while inspecting the db schema, I found out there were a few primary key column names that were empty strings (yes! “” was the name of the column) and this was for sure an issue when trying to replicate the schema on mysql.

After renaming the empty columns to “id” I was able to fully recreate the schema on mysql with the command above, but the following step (data import) failed miserably: only a single record was created. The error reported was:

Error: Sequel::UniqueConstraintViolation: Mysql::Error: Duplicate entry '1' for key 'PRIMARY'

How could this be possible? All record ids in the source table were unique, I knew this for sure, it was fine with sqlite and oracle! But wait, the first record had a id value of 0! That was the reason for the failure: when mysql received the record data with id zero, it changed the id to “1” because by default zero is not allowed as primary key value. Later, when it was the turn of the actual record with id “1” to be inserted, mysql complained that the “1” key number was already taken. The solution was to temporarily update a mysql global variable to allow the insertion of zero values for primary keys: the variable named sql_mode.

I checked the current value of the variable:

mysql> SHOW GLOBAL VARIABLES WHERE Variable_name = "sql_mode";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0,00 sec)

And added the NO_AUTO_VALUE_ON_ZERO value to it:

SET GLOBAL SQL_MODE = "NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION";

After this final fix the sequel import process worked perfectly. I later returned the global variable to its original value:

SET GLOBAL SQL_MODE = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION";

So, be warned: empty column names and zero value primary keys can make your database data transfer a bit though 😉