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 [easyazon_link asin=”0672329166″ locale=”US” new_window=”yes” nofollow=”default” tag=”bomc-21″ add_to_cart=”default” cloaking=”default” localization=”default” popups=”no”] PHP & MySQL Web Development [/easyazon_link] 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 is a Founder of Seguro Ltd, a full time father and husband, part-time tinkerer-with-wires, coder, Muay Thai practitioner, builder and cook. Big fan of equality, tolerance and co-existence.

Disclosure Policy

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.