Allow NULLable columns to be converted to NOT NULL
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Medium
|
Frank Cizmich |
Bug Description
A recent OSC I did required me to change an existing column from being nullable to being NOT NULL.
The new definition included a ... NOT NULL DEFAULT ... clause which should mean that the OSC should be able to run cleanly yet pt-online-
Jul 02 12:30:44 my-wrapper-
pt-online-
No slaves found. See --recursion-method if host ... has slaves.
Will check slave lag on:
some-slave
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `mydb`.`mytable`...
Creating new table...
Created new table mydb._mytable_new OK.
Altering new table...
Altered `mydb`.
2014-07-02T12:30:45 Creating triggers...
2014-07-02T12:30:45 Created triggers OK.
2014-07-02T12:30:45 Copying approximately 28552223 rows...
2014-07-02T12:30:45 Dropping triggers...
2014-07-02T12:30:45 Dropped triggers OK.
2014-07-02T12:30:45 Dropping new table...
2014-07-02T12:30:45 Dropped new table OK.
`mydb`.`mytable` was not altered.
2014-07-02T12:30:45 Error copying rows from `mydb`.`mytable` to `mydb`.
Level: Warning
Code: 1048
Message: Column 'title' cannot be null
Query: INSERT LOW_PRIORITY IGNORE INTO `mydb`.
It looks to me as if this should be checked and if the now NOT NULL column was NULLable before the inserted entry should be something like ISNULL(
Please consider adding this extra functionality.
Seen on: percona-
tags: | added: i64194 |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
tags: | added: pt-online-schema-change |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
Verified.
[root@centos65 mysql]# pt-online- schema- change --alter "MODIFY mobile varchar(10) NOT NULL DEFAULT ''" D=test,t=t1 --execute foreign_ keys, 10, 1 schema- change 2515 copy table*/
No slaves found. See --recursion-method if host centos65 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_
Altering `test`.`t1`...
Creating new table...
Created new table test._t1_new OK.
Altering new table...
Altered `test`.`_t1_new` OK.
2014-08-05T13:22:21 Creating triggers...
2014-08-05T13:22:21 Created triggers OK.
2014-08-05T13:22:21 Copying approximately 4 rows...
2014-08-05T13:22:21 Dropping triggers...
2014-08-05T13:22:21 Dropped triggers OK.
2014-08-05T13:22:21 Dropping new table...
2014-08-05T13:22:21 Dropped new table OK.
`test`.`t1` was not altered.
2014-08-05T13:22:21 Error copying rows from `test`.`t1` to `test`.`_t1_new`: 2014-08-05T13:22:21 Copying rows caused a MySQL error 1048:
Level: Warning
Code: 1048
Message: Column 'mobile' cannot be null
Query: INSERT LOW_PRIORITY IGNORE INTO `test`.`_t1_new` (`a`, `b`, `who`, `rep_count`, `trx_count`, `trx_started`, `mobile`) SELECT `a`, `b`, `who`, `rep_count`, `trx_count`, `trx_started`, `mobile` FROM `test`.`t1` LOCK IN SHARE MODE /*pt-online-
[root@centos65 mysql]#