MySQL – Why InnoDB Corrupts Tables Upon Recovery

innodbMySQLmysql-5.7recovery

When setting

innodb_fast_shutdown=0

MySQL drops all incomplete transactions before shutting down/crash. The default value is 1, which is understandable to preserve incomplete transactions.

However, in the recovery mode (e.g., innodb_force_recovery = 6), MySQL treats all incomplete transactions as committed. This obviously damages the table as the transactions are incomplete.

What is the purpose of preserving incomplete transactions when it damages the whole table?

If it is necessary in some cases, why doesn't innodb_force_recovery have an option to simply drop all incomplete transactions (when they cannot be committed) like innodb_fast_shutdown=0 to save the table? Or am I missing something?

Best Answer

innodb_fast_shutdown controls the change buffer, not the redo log. No matter what value of innodb_fast_shutdown is, if a transaction wasn't committed at the time of shutdown it won't survive the restart.

If a transaction was committed then whether it's going to persist depends on innodb_flush_log_at_trx_commit. If it's the default 1 the transaction will persist. If != 1, may not persist.

Now, innodb_force_recovery. Here's what possible. When you run an UPDATE in a transaction, the new record is written to a page (in the buffer pool, the page becomes dirty). Later on, the data page can be flushed to disk even if the transaction isn't committed yet. When you have to use innodb_force_recovery that means you are ready to sacrifice data consistency (is it what you call a damaged table?) just to get the some data back. With innodb_force_recovery=6 InnoDB tries to salvage as much data as possible. It cannot complete the crash recovery process, but the new record is already written to the page - why not make it available to a user?

Related Solutions

SQL Server – Managing Transaction Log Backups from Two Different Sources

I realized that there are two sources taking full back ups overnight and hourly transaction log backups: TDP (part of IBM's TSM) and a regular SQL job.

If this is the case then, you will end up breaking your log chain and a point in time recovery would not be possible.

Either use TDP (part of IBM's TSM) or a regular SQL job to back-up Transaction logs.

Also, I would suggest you to simulate a recovery scenario on a test server with either TDP or using SQL Native backups (Full backup, differential (if any) and then subsequent log backups) to ensure that in an unfortunate situation of disaster, you would be able to recover your databases.

Note: Full backup wont break the log chain.

Mysql – innodb completely crashed after interruption

it might be not as bad. InnoDB dictionary is corrupt, but data may be still good. As long as MySQL start with innodb_force_recovery=6 dump as many tables tables as it allows:

    for d in `mysql -NBe "select distinct TABLE_SCHEMA  from information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')"`
    do
        mkdir -p "dumps/$d"
        for t in `mysql -NBe "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$d' AND TABLE_TYPE = 'BASE TABLE' AND  ENGINE='InnoDB';"`
        do
            mysqldump --skip-lock-tables $d $t > "dumps/$d/$t.sql"
            # let MySQL start if it crashed
            if [ "$?" -ne 0 ]; then sleep 30; else echo "$d/$t" >> crashed fi
        done
     done

Then take list of "crashed" tables and recover them with data recovery toolkit.

You need to parse InnoDB tables space:

 ./stream_parser -f /path/to/table.ibd

and fetch records from it:

 ./c_parser -5f pages-XXXX/FIL_PAGE_INDEX/0-<minimal id>.pages -t table.sql

Then load the dump back into MySQL(as well as dumps taken with mysqldump)

P.S. I should probably write a blog post as I answered this question hundred times here.

Best Answer

Related Solutions

SQL Server – Managing Transaction Log Backups from Two Different Sources

Mysql – innodb completely crashed after interruption

Related Question