Remove any WAIT <n> or NOWAIT option from the query before binlogging DDL. Otherwise applying the event with mysqlbinlog | mysql may fail on NOWAIT if there is temporarily a lock on the table during the apply (while no such lock was there when the original query ran and was binlogged). Such locks can occur even without user control, since InnoDB background tasks can sometimes hold such locks for a short while. Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org> --- .../suite/binlog/r/binlog_ddl_nowait.result | 64 +++++++++++++++ .../suite/binlog/t/binlog_ddl_nowait.test | 77 +++++++++++++++++++ sql/sql_lex.cc | 26 +++++-- sql/sql_lex.h | 19 +++++ sql/sql_table.cc | 57 +++++++++++++- sql/sql_yacc.yy | 62 ++++++++++++--- 6 files changed, 286 insertions(+), 19 deletions(-) create mode 100644 mysql-test/suite/binlog/r/binlog_ddl_nowait.result create mode 100644 mysql-test/suite/binlog/t/binlog_ddl_nowait.test diff --git a/mysql-test/suite/binlog/r/binlog_ddl_nowait.result b/mysql-test/suite/binlog/r/binlog_ddl_nowait.result new file mode 100644 index 00000000000..86712fb533a --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_ddl_nowait.result @@ -0,0 +1,64 @@ +*** MDEV-31756: WAIT/NOWAIT in DDL makes binary logs difficult or impossible to replay +connection default; +RESET MASTER; +CREATE TABLE t1 (a int PRIMARY KEY) ENGINE=MyISAM; +ALTER TABLE t1 WAIT +0.0 ADD b INT; +ALTER TABLE t1 WAIT +4.3 ADD c INT; +INSERT INTO t1(a,b) VALUES (1,1), (2,1), (3,2), (4,3), (5, 5), (6, 8), (7, 13); +CREATE INDEX b_idx ON t1(b) WAIT 0; +OPTIMIZE TABLE t1 NOWAIT; +Table Op Msg_type Msg_text +test.t1 optimize status OK +TRUNCATE TABLE t1 NOWAIT; +DROP INDEX b_idx ON t1 WAIT 0; +RENAME TABLE t1 NOWAIT TO t2; +DROP TABLE t2 WAIT 1; +connection default; +FLUSH BINARY LOGS; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connect con1,localhost,root,,; +INSERT INTO t1 VALUES (0*SLEEP(0.2)); +connection default; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection con1; +INSERT INTO t1(a) VALUES (0*SLEEP(0.2) + 100); +connection default; +Table Op Msg_type Msg_text +test.t1 optimize status OK +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + PRIMARY KEY (`a`), + KEY `b_idx` (`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection con1; +INSERT INTO t1(a) VALUES (0*SLEEP(0.2) + 200); +connection default; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +connection con1; +disconnect con1; +connection default; +NOT FOUND /WAIT/ in mdev31756.text +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_ddl_nowait.test b/mysql-test/suite/binlog/t/binlog_ddl_nowait.test new file mode 100644 index 00000000000..b2b5a4ec2b6 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_ddl_nowait.test @@ -0,0 +1,77 @@ +# DDL binlogging is the same regardless of binlog format +--source include/have_binlog_format_mixed.inc + +--echo *** MDEV-31756: WAIT/NOWAIT in DDL makes binary logs difficult or impossible to replay + +--connection default +--let $datadir= `select @@datadir` +RESET MASTER; + +--let $file= query_get_value(SHOW MASTER STATUS, File, 1) +--let $pos0= query_get_value(SHOW MASTER STATUS, Position, 1) +CREATE TABLE t1 (a int PRIMARY KEY) ENGINE=MyISAM; +--let $pos1= query_get_value(SHOW MASTER STATUS, Position, 1) +ALTER TABLE t1 WAIT +0.0 ADD b INT; +ALTER TABLE t1 WAIT +4.3 ADD c INT; +INSERT INTO t1(a,b) VALUES (1,1), (2,1), (3,2), (4,3), (5, 5), (6, 8), (7, 13); +--let $pos2= query_get_value(SHOW MASTER STATUS, Position, 1) +CREATE INDEX b_idx ON t1(b) WAIT 0; +OPTIMIZE TABLE t1 NOWAIT; +TRUNCATE TABLE t1 NOWAIT; +--let $pos3= query_get_value(SHOW MASTER STATUS, Position, 1) +DROP INDEX b_idx ON t1 WAIT 0; +--let $pos4= query_get_value(SHOW MASTER STATUS, Position, 1) +RENAME TABLE t1 NOWAIT TO t2; +DROP TABLE t2 WAIT 1; + + +# Test that we can mysqlbinlog|mysql the DDL even though it has to wait for +# the table lock for a short while. +# +# This is very prone to races of course, but that is ok. The case we want to +# test is when mysqbinlog|mysql runs while the table lock is held, and that +# will be the case "most" of the time. If we race and the lock is not held, +# it just means the test is ineffective, it will still pass. + +--connection default +FLUSH BINARY LOGS; +--exec $MYSQL_BINLOG --start-position=$pos0 --stop-position=$pos1 $datadir/master-bin.000001 | $MYSQL test +SHOW CREATE TABLE t1; + +connect (con1,localhost,root,,); +send INSERT INTO t1 VALUES (0*SLEEP(0.2)); + +--connection default +--exec $MYSQL_BINLOG --start-position=$pos1 --stop-position=$pos2 $datadir/master-bin.000001 | $MYSQL test +SHOW CREATE TABLE t1; + +--connection con1 +reap; +send INSERT INTO t1(a) VALUES (0*SLEEP(0.2) + 100); + +--connection default +--exec $MYSQL_BINLOG --start-position=$pos2 --stop-position=$pos3 $datadir/master-bin.000001 | $MYSQL test +SHOW CREATE TABLE t1; + +--connection con1 +reap; +send INSERT INTO t1(a) VALUES (0*SLEEP(0.2) + 200); + +--connection default +--exec $MYSQL_BINLOG --start-position=$pos3 --stop-position=$pos4 $datadir/master-bin.000001 | $MYSQL test +SHOW CREATE TABLE t1; + +--connection con1 +reap; +--disconnect con1 +--connection default + +# Check that there is no WAIT <n> or NOWAIT in binlogged queries. +--exec $MYSQL_BINLOG $datadir/master-bin.000001 >$MYSQL_TMP_DIR/mdev31756.text +--let SEARCH_FILE= $MYSQL_TMP_DIR/mdev31756.text +--let SEARCH_PATTERN= WAIT +--let SEARCH_ABORT= FOUND +--source include/search_pattern_in_file.inc +--remove_file $MYSQL_TMP_DIR/mdev31756.text + +DROP TABLE t1; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 0131068a530..75ec0d9dffe 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1557,6 +1557,19 @@ LEX_CSTRING Lex_input_stream::get_token(uint skip, uint length) } +Lex_string_with_pos_st +Lex_input_stream::get_token_with_pos(uint skip, uint length) +{ + LEX_CSTRING tmp; + Lex_string_with_pos_st out; + out.start_pos= m_tok_start + skip - get_buf(); + tmp= get_token(skip, length); + out.str= tmp.str; + out.length= tmp.length; + return out; +} + + static size_t my_unescape(CHARSET_INFO *cs, char *to, const char *str, const char *end, int sep, bool backslash_escapes) @@ -2193,7 +2206,7 @@ int Lex_input_stream::lex_one_token(YYSTYPE *yylval, THD *thd) if ((yyLength() >= 3) && !ident_map[c]) { /* skip '0x' */ - yylval->lex_str= get_token(2, yyLength() - 2); + yylval->lex_str_with_pos= get_token_with_pos(2, yyLength() - 2); return (HEX_NUM); } yyUnget(); @@ -2233,7 +2246,7 @@ int Lex_input_stream::lex_one_token(YYSTYPE *yylval, THD *thd) { yySkip(); while (my_isdigit(cs, yyGet())) ; - yylval->lex_str= get_token(0, yyLength()); + yylval->lex_str_with_pos= get_token_with_pos(0, yyLength()); return(FLOAT_NUM); } } @@ -2272,8 +2285,9 @@ int Lex_input_stream::lex_one_token(YYSTYPE *yylval, THD *thd) - the number is either not followed by a dot at all, or - the number is followed by a double dot as in: FOR i IN 1..10 */ - yylval->lex_str= get_token(0, yyLength()); - return int_token(yylval->lex_str.str, (uint) yylval->lex_str.length); + yylval->lex_str_with_pos= get_token_with_pos(0, yyLength()); + return int_token(yylval->lex_str_with_pos.str, + (uint) yylval->lex_str_with_pos.length); } // fall through case MY_LEX_REAL: // Incomplete real number @@ -2287,10 +2301,10 @@ int Lex_input_stream::lex_one_token(YYSTYPE *yylval, THD *thd) if (!my_isdigit(cs, c)) return ABORT_SYM; // No digit after sign while (my_isdigit(cs, yyGet())) ; - yylval->lex_str= get_token(0, yyLength()); + yylval->lex_str_with_pos= get_token_with_pos(0, yyLength()); return(FLOAT_NUM); } - yylval->lex_str= get_token(0, yyLength()); + yylval->lex_str_with_pos= get_token_with_pos(0, yyLength()); return(DECIMAL_NUM); case MY_LEX_HEX_NUMBER: // Found x'hexstring' diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 780ad3d9e16..0c17480bf3b 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -103,6 +103,12 @@ struct Lex_string_with_metadata_st: public LEX_CSTRING }; +struct Lex_string_with_pos_st: public LEX_CSTRING +{ + uint start_pos; +}; + + /* Used to store identifiers in the client character set. Points to a query fragment. @@ -2863,6 +2869,7 @@ class Lex_input_stream int find_keyword(Lex_ident_cli_st *str, uint len, bool function) const; int find_keyword_qualified_special_func(Lex_ident_cli_st *str, uint len) const; LEX_CSTRING get_token(uint skip, uint length); + Lex_string_with_pos_st get_token_with_pos(uint skip, uint length); int scan_ident_start(THD *thd, Lex_ident_cli_st *str); int scan_ident_middle(THD *thd, Lex_ident_cli_st *str, CHARSET_INFO **cs, my_lex_states *); @@ -3540,6 +3547,12 @@ struct LEX: public Query_tables_list */ uint table_count_update; + /* + Used to remember a character position during parsing, eg. the end of + ulong_num for ddl_wait_nowait_end_offset. + */ + uint last_lex_end_pos; + uint8 describe; /* A flag that indicates what kinds of derived tables are present in the @@ -3585,15 +3598,21 @@ struct LEX: public Query_tables_list keyword_delayed_begin_offset is the offset to the beginning of the DELAYED keyword in INSERT DELAYED statement. keyword_delayed_end_offset is the offset to the character right after the DELAYED keyword. + + Similarly, ddl_wait_nowait_begin_offset and ddl_wait_nowait_end_offset mark + the start and end of the "NOWAIT" or "WAIT <number>" (including last + character of <number>) option for ALTER TABLE and similar statements. */ union { const char *stmt_definition_begin; uint keyword_delayed_begin_offset; + uint ddl_wait_nowait_begin_offset; }; union { const char *stmt_definition_end; uint keyword_delayed_end_offset; + uint ddl_wait_nowait_end_offset; }; /** diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 748ae999087..a007f7689e6 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -996,6 +996,23 @@ bool mysql_write_frm(ALTER_PARTITION_PARAM_TYPE *lpt, uint flags) } +/** + Create a new query string for DDL with the WAIT <n> or NOWAIT option removed. + This option should not be in the binlog, as a query that succeeded on the + master _must_ also succeed on the slave, even if it needs to wait. +*/ +static int +create_stmt_without_nowait(THD *thd, String *buf) +{ + if (buf->append(thd->query(), thd->query_length()) || + buf->replace(thd->lex->ddl_wait_nowait_begin_offset, + thd->lex->ddl_wait_nowait_end_offset - + thd->lex->ddl_wait_nowait_begin_offset, NULL, 0)) + return 1; + return 0; +} + + /* SYNOPSIS write_bin_log() @@ -1035,9 +1052,43 @@ int write_bin_log(THD *thd, bool clear_error, } else errcode= query_error_code(thd, TRUE); - error= thd->binlog_query(THD::STMT_QUERY_TYPE, - query, query_length, is_trans, FALSE, FALSE, - errcode) > 0; + + /* Remove any NOWAIT or WAIT <n> from DDL. */ + String log_query; + switch(thd->lex->sql_command) + { + case SQLCOM_TRUNCATE: + case SQLCOM_DROP_INDEX: + case SQLCOM_RENAME_TABLE: + case SQLCOM_OPTIMIZE: + case SQLCOM_ALTER_TABLE: + case SQLCOM_CREATE_INDEX: + /* DROP TABLE is binlogged with a rewritten query, so omitted here. */ + if (thd->lex->ddl_wait_nowait_begin_offset > 0) + { + if (create_stmt_without_nowait(thd, &log_query)) + { + sql_print_error("Event Error: An error occurred while creating query " + "string for DDL with NOWAIT/WAIT removed, before " + "writing it into binary log."); + error= 1; + } + else + { + query= log_query.c_ptr(); + query_length= log_query.length(); + } + } + break; + + default: + ; /* Nothing */ + } + + if (!error) + error= thd->binlog_query(THD::STMT_QUERY_TYPE, + query, query_length, is_trans, FALSE, FALSE, + errcode) > 0; thd_proc_info(thd, 0); } return error; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 85717713325..75be19cb4c8 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -201,6 +201,7 @@ void _CONCAT_UNDERSCORED(turn_parser_debug_on,yyparse)() /* structs */ LEX_CSTRING lex_str; + Lex_string_with_pos_st lex_str_with_pos; Lex_ident_cli_st kwd; Lex_ident_cli_st ident_cli; Lex_ident_sys_st ident_sys; @@ -1316,9 +1317,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %right INTO %type <lex_str> - DECIMAL_NUM FLOAT_NUM NUM LONG_NUM - HEX_NUM HEX_STRING - LEX_HOSTNAME ULONGLONG_NUM field_ident select_alias ident_or_text + HEX_STRING + LEX_HOSTNAME field_ident select_alias ident_or_text TEXT_STRING_sys TEXT_STRING_literal key_cache_name sp_opt_label BIN_NUM TEXT_STRING_filesystem @@ -1326,6 +1326,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); sp_block_label sp_control_label opt_place opt_db udt_name +%type <lex_str_with_pos> + DECIMAL_NUM FLOAT_NUM NUM LONG_NUM HEX_NUM ULONGLONG_NUM + %type <ident_sys> IDENT_sys ident_func @@ -12747,12 +12750,41 @@ int_num: ; ulong_num: - opt_plus NUM { int error; $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); } - | HEX_NUM { $$= strtoul($1.str, (char**) 0, 16); } - | opt_plus LONG_NUM { int error; $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); } - | opt_plus ULONGLONG_NUM { int error; $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); } - | opt_plus DECIMAL_NUM { int error; $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); } - | opt_plus FLOAT_NUM { int error; $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); } + opt_plus NUM + { + int error; + $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); + Lex->last_lex_end_pos= $2.start_pos + (uint)$2.length; + } + | HEX_NUM + { + $$= strtoul($1.str, (char**) 0, 16); + Lex->last_lex_end_pos= $1.start_pos + (uint)$1.length; + } + | opt_plus LONG_NUM + { + int error; + $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); + Lex->last_lex_end_pos= $2.start_pos + (uint)$2.length; + } + | opt_plus ULONGLONG_NUM + { + int error; + $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); + Lex->last_lex_end_pos= $2.start_pos + (uint)$2.length; + } + | opt_plus DECIMAL_NUM + { + int error; + $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); + Lex->last_lex_end_pos= $2.start_pos + (uint)$2.length; + } + | opt_plus FLOAT_NUM + { + int error; + $$= (ulong) my_strtoll10($2.str, (char**) 0, &error); + Lex->last_lex_end_pos= $2.start_pos + (uint)$2.length; + } ; real_ulong_num: @@ -17116,15 +17148,25 @@ lock: opt_lock_wait_timeout: /* empty */ - {} + { + LEX *lex= Lex; + lex->ddl_wait_nowait_begin_offset= 0; + lex->ddl_wait_nowait_end_offset= 0; + } | WAIT_SYM ulong_num { + LEX *lex= Lex; + lex->ddl_wait_nowait_begin_offset= (uint)($1.pos() - thd->query()); + lex->ddl_wait_nowait_end_offset= lex->last_lex_end_pos; if (unlikely(set_statement_var_if_exists(thd, STRING_WITH_LEN("lock_wait_timeout"), $2)) || unlikely(set_statement_var_if_exists(thd, STRING_WITH_LEN("innodb_lock_wait_timeout"), $2))) MYSQL_YYABORT; } | NOWAIT_SYM { + LEX *lex= Lex; + lex->ddl_wait_nowait_begin_offset= (uint)($1.pos() - thd->query()); + lex->ddl_wait_nowait_end_offset= (uint)($1.end() - thd->query()); if (unlikely(set_statement_var_if_exists(thd, STRING_WITH_LEN("lock_wait_timeout"), 0)) || unlikely(set_statement_var_if_exists(thd, STRING_WITH_LEN("innodb_lock_wait_timeout"), 0))) MYSQL_YYABORT; -- 2.39.5