MySQL Bugs: #89174: empty result set in transaction after table definition has changed

Bug #89174	empty result set in transaction after table definition has changed
Submitted:	10 Jan 2018 19:27	Modified:	11 Jan 2018 12:54
Reporter:	Eric Promislow	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
While in a long-running transaction, the client code tries to fetch rows from a table. If that table definition has been modified while the transaction is in effect, attempts to fetch rows will quietly return 0 (zero) rows.

Version info:
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.53              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.53-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

How to repeat:
Connect to a mysql server from two different terminal sessions, call them A and B.
In A:

start transaction;
select * from table1;

In B:
alter table table2 add column foo varchar(255);  # table2 has 2,000 rows of data and has a primary key int column named "id"

After the `alter table` has finished in B, switch back to A:

select * from  table2 where id < 10; => 0 rows
commit;
select * from  table2 where id < 10;  => 9 rows

In mysql 5.6, the first select after the table schema has been altered correctly returns an error
"ERROR 1412 (HY000): Table definition has changed, please retry transaction".

Hi,

This is a known limitation of 5.5, not a bug.

The online DDL ( https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html ) work that's put into 5.6 helps with this issue and data dictionary work in 8.0 ( https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html ) will completely fix all the problems around this issue.

kind regards
Bogdan