bzr commit into mysql-5.1 branch (davi:2683) Bug#34306

[Davi Arnaut]

# At a local mysql-5.1 repository of davi

 2683 Davi Arnaut	2008-09-13
      Bug#34306: Can't make copy of log tables when server binary log is enabled
      
      The problem is that when statement-based replication was enabled,
      statements such as INSERT INTO .. SELECT FROM .. and CREATE TABLE
      .. SELECT FROM need to grab a read lock on the source table that
      does not permit concurrent inserts, which would in turn be denied
      if the source table is a log table because log tables can't be
      locked exclusively.
      
      The solution is to not take such a lock when the source table is
      a log table as it is unsafe to replicate log tables under statement
      based replication. Furthermore, the read lock that does not permits
      concurrent inserts is now only taken if statement-based replication
      is enabled and if the source table is not a log table.
added:
  mysql-test/suite/binlog/r/binlog_stm_row.result
  mysql-test/suite/binlog/t/binlog_stm_row.test
modified:
  include/thr_lock.h
  mysql-test/r/log_tables.result
  mysql-test/t/log_tables.test
  sql/lock.cc
  sql/sql_base.cc
  sql/sql_yacc.yy

per-file messages:
  include/thr_lock.h
    Introduce yet another lock type that my get upgraded
    depending on the binary log format. This is not a
    optimal solution but can be easily improved later.
  mysql-test/r/log_tables.result
    Add test case result for Bug#34306
  mysql-test/suite/binlog/r/binlog_stm_row.result
    Add test case result for Bug#34306
  mysql-test/suite/binlog/t/binlog_stm_row.test
    Add test case for Bug#34306
  mysql-test/t/log_tables.test
    Add test case for Bug#34306
  sql/lock.cc
    Assert that TL_READ_DEFAULT is not a real lock type.
  sql/sql_base.cc
    Only take a TL_READ_NO_INSERT log if the binary is enabled and the
    binary log format is statement-based and the table is not a log table.
  sql/sql_yacc.yy
    The lock type is now decided at open_tables time. This was actually
    misleading as the binary log format can be dynamically switched and this 
    would not change for statements that have already been parsed when the
    binary log format is changed (ie: prepared statements).
=== modified file 'include/thr_lock.h'
--- a/include/thr_lock.h	2008-02-18 22:29:39 +0000
+++ b/include/thr_lock.h	2008-09-13 23:31:20 +0000
@@ -29,6 +29,12 @@ extern ulong locks_immediate,locks_waite
 
 enum thr_lock_type { TL_IGNORE=-1,
 		     TL_UNLOCK,			/* UNLOCK ANY LOCK */
+                     /*
+                       Parser only! At open_tables() becomes TL_READ or
+                       TL_READ_NO_INSERT depending on the binary log format
+                       (SBR/RBR) and on the table category (log table).
+                     */
+                     TL_READ_DEFAULT,
 		     TL_READ,			/* Read lock */
 		     TL_READ_WITH_SHARED_LOCKS,
 		     /* High prior. than TL_WRITE. Allow concurrent insert */

=== modified file 'mysql-test/r/log_tables.result'
--- a/mysql-test/r/log_tables.result	2008-02-29 13:56:50 +0000
+++ b/mysql-test/r/log_tables.result	2008-09-13 23:31:20 +0000
@@ -832,6 +832,28 @@ Execute	select '000 001 002 003 004 005 
 Query	set global general_log = off
 deallocate prepare long_query;
 set global general_log = @old_general_log_state;
+DROP TABLE IF EXISTS slow_log_copy;
+DROP TABLE IF EXISTS general_log_copy;
+SET @old_general_log_state = @@global.general_log;
+SET @old_slow_log_state = @@global.slow_query_log;
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log;
+INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log;
+CREATE TABLE general_log_copy SELECT * FROM mysql.general_log;
+INSERT INTO general_log_copy SELECT * FROM mysql.general_log;
+DROP TABLE slow_log_copy;
+DROP TABLE general_log_copy;
+SET GLOBAL general_log = OFF;
+SET GLOBAL slow_query_log = OFF;
+CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log;
+INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log;
+CREATE TABLE general_log_copy SELECT * FROM mysql.general_log;
+INSERT INTO general_log_copy SELECT * FROM mysql.general_log;
+SET GLOBAL general_log = @old_general_log_state;
+SET GLOBAL slow_query_log = @old_slow_log_state;
+DROP TABLE slow_log_copy;
+DROP TABLE general_log_copy;
 SET @old_slow_log_state = @@global.slow_query_log;
 SET SESSION long_query_time = 0;
 SET GLOBAL slow_query_log = ON;

=== added file 'mysql-test/suite/binlog/r/binlog_stm_row.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_row.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_row.result	2008-09-13 23:31:20 +0000
@@ -0,0 +1,58 @@
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+SET GLOBAL BINLOG_FORMAT = STATEMENT;
+SET SESSION BINLOG_FORMAT = STATEMENT;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 LIKE t1;
+select @@SESSION.BINLOG_FORMAT;
+@@SESSION.BINLOG_FORMAT
+STATEMENT
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(2);
+#
+# Ensure that INSERT INTO .. SELECT FROM under SBR takes a read
+# lock that will prevent the source table from being modified.
+#
+SELECT GET_LOCK('Bug#34306', 120);
+GET_LOCK('Bug#34306', 120)
+1
+PREPARE stmt FROM "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)";
+EXECUTE stmt;;
+INSERT INTO t2 VALUES (3);;
+SELECT RELEASE_LOCK('Bug#34306');
+RELEASE_LOCK('Bug#34306')
+1
+SELECT RELEASE_LOCK('Bug#34306');
+RELEASE_LOCK('Bug#34306')
+1
+#
+# Ensure that INSERT INTO .. SELECT FROM prepared under SBR does
+# not prevent the source table from being modified if under RBR.
+#
+SET SESSION BINLOG_FORMAT = ROW;
+SELECT GET_LOCK('Bug#34306', 120);
+GET_LOCK('Bug#34306', 120)
+1
+EXECUTE stmt;;
+INSERT INTO t2 VALUES (4);
+SELECT RELEASE_LOCK('Bug#34306');
+RELEASE_LOCK('Bug#34306')
+1
+# Show binlog events
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS t1
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS t2
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT)
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 LIKE t1
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES(1)
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES(2)
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 SELECT * FROM t2 WHERE
GET_LOCK('Bug#34306', 120)
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (3)
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (4)
+master-bin.000001	#	Query	#	#	use `test`; BEGIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	use `test`; COMMIT
+DROP TABLE t1;
+DROP TABLE t2;

