MySQL Bugs: #38877: Data recovery with mysqlbinlog fails for Falcon, if backup is present in binlog

Bug #38877	Data recovery with mysqlbinlog fails for Falcon, if backup is present in binlog
Submitted:	18 Aug 2008 22:22	Modified:	11 Sep 2008 16:22
Reporter:	Hema Sridharan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	mysql-6.0-backup, mysql-6.0, 6.0.7-bzr	OS:	Linux (64 bit, 32-bit)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
1. Create database and Falcon table. Insert some data in the table. 
2. Enable binary logging and set to row binary log format.
2. Perform backup database operation and drop db.
3. Recover data using mysqlbinlog utility (use backup binlog position to
recover data till point of backup).
4. Verify the database contents.
### Table is empty. Data contents in the table are lost ###
5. Also tried to recover data using the binlog positions that are before
as well as after backup. Data lost in all these cases

*** Conclusion: Presence of backup leads to data loss, if Mysqlbinlog
utility is used for data recovery. ***

Note: 
a. This problem is seen only with falcon storage engines
b. This behavior is not seen if we just drop tables and recover data
without running any backup operation.

How to repeat:
--source include/have_log_bin.inc

SET BINLOG_FORMAT=ROW;
CREATE DATABASE IF NOT EXISTS ptr;
USE ptr;

CREATE TABLE ptr.t1(id INT, a CHAR(4))ENGINE=FALCON;
INSERT INTO ptr.t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
SELECT * FROM ptr.t1;

LET $backup_id = `BACKUP DATABASE ptr TO 'ptr.bak'`;
let $binlog_pos = `SELECT binlog_pos FROM mysql.online_backup WHERE backup_id =$backup_id`;
--echo $binlog_pos;

FLUSH LOGS;
DROP DATABASE ptr;

--exec $MYSQL_BINLOG --database=ptr  --stop-position=$binlog_pos $MYSQLTEST_VARDIR/log/master-bin.000001 |$MYSQL

SHOW DATABASES;
SHOW TABLES FROM ptr;
SELECT * FROM ptr.t1;

RESULTS
=======

SET BINLOG_FORMAT=ROW;
CREATE DATABASE IF NOT EXISTS ptr;
USE ptr;
CREATE TABLE ptr.t1(id INT, a CHAR(4))ENGINE=FALCON;
INSERT INTO ptr.t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
SELECT * FROM ptr.t1;
id      a
1       a
2       b
3       c
4       d
5       e
557;
FLUSH LOGS;
DROP DATABASE ptr;
SHOW DATABASES;
Database
information_schema
mysql
ptr
test
SHOW TABLES FROM ptr;
Tables_in_ptr
t1
SELECT * FROM ptr.t1;
id      a

From the above, we can notice that data contents are lost in table t1. This does not happen if we just drop tables and recover data without performing backup operation.

Thank you for a bug report. Verified just as described with recent 6.0.7 from bzr on 32-bit Linux:

openxs@suse:/home2/openxs/dbs/6.0/mysql-test> ./mysql-test-run.pl --force rep_bug38877
Logging: ./mysql-test-run.pl --force rep_bug38877
MySQL Version 6.0.7
Using dynamic switching of binlog format
Using ndbcluster when necessary, mysqld supports it
Setting mysqld to support SSL connections
Binaries are debug compiled
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9311
Using NDBCLUSTER_PORT_SLAVE = 9312
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
Saving snapshot of installed databases
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.rep_bug38877              [ fail ]

Warning: Not freed memory segments: 2
Warning: Memory that was not free'ed (560 bytes):
           104 bytes at 0x0080957a8, allocated at line 1019 in 'log_event.cc'
           456 bytes at 0x00806f040, allocated at line  201 in 'my_alloc.c'
mysqltest: The specified result file does not exist: '/home2/openxs/dbs/6.0/mysql-test/r/rep_bug38877.result'

The result from queries just before the failure was:
< snip >
id      a
1       a
2       b
3       c
4       d
5       e
557;
FLUSH LOGS;
DROP DATABASE ptr;
SHOW DATABASES;
Database
information_schema
mysql
ptr
test
SHOW TABLES FROM ptr;
Tables_in_ptr
t1
SELECT * FROM ptr.t1;
id      a
...

So, table is empty.

Wasn't able to repeat with current 6.0-falcon-team tree. The problem was likely fixed by patch for BUG#33575. At least without that patch it is repeatable.