From 8d6cbf2057706a3fbc076fa5709a9557435cd74b Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Mon, 29 Sep 2008 10:53:40 -0300 Subject: 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. 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/mysql_priv.h: Export new function. sql/mysqld.cc: Remove using_update_log. sql/sql_base.cc: Introduce function that returns the appropriate read lock type depending on how the statement is going to be replicated. It will 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_parse.cc: Remove using_update_log. sql/sql_update.cc: Use new function to choose read lock type. sql/sql_yacc.yy: The lock type is now decided at open_tables time. This old behavior 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). --- mysql-test/t/log_tables.test | 46 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 46 insertions(+) (limited to 'mysql-test/t/log_tables.test') diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index 3047d16d3b6..34086336fa8 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -936,6 +936,52 @@ select command_type, argument from mysql.general_log where thread_id = @thread_i 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 log_count; +DROP TABLE IF EXISTS slow_log_copy; +DROP TABLE IF EXISTS general_log_copy; +--enable_warnings + +CREATE TABLE log_count (count BIGINT(21)); + +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; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.slow_log)); +DROP TABLE slow_log_copy; + +CREATE TABLE general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log)); +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; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.slow_log)); +DROP TABLE slow_log_copy; + +CREATE TABLE general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO general_log_copy SELECT * FROM mysql.general_log; +INSERT INTO log_count (count) VALUES ((SELECT count(*) FROM mysql.general_log)); +DROP TABLE general_log_copy; + +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; + +DROP TABLE log_count; + # # Bug #31700: thd->examined_row_count not incremented for 'const' type queries # -- cgit v1.2.1