MySQL Bugs: #52994: Tables are lost when db is dropped after backup and restore performed using IHB

Bug #52994	Tables are lost when db is dropped after backup and restore performed using IHB
Submitted:	20 Apr 2010 16:20	Modified:	20 Apr 2010 21:27
Reporter:	Hema Sridharan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Enterprise Backup	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Pekka Lampio	CPU Architecture:	Any

Description:
* Login in mysql client and create databases(db1 and db2), tables (db1.t1 and 
db2.t2) and dump some data in tables
* Now to perform backup, execute innobackup and the resulting output is 
attached in IHB_innobkp.txt
* Drop database db2 from mysql client manually
* Execute the innobackup --apply-log and the result is attached in 
IHB_applylog.txt
* Now execute the innobackup --copy-back option to perform restore. The 
result is attached in IHB_copyback.txt
* Check the tables in db1 and db2. We can notice that db2.t2 will be missing as we dropped the database before performing restore.

How to repeat:
mysql> select @@storage_engine;
+------------------+
| @@storage_engine |
+------------------+
| InnoDB           |
+------------------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_file%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_file_io_threads | 4     |
| innodb_file_per_table  | ON    |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> create table db1.t1(id int);
Query OK, 0 rows affected (0.28 sec)

mysql> create table db2.t2(a char(5));
Query OK, 0 rows affected (0.42 sec)

mysql> insert into db1.t1 values(10),(20),(30),(40);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into db2.t2 values('a'),('b'),('c'),('d');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from db1.t1;
+------+
| id   |
+------+
|   10 |
|   20 |
|   30 |
|   40 |
+------+
4 rows in set (0.00 sec)

mysql> select * from db2.t2
    -> ;
+------+
| a    |
+------+
| a    |
| b    |
| c    |
| d    |
+------+
4 rows in set (0.00 sec)

mysql> show create table db1.t1;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show databases like 'db%';
+----------------+
| Database (db%) |
+----------------+
| db1            |
| db2            |
+----------------+
2 rows in set (0.00 sec)

Now execute innobackup for performing backup of databases (pls see 
IHB_innobkp.test for output and datadir contents)

Drop database db1;

Now execute innobackup --apply-log(please look in IHB_applylog for output and 
also the backup directory contents)

Apply the innobackup --copy-back(attached IHB_copyback.txt for output and  datadir contents after executing this option)

Now from mysql client check the databases

mysql> show databases like 'db%';
+----------------+
| Database (db%) |
+----------------+
| db1            |
| db2            |
+----------------+
2 rows in set (0.00 sec)

mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> show tables from db2;
+---------------+
| Tables_in_db2 |
+---------------+
| t2            |
+---------------+
1 row in set (0.00 sec)

select * from db2.t2;
+------+
| a    |
+------+
| a    |
| b    |
| c    |
| d    |
+------+
4 rows in set (0.00 sec)

select * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

Notice that we can see that table t1 is missing from the database db1 as we dropped db1 after backup.

Output of Innobackup and datadir files

Attachment: IHB_innobkp.txt (text/plain), 8.01 KiB.

The innobackup executed with --apply-log and its output is placed inthe below file

Attachment: IHB_applylog.txt (text/plain), 5.33 KiB.

The innobackup with --copy-back option

Attachment: IHB_copyback.txt (text/plain), 4.81 KiB.

Hema, mysqld should be shut down before copyback, then restarted after the copyback. 
Please reproduce with mysqld restart as above, and provide the following 
information:

- stdout/stderr for both mysqld instances
- mysqld datadir before and after mysqld restart

When I shutdown mysqld, restart the server, then do innobackup --copy-back. I see the following in the mysql client

mysql> show databases like 'db%';
+----------------+
| Database (db%) |
+----------------+
| db1            |
| db2            |
+----------------+
2 rows in set (0.00 sec)

mysql> show tables from db1;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> show tables from db2;
+---------------+
| Tables_in_db2 |
+---------------+
| t2            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

mysql> select * from db2.t2;
ERROR 1146 (42S02): Table 'db2.t2' doesn't exist

We can notice that tables are missing from db1 and db2 when we restart the server and then apply --copy-back option with innobackup

The mysqld.1.err output is attached in the file.

This file contains the datadir contents and mysqd.err

Attachment: mysqld.1.err (application/octet-stream, text), 5.99 KiB.

Hema, to quote from you:

When I shutdown mysqld, restart the server, then do innobackup --copy-back. I see the
following in the mysql client

We can notice that tables are missing from db1 and db2 when we restart the server and
then apply --copy-back option with innobackup

This is wrong. mysqld shouldn't be running when you perform copy-back:

shutdown mysqld
copy-back
restart mysqld

please perform the actions above, providing output at least for the second mysqld and the copyback, backup dir contents, and three instances of datadir contents: after shutdown but before copyback; after copyback but before mysqld restart; after restart when you see the problem.
If we still see commands copying the .ibd files for missing tables after that,
but no tables after the copy,
could you rerun the 'cp' commands manually to see if the copy is actually taking place?

please also post .cnf file for mysqld you start on the restored database.

I would be very interesting to see how we lose the data because this is
the standard procedure of using HB:

<ima> 1. backup copies data and log files to the backup directory. This is more than just copy.
<ima> 2. apply log applies redo log in the backup directory. It neither reads nor writes mysqld data directory and doesn't care if mysqld is running or not
<ima> 3. shutdown mysqld
<ima> 4. copy-back is plain copy, you might as well perform cp -pR. This will be our next experiment
<ima> 5. restart mysqld. It does yet another recovery and most importantly rolls back incomplete transactions
*** sunny|away is now known as sunny
<ima> 6. restore is now done, enjoy

this is both obvious and documented:
a- you can't have mysqld writing over the same files that are being copied
by copyback
b- after the copyback you restart musqld, otherwise we can't use the database,
can we?

so please reproduce following this procedure, otherwise it's not a bug.
As for the files:

- mysqld .cnf file(s)
- mysqld stdout/stderr from both instances
(cd directory; ls -laR `pwd`) for the following:

data directory before backup
data directory after backup
backup directory after backup
backup directory after apply
data directory after mysqld shutdown but before copyback
data directory after the copyback but before mysqld startup
data directory after mysqld startup

since we would seem to have a good backup already, we could start with
this small test:

<ima> 3. shutdown mysqld
<ima> 4. copy-back is plain copy, you might as well perform cp -pR. This will be our next experiment
<ima> 5. restart mysqld. It does yet another recovery and most importantly rolls back incomplete transactions
*** sunny|away is now known as sunny
<ima> 6. restore is now done, enjoy

with mysqld .cnf and stdout/stderr and
backup directory before copyback
data directory after mysqld shutdown but before copyback
data directory after the copyback but before mysqld startup
data directory after mysqld startup

Please follow the instructions to the letter, I believe we've spent
enough time on this already besides there may be still a problem
out there. Failure to shutdown mysqld doesn't easily explain the lack
of the .ibd file. But we need to do things right first.

<hema> ima: yes ./mtr wipes out everything and that is what I mentioned in my bug report
<ima> ok, I thought the restart was by a conventional mysqld .....

the problem is, mysqld is started with mtr which wipes out the database.
copy-back in fact appears to be working.
Hema and I are working on it.

after painstakingly starting the server from the command line, everything 
works. So the problem is how to start mysqld without wiping up
the database with mtr (mysql-test-run.pl --start-dirty?) but 
it does look like there's no bug in ibbackup.

But we need to find a way to execute the tests in MySQL test framework as well.