As a Microsoft SQL Server fan, I’ve found the move to MySQL fairly painless in all but one area: database administration. I have the excellent MySQL WorkBench which makes things a little easier (though I think thats a usability nightmare), on shared hosting however, often the only tool at your disposal is PHPMyAdmin. Now don’t get me wrong – PHPMyAdmin also does an excellent job of the day to day database work, unfortunately when it comes to import and export however, it’s a disaster.
As a test case I recently decided to export a simple database and import it on the same server with the same user credentials – I got a bunch of syntax errors. It seems PHPMyAdmin can’t even parse its own exported SQL create scripts.
After much head scratching, I finally found a set of steps to solve the syntax errors and allow the import to work.
On a side now anyone planning to use MySQL regularly with PHP should check out the PHP & MySQL Web Development book, saved me many hours!
The Solution
I’m pretty certain this is NOT the recommended way to do it but it worked for me so I figured I’d post it anyway.
Firstly, export the data as usual in to an SQL file, downloading it to your PC. I recommend selecting the ‘custom’ option and and ensuring the following:
- Untick ‘Display comments
- Tick ‘Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement’
- Untick Enclose table and column names with backquotes
Once the file is downloaded to your computer, open it in a decent text editor (not Notepad – I used Dreamweaver as it seemed to handle the very large file size well).
Add a ‘USE’ statement to the top of the exported script to identify which database you want to run the script against, for example:
USE myDatabaseName;
Run through the following formatting steps:
- Remove all commented lines
- Remove all of the instances of delimiter $$
- Replace any $$ at the end of a line with ; (semi-colon)
- Replace any $$ on their own on a line with a space
- Replace all backticks with a space
When I did this and uploaded the SQL file to PHPMyAdmin it finally worked.
Caveat: My table, column and procedure names do not include any special characters, spaces or reserved words so I was able to get away without the backticks. If you have anything unusual you will need them.
Seems to me like PHPMyAdmin’s Import/Export system really needs some work. If an a program has syntax errors when importing a script created using it’s own export system, something is definitely wrong.
