pt-online-schema-change dry-run doesn't catch missing default value for not null column being added

[Michael MacDonald]

When adding a new column to an existing database table that is specified as a not null the dry-run does not catch if there is a missing default value for the column leading one to expect that the execute will run without error.

Original table DDL:
CREATE TABLE `table_name` (
  `id` int(11) NOT NULL,
  `existing_column_name` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Observe no warnings in the dry-run:
# pt-online-schema-change --alter "ADD COLUMN new_column_name DECIMAL(10,2) NOT NULL AFTER existing_column_name" D=schema,t=table_name --user username --password the_password --dry-run
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run. `schema`.`table_name` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table schema._table_name_new OK.
Altering new table...
Altered `schema`.`_table_name_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
Dropping new table...
Dropped new table OK.
Dry run complete. `schema`.`table_name` was not altered.

Observe error in the executed run:
# pt-online-schema-change --alter "ADD COLUMN new_column_name DECIMAL(10,2) NOT NULL AFTER existing_column_name" D=schema,t=table_name --user username --password the_password --execute
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `schema`.`table_name`...
Creating new table...
Created new table schema._table_name_new OK.
Altering new table...
Altered `schema`.`_table_name_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 1 rows...
Dropping triggers...
Dropped triggers OK.
Dropping new table...
Dropped new table OK.
`schema`.`table_name` was not altered.
Error copying rows from `schema`.`table_name` to `schema`.`_table_name_new`: Copying rows caused a MySQL error 1364:
    Level: Warning
     Code: 1364
  Message: Field 'new_column_name' doesn't have a default value
    Query: INSERT LOW_PRIORITY IGNORE INTO `schema`.`_table_name_new` (`id`, `existing_column_name`) SELECT `id`, `existing_column_name` FROM `schema`.`table_name` LOCK IN SHARE MODE /*pt-online-schema-change 13872 copy table*/

[Muhammad Irfan (muhammad-irfan)]

Michael,

It verified. For workaround please check this blogpost www.mysqlperformanceblog.com/2012/06/21/pt-online-schema-change-and-default-values/ still it seems to be valid feature request.

[Shahriyar Rzayev (rzayev-sehriyar)]

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