Menu
I'm running the following ALTER command on a MySQL 5.6 database on a large table with 60 million rows:
ALTER TABLE `large_table` ADD COLUMN `note` longtext NULL,
ALGORITHM=INPLACE, LOCK=NONE;
Despite specifying both ALGORITHM=INPLACE and LOCK=NONE, the table gets locked and essentially takes down the app until the migration is complete.
I verified that the table was indeed locked by checking the value of the In_use column on the output of the SHOW OPEN TABLES command. It was set to 1.
From what I gather in the MySQL documentation, this operation should not be locking the table. And, MySQL is supposed to fail the command if it is not able to proceed without a lock. I upgraded the database to MySQL 5.7 to see if it's any better, but I face the same problem on 5.7 too.
Is this an expected behavior? How do I find out what's going wrong here?
762
Third option to prevent table locks with MySQL database is to use AUTOCOMMIT on the database level. This will prevent table locks from occurring unintentionally during report execution since all the transactions are committed after they are executed without additional commit commands.
Table locking causes problems when a session is waiting because the disk is full and free space needs to become available before the session can proceed. In this case, all sessions that want to access the problem table are also put in a waiting state until more disk space is made available.
When an operation on the primary key uses ALGORITHM=INPLACE , even though the data is still copied, it is more efficient than using ALGORITHM=COPY because: No undo logging or associated redo logging is required for ALGORITHM=INPLACE . These operations add overhead to DDL statements that use ALGORITHM=COPY .
In MySQL 5.6, LOCK is not supported for ALTER TABLE operations on partitioned tables. To force use of the COPY algorithm for an ALTER TABLE operation that would otherwise not use it, enable the old_alter_table system variable or specify ALGORITHM=COPY .
At this point, it must acquire an exclusive lock. Which is to say, when adding a column it read locks the table for most of the operation, then gets a write lock at the end. MySQL 5.6 added the Online DDL to InnoDB which speeds up and improves many things such as altering tables and indexes.
ALTER TABLE users ADD COLUMN hat_size TEXT DEFAULT 'L'; This obviously is setting a default for every row. The schema will be changed, and every row will be touched while the default data is written. Locking will occur until this is complete, similar to what would happen in MySQL.
I assume you were not doing some other DDL on that table at about the same time?
For the future:
8.0.12 has ALTER TABLE .. ALGORITHM=INSTANT for ADD COLUMN. See Discussion and ALTER Reference and Online DDL Reference
The following limitations apply when the INSTANT algorithm is used to add a column:
Multiple columns may be added in the same ALTER TABLE statement.
If you can't upgrade then consider Percona's pt-online-schema-change or a new, competing, product gh-ost (which uses the binlog).
197
I also have experienced issues with MySQL 5.6 locking even when ALGORITHM=INPLACE, LOCK=NONE; is used. I would first check the following:
Check constraints on table
The ALTER TABLE clause LOCK=NONE is not permitted if there are ON...CASCADE or ON...SET NULL constraints on the table.
Source: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html
Does the table have a foreign key relationship?
An online DDL operation on a table in a foreign key relationship does not wait for a transaction executing on the other table in the foreign key relationship to commit or rollback. The transaction holds an exclusive metadata lock on the table it is updating and shared metadata lock on the foreign-key-related table (required for foreign key checking). The shared metadata lock permits the online DDL operation to proceed but blocks the operation in its final phase, when an exclusive metadata lock is required to update the table definition. This scenario can result in deadlocks as other transactions wait for the online DDL operation to finish.
Source: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html
And Read about Metadata locking here: https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html
Alter tables from old timeformat first
If you have created your tables from before MySQL 5.6 with DATETIME or TIMESTAMP fields, they need to be upgraded to MySQL's 5.6 new format.
InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns the following error:
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
Source: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html
Check if the table has Partitioning
Partitioning changes how alter table rules apply. Check partitioning status of table
show table status;
Look for 'Engine' not equalling InnoDB
Sources: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html#online-ddl-partitioning
Finally as Rick James in his answer mentioned, upgrading from 5.6 to 8.0 may be an option as it provides other improvements.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
