Plesk Question posted in
The official documentation for Plesk Obsidian can be found here.

PHPMyAdmin Import/Export Same Server Fails – Plesk

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search