mysql gtid backup bug

Bug #1655879 reported by zxszcaijin
10
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Invalid
Undecided
Unassigned
5.6
Triaged
High
Unassigned
5.7
Triaged
High
Unassigned

Bug 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.

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

Revision history for this message
zxszcaijin (zxszcaijin) wrote :

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.

information type: Public → Public Security
zxszcaijin (zxszcaijin)
information type: Public Security → Public
Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :

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.

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

The (original) idea was to avoid FTWRL :)

Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :

There's no denying that

Revision history for this message
Sergei Glushchenko (sergei.glushchenko) wrote :

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.

tags: added: consistent-binlog-snapshot
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.