CODEWITHSUNDEEP
mysqlwindowsphpmyadminplesk
Mr Fett
Mr Fett

Reputation: 8509

PHPMyAdmin Import/Export Same Server Fails

I've been trying to work out the best way of copying structure & data from one database to another but the PHPMyAdmin export seems to churn out pretty poor scripts. The most obvious example is if I export a database (structure & data) then try and re-import on the same server (using the drop tables function to prevent clashes), I get a syntax error!? I would have thought PHPMyAdmin would be able to parse its own exports.

The error I get is:

Error SQL query:

$$

DROP PROCEDURE IF EXISTS `CMS_identifyFileType`$$
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$

DROP PROCEDURE IF EXISTS `CMS_identifyFileType`' at line 1

It looks odd to me that the script has weird apostrophes?

Does anyone have any tips on what I might be doing wrong? I have to manually add the 'use myDatabasename;' to the script to get it to work, not sure if I'm missing some other stuff.

My MySQL version is 5.1.73-community running on a Windows Server 2008 R2 server.

Thanks

Bob

Upvotes: 0

Views: 825

Answers (2)

Mr Fett
Mr Fett

Reputation: 8509

I have no idea if this is the correct way to do it but in the end this worked for me:

I did the export using the default options.

I opened the file in Dreamweaver (it seems to handle the length file better than Notepad++)

Added the 'USE mydatabaseName;' to the beginning

I removed all commented lines

I removed the 'delimiter $$' lines

I replaced any $$ at the end of lines with ;

I replaced any orphan $$ (on their own on a line) with a space

I replaced all backticks with a space

Uploaded the SQL file to PHPMyAdmin and it finally worked (I tried not doing each of the steps above and if I missed anyone of them, I got one of a number of different flavor syntax errors). Seems to me like PHPMyAdmin's Import/Export system really needs some work.

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.

Upvotes: 0

symcbean
symcbean

Reputation: 48357

I suspect you've amended the output file.

For table and index definitions, mysqladmin uses the default delimiter ';' but for procedures and functions it uses '$$'. The DBMS needs to know that the delimiter has changed - hence in the export file there should be a line like this between a table definition and a procedure definition:

DELIMITER $$

BTW the weird apostrophes around (for example) CMS_identifyFileType are to be expected in MySQL - see Using backticks around field names for a discussion.

Upvotes: 1

Related Questions