Mysqldump 1146 Error

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!



CaptainBob007Asked:
Who is Participating?
 
UmeshMySQL Principle Technical Support EngineerCommented:
>>>MySQL version is 5.0.37 (we have to use this version due to vendor specifications) and all database tables are MyISAM.

But from config fle I feel like you are using InnoDB tables only... if that is the case then your dump command should have --single-transaction parameter...


Anyways try this once and let me know..

Please add this line to config file...

[mysqldump]
default-character-set=utf8
max_allowed_packet=1G

And try this dump command...

mysqldump -uuser_name -ppassword --opt --triggers --routines --databases DB1 DB2 DB3 | gzip > /path/to/full_dump.gz
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Could you please post your mysqldump command? also I want to see mysql config file (my.cnf/my.ini depending upon your os environment)
0
 
CaptainBob007Author Commented:
The first thing I do is query for a list of tables that I know won't change.  Then i run the mysqldump.  code is below.
echo "`date +%H:%M:%S` Backing up database dbName">>$LogFile
TableList=mysql dbName -u $DBUSER -p$DBPASSWORD -e "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dbName' AND LEFT(table_name,4) <> 'ztmp';" | grep -v table_name
 
mkfifo dbPipe
gzip <dbPipe> $BackupDir/DB_dbName.sql.gz &
mysqldump -u $DBUSER -p$DBPASSWORD --log-error=$LogFile dbName $TableList > dbPipe
BackupResult=$?
if test $BackupResult -eq 0
then
  echo "success">>$LogFile
else
  ErrorCount=`expr $ErrorCount + 1`
fi
wait
rm dbPipe

Open in new window

my.zip
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.