Description:
As per the documentation, https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
#####
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”.
#####
An 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."
But we can see this "ER_TABLE_DEF_CHANGED" error for ALTER operations that do not create a temporary copy of the table. This conflicts with what is mentioned in the documentation for the "ER_TABLE_DEF_CHANGED" occurrence.(For operations that make a temporary copy)
How to repeat:
Test example:
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.
$ 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:
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:
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:
mysql [localhost] {msandbox} (test) > select * from sbtest1 limit 1;
ERROR 1412 (HY000): Table definition has changed, please retry transaction
mysql [localhost] {msandbox} (test) >
Suggested fix:
Clarification in the documentation about "ER_TABLE_DEF_CHANGED" for ALTER operations that do "NOT" make a temporary copy of the original table and delete the original table when the temporary copy is built.
