Access: Converting your Access Database to Another Format - FogBugz Knowledge Exchange [closed]

Access: Converting your Access Database to Another Format

2009-10-13T20:12:54Z

Official Fog Creek Notice: MS Access support is being deprecated in FogBugz 8.0.

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

Answer by FogBugz KB for Access: Converting your Access Database to Another Format

2009-10-13T20:15:15Z

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.

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.
Backup your existing database. Make a copy of your FogBugz.mdb file.
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.
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.
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 by Rich Armstrong for Access: Converting your Access Database to Another Format

2009-10-20T15:24:30Z

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:

Use FogBugz to create a new database. You don't need to empty it.
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.
Import the converted .sql file into a new database in the same install as the schema.
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)
Run the generated script and watch all the data get copied from the makeshift MySQL database into the fresh, valid schema.
(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';

Answer by unknown (google) for Access: Converting your Access Database to Another Format

2009-11-18T20:33:11Z

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 by jpeacock for Access: Converting your Access Database to Another Format

2011-10-21T20:11:52Z

I am in the process of importing our Access database into MySQL, and have run into a few more quirks than the posts above mentioned. Hopefully this information will help other database newbies like me who are in the same boat.

I am migrating to MySQL Ver 14.14 Distrib 5.5.16, for Win64 (x86) and used Bullzip 3.0.0.148 to convert the database.

Here is how I fixed the problems which Rich Armstrong identified in his answer.

the dtOpened in the Bug table sometimes has a weird default of zero in Access, which bullzip converts literally, which chokes MySQL

In the converted file, dtOpened is defined as:

`dtOpened` DATETIME DEFAULT '0',

I simply removed the default, since none of the other DATETIME fields had one:

`dtOpened` DATETIME,

In addition, I got the following error on a large number of table column definitions:

BLOB/TEXT column can't have a default value

I fixed it by searching for LONGTEXT, LONGBLOB and removed the default value, just like above.

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.

In the converted file they were incorrectly formatted as:

0x0910022...

I changed them to be formatted as below:

X'0910022...'

using the regex (exact syntax will vary from application to application):

s/(INSERT INTO.*VALUES.*,\s)0x([0-9a-fA-F]+)/\1X'\2'/

There were also some other fields that are also declared LONGBLOB with the same quoting problem:

Licenses.sSignature*
IndexFilePage.sPage

The regex above fixed them as well.

If you have large BLOB fields (such as file attachments > 1MB), then you will need to increase the mysql maximum_packet_size to import them: http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html

Finally when importing it is best to output the results to a log file, as errors can easily scroll off the screen. In windows this can be done using:

mysql -uUSER -pPASSWORD -e "source MY_CONVERTED_DATABASE.sql" 2>&1 > sql_import_results.txt

Then review that log file. If there are any errors fix the problem, drop the temporary database that was just created (movedb in my case), and then rerun the script. Most of the importing problems I encountered would not have been discovered until much later had I not done this, as everything appeared to be working on the surface.