=== added file 'mysql-test/suite/binlog/t/binlog_stm_row.test'
--- a/mysql-test/suite/binlog/t/binlog_stm_row.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_stm_row.test	2008-09-13 23:31:20 +0000
@@ -0,0 +1,90 @@
+--source include/have_log_bin.inc
+--source include/have_binlog_format_row_or_statement.inc
+
+# Get rid of previous tests binlog
+--disable_query_log
+reset master;
+--enable_query_log
+
+#
+# Bug#34306: Can't make copy of log tables when server binary log is enabled
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+
+SET GLOBAL BINLOG_FORMAT = STATEMENT;
+SET SESSION BINLOG_FORMAT = STATEMENT;
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 LIKE t1;
+select @@SESSION.BINLOG_FORMAT;
+INSERT INTO t1 VALUES(1);
+INSERT INTO t2 VALUES(2);
+
+--connect(con1,localhost,root,,)
+--connect(con2,localhost,root,,)
+
+--echo #
+--echo # Ensure that INSERT INTO .. SELECT FROM under SBR takes a read
+--echo # lock that will prevent the source table from being modified.
+--echo #
+
+--connection con1
+SELECT GET_LOCK('Bug#34306', 120);
+--connection con2
+PREPARE stmt FROM "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)";
+--send EXECUTE stmt;
+--connection default
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE
+  state = "User lock" AND
+  info = "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)";
+--source include/wait_condition.inc
+--send INSERT INTO t2 VALUES (3);
+--connection con1
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE
+  state = "Locked" and info = "INSERT INTO t2 VALUES (3)";
+--source include/wait_condition.inc
+SELECT RELEASE_LOCK('Bug#34306');
+--connection con2
+--reap
+SELECT RELEASE_LOCK('Bug#34306');
+--connection default
+--reap
+
+--echo #
+--echo # Ensure that INSERT INTO .. SELECT FROM prepared under SBR does
+--echo # not prevent the source table from being modified if under RBR.
+--echo #
+
+--connection con2
+SET SESSION BINLOG_FORMAT = ROW;
+--connection con1
+SELECT GET_LOCK('Bug#34306', 120);
+--connection con2
+--send EXECUTE stmt;
+--connection default
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE
+  state = "User lock" AND
+  info = "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)";
+--source include/wait_condition.inc
+--connection con1
+INSERT INTO t2 VALUES (4);
+SELECT RELEASE_LOCK('Bug#34306');
+--connection con2
+--reap
+
+--disconnect con1
+--disconnect con2
+--connection default
+
+--echo # Show binlog events
+source include/show_binlog_events.inc;
+
+DROP TABLE t1;
+DROP TABLE t2;

=== modified file 'mysql-test/t/log_tables.test'
--- a/mysql-test/t/log_tables.test	2008-02-29 13:56:50 +0000
+++ b/mysql-test/t/log_tables.test	2008-09-13 23:31:20 +0000
@@ -937,6 +937,43 @@ deallocate prepare long_query;
 set global general_log = @old_general_log_state;
 
 #
