Make pt-online-schema-change support converting nullable column to NOT NULL

Registered by Simon J Mudd on 2012-08-31

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.

This probably needs 2 changes:
1. the copy/insert statements wrap some sort of IF(colX IS NULL, <new default>, colX) in the appropriate columns,
2. the triggers should do the same thing.

Would be a nice feature to see.

Blueprint information

Status:
Not started
Approver:
None
Priority:
Undefined
Drafter:
None
Direction:
Needs approval
Assignee:
None
Definition:
New
Series goal:
None
Implementation:
Unknown
Milestone target:
None

Related branches

Sprints

Whiteboard

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.

Subscribers

No subscribers.