XtraBackup 2.2.10 does not work with MariaDB 10

[Julien Pivotto]

From Percona's forums:

 I use xtrabackup on a Mariadb 10.0.15 running on Centos 6.7
It seems something changed in innobackupex. Yesterdays backup using the previous version 2.2.9 had no problems.
Any hints on how to solve this?

Thanks, Michiel

I include the log here:

innobackupex: Using server version 10.0.15-MariaDB-log

innobackupex: Created backup directory /var/backups/mysql/incr/2015-04-01_03-21-01/2015-04-02_03-21-01

150402 03:21:01 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/var/backups/mys
ql/incr/2015-04-01_03-21-01/2015-04-02_03-21-01 --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/mydata/data --compress --compress-threads=4 --tab
les='.*[.].*' --extra-lsndir='/mydata/data' --incremental-basedir='/var/backups/mysql/base/2015-04-01_03-21-01' --suspend-at-start --parallel=4
innobackupex: Waiting for ibbackup (pid=29526) to suspend
innobackupex: Suspend file '/var/backups/mysql/incr/2015-04-01_03-21-01/2015-04-02_03-21-01/xtrabackup_suspended_1'

xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 2555712792976 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mydata/data
xtrabackup: open files limit requested 65535, set to 16384
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 536870912
xtrabackup: using O_DIRECT
>> log scanned up to (2560735523319)
xtrabackup: Generating a list of tablespaces
xtrabackup: Creating suspend file '/var/backups/mysql/incr/2015-04-01_03-21-01/2015-04-02_03-21-01/xtrabackup_suspended_1' with pid '29526'

150402 03:21:02 innobackupex: Continuing after ibbackup has suspended
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHANGED_PAGE_BITMAPS' at line 1 at /usr/bin/innobackupex line 3044.
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3047
main::mysql_query('HASH(0x24ec2f8)', 'FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS') called at /usr/bin/innobackupex line 1970
main::backup() called at /usr/bin/innobackupex line 1601
innobackupex: Error:
Error executing 'FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS': DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHANGED_PAGE_BITMAPS' at line 1 at /usr/bin/innobackupex line 3044.
150402 03:21:02 innobackupex: Waiting for ibbackup (pid=29526) to finish
Tags: None

mbetel
mbetel
Junior Member

    Join Date: Sep 2013
    Posts: 1

#2
04-02-2015, 04:48 AM
looking further, it looks like MariaDB does not support the statement FLUSH CHANGED_PAGE_BITMAPS (see https://mariadb.atlassian.net/browse/MDEV-7472) but the statement setting $have_changed_page_bitmaps on line 4984 of innodbex ('SELECT COUNT(*) FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'INNODB_CHANGED_PAGES') returns 1
thus making the if in #1966 true. Removing the 'CHANGED_PAGE_BITMAPS' from the FLUSH in #1970 makes my backup run agian.

---

I have the same behaviour. The error line seems to come from https://github.com/percona/percona-xtrabackup/commit/a37c7cbca557e94bb5f5ffe4ae02378dcf98daf7 but that commit was there on previous releases as well.

[Julien Pivotto (roidelapluie)]

Original message: http://www.percona.com/forums/questions-discussions/percona-xtrabackup/34901-error-on-flush-no_write_to_binlog-changed_page_bitmaps-after-update-to-2-2-10

[Julien Pivotto (roidelapluie)]

The problem comes from commit https://github.com/percona/percona-xtrabackup/commit/a39237dd1a4de653e205e63b81ae5c4c5659116d

[Sergei Glushchenko (sergei.glushchenko)]

Could you please provide the output of "SELECT COUNT(*) FROM INFORMATION_SCHEMA.PLUGINS"?

[Julien Pivotto (roidelapluie)]

SELECT COUNT(*) FROM INFORMATION_SCHEMA.PLUGINS;
+----------+
| COUNT(*) |
+----------+
| 47 |
+----------+
1 row in set (0.01 sec)

SELECT COUNT(*) FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'INNODB_CHANGED_PAGES';
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.01 sec)

Upstream 'bug' is at https://mariadb.atlassian.net/browse/MDEV-7472

[Sergei Glushchenko (sergei.glushchenko)]

Thank you Julien. The workaround for MariaDB is to use --incremental-force-scan option.

[Sergei Glushchenko (sergei.glushchenko)]

https://github.com/percona/percona-xtrabackup/pull/56
https://github.com/percona/percona-xtrabackup/pull/57

[苏信东 (webmaster-wavecn)]

I'm using MariaDB 5.5.45 and this version of MariaDB does not support FLUSH CHANGED_PAGE_BITMAPS too. According to https://mariadb.atlassian.net/browse/MDEV-7472, MariaDB supports it since version 10.1.6 (but just a workaround).

So I suggest to change the version checking sql to:

SELECT COUNT(*) FROM information_schema.plugins WHERE plugin_name LIKE "innodb_changed_pages" AND
IF (VERSION() NOT LIKE "%MariaDB%", TRUE,
(
SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 1), '.', 1) * 10000 +
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 2), '.', 1), '.', -1) * 100 +
SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 1), '.', -1)
) >= 100116
)

This version checking method comes from http://stackoverflow.com/questions/32104142/sql-compare-version-string-w-x-y-z-in-mysql

[Shahriyar Rzayev (rzayev-sehriyar)]

I hit this bug using 2.2.12 xtrabackup and MariaDB 5.5.44:

[root@centos-base ~]# xtrabackup --version
xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)

