Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-6112

Correct Binlog_snapshot_gtid inconsistency when mysqldump was used with --single-transaction.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.28-31
    • Fix Version/s: 8.0.19-10, 5.7.30-33
    • Component/s: None
    • Labels:
      None
    • Needs Review:
      Yes

      Description

      Percona Server provides ability to get replication coordinates with Binlog_snapshot_file and Binlog_snapshot_position.

      In order to setup a gtid replication, it's not comfortable to convert (file,pos) to GTID form.
      At the same time @@GLOBAL.GTID_EXECUTED is changing during --single-transaction mysqldump backup:

      mysql> LOCK TABLES FOR BACKUP;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */; SELECT @@GLOBAL.GTID_EXECUTED;select sleep(10);SELECT @@GLOBAL.GTID_EXECUTED;SELECT @@GLOBAL.GTID_EXECUTED;
      Query OK, 0 rows affected (0.04 sec)
      Query OK, 0 rows affected (0.00 sec)
      Query OK, 0 rows affected (0.00 sec)
      
      +------------------------------------------------+
      | @@GLOBAL.GTID_EXECUTED                         |
      +------------------------------------------------+
      | 1e7becbc-0dd6-11ea-96b1-0242ac110002:1-4090778 |
      +------------------------------------------------+
      1 row in set (0.00 sec)
      
      +-----------+
      | sleep(10) |
      +-----------+
      |         0 |
      +-----------+
      1 row in set (10.03 sec)
      
      +------------------------------------------------+
      | @@GLOBAL.GTID_EXECUTED                         |
      +------------------------------------------------+
      | 1e7becbc-0dd6-11ea-96b1-0242ac110002:1-4128520 |
      +------------------------------------------------+
      1 row in set (0.00 sec)
      

      The feature breaks SELECT @@GLOBAL.GTID_EXECUTED compare to vanilla mysqldump implementation:
      https://github.com/mysql/mysql-server/blob/8.0/client/mysqldump.cc#L5651-L5688
      https://github.com/percona/percona-server/blob/8.0/client/mysqldump.cc#L6477-L6522

      In percona server's mysqldump:

      if (opt_single_transaction && opt_master_data)
      { /* See if we can avoid FLUSH TABLES WITH READ LOCK with Binlog_snapshot_* variables. */ consistent_binlog_pos = check_consistent_binlog_pos(nullptr, nullptr); }
      if ((opt_lock_all_tables || (opt_master_data && !consistent_binlog_pos) ||
       (opt_single_transaction && flush_logs)))
      { if (do_flush_tables_read_lock(mysql)) goto err; } else if (opt_lock_for_backup && do_lock_tables_for_backup(mysql))
       goto err;
       
       
       if (opt_master_data && do_show_master_status(mysql, consistent_binlog_pos))
       goto err;
       if (opt_slave_data && do_show_slave_status(mysql)) goto err;
       if (opt_single_transaction && (!opt_lock_for_backup || opt_master_data) &&
       do_unlock_tables(mysql)) /* unlock but no commit! */
       goto err;
      

      As you can see "FLUSH TABLES WITH READ LOCK" was disabled for (opt_single_transaction | consistent_binlog_pos), but unlock tables called only for (!opt_lock_for_backup || opt_master_data).

      Until GTID_EXECUTED will be covered by Binlog_snapshot_GTID, is possible to change
      https://github.com/percona/percona-server/blob/8.0/client/mysqldump.cc#L6477
      to

      if ((opt_lock_all_tables || (opt_master_data && !(consistent_binlog_pos && opt_lock_for_backup)) ||
       (opt_single_transaction && flush_logs))) { if (do_flush_tables_read_lock(mysql)) goto err; }
      else if (opt_lock_for_backup && do_lock_tables_for_backup(mysql))
       goto err;
      

       

      This allows to have upstream mysqldump behavior for setting GTID_EXECUTED with single-transaction, master-data and --skip-lock-for-backup

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                kamil.holubicki Kamil Holubicki
                Reporter:
                nickolay.ihalainen Nickolay Ihalainen
                Votes:
                1 Vote for this issue
                Watchers:
                8 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - Not Specified
                    Not Specified
                    Logged:
                    Time Spent - 1 week, 3 days, 3 hours, 5 minutes
                    1w 3d 3h 5m