when xtrabackup fails with 'read' returned OS error 0 after upgrading to 5.7

Here's something that has puzzled me for several weeks.
Right after migrating  MySQL from 5.6 to 5.7, we started experiencing random xtrabackup failures on some, but not all, of our slaves.
The failures were only happening when taking an incremental backup, and it would always fail on the same table on each slave, with errors similar to the following:

171106 13:00:33 [01] Streaming ./gls/C_GLS_IDS_AUX.ibd
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: Retry attempts for reading partial data failed.
InnoDB: Tried to read 262144 bytes at offset 0, but was only able to read 114688
InnoDB: File (unknown): 'read' returned OS error 0. Cannot continue operation
InnoDB: Cannot continue operation.

Searching the web didn't yield any useful result.
The error was happening on one slave but not on  another, OS, MySQL and Xtrabackup versions being the same:

• CentOS release 6.8 (Final)
• xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
• mysqld  Ver 5.7.19-17 for Linux on x86_64 (Percona Server (GPL), Release 17, Revision e19a6b7b73f)

To properly identify the table where the backup was failing I had to set parallelism to 1 (option --parallel=1 of xtrabackup). Examining the table with SHOW CREATE TABLE didn't show anything particular, the backup was failing on different tables and schemas, and tables were all different from each other and didn't have anything special,  or at least nothing that would  give me an hint.

I even tried to dump the offending schema from a failing slave and import it into a non failing slave, but on the non-failing slave the same table in that schema backed up just fine.

This has been going on for a while, where I used to run without incremental backups on the migrated slaves, until today, when it happened again on yet another slave that we had just migrated, and I had  an intuition:  since the failure of the incremental backup was only happening after the migration, of course it had to be related to the migration itself. So I started suspecting some incompatibility between 5.7 and/or xtrabackup, and tablespaces created with MySQL 5.6.

Verifying my suspect was as easy as running:

  ALTER TABLE C_GLS_IDS_AUX Engine=InnoDB

Et voilà!! The rebuilt tablespace was not crashing the incremental backup anymore.
Eventually, another table(space) would crash the backup again later, but running a NULL alter table on it and relaunching the backup would fix that. Wash, rinse, repeat until the incremental completes, and you're done.

Hope this helps someone saving some time..
And if you are looking for xtrabackup based backup scripts, feel free to check my own set here on my GitHub page.


ADDENDUM
 
Looks like I have been a little overenthusiastic about this.
In fact, the problem reappeared the day after writing this blog post - apparently, the null alter table only fixes it temporarily.

Turns out this has to be some incompatibility between Xtrabackup 2.4.x and MySQL 5.7.x as I was able to reproduce the issue. Please see this bug if you are interested in the details.

Using xtrabackup with MariaDB and multi source replication

Quick post today, because I wanted to share a snippet (actually, a patch) that allows the great xtrabackup tool to take backups of MariaDB when multi source replication is in use.

Granted, this is supported already, except the slave(s) position is not saved when the backup is taken, and this somewhat defeats the purpose of the backup, which should be used to rebuild the server and resync it with its master(s) in case of disaster.

A workaround for using xtrabackup with MariaDB exists, but only works when you have a single master: just set the default_master_connection variable in your my.cnf to the name of your master connection,  and you are set - in fact, in this situation, SHOW SLAVE STATUS will work exactly the same as in stock MySQL, and xtrabackup is happy.

The trouble begins when you have more than one master.
Each master connection has its own name, and in order to get the slave position you have to issue a SHOW SLAVE 'connectionname' STATUS.

A more easy way to get information for all of them is to issue a SHOW ALL SLAVES STATUS, which will report the same information as SHOW SLAVE STATUS for each connection that you have defined.

The bad news is that this syntax is not understood by xtrabackup, so if you happen to use MariaDB with multiple masters, you have no way to save the slave position of each connection when you take a backup.

So here comes the little snippet below - actually a UNIX style diff, that will work against the innobackupex script which is part of xtrabackup, tested with 2.2.12 and above. It will patch the script so that information for each connection is properly saved into the xtrabackup_slave_info file when backup is taken.

This will not work on version 2.3 because the innobackupex script is now an hard link to the xtrabackup binary, so no easy patching is possible.

Please note - only tested with old school replication, so if you are using GTIDs you'll need to modify the patch, but shouldn't be too hard if you follow the changes I did.

Hope this helps. Enjoy!

$ diff -u /usr/bin/innobackupex /usr/bin/innobackupex.rick
--- /usr/bin/innobackupex    2016-02-02 11:20:55.000000000 +0100
+++ /usr/bin/innobackupex.rick    2017-03-07 16:29:19.000000000 +0100
@@ -3263,12 +3263,27 @@

     if (!defined($master) || !defined($filename) || !defined($position)) {
         my $now = current_time();
+    my $mspos = '';

-        print STDERR "$now  $prefix Failed to get master binlog coordinates " .
-            "from SHOW SLAVE STATUS\n";
-        print STDERR "$now  $prefix This means that the server is not a " .
-            "replication slave. Ignoring the --slave-info option\n";
+    # Rick
+        print STDERR "$now  trying multisource replication setup\n";
+     my $rows = $con->{dbh}->selectall_hashref("SHOW ALL SLAVES STATUS", 'Connection_name');
+    foreach my $conn_id (keys %$rows) {
+            $mspos = $mspos
+                        . "Connection $rows->{$conn_id}{Connection_name} "
+                        . "Master Host $rows->{$conn_id}{Master_Host}, "
+                        . "File $rows->{$conn_id}{Relay_Master_Log_File}, "
+                        . "Position $rows->{$conn_id}{Exec_Master_Log_Pos}\n";
+    }
+        write_to_backup_file("$slave_info", $mspos);
+    if ($mspos eq '') {
+
+            print STDERR "$now  $prefix Failed to get master binlog coordinates " .
+                "from SHOW SLAVE STATUS\n";
+            print STDERR "$now  $prefix This means that the server is not a " .
+                "replication slave. Ignoring the --slave-info option\n";

+    }
         return;
     }