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-12
      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.
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/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
    a bug as the binary log can be dynamic enabled and this would conflict
    with 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 00:44:12 +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 00:44:12 +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;

=== 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 00:44:12 +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 00:44:12 +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 00:44:12 +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= global_system_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 00:44:12 +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)