Description:
select returns empty set if autocommit=0 after add/drop index in another thread when innodb_locks_unsafe_for_binlog=1 .
1. Thread 1: set autocommit=0;
2. Thread 1: select * from t => multiple rows
3. Thread 2: alter t add or drop index;
4. Thread 1: select * from t => empty set
5. Thread 1: rollback
6. Thread 1: select * from t => multiple rows again
If autocommit=1 or innodb_locks_unsafe_for_binlog is off or engine is myisam => step 4 returns resultset
Review two sessions scripts below (note timestamp in prompt shows time when previous command finished execution).
It is recommended to repeat scripts on fresh server. (Problem may disappear after several iterations).
======= Thread1 ============
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.30-enterprise-gpl-advanced MySQL Enterprise Server - Advanced Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
03:34:26(ODBC@localhost)[(none)]>select version();
+--------------------------------+
| version() |
+--------------------------------+
| 5.1.30-enterprise-gpl-advanced |
+--------------------------------+
1 row in set (0.01 sec)
03:34:34(ODBC@localhost)[(none)]>drop database test;
Query OK, 1 row affected (0.00 sec)
03:34:49(ODBC@localhost)[(none)]>create database test;
Query OK, 1 row affected (0.02 sec)
03:35:10(ODBC@localhost)[(none)]>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
03:35:34(ODBC@localhost)[(none)]>select * from t;
ERROR 1046 (3D000): No database selected
03:36:33(ODBC@localhost)[(none)]>use test;
Database changed
03:36:37(ODBC@localhost)[test]>select * from t;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 2 |
| 4 | 3 |
| 5 | 2 |
+---+------+
5 rows in set (0.00 sec)
03:36:38(ODBC@localhost)[test]>select * from t;
Empty set (0.00 sec)
03:37:00(ODBC@localhost)[test]>select SQL_NO_CACHE * from t;
Empty set (0.00 sec)
03:37:07(ODBC@localhost)[test]>rollback;
Query OK, 0 rows affected (0.00 sec)
03:37:14(ODBC@localhost)[test]>select * from t;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 2 |
| 4 | 3 |
| 5 | 2 |
+---+------+
5 rows in set (0.00 sec)
03:37:17(ODBC@localhost)[test]>
=========== Thread 2 =================
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.30-enterprise-gpl-advanced MySQL Enterprise Server - Advanced Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
03:34:23(root@localhost)[(none)]>use test;
ERROR 1049 (42000): Unknown database 'test'
03:35:02(root@localhost)[(none)]>use test;
Database changed
03:35:11(root@localhost)[test]>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
03:35:46(root@localhost)[test]>CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)
03:36:15(root@localhost)[test]>INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
03:36:15(root@localhost)[test]>COMMIT;
Query OK, 0 rows affected (0.00 sec)
03:36:15(root@localhost)[test]>select * from t;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 2 |
| 4 | 3 |
| 5 | 2 |
+---+------+
5 rows in set (0.00 sec)
03:36:26(root@localhost)[test]>alter table t add index ib(b);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
03:36:52(root@localhost)[test]>select * from t;
+---+------+
| a | b |
+---+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 2 |
| 4 | 3 |
| 5 | 2 |
+---+------+
5 rows in set (0.00 sec)
03:36:57(root@localhost)[test]>
How to repeat:
1. Configure innodb_locks_unsafe_for_binlog=1 and start server
2. Start mysql1 and execute:
create database bug;
use bug;
create table t(id int) engine=innodb;
insert into t values(1), (2);
set autocommit=0;
select * from t; -- this is important to see the bug!
-- => see expected output
3. start mysql2 and execute:
use bug;
set autocommit=0;
alter table t add index i(id);
4. in mysql1:
select * from t;
-- => empty resultset shown => bug
rollback;
select * from t;
-- => correct output (1), (2)
Suggested fix:
Return correct data
