MySQL Add Column with Online DDL
I am currently trying to add a column to a table of ~ 25m rows. I need to have about 0 latency, so I was hoping to use online DDL. It works for a while, but it ends up with a problem:
"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 am performing operations INSERT ... ON DUPLICATE KEY UPDATE ...
against the table during the operation. This appears to be a known limitation .
After that, I did not try to use the Percona tool pt-online-schema-change
, but unfortunately, because columns were created in my table that did not work either with an error:
The value specified for generated column 'my_generated_column' in table '_my_table_new' is not allowed.
So, I'm at a loss now. What are my other options for adding a column without blocking DML operations?
source to share
The Alter statement creates a column with invalid values, defaults to false. I would suspect that you have placed an exclusive lock on your table, try to create a column, and then set it to False on each row.
If you have no available downtime, I suggest you
-
Add the column as nullable and no default
ALTER TABLE my_table ADD COLUMN my_coumn BOOL NULL;
-
Update values for existing rows to false
update my_table set my_coumn=false;
-
Change the table a second time so that it is not null and default.
ALTER TABLE my_table modify my_coumn BOOL NOT NULL DEFAULT false;
Alternatively, you can use something like Percona , which manages schema changes using triggers and is designed to provide the ability to update schemas without locking the table.
Any option that I suggest you test in your development environment, with a record of the process in a table to simulate user activity.
source to share