Solving PHPMyAdmin Import/Export Syntax Errors

Solving PHPMyAdmin Import/Export Syntax Errors

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!

phpMyAdmin Import Export OptionsThe 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:

  1. Remove all commented lines
  2. Remove all of the instances of delimiter $$
  3. Replace any $$ at the end of a line with ;  (semi-colon)
  4. Replace any $$ on their own on a line with a space
  5. 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.

Bob McKay

About Bob McKay

Bob McKay works at Perfect Image, is a father, programmer and a self confessed techie-geek type.

Disclosure Policy

Bob on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *