pt-online-schema-change fails when changing columns from NULL TO NOT NULL (if a DEFAULT is provided)

[Simon J Mudd]

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

[Alejandro Recarey]

MySQL allows you to change a NULLable column to a NOT NULL (if you provide a DEFAULT) with a simple ALTER TABLE statement.

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:

I would second this feature request. This type of "surgery" is unfortunately common.