Bug #84479 mysql not consist with single-transaction when use gtid
Submitted: 12 Jan 2017 7:20 Modified: 12 Jan 2017 7:57
Reporter: xiaocai xiaocai Email Updates:
Status: Duplicate Impact on me:
None 
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:20] xiaocai xiaocai
Description:
When we use mysqldump to dump database with --single-transaction ,it use Binlog_snapshot_* to get consist binlog file/position in  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  use gtid.

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)
      fprintf(md_result_file,
          "\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
Please do not submit the same bug more than once. An existing bug report Bug#84478 already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.