I am working on fixing in the process of importing our Access database into MySQL, and have run into a file converted using 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 and have some questionsto 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
Do you recommend just removing
I simply removed the default, like below, or changing it to something else?since none of the other DATETIME fields had one:
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 are currently were incorrectly formatted as:
Is this the correct quoting format
I changed them to be formatted as below:
I couldn't find anything authoritative on
using the MySQL website, but saw this on a few discussion forums.regex (exact syntax will vary from application to application):
s/(INSERT INTO.*VALUES.*,\s)0x([0-9a-fA-F]+)/\1X'\2'/There are were also some other fields that are also declared LONGBLOB with the same quoting problem. I assume these need to be fixed as well:
Sorry for posting this
The regex above fixed them as an answer but well.
If you have large BLOB fields (such as file attachments > 1MB), then you will need to increase the forum software wouldn't accept 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 comment log file, as errors can easily scroll off the screen. In windows this longcan be done using:
mysql -uUSER -pPASSWORD -e "source MY_CONVERTED_DATABASE.sql" 2>&1 > sql_import_results.txtThen review that log file. If there are any errors fix the problem, nor allow formattingdrop 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.