The exact condition was, while taking full backup it will success, but if you try to take an incremental backup it will fail:

innobackupex: Created backup directory /home/MySQL-AutoXtraBackup/backup_dir/inc/2015-08-30_12-33-43

150830 12:33:43 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/MySQL-AutoXtraBackup/backup_dir/inc/2015-08-30_12-33-43 --innodb_log_file_size="5242880" --innodb_data_file_path="ibdata1:10M:autoextend" --tmpdir=/var/tmp --extra-lsndir='/var/tmp' --incremental-basedir='/home/MySQL-AutoXtraBackup/backup_dir/full/2015-08-30_12-33-25' --suspend-at-start
innobackupex: Waiting for ibbackup (pid=3885) to suspend
innobackupex: Suspend file '/home/MySQL-AutoXtraBackup/backup_dir/inc/2015-08-30_12-33-43/xtrabackup_suspended_1'

xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 8726828)
incremental backup from 1597945 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (1597945)
xtrabackup: Generating a list of tablespaces
xtrabackup: Creating suspend file '/home/MySQL-AutoXtraBackup/backup_dir/inc/2015-08-30_12-33-43/xtrabackup_suspended_1' with pid '3885'

150830 12:33:44 innobackupex: Continuing after ibbackup has suspended
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHANGED_PAGE_BITMAPS' at line 1 at /usr/bin/innobackupex line 3052.
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3055.
 main::mysql_query('HASH(0x2bb12c8)', 'FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS') called at /usr/bin/innobackupex line 1978
 main::backup() called at /usr/bin/innobackupex line 1609
innobackupex: Error:
Error executing 'FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS': DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHANGED_PAGE_BITMAPS' at line 1 at /usr/bin/innobackupex line 3052.
150830 12:33:44 innobackupex: Waiting for ibbackup (pid=3885) to finish

[Guillaume Lefranc (tanj)]

Hello Shahriyar,

you're right, issue is fixed for 10.0 only, check lines 5013-5017 of
innobackupex:

    if ($option_incremental) {
        $have_changed_page_bitmaps =
            mysql_get_one_value($con, "SELECT COUNT(*) FROM " .
                        "INFORMATION_SCHEMA.PLUGINS " .
                        "WHERE PLUGIN_NAME LIKE 'INNODB_CHANGED_PAGES' " .
                        "AND NOT VERSION() LIKE '10.%MariaDB%'");
    }

2015-08-30 18:38 GMT+02:00 Shahriyar Rzayev <email address hidden>:

> I hit this bug using 2.2.12 xtrabackup and MariaDB 5.5.44:
>
> [root@centos-base ~]# xtrabackup --version
> xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64)
> (revision id: 8726828)
>
> The exact condition was, while taking full backup it will success, but
> if you try to take an incremental backup it will fail:
>
> innobackupex: Created backup directory /home/MySQL-
> AutoXtraBackup/backup_dir/inc/2015-08-30_12-33-43
>
> 150830 12:33:43 innobackupex: Starting ibbackup with command: xtrabackup
> --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup
> --suspend-at-end
> --target-dir=/home/MySQL-AutoXtraBackup/backup_dir/inc/2015-08-30_12-33-43
> --innodb_log_file_size="5242880"
> --innodb_data_file_path="ibdata1:10M:autoextend" --tmpdir=/var/tmp
> --extra-lsndir='/var/tmp'
> --incremental-basedir='/home/MySQL-AutoXtraBackup/backup_dir/full/2015-08-30_12-33-25'
> --suspend-at-start
> innobackupex: Waiting for ibbackup (pid=3885) to suspend
> innobackupex: Suspend file
> '/home/MySQL-AutoXtraBackup/backup_dir/inc/2015-08-30_12-33-43/xtrabackup_suspended_1'
>
> xtrabackup version 2.2.12 based on MySQL server 5.6.24 Linux (x86_64)
> (revision id: 8726828)
> incremental backup from 1597945 is enabled.
> xtrabackup: uses posix_fadvise().
> xtrabackup: cd to /var/lib/mysql
> xtrabackup: open files limit requested 0, set to 1024
> xtrabackup: using the following InnoDB configuration:
> xtrabackup: innodb_data_home_dir = ./
> xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
> xtrabackup: innodb_log_group_home_dir = ./
> xtrabackup: innodb_log_files_in_group = 2
> xtrabackup: innodb_log_file_size = 5242880
> >> log scanned up to (1597945)
> xtrabackup: Generating a list of tablespaces
> xtrabackup: Creating suspend file
> '/home/MySQL-AutoXtraBackup/backup_dir/inc/2015-08-30_12-33-43/xtrabackup_suspended_1'
> with pid '3885'
>
> 150830 12:33:44 innobackupex: Continuing after ibbackup has suspended
> DBD::mysql::db do failed: You have an error in your SQL syntax; check the
> manual that corresponds to your MariaDB server version for the right syntax
> to use near 'CHANGED_PAGE_BITMAPS' at line 1 at /usr/bin/innobackupex line
> 3052.
> innobackupex: got a fatal error with the following stacktrace: at
> /usr/bin/innobackupex line 3055.
> main::mysql_query('HASH(0x2bb12c8)', 'FLUSH NO_WRITE_TO_BINLOG
> CHANGED_PAGE_BITMAPS') called at /usr/bin/innobackupex line 1978
> main::backup() called at /usr/bin/innobackupex line 1609
> innobackupex: Error:
> Error executing 'FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS':
> DBD::mysql::db do failed: You have an error in your SQL syntax; chec...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-723