Question

I just had great results with the MySQL conversion utility offered by bullzip.com. Really great.

No matter what utility you use, if you have odd behavior afterward, contact us. This is an ongoing answer that will evolve as I discover wrinkles and strategies around them.

Here's my current strategy:

1. Use FogBugz to create a new database. You don't need to empty it.
2. Use this MSAccess to MySQL converter. This has some quirks:
   • the dtOpened in the Bug table sometimes has a weird default of zero in Access, mostly around which bullzip converts literally, which chokes MySQL
   • the Attachment tabletable's sData field seems to come over without quotes around the data. I needed to do a regex and use TextPad to correct this.
   • the BugEvent table's s field exports as TEXT, but needs to be LONGTEXT to come across correctly.
3. Import the converted .sql file into a new database in the same install as the schema.
4. Use the script below to query the information_schema database of MySQL and create another script that will pull all the data from the old database (bullzip_import) to the new one (fogbugz_created)
5. Run the generated script and watch all the data get copied from the makeshift MySQL database into the fresh, valid schema.
6. (Where necessary) Dump the database and ship off to the client!

Heres the script:

SET @old_database = 'bullzip_import';
SET @new_database = 'fogbugz_created';

SELECT CONCAT(
  "SELECT '",TABLE_NAME, "'; ",
  " TRUNCATE ",@new_database,".", TABLE_NAME,"; ",
  " INSERT INTO ", @new_database,".",TABLE_NAME,"(", GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION), ") ",
  " SELECT ", GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION), " FROM ",@old_database,".", TABLE_NAME,"; ",
  " SHOW WARNINGS;") AS Statement
FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = @new_database
      AND TABLE_NAME NOT LIKE ('%index%')
      AND TABLE NAME NOT LIKE ('%license%') 
      AND COLUMN_NAME NOT LIKE 'FILTER_%'
    GROUP BY TABLE_NAME
INTO OUTFILE 'c:\\dump.sql'; 

The problem I'm having right now is that the bullzip converter doesn't seem to do LONGBLOB columns correctly.