pt-online-schema-change fails with virtual columns

[[e-mail]]

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

[[e-mail] (sblackstone)]

I'm using 2.2.7 by the way..

[monty solomon (monty+launchpad)]

We are experiencing this issue with virtual generated columns in MySQL 5.7 using pt-online-schema-change 2.2.18

2016-08-11T17:55:29 Error copying rows from `cache` to `_cache_new`: 2016-08-11T17:55:29 DBD::mysql::st execute failed: The value specified for generated column 'score' in table '_cache_new' is not allowed. [for Statement "INSERT LOW_PRIORITY IGNORE INTO `_cache_new` (`domain`, `company`, `companyjson`, `score`) SELECT `domain`, `company`, `companyjson`, `score` FROM `cache` FORCE INDEX(`PRIMARY`) WHERE ((`domain` >= ?)) AND ((`domain` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 23000 copy nibble*/" with ParamValues:] at /usr/bin/pt-online-schema-change line 10805.

[Sveta Smirnova (svetasmirnova)]

Same is repeatable with PS 5.7:

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.

[Sveta Smirnova (svetasmirnova)]

Status of this bug tracked in https://jira.percona.com/browse/PT-202

[Shahriyar Rzayev (rzayev-sehriyar)]

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