SQL Server migrate to MySQL generateChangeLog DB snapshot too much

General Discussion
1

Hello, in a previous posting on this same topic I got very good instructions about a similar migration from MySQL to SQL Server. The generateChangeLog DB snapshot worked by my brief inspection of the very large (1.7 MB) db.changelog.xml file output from the liquibase commandline (see included below).

The snapshot is very nice but ultimately what I need is not just migration but I need to refactor for the target database (MySQL). Briefly, the source DB (SQL Server) is a 800+ table and view denormalized nightmare. What are my options?:

  1. hand edit the db.changelog.xml (would take a few weeks @ fulltime).
  2. RTFM for special parameters to constrain the db.changelog.xml file to just those tables and columns desired?
  3. Implement known methods on how to integrate refactoring with the migration in parallel. SQL Server --> MySQL (tablename changes, column-name changes, datatype changes, schema changes).

liquibase commandline: liquibase --changeLogFile=com/sexingtechnologies/bullseye/db.changelog.xml generateChangeLog

liquibase.properties:

#liquibase.properties
driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
classpath: …/liquibase/lib/sqljdbc4.jar
url: jdbc:sqlserver://10.1.0.252:6403;DatabaseName=stprod
username: sa
password:

liquibase generateChangeLog errors:

david@sex-cess:~/dev/st/elblanco_prod$ liquibase --changeLogFile=com/sexingtechnologies/bullseye/db.changelog.xml generateChangeLog
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateDatabase: Missing dependency org/hibernate/cfg/Configuration
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateGenericDialect: Missing dependency org/hibernate/dialect/Dialect
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.snapshot.core.HibernateDatabaseSnapshotGenerator: Missing dependency org/hibernate/dialect/Dialect
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateDatabase: Missing dependency org/hibernate/cfg/Configuration
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateGenericDialect: Missing dependency org/hibernate/dialect/Dialect
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.snapshot.core.HibernateDatabaseSnapshotGenerator: Missing dependency org/hibernate/dialect/Dialect
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateDatabase: Missing dependency org/hibernate/cfg/Configuration
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateGenericDialect: Missing dependency org/hibernate/dialect/Dialect
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.snapshot.core.HibernateDatabaseSnapshotGenerator: Missing dependency org/hibernate/dialect/Dialect
INFO 4/27/10 10:56 AM:liquibase: Reading tables for sa @ jdbc:sqlserver://10.1.0.252:6403;xopenStates=false;trustServerCertificate=false;
sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;loginTimeout=15;lockTimeout=-1;
lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=stprod;applicationName=Microsoft SQL Server JDBC Driver; (Default Schema: dbo) …
INFO 4/27/10 10:56 AM:liquibase: Reading views for sa @ jdbc:sqlserver://10.1.0.252:6403;xopenStates=false;trustServerCertificate=false;
sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;loginTimeout=15;lockTimeout=-1;
lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=stprod;applicationName=Microsoft SQL Server JDBC Driver; (Default Schema: dbo) …
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateDatabase: Missing dependency org/hibernate/cfg/Configuration
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateGenericDialect: Missing dependency org/hibernate/dialect/Dialect
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.snapshot.core.HibernateDatabaseSnapshotGenerator: Missing dependency org/hibernate/dialect/Dialect
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateDatabase: Missing dependency org/hibernate/cfg/Configuration
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.database.core.HibernateGenericDialect: Missing dependency org/hibernate/dialect/Dialect
WARNING 4/27/10 10:56 AM:liquibase: Could not configure extension class liquibase.snapshot.core.HibernateDatabaseSnapshotGenerator: Missing dependency org/hibernate/dialect/Dialect
INFO 4/27/10 10:57 AM:liquibase: Reading foreign keys for sa @ jdbc:sqlserver://10.1.0.252:6403;xopenStates=false;trustServerCertificate=false;
sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;
encrypt=false;disableStatementPooling=true;databaseName=stprod;applicationName=Microsoft SQL Server JDBC Driver; (Default Schema: dbo) …
INFO 4/27/10 10:59 AM:liquibase: Reading primary keys for sa @ jdbc:sqlserver://10.1.0.252:6403;xopenStates=false;
trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;
loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=stprod;
applicationName=Microsoft SQL Server JDBC Driver; (Default Schema: dbo) …

2

To create my cleanup script, the general idea is to write an XML parser that uses xpath to find things that I know need to be changed (data types, view defintiions,etc) and change them.  Building up this
script was an iterative process of:

  1. backup original database to changelog
  2. write script
  3. run script to generate fixed changelog
  4. run fixed changelog against target database
  5. watch for errors
  6. delete fixed changelog, goto 2

You’ll probably need a few iterations, but you should be able to find everything you need to change.  How clean you make that conversion script will depend on if the process is one time or something you need to support long-term.

As far as making structural changes (foreign keys, normalization) beyond simply moving the database from one to the other goes, there won’t be anything automated you can do there.  Fortunately, that is what liquibase is actually for.  Once you have you database in your initial state, you can start adding changeSets to clean up your database, one refactoring at a time.  There are some higher-level refactorings like “introduce lookup table” that may help with the normalization process.

Nathan

3

Hello Nathan, I have your Java code snippet working but with a couple of small Exceptions. Tim won’t be back in touch until next week and I would for the sake of management like to show some progress on this issue. I have hacked your Java code snippet into a simple Java command-line app. I am actutally running it in Eclipse for the moment with the hardwired resources and paths changed for my environment. The so-called changelog backup file is being created against the correct parsing of the input changelog file. The only problem is the output changelog backup file has a zero byte length. I would appreciate If you could shed any light on the issue. Please find included below the Console exception messages displayed when your application is executed. Thanks in advance and regards, David.

java.lang.InstantiationException: com.sun.org.apache.xerces.internal.dom.DeferredElementImpl
Continuing …
java.lang.Exception: XMLEncoder: discarding statement XMLEncoder.writeObject(DeferredElementImpl);
Continuing …

4

Are you doing a flush() on the FileOutputStream()  Sometimes it won’t write to disk until you call that, even if you call close() correctly.

Nathan

5

Hello Nathan, thnx 4 the reply. The final Try/Catch is included below. I added the .flush() just b4 the .close() but nochange. Still getting the odd Exception: java.lang.InstantiationException: com.sun.org.apache.xerces.internal.dom.DeferredElementImpl Continuing …

FileOutputStream dbBackupOutputStream;
try {
dbBackupOutputStream = new FileOutputStream(new File(outputDir, “stprod_changelog_backup.xml”));
XMLEncoder xenc = new XMLEncoder(dbBackupOutputStream);
xenc.writeObject(doc.getDocumentElement());
dbBackupOutputStream.flush();
dbBackupOutputStream.close();
} catch (FileNotFoundException e)

6

It seems right.  What is the full exception?  It looks like a classloader or xml parser version issue.

Nathan