Bug #50914 mysqlbinlog not handling drop of current default database

• Submitted: 4 Feb 2010 14:00
• Modified: 8 Jan 2011 1:06
• Reporter: Hartmut Holzgraefe
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• OS: Linux
• Assigned to: Daogang Qu
• CPU Architecture: Any

[4 Feb 2010 14:00] Hartmut Holzgraefe

Description:
mysqlbinlog only prints "use $database" statements to its output stream when the active default database changes between events. This fails when dropping and recreating that database though.

How to repeat:
Perform the following sequence of commands on a fresh server installation:

  CREATE DATABASE test1;
  USE test1;
  CREATE TABLE t1(id int);
  DROP DATABASE test1;
  CREATE DATABASE test1;
  USE test1;
  CREATE TABLE t1(id int);

When replaying the binary log recorded for the above it 
essentially becomes:

  CREATE DATABASE test1;
  USE test1;
  CREATE TABLE t1(id int);
  DROP DATABASE test1;
  CREATE DATABASE test1;
  CREATE TABLE t1(id int);

which is missing the 2nd "USE test1" statement
from the original statement sequence. This leads
to 

  Error: No Database Selected

on the final CREATE statement

Suggested fix:
Make sure "use $database" statements are issued by mysqlbinlog in such cases.

[29 Nov 2010 2:44] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125282

3521 [e-mail]	2010-11-29
      Bug #50914  	mysqlbinlog not handling drop of current default database
      
      mysqlbinlog only prints "use $database" statements to its output stream
      when the active default database changes between events. Which will cause
      "No Database Selected" error when dropping and recreating that database
      though.
      
      To fix the problem, mysqlbinlog will print "use $database" statements
      to its output stream for every query log event as long as the database
      is not suppressed expressly.
     @ mysql-test/extra/binlog_tests/database.test
        Added test to verify if the approach of the mysqlbinlog prints
        "use $database"statements to its output stream will cause
        "No Database Selected" error when dropping and recreating
        that database though.
     @ mysql-test/r/mysqlbinlog.result
        Updated for the patch of Bug#50914.
     @ mysql-test/r/mysqlbinlog2.result
        Updated for the patch of Bug#50914.
     @ mysql-test/r/user_var-binlog.result
        Updated for the patch of Bug#50914.
     @ mysql-test/suite/binlog/r/binlog_database.result
        Test result for Bug#50914.
     @ mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
        Updated for the patch of Bug#50914.
     @ mysql-test/suite/rpl/r/rpl_sp.result
        Updated for the patch of Bug#50914.
     @ sql/log_event.cc
        Updated code to make mysqlbinlog print "use $database" statements
        to its output stream for every query log event as long as the
        database is not suppressed expressly.

[7 Dec 2010 7:03] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126184

3521 [e-mail]	2010-12-07
      Bug #50914  	mysqlbinlog not handling drop of current default database
      
      mysqlbinlog only prints "use $database" statements to its output stream
      when the active default database changes between events. Which will cause
      "No Database Selected" error when dropping and recreating that database
      though.
      
      To fix the problem to clear print_event_info->db when printing an event
      with LOG_EVENT_SUPPRESS_USE flag, which will be set for CREATE/DROP/ALTER
      database statements, so that the Query_log_event after such statements
      will be printed with the use 'db' anyway.
     @ mysql-test/extra/binlog_tests/database.test
        Added test to verify if the approach of the mysqlbinlog prints
        "use $database"statements to its output stream will cause
        "No Database Selected" error when dropping and recreating
        that database though.
     @ mysql-test/r/mysqlbinlog.result
        Updated for the patch of Bug#50914.
     @ mysql-test/suite/binlog/r/binlog_database.result
        Test result for Bug#50914.
     @ sql/log_event.cc
        Updated code to make mysqlbinlog print "use $database" statements
        to its output stream for every query log event as long as the
        database is not suppressed expressly.

[14 Dec 2010 3:10] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126718

3521 [e-mail]	2010-12-14
      Bug #50914  	mysqlbinlog not handling drop of current default database
      
      mysqlbinlog only prints "use $database" statements to its output stream
      when the active default database changes between events. Which will cause
      "No Database Selected" error when dropping and recreating that database
      though.
      
      To fix the problem to clear print_event_info->db when printing an event
      of CREATE/DROP/ALTER database statements, so that the Query_log_event
      after such statements will be printed with the use 'db' anyway except
      transaction keywords.
     @ mysql-test/r/mysqlbinlog.result
        Test result for Bug#50914.
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the approach of the mysqlbinlog prints
        "use $database"statements to its output stream will cause
        "No Database Selected" error when dropping and recreating
        that database though.
     @ sql/log_event.cc
        Updated code to clear print_event_info->db when printing an event
        of CREATE/DROP/ALTER database statements, so that the Query_log_event
        after such statements will be printed with the use 'db' anyway except
        transaction keywords.

[28 Dec 2010 10:45] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127619

