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: | |
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
[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".