Menu
In one of the use cases of my application, I have two concurrent MySQL connections:
T (actually, continuously updating a single row in this table), andALTER TABLE, adding 8 new columns and extending one column from varchar(80) to varchar(2000)). The DDL is expected to eventually complete.The columns in the UPDATE DML are not affected by the DDL.
The table contains only a single row (the one being UPDATE'd).
Analysis
What I observe when an integration test covering this use case is run is a test time out (the table is being so actively written to, so that the DDL never completes), but only for MySQL 5.7. Normally, the test is expected to complete in under 30 seconds on our hardware (which indeed happens for MySQL 5.6 and 8.0), but for MySQL 5.7 even 200 seconds is not sufficient. I have experimented with different ALGORITHM and LOCK values (see 13.1.8 ALTER TABLE Syntax), with no luck.
When I profile my application (MySQL 5.7 case), I observe that 99% of CPU time is spent reading from a socket (i. e. waiting for MySQL to respond that the table has been altered), but the database instance is a sort of a black box to me -- of course I have performance_schema enabled and can run queries against it, but I have no idea which exact information I am looking for.
Synthesis
At the same time, I failed to reduce the problem to a minimal self-contained unit test -- the only thing I observe is 3x to 10x increase in test elapsed time for MySQL 5.7 compared to other MySQL versions, but the DDL doesn't hang forever:
All MySQL versions are either stock versions for Windows or Debian Linux downloaded from www.mysql.com with minimal changes to my.cnf, or the official Docker images.
Questions:
ALTER TABLE DDL forever? Or what I'm observing is just a very busy database instance? Is it possible to either
ALTER TABLE is executed interruptibly, i. e. an error is returned by the database if a certain time-out is exceeded, orSHARED lock on the table or some of its rows to pause, so that they don't intervene while the DDL is being executed?
660
If you get a duplicate-key error when using ALTER TABLE to change the character set or collation of a character column, the cause is either that the new column collation maps two keys to the same value or that the table is corrupted. In the latter case, you should run REPAIR TABLE on the table.
You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.
You can add or modify the columns or indexes of a table, change the engine, add foreign keys, or alter the table name. To access the MySQL Table Editor, right-click a table name in the Navigator area of the sidebar with the Schemas secondary tab selected and click Alter Table.
Introduction to Alter Table Mysql In MySQL, ALTER TABLE command is used to change the name of the table or rename one or more columns of the table, add new columns, remove existing ones, modify the datatype, length, index of one or more column and we can also rename the name of the table.
We can modify the column by changing its definition and position by using the ALTER TABLE statement with MODIFY command. The syntax of modifying the column is similar to adding the column which is as follows –
First, specify the name of the table that you want to rename after the ALTER TABLE keywords. Second, specify the new name for the table after the RENAME TO keywords. In this tutorial, you have learned how to use the MySQL ALTER TABLE statement to add a column, modify a column, rename a column, drop a column and rename a table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table. To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
TL;DR — commit your transactions to unblock your ALTER TABLE.
Yes, ALTER TABLE can block for a long time. It may seem like forever. It's actually the value of lock_wait_timeout, which is 31536000 seconds by default, or 365 days.
In MySQL, DDL statements like ALTER TABLE require an exclusive metadata lock on the table. The purpose is to make sure you don't ALTER TABLE from two concurrent sessions at the same time.
DML statement like SELECT, INSERT, UPDATE, DELETE also hold "shared" metadata locks. Shared locks can be held by multiple sessions concurrently, but block exclusive locks, because exclusive locks require that they be the only one to hold any type of lock on the table.
Documentation states:
This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.
The purpose of DML statements holding a metadata lock is so they can preserve their repeatable-read view of the table without worry that another session is doing DROP TABLE or ALTER TABLE to compromise their view of the table. This locking is necessary because MySQL does not have versioned metadata (they are gradually working toward that).
This means a transaction that has run a simple SELECT and doesn't commit will block a DROP TABLE or ALTER TABLE that requires a locking change.
There is some nuance with the introduction of online DDL.
Online DDL Performance and Concurrency describes in more detail that an ALTER TABLE starts out by acquiring a shared metadata lock, so the uncommitted transaction will not block it. But the next phase may upgrade the shared metadata lock to an exclusive metadata lock, if the nature of the ALTER TABLE change requires it. At this point, the lock acquisition is blocked because the other transaction still holds its own metadata lock.
Online DDL doesn't apply to every type of ALTER TABLE operation; some still require exclusive locks. Changing a data type, for example, as you are doing, requires an exclusive lock. See Online DDL Overview for details.
77
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
