0

This specific machine is running MySQL version 5.0.27.

I have tried to get a full dump of this database several times and each time it bombs out. Tried getting just a structure dump of it and this is the immediate output:

mysqldump: Got error: 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 '����������� FOR EACH ROW BEGIN
����������������������� DECLARE strCity VARCHAR(1' at line 2 when using LOCK TABLES

Any insight to this would be helpful. I've tried running this directly after restarting mysqld to no avail.

Aside from running through each(103) tables looking for the table it's bombing on are there any signs I can look for that might lead my to the issue?

Thanks!

  • Are you using mysqldump on that specific machine or are you using the mysqldump of a higher version ??? – RolandoMySQLDBA Jun 24 '15 at 13:46
  • Question: How many databases are you dumping (SELECT COUNT(1) DBCount FROM information_schema.schemata;) ??? How many tables (SELECT COUNT(1) TableCount FROM information_schema.tables;) ??? – RolandoMySQLDBA Jun 24 '15 at 13:56
  • I'm local on the machine running mysqldump so the version should be the same. It's only 1 database with 103 tables. – Sezotove Jun 24 '15 at 14:00
  • Do You Have Stored Procedures ??? – RolandoMySQLDBA Jun 24 '15 at 14:01
  • Not to my knowledge. How can I verify this? – Sezotove Jun 24 '15 at 14:04
0

Based on the error message given, there is a troublemaking table that has a trigger.

You may have to perform some creative dumps

Dump Triggers, Stored Procedures, Table Structures and Data into Separate Files

DUMPDB=mydb
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump -${MYSQL_CONN} -d -t --skip-routines --triggers   ${DUMPDB} > triggers.sql
mysqldump -${MYSQL_CONN} -d -t --routines --skip-triggers   ${DUMPDB} > str_proc.sql
mysqldump -${MYSQL_CONN} -d --skip-routines --skip-triggers ${DUMPDB} > myschema.sql
mysqldump -${MYSQL_CONN} -t --skip-routines --skip-triggers ${DUMPDB} > mydata.sql

That would be just a starting point.

If these four(4) dumps run without incident, migrate them as you see fit.

If you have an issue with dumping mysqldat.sql, dump the tables separately

See my answer to How can I optimize a mysqldump of a large database? under the heading Option 3 : mysqldump separate tables into separate data files. When dumping each table, one of them should produce this error. If no error occurs, don't bother diving any deep. Just migrate the 103 table dumps into the new database.

  • Thanks for the help. I will test this out and see if it works. If not I will just dump the tables separately and see what it fouls on. – Sezotove Jun 24 '15 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.