+# Bug#34306: Can't make copy of log tables when server binary log is enabled
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS slow_log_copy;
+DROP TABLE IF EXISTS general_log_copy;
+--enable_warnings
+
+SET @old_general_log_state = @@global.general_log;
+SET @old_slow_log_state = @@global.slow_query_log;
+
+SET GLOBAL general_log = ON;
+SET GLOBAL slow_query_log = ON;
+
+CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log;
+INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log;
+CREATE TABLE general_log_copy SELECT * FROM mysql.general_log;
+INSERT INTO general_log_copy SELECT * FROM mysql.general_log;
+
+DROP TABLE slow_log_copy;
+DROP TABLE general_log_copy;
+
+SET GLOBAL general_log = OFF;
+SET GLOBAL slow_query_log = OFF;
+
+CREATE TABLE slow_log_copy SELECT * FROM mysql.slow_log;
+INSERT INTO slow_log_copy SELECT * FROM mysql.slow_log;
+CREATE TABLE general_log_copy SELECT * FROM mysql.general_log;
+INSERT INTO general_log_copy SELECT * FROM mysql.general_log;
+
+SET GLOBAL general_log = @old_general_log_state;
+SET GLOBAL slow_query_log = @old_slow_log_state;
+
+DROP TABLE slow_log_copy;
+DROP TABLE general_log_copy;
+
+#
 # Bug #31700: thd->examined_row_count not incremented for 'const' type queries
 #
 SET @old_slow_log_state = @@global.slow_query_log;

=== modified file 'sql/lock.cc'
--- a/sql/lock.cc	2008-04-08 05:20:58 +0000
+++ b/sql/lock.cc	2008-09-13 23:31:20 +0000
@@ -854,7 +854,7 @@ static MYSQL_LOCK *get_lock_data(THD *th
     if ((table=table_ptr[i])->s->tmp_table == NON_TRANSACTIONAL_TMP_TABLE)
       continue;
     lock_type= table->reginfo.lock_type;
-    DBUG_ASSERT (lock_type != TL_WRITE_DEFAULT);
+    DBUG_ASSERT(lock_type != TL_WRITE_DEFAULT && lock_type != TL_READ_DEFAULT);
     if (lock_type >= TL_WRITE_ALLOW_WRITE)
     {
       *write_lock_used=table;

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	2008-09-03 14:45:40 +0000
+++ b/sql/sql_base.cc	2008-09-13 23:31:20 +0000
@@ -4629,6 +4629,31 @@ int open_tables(THD *thd, TABLE_LIST **s
     {
       if (tables->lock_type == TL_WRITE_DEFAULT)
         tables->table->reginfo.lock_type= thd->update_lock_default;
+      else if (tables->lock_type == TL_READ_DEFAULT)
+      {
+        /*
+          Due to a statement-based replication limitation, statements such as
+          INSERT INTO .. SELECT FROM .. and CREATE TABLE .. SELECT FROM need
+          to grab a TL_READ_NO_INSERT lock on the source table in order to
+          prevent the replication of a concurrent statement that modifies the
+          source table. If such a statement gets applied on the slave before
+          the INSERT .. SELECT statement finishes, data on the master could
+          differ from data on the slave and end-up with a discrepancy between
+          the binary log and table state.
+          Furthermore, this does not apply to I_S and log tables as it's always
+          unsafe to replicate such tables under statement-based replication as
+          the table on the slave might contain other data (ie: general_log is
+          enabled on the slave). The statement will be marked as unsafe for SBR
+          in decide_logging_format().
+        */
+        bool log_on= mysql_bin_log.is_open() && (thd->options &
OPTION_BIN_LOG);
+        ulong binlog_format= thd->variables.binlog_format;
+        if ((log_on == FALSE) || (binlog_format == BINLOG_FORMAT_ROW) ||
+            (tables->table->s->table_category == TABLE_CATEGORY_PERFORMANCE))
+          tables->table->reginfo.lock_type= TL_READ;
+        else
+          tables->table->reginfo.lock_type= TL_READ_NO_INSERT;
+      }
       else if (tables->table->s->tmp_table == NO_TMP_TABLE)
         tables->table->reginfo.lock_type= tables->lock_type;
     }
@@ -5036,6 +5061,9 @@ int decide_logging_format(THD *thd, TABL
     void* prev_ht= NULL;
     for (TABLE_LIST *table= tables; table; table= table->next_global)
     {
+      TABLE_SHARE *share= table->table ? table->table->s : NULL;
+      if (share && share->table_category == TABLE_CATEGORY_PERFORMANCE)
+        thd->lex->set_stmt_unsafe();
       if (!table->placeholder() && table->lock_type >=
TL_WRITE_ALLOW_WRITE)
       {
         ulonglong const flags= table->table->file->ha_table_flags();

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2008-09-05 10:44:16 +0000
+++ b/sql/sql_yacc.yy	2008-09-13 23:31:20 +0000
@@ -4299,7 +4299,7 @@ create_select:
           SELECT_SYM
           {
             LEX *lex=Lex;
-            lex->lock_option= using_update_log ? TL_READ_NO_INSERT : TL_READ;
+            lex->lock_option= TL_READ_DEFAULT;
             if (lex->sql_command == SQLCOM_INSERT)
               lex->sql_command= SQLCOM_INSERT_SELECT;
             else if (lex->sql_command == SQLCOM_REPLACE)