This blog post explains the cause of “ERROR 1412 (HY000): Table definition has changed, please retry transaction” with the specific Isolation level settings.
Background
As per the MySQL documentation, this error should occur for “operations that make a temporary copy of the original table and delete the original table when the temporary copy is built.”
Consistent read does not work over ALTER TABLE operations that make a temporary copy of the original table and delete the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction’s snapshot was taken. In this case, the transaction returns an error: ER_TABLE_DEF_CHANGED, “Table definition has changed, please retry transaction”.
Here is the test example verifying the above statement when transaction_isolation | REPEATABLE-READ and temp table are created.
1 2 3 4 5 6 7 | mysql [localhost] {msandbox} (test) > show variables like '%isolation%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.01 sec) |
Session 1:
1 2 3 4 5 6 7 8 9 10 | mysql [localhost] {msandbox} (test) > begin; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > select * from sbtest2 limit 1; +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 501462 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.01 sec) |
Session 2: Changing the column data type will create a temp table.
1 2 3 | mysql [localhost] {msandbox} (test) > ALTER TABLE sbtest1 modify COLUMN c1 char(10); Query OK, 1000000 rows affected (31.14 sec) Records: 1000000 Duplicates: 0 Warnings: 0 |
## While this is running, checked shows processlist, which shows the temp table,
1 2 3 | | 55 | msandbox | localhost | test | Query | 4 | copy to tmp table | ALTER TABLE sbtest1 modify COLUMN c1 char(10) | 3541 | 0 | 0 | +----+-----------------+-----------------+------+---------+---------+----------------------------------------------------------+-----------------------------------------------+------------+-----------+---------------+ 22 rows in set, 1 warning (0.00 sec) |
Session 1:
1 2 | mysql [localhost] {msandbox} (test) > select * from sbtest1 limit 1; ERROR 1412 (HY000): Table definition has changed, please retry transaction |
Another undocumented case
However, we can also see this ER_TABLE_DEF_CHANGED error for ALTER operations, which do not create a temporary copy of the table. This conflicts with what is mentioned in the MySQL documentation for the ER_TABLE_DEF_CHANGED occurrence.
Here is the example case:
Adding a column with ALGORITHM=INSTANT will NOT make a temporary copy, so the expectation is that it should not throw an ER_TABLE_DEF_CHANGED error.
# Create two tables with some data using sysbench.
1 2 3 4 5 6 7 8 9 | $ sysbench /usr/share/sysbench/oltp_read_write.lua --table-size=1000000 --tables=2 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --mysql-socket=/tmp/mysql_sandbox8035.sock --time=0 --report-interval=1 --events=0 --db-driver=mysql prepare mysql [localhost] {msandbox} (test) > show variables like '%isolation%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec) |
Session1:
1 2 3 4 5 6 7 8 9 10 | mysql [localhost] {msandbox} (test) > begin; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > select * from sbtest2 limit 1; +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k | c | pad | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 501462 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 | +----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec) |
Session 2:
1 2 3 | mysql [localhost] {msandbox} (test) > ALTER TABLE sbtest1 ADD COLUMN c1 int, ALGORITHM=INSTANT; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Session 1:
1 2 | mysql [localhost] {msandbox} (test) > select * from sbtest1 limit 1; ERROR 1412 (HY000): Table definition has changed, please retry transaction |
Summary
The error occurs only when transaction_isolation is REPEATABLE-READ, as it reads the snapshot established by the first such read in that transaction. You will not see this issue with the READ COMMITTED isolation level, as each consistent read within a transaction sets and reads its own fresh snapshot.
Here is the documentation bug: https://bugs.mysql.com/bug.php?id=116132
Percona offers enterprise-grade scalability and performance without traditional enterprise drawbacks. We deliver secure, tested, open source software complete with advanced features like backup, monitoring, and encryption only otherwise found in MySQL Enterprise Edition.
Why Customers Choose Percona for MySQL
About the Author
Lalit ChoudharyLalit works as a Database Engineer in Percona. His main professional interests are problem-solving, working on database issues and testing.
