Question

How do you convert an MS Access database to another format?

Answer 1

Many people end up with strange FogBugz problems after attempting to transfer to another format because of schema issues. If this happens to you, contact us.

For instructions on migrating your data from MS Access to SQL Server click here.

For migrating from MS Access to MySQL, read on.

1. If you are upgrading from a previous version of FogBugz and switching to MySQL, please install the upgraded FogBugz code against the old database, log in once (so FogBugz can do the upgrade) and then proceed to convert your database.
2. Backup your existing database. Make a copy of your FogBugz.mdb file.
3. Create a shell MySQL database. Follow the instructions here so you end up with just the schema and no data. i.e. make sure you run the truncate.sql script after letting fogbugz build the db.
4. Convert your Access database. Best results are obtained by using ESF Database Convert. But you can find a list of (free) conversion utilities on the MySQL site. Choose one of these programs and convert your database to MySQL format.
5. Check your new database to make sure the sSignature field in the Licenses table is of type "blob". Some conversion tools will give the sSignature field another type and you will not be able to install licenses! You will simply be prompted to install new licenses over and over again. We have seen this happen using Navicat.

Answer 2

Addendum to step 5:

To deselect Strict mode:

Open your "my.ini" file within the MySQL installation directory, and look for the text "sql-mode".

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Replace with:

# Set the SQL mode to strict
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Answer 3

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, which bullzip converts literally, which chokes MySQL
   • the Attachment table'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';