Hi,
MySQL allows you to change a NULLable column to a NOT NULL (if you provide
a DEFAULT) with a simple ALTER TABLE statement.
So If I had a column defined like this:
col15 enum('value1','value2') NULL
I can do the following:
ALTER TABLE my_table MODIFY col15 enum('value1','value2') NOT NULL DEFAULT
'value1'
and while rebuilding the table MySQL will correctly convert any NULL values
into 'value1'.
I tried to run pt-online-schema-change and do the same thing but it is not
aware of what I'm trying to do and does not work:
pt-online-schema-change --critical-load="Threads_running:128" --max-load
"Threads_running:108" --recurse=0 F=/root/.my.cnf,D=my_db,t=MyTable --alter
"MODIFY col15 enum('value1','value2') NOT NULL DEFAULT 'value1', DROP
PRIMARY KEY, ADD PRIMARY KEY ( col1_id, col2_id ), DROP INDEX col1_id, ADD
KEY ( col2_id )" --check-slave-lag h=my-slave,u=my_user,p='my_pass'
--max-lag=0.2 --chunk-time=0.1 --execute
Altering `my_db`.`MyTable`...
Creating new table...
Created new table my_db._MyTable_new OK.
Altering new table...
Altered `my_db`.`_MyTable_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 34327495 rows...
Dropping triggers...
Dropped triggers OK.
Dropping new table...
Dropped new table OK.
`my_db`.`MyTable` was not altered.
Error copying rows from `my_db`.`MyTable` to `my_db`.`_MyTable_new`:
Copying rows caused a MySQL error 1048:
Level: Warning
Code: 1048
Message: Column 'col15' cannot be null Query: INSERT LOW_PRIORITY
IGNORE INTO `my_db`.`_MyTable_new` (`col1_id`, `col2_id`, `col3_id`,
`col4_id`, `col5`, `col6`, `col7`, `col7`, `col8`, `col9`, `col10`,
`col11_id`, `col12`, `col13`, `col14`, `col15`) SELECT `col1_id`,
`col2_id`, `col3_id`, `col4_id`, `col5`, `col6`, `col7`, `col7`, `col8`,
`col9`, `col10`, `col11_id`, `col12`, `col13`, `col14`, `col15` FROM
`my_db`.`MyTable` FORCE INDEX(`PRIMARY`) WHERE ((`col2_id` >= ?)) AND
((`col2_id` <= ?)) /*pt-online-schema-change 22057 copy nibble*/
So the new table has a NOT NULL definition, but the insert statement (to
copy) does not see if the value it would insert is NULL and therefore
insert the DEFAULT value.
Changing that behaviour and recognising that edge case would be really nice.
The column concerned is not indexed, so I have to manually go through the
whole table to first change the NULL value to 'value1', and slowly enough
to not affect replication. That's precisely what pt-online-schema-change is
supposed to do for me.
Note: this is with percona-toolkit-2.1.2-1, On CentOS 5, talking to MySQL
5.5.16.
I'll add a blueprint / feature request for this as while it may not be that
common a need, if it happens working around this manually for large tables
is quite fiddly.
Simon
