#At file:///home/acorreia/workspace.sun/repository.mysql/bzrwork/bug-50479/mysql-5.1-rep%2B2/ based on revid:sven.sandberg@stripped
3182 Alfranio Correia 2010-03-08
BUG#50479 DDL stmt on row-only/stmt-only tables generate spurious binlog_format errors
In the fix of BUG#39934 in 5.1-rep+3, errors are generated when
binlog_format=row and a statement modifies a table restricted to
statement-logging (ER_BINLOG_ROW_MODE_AND_STMT_ENGINE); or if
binlog_format=statement and a statement modifies a table limited to
row-logging (ER_BINLOG_STMT_MODE_AND_ROW_ENGINE).
However, some DDL statements that lock tables (e.g. ALTER TABLE,
CREATE INDEX and CREATE TRIGGER) were causing spurious errors,
although any row might be inserted into the binary log.
To fix the problem, we tagged statements that may generate
rows into the binary log and thence the warning messages are
only printed out wen the appropriate conditions hold and rows
might be changed.
@ mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result
Added test case.
@ mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt
Added test case.
@ mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test
Added test case.
added:
mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result
mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt
mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test
modified:
sql/sql_class.cc
sql/sql_class.h
sql/sql_parse.cc
storage/innobase/handler/ha_innodb.cc
storage/innobase/handler/ha_innodb.h
storage/innodb_plugin/handler/ha_innodb.cc
storage/innodb_plugin/handler/ha_innodb.h
=== added file 'mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result'
--- a/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/r/binlog_spurious_ddl_errors.result 2010-03-08 19:09:08 +0000
@@ -0,0 +1,23 @@
+SET @old_binlog_format= @@global.binlog_format;
+INSTALL PLUGIN example SONAME 'ha_example.so';
+SET binlog_format = STATEMENT;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+CREATE TABLE t_row (a VARCHAR(100)) ENGINE = InnoDB;
+ALTER TABLE t_row ADD COLUMN b INT;
+CREATE TRIGGER trig_row BEFORE INSERT ON t_row FOR EACH ROW INSERT INTO t_stmt VALUES (1);
+CREATE INDEX i ON t_row(a);
+DROP TABLE t_row;
+SET binlog_format = ROW;
+CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE;
+ALTER TABLE t_stmt ADD COLUMN b INT;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE'
+CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1);
+CREATE INDEX i ON t_stmt(a);
+ERROR 42000: Too many key parts specified; max 0 parts allowed
+SET binlog_format = STATEMENT;
+CREATE TABLE t_row ENGINE = InnoDB SELECT * FROM t_stmt;
+ERROR HY000: Cannot execute statement: binlogging impossible since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
+DROP TABLE t_stmt;
+UNINSTALL PLUGIN example;
+SET @@global.binlog_format = @old_binlog_format;
+SET @@session.binlog_format = @old_binlog_format;
=== added file 'mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt'
--- a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors-master.opt 2010-03-08 19:09:08 +0000
@@ -0,0 +1 @@
+--innodb $EXAMPLE_PLUGIN_OPT
=== added file 'mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test'
--- a/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_spurious_ddl_errors.test 2010-03-08 19:09:08 +0000
@@ -0,0 +1,62 @@
+################################################################################
+# BUG#50479:
+#
+# In the fix of BUG#39934 in 5.1-rep+3, errors are generated when
+# binlog_format=row and a statement modifies a table restricted to
+# statement-logging (ER_BINLOG_ROW_MODE_AND_STMT_ENGINE); or if
+# binlog_format=statement and a statement modifies a table limited to
+# row-logging (ER_BINLOG_STMT_MODE_AND_ROW_ENGINE).
+#
+# In this test case, we check if some DDL statements that lock tables do not
+# trigger errors as they do not generate rows events and as such are harmless
+# from the point of view of conflicts between the engine's supported logging
+# format and the value of binlog_format.
+#
+# Currently, ALTER TABLE, CREATE INDEX and CREATE TRIGGER.
+#
+# CREATE TABLE ... SELECT should generate an error because the command can
+# generate row events. In contrast, CREATE TABLE without SELECT cannot generate
+# an error.
+################################################################################
+--source include/have_innodb.inc
+--source include/have_example_plugin.inc
+--source include/have_log_bin.inc
+
+SET @old_binlog_format= @@global.binlog_format;
+
+INSTALL PLUGIN example SONAME 'ha_example.so';
+################# ER_BINLOG_STMT_MODE_AND_ROW_ENGINE #################
+SET binlog_format = STATEMENT;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+CREATE TABLE t_row (a VARCHAR(100)) ENGINE = InnoDB;
+
+ALTER TABLE t_row ADD COLUMN b INT;
+
+CREATE TRIGGER trig_row BEFORE INSERT ON t_row FOR EACH ROW INSERT INTO t_stmt VALUES (1);
+
+CREATE INDEX i ON t_row(a);
+
+DROP TABLE t_row;
+
+################# ER_BINLOG_ROW_MODE_AND_STMT_ENGINE #################
+SET binlog_format = ROW;
+CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE;
+
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE t_stmt ADD COLUMN b INT;
+
+CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1);
+
+--error ER_TOO_MANY_KEY_PARTS
+CREATE INDEX i ON t_stmt(a);
+
+################# CREATE SELECT #################
+SET binlog_format = STATEMENT;
+--error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
+CREATE TABLE t_row ENGINE = InnoDB SELECT * FROM t_stmt;
+
+################# CLEAN UP #################
+DROP TABLE t_stmt;
+UNINSTALL PLUGIN example;
+SET @@global.binlog_format = @old_binlog_format;
+SET @@session.binlog_format = @old_binlog_format;
=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc 2010-03-03 14:43:35 +0000
+++ b/sql/sql_class.cc 2010-03-08 19:09:08 +0000
@@ -3089,6 +3089,12 @@ extern "C" bool thd_binlog_filter_ok(con
{
return binlog_filter->db_ok(thd->db);
}
+
+extern "C" bool thd_generates_rows(const MYSQL_THD thd)
+{
+ return ((sql_command_flags[thd->lex->sql_command] &
+ CF_CAN_GENERATE_ROW_EVENTS));
+}
#endif // INNODB_COMPATIBILITY_HOOKS */
/****************************************************************************
@@ -3647,7 +3653,9 @@ int THD::decide_logging_format(TABLE_LIS
*/
my_error((error= ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE), MYF(0));
}
- else if (variables.binlog_format == BINLOG_FORMAT_ROW)
+ else if (variables.binlog_format == BINLOG_FORMAT_ROW &&
+ (sql_command_flags[lex->sql_command] &
+ CF_CAN_GENERATE_ROW_EVENTS))
{
/*
2. Error: Cannot modify table that uses a storage engine
@@ -3685,7 +3693,9 @@ int THD::decide_logging_format(TABLE_LIS
*/
my_error((error= ER_BINLOG_ROW_INJECTION_AND_STMT_MODE), MYF(0));
}
- else if ((flags_all_set & HA_BINLOG_STMT_CAPABLE) == 0)
+ else if ((flags_all_set & HA_BINLOG_STMT_CAPABLE) == 0 &&
+ (sql_command_flags[lex->sql_command] &
+ CF_CAN_GENERATE_ROW_EVENTS))
{
/*
5. Error: Cannot modify table that uses a storage engine
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2010-03-03 14:43:35 +0000
+++ b/sql/sql_class.h 2010-03-08 19:09:08 +0000
@@ -3130,6 +3130,11 @@ public:
joins are currently prohibited in these statements.
*/
#define CF_REEXECUTION_FRAGILE 32
+/**
+ Identifies statements that may generate row events
+ that eventually will end up in the binary log.
+*/
+#define CF_CAN_GENERATE_ROW_EVENTS 64
/* Functions in sql_class.cc */
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2010-03-03 14:43:35 +0000
+++ b/sql/sql_parse.cc 2010-03-08 19:09:08 +0000
@@ -253,12 +253,14 @@ void init_update_queries(void)
{
bzero((uchar*) &sql_command_flags, sizeof(sql_command_flags));
- sql_command_flags[SQLCOM_CREATE_TABLE]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE;
+ sql_command_flags[SQLCOM_CREATE_TABLE]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
+ CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_CREATE_INDEX]= CF_CHANGES_DATA;
sql_command_flags[SQLCOM_ALTER_TABLE]= CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND;
sql_command_flags[SQLCOM_TRUNCATE]= CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND;
sql_command_flags[SQLCOM_DROP_TABLE]= CF_CHANGES_DATA;
- sql_command_flags[SQLCOM_LOAD]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE;
+ sql_command_flags[SQLCOM_LOAD]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |
+ CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_CREATE_DB]= CF_CHANGES_DATA;
sql_command_flags[SQLCOM_DROP_DB]= CF_CHANGES_DATA;
sql_command_flags[SQLCOM_RENAME_TABLE]= CF_CHANGES_DATA;
@@ -270,21 +272,21 @@ void init_update_queries(void)
sql_command_flags[SQLCOM_DROP_EVENT]= CF_CHANGES_DATA;
sql_command_flags[SQLCOM_UPDATE]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
- CF_REEXECUTION_FRAGILE;
+ CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_UPDATE_MULTI]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
- CF_REEXECUTION_FRAGILE;
+ CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_INSERT]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
- CF_REEXECUTION_FRAGILE;
+ CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_INSERT_SELECT]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
- CF_REEXECUTION_FRAGILE;
+ CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_DELETE]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
CF_REEXECUTION_FRAGILE;
sql_command_flags[SQLCOM_DELETE_MULTI]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
- CF_REEXECUTION_FRAGILE;
+ CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_REPLACE]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
- CF_REEXECUTION_FRAGILE;
+ CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_REPLACE_SELECT]= CF_CHANGES_DATA | CF_HAS_ROW_COUNT |
- CF_REEXECUTION_FRAGILE;
+ CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS;
sql_command_flags[SQLCOM_SELECT]= CF_REEXECUTION_FRAGILE;
sql_command_flags[SQLCOM_SET_OPTION]= CF_REEXECUTION_FRAGILE;
sql_command_flags[SQLCOM_DO]= CF_REEXECUTION_FRAGILE;
=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc 2010-03-03 14:43:35 +0000
+++ b/storage/innobase/handler/ha_innodb.cc 2010-03-08 19:09:08 +0000
@@ -7165,7 +7165,9 @@ ha_innobase::external_lock(
if (lock_type == F_WRLCK &&
!(table_flags() & HA_BINLOG_STMT_CAPABLE) &&
thd_binlog_format(thd) == BINLOG_FORMAT_STMT &&
- thd_binlog_filter_ok(thd))
+ thd_binlog_filter_ok(thd)
+ && thd_generates_rows(thd)
+ )
{
/* The error may be suppressed by test cases, by setting
the no_innodb_binlog_errors debug symbol. */
=== modified file 'storage/innobase/handler/ha_innodb.h'
--- a/storage/innobase/handler/ha_innodb.h 2010-01-22 09:57:02 +0000
+++ b/storage/innobase/handler/ha_innodb.h 2010-03-08 19:09:08 +0000
@@ -258,4 +258,11 @@ void thd_mark_transaction_to_rollback(MY
@retval 1 the query is not filtered, 0 otherwise.
*/
bool thd_binlog_filter_ok(const MYSQL_THD thd);
+/**
+ Check if the query may generate changes which
+ eventually will end up in the binary.
+ @param thd Thread handle
+ @retval 1 the query may generate changes, 0 otherwise.
+*/
+bool thd_generates_rows(const MYSQL_THD thd);
}
=== modified file 'storage/innodb_plugin/handler/ha_innodb.cc'
--- a/storage/innodb_plugin/handler/ha_innodb.cc 2010-02-12 23:30:44 +0000
+++ b/storage/innodb_plugin/handler/ha_innodb.cc 2010-03-08 19:09:08 +0000
@@ -7914,6 +7914,7 @@ ha_innobase::external_lock(
&& !(table_flags() & HA_BINLOG_STMT_CAPABLE)
#if MYSQL_VERSION_ID > 50140
&& thd_binlog_filter_ok(thd)
+ && thd_generates_rows(thd)
#endif /* MYSQL_VERSION_ID > 50140 */
)
{
=== modified file 'storage/innodb_plugin/handler/ha_innodb.h'
--- a/storage/innodb_plugin/handler/ha_innodb.h 2009-11-30 12:11:36 +0000
+++ b/storage/innodb_plugin/handler/ha_innodb.h 2010-03-08 19:09:08 +0000
@@ -265,6 +265,13 @@ void thd_mark_transaction_to_rollback(MY
@retval 1 the query is not filtered, 0 otherwise.
*/
bool thd_binlog_filter_ok(const MYSQL_THD thd);
+/**
+ Check if the query may generate changes which
+ eventually will end up in the binary.
+ @param thd Thread handle
+ @retval 1 the query may generate changes, 0 otherwise.
+*/
+bool thd_generates_rows(const MYSQL_THD thd);
#endif /* MYSQL_VERSION_ID > 50140 */
}
Attachment: [text/bzr-bundle]
| Thread |
|---|
| • bzr commit into mysql-5.1-rep+2 branch (alfranio.correia:3182)Bug#50479 | Alfranio Correia | 8 Mar |
