Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
3.0.4
-
None
-
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
[email protected]:~/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. [email protected]:~/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.