Help

In the last few weeks I had to migrate a MySQL database and it turned out to be more difficult than I thought. In the past I've used the tools that ship with MySQL, such as mysqldump and its various options. For the recent migrations that was surprisingly... impossible.

The first migration was from a latin MySQL database to a UTF encoded database. By default MySQL and the JDBC driver all use latin encoding (or they derive it from the system character set), so you better make sure that your database is using UTF8 if you want, for example, Chinese users to be able to store their data. I recommend doing this on a per-table basis, which is easy if you export the schema with Hibernate - just add an extension to your dialect. Also make sure that you set characterEncoding=UTF-8 on your JDBC connection string to initialize the SQL session properly. Note that the useUnicode=true switch is not necessary for MySQL 5.x.

The problem I had was the seamframework.org production database, which was latin encoded when it was created a year ago. I've been pushing migration back because we never had any issue with it and the manual migration with mysqldump and recode turned out not to work for me (some instructions for this if you want to try).

The second migration I was looking at was a migration from MySQL to PostgreSQL, for development and testing purposes. Now, many people use mysqldump for this, then fiddle about with its many command line options and switches (make it ANSI compatible SQL damnyou!) and then close their eyes and pray when they import the dump into Postgres. Well, that didn't work in my case because mysqldump exports bit typed columns as raw binary. You can't make it to export something like true or false or anything that you can import into a Postgres boolean type. The problem here is actually that MySQL (just like the mighty Oracle) doesn't support a true boolean datatype and that Hibernate defaults to creating a bit column for a java.lang.Boolean mapping. In retrospect, Hibernate should probably not do this on MySQL for portability reasons and use a tinyint(1) mapping - on the other hand it is fine if you always stay on MySQL.

So mysqldump didn't work in both cases, I had to find another solution. I solved it with DBUnit and a simple 20 line class:

import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.operation.DatabaseOperation;

import java.sql.Connection;
import java.sql.DriverManager;

public class Migration {

    public static final String[] TABLES = new String[]{ "FOO", "BAR", "BAZ" };

    public static void main(String[] args) throws Exception {
        System.out.println("Running Migration...");

        Class.forName("com.mysql.jdbc.Driver");
        //Class.forName("org.postgresql.Driver");

        Connection exportConnection = DriverManager.getConnection(
            "jdbc:mysql://localhost/mydb", 
            "johndoe", 
            "secret"
        );
        IDatabaseConnection exportDatabaseConnection = new DatabaseConnection(exportConnection);

        Connection importConnection = DriverManager.getConnection(
            "jdbc:mysql://localhost/mytarget?characterEncoding=UTF-8&sessionVariables=FOREIGN_KEY_CHECKS=0", 
            "johndoe", 
            "secret"
        );
        IDatabaseConnection importDatabaseConnection = new DatabaseConnection(importConnection);

        for (String table : TABLES) {
        System.out.println("Migrating table: " + table);
            QueryDataSet exportDataSet = new QueryDataSet(exportDatabaseConnection);
            exportDataSet.addTable(table, "SELECT * FROM " + table);
            DatabaseOperation.INSERT.execute(importDatabaseConnection, exportDataSet);
        }

        exportDatabaseConnection.close();
        importDatabaseConnection.close();
        System.out.println("Migration complete");

    }
}

This is the code I used to migrate from MySQL latin to MySQL UTF encoding. For the PostgreSQL migration, uncomment the driver and use a different import JDBC URL. Make sure that you disable foreign key checks for the importing SQL session as you don't know or control in which order tables and rows will be exported and imported.

9 comments:
 
15. Apr 2009, 20:05 CET | Link
chris
you don't know or control in which order tables and rows will be exported and imported.

Of course you do, you use a for on a String array...

 
15. Apr 2009, 20:23 CET | Link
chris wrote on Apr 15, 2009 14:05:
you don't know or control in which order tables and rows will be exported and imported. Of course you do, you use a for on a String array...

No, you don't. FOO could have a foreign key constraint that depends on data in BAR, BAR could have a foreign key constraint that depends on data in FOO. Welcome to the wonderful world of NULL.

 
15. Apr 2009, 20:45 CET | Link
Marcio Endo | marcioendo(AT)gmail.com
a migration from MySQL to PostgreSQL, for development and testing purposes

Just out of curiosity, what is the reasoning behind this?

I once tried PostgreSQL but ended up staying with MySQL.

 
15. Apr 2009, 21:05 CET | Link
Fernando Montaño

In order to convert a latin1 production database to UTF-8 in MySQL (4.1) I used (some weeks ago) the following alter (as example) on my database tables:

ALTER TABLE <mylatin1table> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

It worked like a charm, it preserved all data consistent.

Because our production system must work with MySQL and Informix DS (same schema) in parallel, some years ago we had to do a complete migration from MySQL data to Informix, after a lot of research, what really worked fine (even better than some commercial products I tried) was the openDBCopy tool, you just to be sure to give it enough memory (heap) accordingly to the size of your database.

Just my 2 cents.

 
04. Oct 2014, 13:14 CET | Link
possible

Making the process as simple as possible for both yourselves and the customer is the key to having a successful encounter in promoting your home. Choosing the right broker will allow you to have this encounter. Sell my house quickly

 
12. Nov 2014, 22:27 CET | Link

I'm impressed by your composition. It crucial to remark that nowadays it is hardly gentle to discover estimable merit column on the Internet. Only unique try among my skill of selecting college research paper writing service for my school wants.

 
14. Nov 2014, 00:22 CET | Link

I'm happy to be conversant accompanying your estimate being I typically succession treatises at paper writing besides sum my consumers are happy. Definitely, I won't restrain doing this whereas I promote juvenile persons to reclaim their tour. Consequently they are satisfied by their undergraduates' essence.

 
18. Dec 2014, 15:07 CET | Link

Pupils in schools across the UK and the globe will be writing letters on behalf of the cases on 9 December.