MySQL Bugs: #60342: InnoDB, weird result on DML

Bug #60342	InnoDB, weird result on DML
Submitted:	4 Mar 2011 15:43	Modified:	4 Mar 2011 16:15
Reporter:	f p	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Storage Engines	Severity:	S3 (Non-critical)
Version:	5.5.9	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Problem:
we got unexpected result from DML on innodb table

Tested on:
OS: GNU/Linux, CentOS 5.5, 2.6.18-194.32.1.el5

MySQL:

mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| innodb_version          | 1.1.5                                |
| protocol_version        | 10                                   |
| slave_type_conversions  |                                      |
| version                 | 5.5.9-log                            |
| version_comment         | MySQL Community Server (GPL) by Remi |
| version_compile_machine | x86_64                               |
| version_compile_os      | Linux                                |
+-------------------------+--------------------------------------+

- and -

mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.5                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.9-log                    |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

This problem NOT reproducible on MyISAM storage engine.

How to repeat:
(0) open client on two terminal, connect to same same database server, same schema

(1) on terminal 1: create table and insert a record

CREATE TABLE IF NOT EXISTS `mod_data` (
  `mod_id` int(10) unsigned NOT NULL DEFAULT '0',
  `mod_sts` char(1) NOT NULL DEFAULT 'D',
  `mod_todo` int(10) unsigned NOT NULL DEFAULT '0',
  `mod_flags` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`mod_id`),
  KEY `mod_sts` (`mod_sts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `mod_data` (`mod_id`, `mod_sts`, `mod_todo`, `mod_flags`) VALUES
(1, 'D', 0, '2011-03-04 22:15:23');

(2) on terminal 1: do select query
select SQL_NO_CACHE * from `mod_data`;

(3) on terminal 2: do select query
select SQL_NO_CACHE * from `mod_data`;

(NOTE) both query (2) & (3) MUST returned same result

(4) on terminal 2: do update query (transactional)
start transaction;
update `mod_data` set `mod_sts` = 'U' where `mod_id` = 1;
commit;

(5) on terminal 2: do select query
select SQL_NO_CACHE * from `mod_data`;

(6) on terminal 1: do select query
select SQL_NO_CACHE * from `mod_data`;

(EXPECTED) both query (5) & (6) returned same result
(RESULT) both query (5) & (6) returned DIFFERENT result

Please, send the output of:

show variables like '%isilation%';
show variables like 'autocomm%';

from your server.

on both server:

mysql> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

mysql> show variables like 'autocomm%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

As you have autocommit=OFF, bith your SELECTs in the second terminal are in the same transaction with repeatable read isolation level. So, they see data as they were at the moment of the first SELECT and ignore committed update that happened after that.

Read http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html.

Well, thanks a ton Valeriy!