Bug #84478 mysql not consist with single-transaction when use gtid
Submitted: 12 Jan 2017 7:14 Modified: 24 Jan 2017 14:51
Reporter: xiaocai xiaocai Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.6+ OS:Any
Assigned to: CPU Architecture:Any

[12 Jan 2017 7:14] xiaocai xiaocai
When we use mysqldump to dump database with --single-transaction ,it use Binlog_snapshot_* to get consist binlog file/position using rr transaction without flush tables with read lock.

But it can't get the consist gtid when use single-transaction without ftwrl. So ,it's very dangerous use the dump file header include gtid_purged to construct slave .

How to repeat:
code :
   if use --single-transaction:
       mysqldump use the function to get gtid without any lock. 
static my_bool add_set_gtid_purged(MYSQL *mysql_con)
  MYSQL_RES  *gtid_purged_res;
  MYSQL_ROW  gtid_set;
  ulong     num_sets, idx;

  /* query to get the GTID_EXECUTED */
  if (mysql_query_with_error_report(mysql_con, &gtid_purged_res,
                  "SELECT @@GLOBAL.GTID_EXECUTED"))
    return TRUE;

  /* Proceed only if gtid_purged_res is non empty */
  if ((num_sets= mysql_num_rows(gtid_purged_res)) > 0)
    if (opt_comments)
          "\n--\n-- GTID state at the beginning of the backup \n--\n\n");

    fprintf(md_result_file,"SET @@GLOBAL.GTID_PURGED='");

    /* formatting is not required, even for multiple gtid sets */
    for (idx= 0; idx< num_sets-1; idx++)
      gtid_set= mysql_fetch_row(gtid_purged_res);
      fprintf(md_result_file,"%s,", (char*)gtid_set[0]);
    /* for the last set */
    gtid_set= mysql_fetch_row(gtid_purged_res);
    /* close the SET expression */
    fprintf(md_result_file,"%s';\n", (char*)gtid_set[0]);

  return FALSE;  /*success */

Suggested fix:
Add a global variable Binlog_snapshot_gtid  in mysql server ,and when mysqldump backup databases with --single-transaction,use Binlog_snapshot_gtid to get conist gtid in rr transaction .
[12 Jan 2017 7:57] MySQL Verification Team
Bug #84479 marked as duplicate of this
[24 Jan 2017 14:51] MySQL Verification Team

Thanks for the bug submission. Yes, with gtid mysqldump might have an issue. We normally suggest one use mysqlbackup and not mysqldump.

all best