Mysqldump 1146 Error
Posted on 2009-02-13
I'm trying to backup a MySQL database using mysqldump, and am running into problems.
MySQL version is 5.0.37 (we have to use this version due to vendor specifications) and all database tables are MyISAM.
This is a fairly large database (~100GB) and backups run overnight. Due to system load during the day, I can't run backups during business hours, hence I have to try making a change and don't get feedback on it until the next day. The backup fails after about 90 minutes, and it always fails on one table. What's special about this table is that every 30 minutes there is a process that drops and re-creates it. The backup always fails on this table with error 1146, saying the table does not exist.
My first attempt was to just do a backup specifying the database name. It failed with the 1146 error.
After realizing that this other process was dropping and re-creating this table, I decided to run mysqldump with the --ignore-table option and ignore the table. This also failed with the same error. My thought process was that mysqldump probably makes a list of tables to backup (or ignore) based on some table identifier rather than the name, so when I said to ignore that table, it was really looking to ignore its id instead. After the table was dropped, a new one was created with the same name but a different id, so it would attempt to back that new table up as well.
My latest thought was to query INFORMATION_SCHEMA.TABLES for all table names EXCEPT the one that is failing, and then using mysqldump and specifying exactly which tables to back up. This also failed with the same error. Apparently it feels compelled to back up this table even though I don't say to do so.
So now I don't know what to do. I need to back up this database, and can skip this table that keeps getting dropped without consequence, I just need to figure out how to get mysqldump to let me do so. Any ideas would be greatly appreciated!