3535 [e-mail]	2010-12-28
      Bug #50914  	mysqlbinlog not handling drop of current default database
      
      mysqlbinlog only prints "use $database" statements to its output stream
      when the active default database changes between events. This will cause
      "No Database Selected" error when dropping and recreating that database.
      
      To fix the problem, we clear print_event_info->db when printing an event
      of CREATE/DROP/ALTER database statements, so that the Query_log_event
      after such statements will be printed with the use 'db' anyway except
      transaction keywords.
     @ mysql-test/r/mysqlbinlog.result
        Test result for Bug#50914.
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the approach of the mysqlbinlog prints
        "use $database" statements to its output stream will cause
        "No Database Selected" error when dropping and recreating
        that database.
     @ sql/log_event.cc
        Updated code to clear print_event_info->db when printing an event
        of CREATE/DROP/ALTER database statements, so that the Query_log_event
        after such statements will be printed with the use 'db' anyway except
        transaction keywords.

[29 Dec 2010 4:02] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127639

3536 [e-mail]	2010-12-29
      Bug #50914  	mysqlbinlog not handling drop of current default database
      
      mysqlbinlog only prints "use $database" statements to its output stream
      when the active default database changes between events. This will cause
      "No Database Selected" error when dropping and recreating that database.
      
      To fix the problem, we clear print_event_info->db when printing an event
      of CREATE/DROP/ALTER database statements, so that the Query_log_event
      after such statements will be printed with the use 'db' anyway except
      transaction keywords.
     @ mysql-test/r/mysqlbinlog.result
        Test result for Bug#50914.
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the approach of the mysqlbinlog prints
        "use $database" statements to its output stream will cause
        "No Database Selected" error when dropping and recreating
        that database.
     @ sql/log_event.cc
        Updated code to clear print_event_info->db when printing an event
        of CREATE/DROP/ALTER database statements, so that the Query_log_event
        after such statements will be printed with the use 'db' anyway except
        transaction keywords.

[29 Dec 2010 5:32] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127642

3228 [e-mail]	2010-12-29 [merge]
      Bug #50914    mysqlbinlog not handling drop of current default database
            
      mysqlbinlog only prints "use $database" statements to its output stream
      when the active default database changes between events. This will cause
      "No Database Selected" error when dropping and recreating that database.
            
      To fix the problem, we clear print_event_info->db when printing an event
      of CREATE/DROP/ALTER database statements, so that the Query_log_event
      after such statements will be printed with the use 'db' anyway except
      transaction keywords.
     @ mysql-test/r/mysqlbinlog.result
        Test result for Bug#50914.
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the approach of the mysqlbinlog prints
        "use $database" statements to its output stream will cause
        "No Database Selected" error when dropping and recreating
        that database.
     @ sql/log_event.cc
        Updated code to clear print_event_info->db when printing an event
        of CREATE/DROP/ALTER database statements, so that the Query_log_event
        after such statements will be printed with the use 'db' anyway except
        transaction keywords.

[29 Dec 2010 5:45] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127643

3474 [e-mail]	2010-12-29 [merge]
      Bug #50914  	mysqlbinlog not handling drop of current default database
            
      mysqlbinlog only prints "use $database" statements to its output stream
      when the active default database changes between events. This will cause
      "No Database Selected" error when dropping and recreating that database.
            
      To fix the problem, we clear print_event_info->db when printing an event
      of CREATE/DROP/ALTER database statements, so that the Query_log_event
      after such statements will be printed with the use 'db' anyway except
      transaction keywords.
     @ mysql-test/r/mysqlbinlog.result
        Test result for Bug#50914.
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the approach of the mysqlbinlog prints
        "use $database" statements to its output stream will cause
        "No Database Selected" error when dropping and recreating
        that database.
     @ sql/log_event.cc
        Updated code to clear print_event_info->db when printing an event
        of CREATE/DROP/ALTER database statements, so that the Query_log_event
        after such statements will be printed with the use 'db' anyway except
        transaction keywords.

[29 Dec 2010 12:51] Bugs System

Pushed into mysql-5.1 5.1.55 (revid:[e-mail]-20101229113432-o0c3f1pqklek1txm) (version source revid:[e-mail]-20101229113109-ft3q9tewyueopxgf) (merge vers: 5.1.55) (pib:24)

[29 Dec 2010 12:52] Bugs System

Pushed into mysql-trunk 5.6.2 (revid:[e-mail]-20101229113850-5rhksndrwz5dwcd7) (version source revid:[e-mail]-20101229113149-c4hnmghox8obr75c) (merge vers: 5.6.2) (pib:24)

[29 Dec 2010 12:53] Bugs System

Pushed into mysql-5.5 5.5.9 (revid:[e-mail]-20101229113652-km2v993aurv7h79j) (version source revid:[e-mail]-20101229113132-uonlbcc2uopff8yb) (merge vers: 5.5.9) (pib:24)

[4 Jan 2011 7:48] Daogang Qu

Pushed into mysql-5.1-bugteam, mysql-5.5-bugteam and mysql-trunk-bugfixing.

[8 Jan 2011 1:06] Jon Stephens

Documented bugfix in the 5.1.51, 5.5.9, and 5.6.2 changelogs as follows:

        mysqlbinlog printed USE statements to its output only when the
        default database changed between events. To illustrate how this
        cause problems, suppose that a user issued the following
        sequence of statements:

CREATE DATABASE mydb;
USE mydb;
CREATE TABLE mytable(column_definitions);
DROP DATABASE mydb;
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE mytable(column_definitions);

        When played back using mysqlbinlog, the second CREATE TABLE
        statement failed with Error: No Database Selected because the
        second USE statement was not played back due to the fact a
        database other than mydb was never selected.

        This fix insures that mysqlbinlog outputs a USE statement 
        whenever it read from the binary log.

Closed.