mysql gtid backup bug

[zxszcaijin]

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.

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 */
}

[zxszcaijin (zxszcaijin)]

from Valerii Kravchuk :
In MariaDB's mysqldump.c though the function is used: BINLOG_GTID_POS()
based on outputs of "SHOW STATUS LIKE 'binlog_snapshot_%'"
So, MariaDB's mysqldump works correctly with MariaDB servr and its GTIDs, Percona's implementation must fail.

[Sergei Glushchenko (sergei.glushchenko)]

I think if you specify both --single-transaction and --flush-logs, then FTWRL will be issued and GTID_PURGED will be consistent.

But it might be better to save GTID_EXECUTED when snapshot created using START TRANSACTION WITH CONSISTENT SNAPSHOT.

[Valerii Kravchuk (valerii-kravchuk)]

The (original) idea was to avoid FTWRL :)

[Sergei Glushchenko (sergei.glushchenko)]

There's no denying that

[Sergei Glushchenko (sergei.glushchenko)]

However, there is no BINLOG_GTID_POS in PS and GTID_EXECUTED isn't saved by START TRANSACTION WITH CONSISTENT SNAPSHOT, so I see no other workaround than to force FTWRL or don't use GTID.

Possible ways to fix are to implement something like BINLOG_GTID_POS or to save GTID_EXECUTED along with binlog position when snapshot created.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1047