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

pt-online-schema-change --no-use-insert-ignore is broken

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.0.4
    • Component/s: None

      Description

      pt-online-schema-change pt-online-schema-change --no-use-insert-ignore is broken:

      • no INSERT statements can happen during the --no-use-insert-ignore
      • UPDATE and DELETE can only be done on rows that are already copied.

      How to reproduce:

      mysql> ...
          drop database pt;
          create database pt;
          use pt
          create table test (id int primary key auto_increment, otherid int);
          insert into test values(null,null);
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          insert into test select null, null  from test;
          update test set otherid=id;
      
      # pt-online-schema-change --version
      pt-online-schema-change 3.0.3
      
      # touch /tmp/ptosc.pause ; 
      # pt-online-schema-change --pause-file=/tmp/ptosc.pause --no-use-insert-ignore --execute D=pt,t=test --alter "add unique key (otherid)" 
      No slaves found.  See --recursion-method if host perconaserver has slaves.
      Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
      Operation, tries, wait:
        analyze_table, 10, 1
        copy_rows, 10, 0.25
        create_triggers, 10, 1
        drop_triggers, 10, 1
        swap_tables, 10, 1
        update_foreign_keys, 10, 1
      Altering `pt`.`test`...
      Creating new table...
      Created new table pt._test_new OK.
      Altering new table...
      Altered `pt`.`_test_new` OK.
      2017-06-13T20:28:11 Creating triggers...
      2017-06-13T20:28:11 Created triggers OK.
      2017-06-13T20:28:11 Copying approximately 262682 rows...
      Sleeping 60 seconds because /tmp/ptosc.pause exists
      

      When you get this...

      In another terminal:

      # mysql -e "insert into pt.test values (null, 10000000);"
      # rm /tmp/ptosc.pause
      

      This creates a row. the trigger on the table will copy it to the new table.

      ....

      then in the other terminal:

      Copying `pt`.`test`:   0% 04:21:16 remain
      2017-06-13T20:32:51 Dropping triggers...
      2017-06-13T20:32:51 Dropped triggers OK.
      2017-06-13T20:32:51 Dropping new table...
      2017-06-13T20:32:51 Dropped new table OK.
      `pt`.`test` was not altered.
      2017-06-13T20:32:51 Error copying rows from `pt`.`test` to `pt`.`_test_new`: 2017-06-13T20:32:51 DBD::mysql::st execute failed: Duplicate entry '458732' for key 'PRIMARY' [for Statement "INSERT LOW_PRIORITY  INTO `pt`.`_test_new` (`id`, `otherid`) SELECT `id`, `otherid` FROM `pt`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 4241 copy nibble*/" with ParamValues: 0='368360', 1='458732'] at /bin/pt-online-schema-change line 11011.
      

      Because insert ignore is not used, it will fail...

      Credits mainly to Dov Endress and Team atlas (Bradley Mickel, Emanuel Calvo, Max Bubenick, Dov Endress, Marcelo Goncalves)

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                gryp Kenny Gryp (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: