From 438a85460d7c517e2d3509a196ae8852963759dc Mon Sep 17 00:00:00 2001 From: Kevin Michel Date: Tue, 29 Jun 2021 16:31:14 +0200 Subject: [PATCH] fix CREATE...SELECT row-based binlog in ANSI mode # What is changed Ignore the ANSI sql mode when generating queries for the binlog. # Why Since MySQL 8.0.21, CREATE...SELECT statement are replicated using a single transaction instead of two. To implement that correctly, the binlog contain a CREATE TABLE...START TRANSACTION Query followed by Table_map/Write_rows events to insert the rows found by the SELECT : 'binlog.000001', 1336, 'Gtid', 1, 1415, "SET @@SESSION.GTID_NEXT= 'c8ec3a3b-ce8d-11eb-a313-8c8caaa4a076:6'" 'binlog.000001', 1415, 'Query', 1, 1492, 'BEGIN' 'binlog.000001', 1492, 'Query', 1, 1636, 'use `db_one`; CREATE TABLE "t1" (\n "1" int NOT NULL DEFAULT \'0\'\n) START TRANSACTION' 'binlog.000001', 1636, 'Table_map', 1, 1686, 'table_id: 111 (db_one.t1)' 'binlog.000001', 1686, 'Write_rows', 1, 1726, 'table_id: 111 flags: STMT_END_F' 'binlog.000001', 1726, 'Xid', 1, 1757, 'COMMIT /* xid=24 */' This non-standard SQL syntax is correctly recognised as "transactional DDL" and does not generate an implicit commit in the middle of a binlog transaction. To write the modified query inside the binlog, the same code used to implement SHOW TABLES is reused. In addition to its documented effects, the ANSI sql mode also alters the behavior of SHOW TABLES to hide MySQL specific syntax (this mode is internally named "foreign_db_mode"). This is useful to create standard SQL dumps. Because of the two previous points, when ANSI mode is enabled, the generated query inside the binlog does not contain the necessary START TRANSACTION. Without the START TRANSACTION, the replica trying to execute the binlog will see a normal CREATE TABLE query, which will try to do an implicit commit in the middle of a binlog transaction and fail with this error : Error 'Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER' '.' on query. To keep the query short, the binlog example at the beginning shows a query which requires tables without primary keys to be allowed, but the problem is the same when creating a table with a primary key, or when selecting rows from an actual table instead of just SELECT 1. --- sql/sql_insert.cc | 3 ++- sql/sql_show.cc | 13 ++++++++----- sql/sql_show.h | 3 ++- sql/sql_table.cc | 3 ++- 4 files changed, 14 insertions(+), 8 deletions(-) diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 706ef0ee5462..6e5f8e6c8cb1 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -3012,7 +3012,8 @@ int Query_result_create::binlog_show_create_table(THD *thd) { query.length(0); // Have to zero it since constructor doesn't result = store_create_info(thd, &tmp_table_list, &query, create_info, - /* show_database */ true); + /* show_database */ true, + /* foreign_db_mode */ false); assert(result == 0); /* store_create_info() always return 0 */ if (mysql_bin_log.is_open()) { diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 5b0e0c753b6e..cd861422a691 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -1181,9 +1181,12 @@ bool mysqld_show_create(THD *thd, TABLE_LIST *table_list) { if (table_list->is_view()) view_store_create_info(thd, table_list, &buffer); - else if (store_create_info(thd, table_list, &buffer, nullptr, - false /* show_database */)) - goto exit; + else { + bool foreign_db_mode = (thd->variables.sql_mode & MODE_ANSI) != 0; + if (store_create_info(thd, table_list, &buffer, nullptr, + false /* show_database */, foreign_db_mode)) + goto exit; + } if (table_list->is_view()) { field_list.push_back(new Item_empty_string("View", NAME_CHAR_LEN)); @@ -1856,7 +1859,8 @@ static void print_foreign_key_info(THD *thd, const LEX_CSTRING *db, */ bool store_create_info(THD *thd, TABLE_LIST *table_list, String *packet, - HA_CREATE_INFO *create_info_arg, bool show_database) { + HA_CREATE_INFO *create_info_arg, bool show_database, + bool foreign_db_mode) { char tmp[MAX_FIELD_WIDTH], buff[128], def_value_buf[MAX_FIELD_WIDTH]; const char *alias; String type(tmp, sizeof(tmp), system_charset_info); @@ -1869,7 +1873,6 @@ bool store_create_info(THD *thd, TABLE_LIST *table_list, String *packet, TABLE_SHARE *share = table->s; HA_CREATE_INFO create_info; bool show_table_options = false; - bool foreign_db_mode = (thd->variables.sql_mode & MODE_ANSI) != 0; my_bitmap_map *old_map; bool error = false; DBUG_TRACE; diff --git a/sql/sql_show.h b/sql/sql_show.h index 2caffd2d07f6..a97f0f41bbdb 100644 --- a/sql/sql_show.h +++ b/sql/sql_show.h @@ -55,7 +55,8 @@ enum enum_var_type : int; enum enum_field_types : int; bool store_create_info(THD *thd, TABLE_LIST *table_list, String *packet, - HA_CREATE_INFO *create_info_arg, bool show_database); + HA_CREATE_INFO *create_info_arg, bool show_database, + bool foreign_db_mode); void append_identifier(const THD *thd, String *packet, const char *name, size_t length, const CHARSET_INFO *from_cs, diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 5a7b4b29e97c..e88e8cabe9f8 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -10704,7 +10704,8 @@ bool mysql_create_like_table(THD *thd, TABLE_LIST *table, TABLE_LIST *src_table, create_info->used_fields |= HA_CREATE_USED_ENGINE; bool result MY_ATTRIBUTE((unused)) = store_create_info( - thd, table, &query, create_info, true /* show_database */); + thd, table, &query, create_info, true /* show_database */, + false /* foreign_db_mode */); assert(result == 0); // store_create_info() always return 0