point in time recovery not working with blackhole engine tables involved

Question

The basic problem summary:

I am trying to do a point in time recovery on a specific table. To speed things up I made every other table use the blackhole engine. Unfortunately this runs into errors like

ERROR 1032 (HY000) at line 88970: Can't find record in 'a-blackhole-engine-table'

Meanwhile I was able to recover the table without using blackhole engine tables, but this took ages. I would like to avoid this in the future. Therefore I'm still looking for a solution, but I'm running out of ideas. Hope someone can help me here.

EDIT: I found this bug: BLACKHOLE replication with RBR is broken
but it was fixed in 5.1.29. I'm using 5.6.27. Upon inspection this must be a very special statement. Other ROW format statements worked before I got the above error. When I find out, what exactly the issue is, I will of course write a bug report. I'm still hoping though that someone has a workaround of some sort. From my experience bugs in MySQL can take years until they are fixed.

The exact steps I did for this PITR:

The backup was taken with xtrabackup. I restored the backup in a VM as usual with

# xtrabackup --decompress --target-dir=$BACKUPDIR
# xtrabackup --prepare --export --target-dir=$BACKUPDIR

Then I restored just this table with

mysql> CREATE TABLE foo (...);
mysql> ALTER TABLE foo DISCARD TABLESPACE;
# # copied the .frm, .ibd, .exp and .cfg files to the appropriate place
mysql> ALTER TABLE foo IMPORT TABLESPACE;

This worked nicely, so I dumped this table to restore it later.

From the backup I took the binlog file and position.

# cat xtrabackup_binlog_pos_innodb
mysql-bin.006329        206749567

From the live server I dumped the schema without data.

# mysqldump schemaname --single-transaction --no-data --triggers --routines > no_data_dump.sql

To restore this dump with blackhole engine tables:

# sed -f dump2blackhole.sed no_data_dump.sql | mysql schemaname

The dump2blackhole.sed file looks like this:

s/ENGINE=InnoDB/ENGINE=BLACKHOLE/g
/^ \+UNIQUE\|^ \+KEY\|^ \+PRIMARY/s/(\([^,]*\).*)/(\1)/g

The second line is necessary to overcome this bug: Blackhole : Specified key was too long; max key length is 1000 bytes
So there are no more compound indexes. I haven't found another nice way to overcome this.

Now I restored the dumped table so that only this table uses InnoDB.

Finally I tried to do the PITR with

# mysqlbinlog mysql-bin.006329 mysql-bin.006330 -d schemaname --start-position=206749567 --stop-position=865393430 | pv | mysql schemaname

And here comes the above mentioned error.

What I tried to solve this:

1. First I checked of course which statement in the binary log is causing trouble. The binlog_format is set to MIXED. The statement in question is in ROW format.

2. Then I tried to replace the table where the error appears with its InnoDB counterpart. Then the same error appears with another table. So I replaced this, too. Finally I ran into duplicate key errors, which happened because I haven't used the table from the backup. My mistake, didn't have enough space. Dropping the primary key also didn't help. Anyway, this can't be the solution to start all over again and again until you have replaced x tables and end up with half of the schema with InnoDB tables again.

3. I made sure that the data between master and slave is consistent. I checked this with pt-table-checksum. There's no difference between master and slave.

4. I wanted to make sure, that the table definition is not the problem. The above mentioned bug should have been fixed in MySQL 8.0.11. I already commented in the bug ticket, that it's still not working in 8.0.12. I also tried to overcome this bug with another sed script by making every varchar column shorter but keeping compound indexes. But then it's running into errors when applying the binary log.

That's pretty much it, if I didn't forget something. Has anyone else a good idea how to do this with blackhole engines? Or what is my mistake?

Thanks in advance.