How to check MySQL integrity?
I have a big MySQL 5 Database for a drupal site on an old machine. After backing up and restoring on a fresh database, I got:
ERROR 1064 (42000) at line **: 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 ''captcha_success_form_ids|a:1:{s:13:\"user_register\";s:13:\"user_register\";}sp' at line 1
I am not sure which table is the origion of this error (I have no table called 'user_register'). So I am wondering how can I have a quick check of the original database for integrity before making another failed backup/restore effort? (I have command line access). Thanks
Answer
If you suspect corruption in the original database, you can do the following:
For MyISAM tables:
CHECK TABLE <table_name>;
REPAIR TABLE <table_name>;
For InnoDB tables:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/
I've personally seen corruption with MyISAM tables on a few rare occasions that CHECK and REPAIR was able to detect and correct. I've never experienced corruption with InnoDB tables, so can't speak from personal experience regarding the info provided in the link.
That said, if I were in your shoes, I would start by taking a closer look at the output file produced by mysqldump to see if I could pinpoint the source of the error. mysqldump usually outputs a single INSERT statement per table with all of the data on a single line, so it's a little tricky to diagnose errors because the line number included with the error message does not help much. So, what I would do is to edit the mysqldump output file and insert newlines between each row. For example:
Original:
INSERT INTO table VALUES (a,b,c),(d,e,f),(g,h,i), ...
Change To:
INSERT INTO table VALUES (a,b,c),
(d,e,f),
(g,h,i),
...
Since you're comfortable with the command line, you could probably automate this with sed, etc.
Then, try to import the modified file. You should get the same error, but this time the line number will help pinpoint the exact row that's causing the problem. At that point, you should be able to diagnose the problem (or post the offending portion of the mysqldump output here and we'll try to help).
EDIT:
Is the error message you cited happening when you import the database? Or does the database import successfully, but the application produces the error message when accessing the database? I was assuming the former, but now thinking it may be the latter after re-reading your question.
One more idea: Does your database include stored procs? If so, mysqldump will not include those by default. You need to use the --routines option:
mysqldump --routines -u <user> -p<password> <database> > output