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-schema-change does not see this and complains.
Jul 02 12:30:44 my-wrapper-script[26818]: Starting run...
pt-online-schema-change --no-version-check --critical-load="Threads_running:296" --max-load "Threads_running:196" --recurse=0 F=...,D=mydb,t=mytable --alter "MODIFY title varchar(120) CHARACTER SET utf8 not null default '' ..." --check-slave-lag h=some-slave,u=someuser,p='somepass' --max-lag=0.1 --chunk-time=0.1 --execute
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_foreign_keys, 10, 1
Altering `mydb`.`mytable`...
Creating new table...
Created new table mydb._mytable_new OK.
Altering new table...
Altered `mydb`.`_mytable_new` OK.
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`.`_mytable_new`: 2014-07-02T12:30:45 Copying rows caused a MySQL error 1048:
Level: Warning
Code: 1048
Message: Column 'title' cannot be null
Query: INSERT LOW_PRIORITY IGNORE INTO `mydb`.`_mytable_new` (...
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(column,<default value of new column>). That would then prevent the error occurring and allow a column to be changed from being NULLable to being NOT NULL (assuming a DEFAULT value is provided).
Please consider adding this extra functionality.
Seen on: percona-toolkit-2.2.8-1.noarch (CentOS)
Verified.
[root@centos65 mysql]# pt-online-
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]#