Mysqldump 1146 Error

Posted on 2009-02-13
Last Modified: 2012-08-14
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!

Question by:CaptainBob007
    LVL 26

    Expert Comment

    Could you please post your mysqldump command? also I want to see mysql config file (my.cnf/my.ini depending upon your os environment)

    Author Comment

    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
    if test $BackupResult -eq 0
      echo "success">>$LogFile
      ErrorCount=`expr $ErrorCount + 1`
    rm dbPipe

    Open in new window
    LVL 26

    Accepted Solution

    >>>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...


    And try this dump command...

    mysqldump -uuser_name -ppassword --opt --triggers --routines --databases DB1 DB2 DB3 | gzip > /path/to/full_dump.gz

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Index not getting used in mysql query 2 28
    MySQL 11 50
    simple sql statement 3 36
    PHP strip quotes and line feeds out of array 13 22
    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now