0

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

Mr Fett
  • 7,979
  • 5
  • 20
  • 21
  • Which phpMyAdmin version? – Marc Delisle Jul 28 '14 at 17:16
  • PHPMyAdmin version 4.2.5 – Mr Fett Jul 28 '14 at 17:46
  • I cannot reproduce your problem with phpMyAdmin 4.2.5, exporting and importing the sakila sample database which contains procedures. When exporting, I chose the option to generate the DROP statements, as you probably did. I am running MySQL 5.5.38 and PHP 5.4.4 on Linux. How exactly did your import (which menu)? – Marc Delisle Jul 29 '14 at 20:50

2 Answers2

1

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.

Community
  • 1
  • 1
symcbean
  • 47,736
  • 6
  • 59
  • 94
  • Hey symcbean, Thanks for this - there is a DELIMITER $$ line in the export (the error message only shows a snippet). I have literally exported and then imported the SQL file untouched (aside from adding the 'USE' statement at the beginning). – Mr Fett Jul 28 '14 at 14:31
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.

Mr Fett
  • 7,979
  • 5
  • 20
  • 21