innobackup fails on different positions

hello,

i want to take a backup with innobackupex to add a new slave.

i execute
innobackupex --user=xxxx --password=xxxx .
but it fails on different positions:

=========
first run:


161206 09:20:39 [01] Copying ./_session_em_at/v_2016_11_24.ibd to /var/lib/mysql/.tmp/2016-12-06_09-18-45/_session_em_at/v_2016_11_24.ibd
161206 09:20:40 [01] …done
161206 09:20:40 [01] Copying ./_session_em_at/FTS_00000000000ea744_BEING_DELETED_CACHE.ibd to /var/lib/mysql/.tmp/2016-12-06_09-18-45/_session_em_at/FTS_00000000000ea744_BEING_DELETED_CACHE.ibd
161206 09:20:40 [01] …done
161206 09:20:40 [01] Copying ./_session_em_at/FTS_00000000000ec3c9_00000000001b985e_INDEX_6.ibd to /var/lib/mysql/.tmp/2016-12-06_09-18-45/_session_em_at/FTS_00000000000ec3c9_00000000001b985e_INDEX_6.ibd
InnoDB: Last flushed lsn: 4472480740170 load_index lsn 4472506073978
[FATAL] InnoDB: An optimized(without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
PXB will not be able take a consistent backup. Retry the backup operation
2016-12-06 09:20:40 0x7f7d6c35f700 InnoDB: Assertion failure in thread 140176663115520 in file ut0ut.cc line 916
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: [url]http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html[/url]
InnoDB: about forcing recovery.
08:20:40 UTC - xtrabackup got signal 6 ;
This could be because you hit a bug or data is corrupted.
This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 0 thread_stack 0x10000
innobackupex(my_print_stacktrace+0x3b)[0xc7183b]
innobackupex(handle_fatal_signal+0x281)[0xa8da01]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x113d0)[0x7f7d6eaa33d0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7f7d6d11c418]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f7d6d11e01a]
innobackupex[0x6f1b41]
innobackupex(_ZN2ib5fatalD1Ev+0x145)[0x9d62c5]
innobackupex[0x942177]
innobackupex(_Z19recv_parse_log_recsm7store_tb+0x281)[0x946df1]
innobackupex[0x713558]
innobackupex[0x7137b8]
innobackupex[0x713c8b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76fa)[0x7f7d6ea996fa]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f7d6d1edb5d]

=========
second run:


161206 09:40:46 [01] Copying ./_session_em_at/FTS_0000000000007cae_000000000001365a_INDEX_5.ibd to /var/lib/mysql/.tmp/2016-12-06_09-36-26/_session_em_at/FTS_0000000000007cae_000000000001365a_INDEX_5.ibd
161206 09:40:46 [01] …done
161206 09:40:46 [01] Copying ./_session_em_at/v_2016_11_30.ibd to /var/lib/mysql/.tmp/2016-12-06_09-36-26/_session_em_at/v_2016_11_30.ibd
InnoDB: Last flushed lsn: 4472662375842 load_index lsn 4472756690590
[FATAL] InnoDB: An optimized(without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
PXB will not be able take a consistent backup. Retry the backup operation
2016-12-06 09:40:47 0x7f910132d700 InnoDB: Assertion failure in thread 140260767094528 in file ut0ut.cc line 916
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: [url]http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html[/url]
InnoDB: about forcing recovery.
08:40:47 UTC - xtrabackup got signal 6 ;
This could be because you hit a bug or data is corrupted.
This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 0 thread_stack 0x10000
innobackupex(my_print_stacktrace+0x3b)[0xc7183b]
innobackupex(handle_fatal_signal+0x281)[0xa8da01]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x113d0)[0x7f9103a713d0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7f91020ea418]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f91020ec01a]
innobackupex[0x6f1b41]
innobackupex(_ZN2ib5fatalD1Ev+0x145)[0x9d62c5]
innobackupex[0x942177]
innobackupex(_Z19recv_parse_log_recsm7store_tb+0x281)[0x946df1]
innobackupex[0x713558]
innobackupex[0x7137b8]
innobackupex[0x713c8b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76fa)[0x7f9103a676fa]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f91021bbb5d]

during the second run, the error occurred later.

has anyone have an idea?

my system:
Ubuntu 16.04.1
Percona Server 5.7.12-5-1.xenial
xtrabackup 2.4.4-1.xenial

More likely you are affected by this bug: [url]https://bugs.launchpad.net/percona-xtrabackup/+bug/1532878[/url]

In both your first and second runs we can see:
[FATAL] InnoDB: An optimized(without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
PXB will not be able take a consistent backup. Retry the backup operation
2016-12-06 09:20:40 0x7f7d6c35f700 InnoDB: Assertion failure in thread 140176663115520 in file ut0ut.cc line 916

Are you running an ALTER (DDL) at the same time you were backing up the database?

hello jrivera,

no, nothing was written or changed in the affected tables for days.

Hi Jrivera,

We are facing exact same issue and we are successfully able to reproduce it by executing an alter statement while running xtrabackup.

Any help is appreciated.

Thanks,
Suresh

Suresh, it is not advisable to run any DDL (ALTER) while Xtrabackup is running. Maybe in future Xtrabackup releases DDLs will be supported while backup is running but up to the current release it is not supported.

Hi Jrivera,

Thanks for your quick response.

It is difficult to guarantee that DDLs will not be executed during the backup. These are mostly application driven DDLs on temporary tables. Is there any workaround that you have come across which addresses this issue? At least in 5.7.13 (which is supported by xtrabackup).

We have tested “ALGORITHM = COPY” and old_alter_table = 1, both do not work. Without a fix or any workaround, it is a No-GO for us to upgrade to 5.7.

Please share your thoughts.

Thanks,
Suresh

We are experiencing the same problem and we are doing the same thing. Creating indexes on temporary tables.

There has to be a work around for this! The older version of innobackupex worked fine. It was not until we were forced to move the the new (2.4.x) version when we upgraded to mysql 5.7.x that this started to be a problem. The previous version of innobackupex clearly allowed for these statements to be executed.

To counter the problem I have started stopping the SQL thread of my salve before I start the backup and re-start it after the backup completes. This works fine in a master/slave situation, but if I was backing up my primary database I would not have this luxury. And my backups would be failing all the time.

I only stop the SQL thread so that the bin-logs are actively copied to my slave machine during the backup, thus I at least have a second copy of the statements to be applied archived on the slave machine while the backup is still running.

But, I am still not liking the situation where I have a database that gets many many minutes (even hours during a full backup) out of sync with my primary during backups.

Older versions of the backup allowed for DDL already. This is a regression of the backup tool.

Related bug report see response from Lead Dev regarding this - [url]https://bugs.launchpad.net/percona-xtrabackup/+bug/1653545/comments/3[/url]