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

pt-online-schema-change fails with virtual columns

    Details

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

      Description

      Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1400929

      Bug description:
      Using Maria 5.5 with a table with virtual (persistent) columns, it attempts to include those columns when inserting rows into the new table..

      The error(warning) generated is -

        # pt_online_schema_change:10337 27978 SHOW WARNINGS
        # Retry:3671 27978 Try code failed: Copying rows caused a MySQL error 1906:
        #     Level: Warning
        #      Code: 1906
        #   Message: The value specified for computed column 'is_aff_trans' in table '_transactions_new' ignored
      

      Suggested behavior is that it should skip inserting the columns which
      are persistent and let them be re-computed when they are inserted by
      Maria instead..

      How to reproduce:

      mysql> CREATE TABLE `new_table` (
          -> `ID` int(11) NOT NULL,
          -> `Column2` int(11) DEFAULT NULL,
          -> `Column3` int(11) GENERATED ALWAYS AS ((`Column2` + 1)) STORED,
          -> PRIMARY KEY (`ID`)
          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      Query OK, 0 rows affected (0.38 sec)
      
      mysql> INSERT INTO `new_table` (`ID`, `Column2`) VALUES ('1', '2');
      Query OK, 1 row affected (0.07 sec)
      
      mysql> \q
      Bye
      
      sveta@Thinkie:~/build/ps-5.7/mysql-test$ ~/build/percona-toolkit/bin/pt-
      online-schema-change --alter 'ADD COLUMN `Column4` VARCHAR(45) NULL
      AFTER `Column3`' D=test,t=new_table,h=127.0.0.1,P=13001,u=root
      
      # A software update is available:
      #   * The current version for Percona::Toolkit is 3.0.1
      
      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
      Exiting without altering `test`.`new_table` because neither --dry-run nor --execute was specified.  Please read the tool's documentation carefully before using this tool.
      sveta@Thinkie:~/build/ps-5.7/mysql-test$ ~/build/percona-toolkit/bin/pt-online-schema-change --alter 'ADD COLUMN `Column4` VARCHAR(45) NULL AFTER `Column3`' D=test,t=new_table,h=127.0.0.1,P=13001,u=root --execute
      No slaves found.  See --recursion-method if host Thinkie 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 `test`.`new_table`...
      Creating new table...
      Created new table test._new_table_new OK.
      Altering new table...
      Altered `test`.`_new_table_new` OK.
      2017-09-29T15:51:18 Creating triggers...
      2017-09-29T15:51:19 Created triggers OK.
      2017-09-29T15:51:19 Copying approximately 1 rows...
      2017-09-29T15:51:19 Dropping triggers...
      2017-09-29T15:51:19 Dropped triggers OK.
      2017-09-29T15:51:19 Dropping new table...
      2017-09-29T15:51:19 Dropped new table OK.
      `test`.`new_table` was not altered.
      2017-09-29T15:51:19 Error copying rows from `test`.`new_table` to `test`.`_new_table_new`: 2017-09-29T15:51:19 DBD::mysql::st execute failed: The value specified for generated column 'Column3' in table '_new_table_new' is not allowed. [for Statement "INSERT LOW_PRIORITY IGNORE INTO `test`.`_new_table_new` (`id`, `column2`, `column3`) SELECT `id`, `column2`, `column3` FROM `test`.`new_table` LOCK IN SHARE MODE /*pt-online-schema-change 12426 copy table*/"] at /home/sveta/build/percona-toolkit/bin/pt-online-schema-change line 10924.
      
      

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                sveta.smirnova Sveta Smirnova
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: