MySQL database dump bombs out Error 1064 when using LOCK TABLES

Question

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!

Answer 1

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.