From: Andrei When mysqldump run on mysql system database it generates inserts sql commands into mysql.gtid_slave_pos. After running the backup script those inserts did not produce an expected gtid state on slave. In particular the maximum of mysql.gtid_slave_pos.sub_id did not make into rpl_global_gtid_slave_state.last_sub_id a memory object that the table persists. And that was regardless of whether --gtid option in or out. Later when the backup recipient server starts as slave in *non-gtid* mode this desychronization may lead to a duplicate key error. This effect is corrected for --gtid mode mysqldump/mariadb-dump only as the following. The fixes ensure the insert block of the dump script is followed with a "summing-up" SET @global.gtid_slave_pos assignment. To address MDEV-34615 the assignment makes sure a possibly pre-existing gtid state (e.g reflecting another domain) remains as a sub-state upon the dump installation. A new test provides a how-to-reproduce mode as well. For the implemenation part, note a deferred print-out of SET-gtid_slave_pos and associated comments is prefered over relocating of the entire blocks if (opt_master,slave_data && do_show_master,slave_status) ... because of compatiblity concern. Namely an error inside do_show_*() is handled in the new code the same way, as early, as before. --- client/mysqldump.c | 53 +++++++++++++++++++++++++++++++++++----------- 1 file changed, 41 insertions(+), 12 deletions(-) diff --git a/client/mysqldump.c b/client/mysqldump.c index 1c9d92d6e09..0be890e2528 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -5985,8 +5985,13 @@ static int dump_selected_tables(char *db, char **table_names, int tables) } /* dump_selected_tables */ +const char fmt_gtid_pos[] = "%sSET GLOBAL gtid_slave_pos=concat('%s', " + "if(length(@@global.gtid_slave_pos) = 0, '', ','), " + "@@global.gtid_slave_pos);\n"; + static int do_show_master_status(MYSQL *mysql_con, int consistent_binlog_pos, - int have_mariadb_gtid, int use_gtid) + int have_mariadb_gtid, int use_gtid, + char *set_gtid_pos) { MYSQL_ROW row; MYSQL_RES *UNINIT_VAR(master); @@ -6054,8 +6059,11 @@ static int do_show_master_status(MYSQL *mysql_con, int consistent_binlog_pos, fprintf(md_result_file, "%sCHANGE MASTER TO MASTER_USE_GTID=slave_pos;\n", comment_prefix); - fprintf(md_result_file, - "%sSET GLOBAL gtid_slave_pos='%s';\n", + /* + Defer print of SET gtid_slave_pos until after its placeholder + table is guaranteed to have been dumped. + */ + sprintf(set_gtid_pos, fmt_gtid_pos, (!use_gtid ? "-- " : comment_prefix), gtid_pos); } @@ -6140,8 +6148,8 @@ static int add_slave_statements(void) return(0); } -static int do_show_slave_status(MYSQL *mysql_con, int use_gtid, - int have_mariadb_gtid) +static int do_show_slave_status(MYSQL *mysql_con, int have_mariadb_gtid, + int use_gtid, char* set_gtid_pos) { MYSQL_RES *UNINIT_VAR(slave); MYSQL_ROW row; @@ -6181,10 +6189,8 @@ static int do_show_slave_status(MYSQL *mysql_con, int use_gtid, mysql_free_result(slave); return 1; } - print_comment(md_result_file, 0, - "-- GTID position to start replication:\n"); - fprintf(md_result_file, "%sSET GLOBAL gtid_slave_pos='%s';\n", - gtid_comment_prefix, gtid_pos); + /* similarly to do_show_master_status */ + sprintf(set_gtid_pos, fmt_gtid_pos, gtid_comment_prefix, gtid_pos); } if (use_gtid) print_comment(md_result_file, 0, @@ -6905,6 +6911,19 @@ static void dynstr_realloc_checked(DYNAMIC_STRING *str, ulong additional_size) die(EX_MYSQLERR, DYNAMIC_STR_ERROR_MSG); } +/** + Print earlier prepared SET to @@global.gtid_slave_pos. + + @param set_gtid_pos[in] formatted sql set statement +**/ +static void do_print_set_gtid_slave_pos(const char *set_gtid_pos) +{ + DBUG_ASSERT(opt_master_data || opt_slave_data); + if (opt_slave_data) + print_comment(md_result_file, 0, + "-- GTID position to start replication:\n"); + fprintf(md_result_file, "%s", set_gtid_pos); +} int main(int argc, char **argv) { @@ -6913,6 +6932,11 @@ int main(int argc, char **argv) int exit_code; int consistent_binlog_pos= 0; int have_mariadb_gtid= 0; + /* + to hold SET @@global.gtid_slave_pos which is deferred to print + until the function epilogue. + */ + char set_gtid_pos[3 + sizeof(fmt_gtid_pos) + MAX_GTID_LENGTH]= {0}; MY_INIT(argv[0]); sf_leaking_memory=1; /* don't report memory leaks on early exits */ @@ -7016,10 +7040,12 @@ int main(int argc, char **argv) goto err; if (opt_master_data && do_show_master_status(mysql, consistent_binlog_pos, - have_mariadb_gtid, opt_use_gtid)) + have_mariadb_gtid, + opt_use_gtid, set_gtid_pos)) goto err; - if (opt_slave_data && do_show_slave_status(mysql, opt_use_gtid, - have_mariadb_gtid)) + if (opt_slave_data && do_show_slave_status(mysql, + have_mariadb_gtid, + opt_use_gtid, set_gtid_pos)) goto err; if (opt_single_transaction && do_unlock_tables(mysql)) /* unlock but no commit! */ goto err; @@ -7087,6 +7113,9 @@ int main(int argc, char **argv) if (opt_system & OPT_SYSTEM_TIMEZONES) dump_all_timezones(); + if ((opt_master_data || opt_slave_data) && set_gtid_pos[0]) + do_print_set_gtid_slave_pos(set_gtid_pos); + /* add 'START SLAVE' to end of dump */ if (opt_slave_apply && add_slave_statements()) goto err; -- 2.39.2