CODEWITHSUNDEEP
mysqlalter-table
Eli
Eli

Reputation: 38979

MySQL Add Column with Online DDL

I'm currently trying to add a column to a table of ~25m rows. I need to have near-0 down time, so was hoping to use online DDL. It runs for a while, but eventually runs into the issue:

"Duplicate entry '1234' for key 'PRIMARY'" 
[SQL: u'ALTER TABLE my_table ADD COLUMN my_coumn BOOL NOT NULL DEFAULT false']

I think this is happening because I'm running INSERT ... ON DUPLICATE KEY UPDATE ... operations against the table while running the operation. This seems to be a known limitation.

After this didn't work, I tried using the Percona pt-online-schema-change tool, but unfortunately, because my table has generated columns, that didn't work either with error: 

The value specified for generated column 'my_generated_column' in table '_my_table_new' is not allowed.

So, I'm now at a loss. What are my other options for adding a column without blocking DML operations?

Upvotes: 4

Views: 1731

Answers (1)

Dom DaFonte
Dom DaFonte

Reputation: 1779

Your Alter statement is creating a non nullable column with a default of false. I'd suspect this to place an exclusive lock on your table, attempt to create the column, then setting it to False across each row.

If you don't have any available downtime, I'd suggest you

  1. Add the column as nullable and with no default 

    ALTER TABLE my_table ADD COLUMN my_coumn BOOL NULL;

  2. Update the values for existing rows to false

    update my_table set my_coumn=false;

  3. Alter the table a second time to be not nullable and with a default. 

    ALTER TABLE my_table modify my_coumn BOOL NOT NULL DEFAULT false;

Alternatively you could use something like Percona which manages schema changes using triggers and is meant to offer the ability to update schemas without locking the table.

Either option I'd suggest you test in your development environment with some process writing to the table to simulate user activity.

Upvotes: 3

Related Questions