Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-837

LP #1653671: [FR] Add warning for pt-archiver that the last row will be missing with auto_increment

Details

    • Bug
    • Status: On Hold
    • Low
    • Resolution: Unresolved
    • None
    • None
    • None

    Description

      **Reported in Launchpad by Przemek last update 03-01-2017 11:42:37

      For tables with auto_increment, pt-archiver skips the last row, even if it works in copying mode, with --no-delete option.
      The workaround to have the last row copied too is to use the --no-safe-auto-increment option.
      This seems to be the expected behaviour according to lp:1237147

      However, I think that the tool should print warning that the last row will be skipped, in order to not leave the user unaware of this issue.

      Simple test case:

      [[email protected] ~]# pt-archiver --version
       pt-archiver 2.2.20
      
      mysql> show create table tt1\G
      1. row *************************** Table: tt1
       Create Table: CREATE TABLE `tt1` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `a` char(5) DEFAULT NULL,
       PRIMARY KEY (`id`)
       ) ENGINE=InnoDB AUTO_INCREMENT=62 DEFAULT CHARSET=latin1
       1 row in set (0.00 sec)
      
      mysql> show create table tt2\G
       *************************** 1. row ***************************
       Table: tt2
       Create Table: CREATE TABLE `tt2` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `a` char(5) DEFAULT NULL,
       PRIMARY KEY (`id`)
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       1 row in set (0.00 sec)
      
      mysql> select count(*) from tt1; select count(*) from tt2;
      +----------+
      |count(*)|
      +----------+
      |50|
      +----------+
      1 row in set (0.00 sec)
      +----------+
      |count(*)|
      +----------+
      |0|
      +----------+
       1 row in set (0.00 sec)
      
      [[email protected] ~]# pt-archiver --source=h=localhost,D=test,t=tt1 --dest=h=localhost,D=test,t=tt2 --no-delete --where "1=1"
       [[email protected] ~]#
      
      mysql> select count(*) from tt1; select count(*) from tt2;
      +----------+
      |count(*)|
      +----------+
      |50|
      +----------+
       1 row in set (0.00 sec)
      
      +----------+
      |count(*)|
      +----------+
      |49|
      +----------+
       1 row in set (0.00 sec)
      
      In case of normal archiving mode, the last row is also skipped - not deleted from original table and not copied to the new one:
      
      [[email protected] ~]# pt-archiver --source=h=localhost,D=test,t=tt1 --dest=h=localhost,D=test,t=tt2 --where "1=1"
       [[email protected] ~]#
      
      mysql> select count(*) from tt1; select count(*) from tt2;
      +----------+
      |count(*)|
      +----------+
      |1|
      +----------+
       1 row in set (0.00 sec)
      
      +----------+
      |count(*)|
      +----------+
      |49|
      +----------+
       1 row in set (0.00 sec)
      

      This will lead to data loss in case the original table is later dropped (as user is convinced all data is archived).

      Attachments

        Activity

          People

            Unassigned Unassigned
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Smart Checklist