MySQL Bugs: #57527: ibbackup can fail when using ALTER TABLE with options ROW_FORMAT, KEY_BLOCK

Bug #57527	ibbackup can fail when using ALTER TABLE with options ROW_FORMAT, KEY_BLOCK_SIZE
Submitted:	18 Oct 2010 14:37	Modified:	10 Feb 2011 23:21
Reporter:	Victor Kirkebo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Backup	Severity:	S3 (Non-critical)
Version:	3.5	OS:	Any
Assigned to:	Pekka Lampio	CPU Architecture:	Any

Description:
This bug report could be considered a request for additional product documentation as the issue described does not seem to be documented.
In the current MySQL Enterprise Backup version there is no DDL locking when a backup is taken. This can cause a backup to fail if, during the backup of InnoDB tables (ibbackup), an ALTER TABLE command setting the option ROW_FORMAT or KEY_BLOCK_SIZE is issued.

How to repeat:
With MySQL version 5.5 or later:
Take a backup while simultaneously issuing ALTER TABLE commands that are changing the table format with the ROW_FORMAT option or the compressed page size with the option KEY_BLOCK_SIZE.
Observe that the backup will frequently fail with table page checksum errors.

Suggested fix:
Describe this limitation in the product documentation until the time DDL locking is added as a backup feature.
If possible, detect this issue during backup and include this in an error message before aborting backup. It would desirable to always abort the backup if it can be detected that the table format or compressed page size has been changed during backup of InnoDB tables. The reason is that a backup might occasionally report success but still contain corrupted table pages if such table changes were done concurrently with the backup.

Thank you for the report.

Test fails with error "Can't locate mtr_path_funcs.pm in @INC" There is mtr_path_funcs.pm located?

Thank you for the feedback.

Verified as described. I had to repeat test about 5 times to get inconsistency. I also modified run_backup_with_tableformat_ddl  as follow:

$diff -u run_backup_with_tableformat_ddl  run_backup_with_tableformat_ddl.modif 
--- run_backup_with_tableformat_ddl     2010-10-17 21:20:43.000000000 +0200
+++ run_backup_with_tableformat_ddl.modif       2010-11-02 21:53:29.000000000 +0100
@@ -5,9 +5,9 @@
 cd mysql-test
 
 # For release package
- export CLIENT_PATH=$CUR_PATH/bin
- export MYSQLD_PATH=$CUR_PATH/bin
- export SHARE_PATH=$CUR_PATH/share
+ export CLIENT_PATH=$CUR_PATH/client
+ export MYSQLD_PATH=$CUR_PATH/sql
+ export SHARE_PATH=$CUR_PATH/sql/share
 
 # For source tree
 # export CLIENT_PATH=$CUR_PATH/client
@@ -93,11 +93,15 @@
 echo =======================================================================
 echo character-sets-dir=$SHARE_PATH/charsets >> $TST_PATH/backup/backup-my.cnf
 echo port=10760 >> $TST_PATH/backup/backup-my.cnf
+echo socket=/tmp/10760.sock >> $TST_PATH/backup/backup-my.cnf
+
 
 # for mysql 5.1:
-echo language=$SHARE_PATH/english >> $TST_PATH/backup/backup-my.cnf
+#echo language=$SHARE_PATH/english >> $TST_PATH/backup/backup-my.cnf
 # for mysql 5.5 :
-#echo lc-messages-dir=$SHARE_PATH >> $TST_PATH/backup/backup-my.cnf
+echo lc-messages-dir=$SHARE_PATH >> $TST_PATH/backup/backup-my.cnf
+
+echo skip-grant-tables >> $TST_PATH/backup/backup-my.cnf
 
 $MYSQLD_PATH/mysqld --defaults-file=$TST_PATH/backup/backup-my.cnf &
 sleep 5

I suggest to add the following text to the
MySQL Enterprise Backup User's Guide, appendix
A.3. (Known ibbackup and innobackup Bugs):

If you run ALTER TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, REPAIR TABLE,
or RESTORE TABLE while a backup operation is going on, the resulting
backup may be corrupted.

There are few exceptions for ALTER TABLE, which do not cause problems
when run in parallel with a backup. These exceptions are operations
that do not influence the physical representation of records on disk.
Examples are changes of column names or changes of default values.

Does this problem happen with --innodb-file-per-table=1 only?
Or does it happen with all tables in a single system tablespace too?

With the latest 5.5.7 release candidate this seems to only happen when using the option --innodb-file-per-table=1. Without that option ALTER TABLE with ROW_FORMAT, KEY_BLOCK_SIZE will simply be ignored after producing warnings such as:
ROW_FORMAT=COMPRESSED requires innodb_file_per_table
InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table

Pre-5.5.7 release candidates (5.5.7-m3) would also fail when NOT using --innodb-file-per-table=1 (i.e. single system tablespace).

Thanks, Victor.

I understand that ALTER TABLE ... ROW_FORMAT or KEY_BLOCK_SIZE are rejected for a single tablespace.

But how about ALTER TABLE ADD/DROP COLUMN ? I could imagine that this might lead to inconsistencies in the backup too. And these should not be rejected.

Hi Ingo,
I ran some simple tests with ALTER TABLE ADD/DROP COLUMN.
The tests just add and drop the same columns repeatedly. The columns are empty.
With both --innodb-file-per-table=1 and single system table space I get inconsistencies: e.g. the backed up tables may contain the added columns when they shouldn't - i.e. the columns were dropped before the binlog position that the backup process reports as having backed up to.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Patch approved. All questions have been answered by email.

Patch has been reviewed and approved.

Fix is available in meb-trunk rev. 264

This fix makes all DDL operations safe during a backup.

The bug fix is in meb-trunk repository, so it will be included in MEB 3.6.

MySQL Enterprise Backup version 3.6 hasn't been released yet.