Bug #38617 Mysqlbinlog fails to recover data if table is created without "use db" option.
Submitted: 6 Aug 2008 22:43 Modified: 11 Sep 2008 13:10
Reporter: Hema Sridharan Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:mysql-6.0-backup, mysql-6.0 OS:Linux
Assigned to: CPU Architecture:Any

[6 Aug 2008 22:43] Hema Sridharan
Description:
1) Enable binary logging and create database and tables .
2) Insert some values in the table.
3) Drop database and perform recovery of database using mysqlbinlog utility.
4) Mysqlbinlog fails to recover data if "use db" option is not used before creating tables.

How to repeat:
#Enable binary logging
--source include/have_log_bin.inc

--error 0,1
--remove_file $MYSQLTEST_VARDIR/master-data/ptr.bak;
CREATE DATABASE IF NOT EXISTS ptr;
CREATE TABLE ptr.t1(id int, a char(4));
INSERT INTO ptr.t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
SELECT * FROM ptr.t1;
FLUSH LOGS;
DROP DATABASE ptr;
--exec $MYSQL_BINLOG --database=ptr  $MYSQLTEST_VARDIR/log/master-bin.000001 |$MYSQL
SHOW DATABASES;
SHOW TABLES FROM ptr;
SELECT * FROM ptr.t1;

Result
======

mysqltest: At line 22: query 'SELECT * FROM t1' failed: 1146: Table 'ptr.t1' doesn't exist

The result from queries just before the failure was:
< snip >
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
FLUSH LOGS;
DROP DATABASE ptr;
SHOW DATABASES;
Database
information_schema
mysql
ptr
test
USE ptr;
SHOW TABLES FROM ptr;
Tables_in_ptr
SELECT * FROM t1;
[7 Aug 2008 5:45] Susanne Ebrecht
Many thanks for writing a bug report.

I can't repeat this with MySQL 6.0 bzr tree.

CREATE DATABASE IF NOT EXISTS ptr;
CREATE TABLE ptr.t1(id int, a char(4));
INSERT INTO ptr.t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
SELECT * FROM ptr.t1;
FLUSH LOGS;
DROP DATABASE ptr;

$ ./bin/mysqlbinlog var/mysql-bin.000001 | ./bin/mysql

$ ./bin/mysql ptr
mysql> show tables; 
+---------------+
| Tables_in_ptr |
+---------------+
| t1            | 
+---------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | a    |
+------+------+
|    1 | a    | 
|    2 | b    | 
|    3 | c    | 
|    4 | d    | 
|    5 | e    | 
+------+------+
5 rows in set (0.00 sec)

Just take a look into mysql-bin.000001:

$ less mysql-bin.000001
"var/mysql-bin.000001" may be a binary file.  See it anyway? y
CREATE DATABASE IF NOT EXISTS ptr
CREATE TABLE ptr.t1(id int, a char(4))
INSERT INTO ptr.t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e')

As you can see there is always the database name in front of the table name.
[7 Aug 2008 6:28] Susanne Ebrecht
$ ./bin/mysqlbinlog --help
....
-d, --database=name List entries for just this database (local log only).
....
[7 Aug 2008 16:59] Hema Sridharan
I am still able to reproduce the issue in latest mysql-6.0 bzr tree and also in backup tree. 
Please see the below example that I performed manually.
mysql> CREATE DATABASE IF NOT EXISTS hh;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE hh.t1(id int, a char(4));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO hh.t1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM hh.t1;
+------+------+
| id   | a    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)

mysql>
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.12 sec)

mysql> drop database hh;
Query OK, 1 row affected (0.00 sec)

-bash-3.1$ ./mysqlbinlog --database=hh /export/home/tmp/hema/mainp/mysql-test/var/log/master-bin.000001 | /export/home/tmp/hema/mainp/bin/mysql -u root --port=9306 --protocol=tcp
ERROR 1146 (42S02) at line 31: Table 'hh.t1' doesn't exist

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hh                 |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.01 sec)

mysql> use hh;
Database changed
mysql> show tables;
Empty set (0.00 sec)

From the above we can notice that database is recovered and not the tables. If we have created table using "use ptr" option, then everything works fine. 

I am using 64bit Linux OS.
[10 Aug 2008 0:05] Hema Sridharan
Binlog file with use db option

Attachment: win.bin (application/octet-stream, text), 2.03 KiB.

[10 Aug 2008 0:06] Hema Sridharan
Binlog file without use db option

Attachment: wout.bin (application/octet-stream, text), 1.60 KiB.

[10 Aug 2008 0:10] Hema Sridharan
Hi Shane,

Please find the attached binlog files (with / without use db option(win.bin / wout.bin). I see that when we don't include the "use db" option, the tables are not dumped in the binlog file (though those tables are created in that database).

For example:
CREATE DATABASE IF NOT EXISTS hh;
CREATE TABLE hh.t1 (id int);

1. Table t1 is created in database hh. Insert some values in to table hh.t1
2. In this case, we don't have to include "use db" option before creating table to indicate which database it is in.
3. Now if I try to recover data(after dropping database) using mysqlbinlog utility, I get the error "ERROR 1146 (42S02) at line 31: Table 'hh.t1' doesn't exist".
4. If I send "use db" option before creating table, I don't see this error.

Note: This shouldn't be the behavior, as the table is created in the specific database only. I feel that "USE db" option should not be blocking the data dump in the binlog file.
[11 Aug 2008 13:10] Sveta Smirnova
Hema,

have you tried

./mysqlbinlog --database=hh
/export/home/tmp/hema/mainp/mysql-test/var/log/master-bin.000001 |
/export/home/tmp/hema/mainp/bin/mysql -u root --port=9306 --protocol=tcp hh

?

(Same command as you provided in the comment "[7 Aug 18:59] Hema Sridharan", but with database specified for mysql command line client too)

What is the result?
[11 Sep 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".