MySQL Bugs: #98642: CONSISTENT SNAPSHOT CAN BE CORRUPTED BY OTHER TRANSACTIONS

Bug #98642	CONSISTENT SNAPSHOT CAN BE CORRUPTED BY OTHER TRANSACTIONS
Submitted:	18 Feb 2020 6:08	Modified:	12 Aug 2020 19:50
Reporter:	Erwan MAS	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6 after 5.6.38, 5.7 after 5.7.18	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:

During a long session created with  START TRANSACTION WITH CONSISTENT SNAPSHOT ;
and in parallel with activities on the table , the long session can have a response truncated , when doing a query of a range .

The definition of the table is :

CREATE TABLE `bug_consistent_read` (
  `clientid` bigint(20) unsigned NOT NULL,
  `ticketid` bigint(20) unsigned NOT NULL,
  `state` int(11) NOT NULL,
  `modifed` timestamp(6) NULL DEFAULT NULL,
  `border` smallint(6) DEFAULT '0',
  PRIMARY KEY (`clientid`,`ticketid`),
  KEY `ix_ticketid` (`ticketid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The query is :

SELECT count(*) as cnt   FROM test.bug_consistent_read  WHERE  (  (  ( `clientid`  >   XXXXX )  ) or (  ( `ticketid`  >=  YYYYYYY )  and ( `clientid` = XXXXX  )  )  )  AND
                                                               (  (  ( `clientid`  <   AAAAAAAA )  ) or (  ( `ticketid`  <=  BBBBBBB )  and ( `clientid` = AAAAAAAAA )  )  )  ;

this regression appear between version version 5.6.35 and version 5.6.36 and 5.6.47 has still the bug

this regression appear between version version 5.7.17 and version 5.7.18 and 5.7.29 has still the bug

How to repeat:
I created a dataset ( https://github.com/ErwanMAS/consistent-read-mysql-bug/ )

0) load data :

   bzcat mysqldump/dump_bug_consistent_read.bz2       | mysql test
   bzcat mysqldump/dump_bug_consistent_read_feed.bz2  | mysql test

1)    open a session A

source bug_buggy_chunks.sql ;

must return always a rounded number ( 2000 , 5000 , 10000 , 15000 , 20000 )

2)    open a session B

start a consistent snapshot with

START TRANSACTION WITH CONSISTENT SNAPSHOT ;

source bug_buggy_chunks.sql ;

must return always a rounded number ( 2000 , 5000 , 10000 , 15000 , 20000 )

3)    now back on session A

source file bug_create_buggy.sql

this file will pouplate bug_consistent_read with some row of bug_consistent_read_feed

when is done

4)    back to session B

run again the script and you will see many values not rounded

source bug_buggy_chunks.sql ;

Hi Mr. MAS,

Thank you for your bug report.

However, before proceeding further, we have to ask you a number of questions.

First of all, START TRANSACTION WITH CONSISTENT SNAPSHOT  is designed specifically for the backup of data, not for read-only sessions. 

Hence, can you try executing 	bug_buggy_chunks.sql in a repeatable-read isolation level, instead. That is the one that will not be inflicted by concurrent writing operation.  Also, restoring data from the mysqldumped file(s) should run as well in the repeatable-read isolation level.

Second, I have taken a look at the mentioned 	bug_buggy_chunks.sql and I do not see why would this script always return rounded number. There is nothing in those queries that 

Can you please try these three recommendations before we proceed further ????

1) i have the same behavior with start transaction , and my isolation level is REPEATABLE_READ 

2) i dont have a small dataset , so you need to use the dataset from my github repo .

3) the first query of bug_buggy_chunks  return 2000  before any concurrent transactions , and after this query return 1706 , so 294 rows are missing .

You can trigger the bug in another way .

In the following comment , i will reference the first query of bug_buggy_chunk.sql as QUERY_COUNT .

start a session A 

START TRANSACTION ;

execute QUERY_COUNT , you will see a value 2000

start a session B

execute QUERY_COUNT , you will see a value 2000

start a session C 

START TRANSACTION ;

source bug_create_buggy.sql ;

but we are not going to commit .

back in session A  and in session B :

execute QUERY_COUNT , you will see a value 1706 why ?
            
these 2 sessions are missing data that was here !!!!

back in session C

rollback your transaction 

in session A and in session B , execute QUERY_COUNT , you are seeing now 2000

and the file bug_create_buggy.sql contains only INSERT

Hi Mr. MAS,

We still have several questions for you .....

* Both of your transactions should have repeatable read isolation level, not just one. That means that dump file should be run in the repeatable read, in parallel with those SELECT statements

* Next, How can, on the first run, SELECT statement return 2.000 etc, when the table does not exist yet ??? 

* Next, none of your  dump files have BEGIN nor COMMIT statements , so how do you know that it is not run in a single transaction ??? How do you know that it is not run in the auto-commit mode ???

* Next, you have two dump files, so which one should be used ??? I guess that one is used before SELECT queries and the other is used while SELECTs are running. Please, confirm which is which ....

* Next, are you sure that you have only two connections that are touching that table ??? In your further comment you are mentioning three concurrent transactions, which is very confusing

* Next, you did not answer our question on why do you think that this query should always return round numbers ???

* Last, I see that the mentioned SQL has only INSERTs, which further complicates the matters. We have two mysqldump files and a third file with INSERTs, which should be run when exactly ????

We do need these clarifications before we proceed to attempt to reproduce the behaviour that you describe.

IN How to repeat  I SAID

  I created a dataset ( https://github.com/ErwanMAS/consistent-read-mysql-bug/ )

  0) load data :

   bzcat mysqldump/dump_bug_consistent_read.bz2       | mysql test
   bzcat mysqldump/dump_bug_consistent_read_feed.bz2  | mysql test

To trigger the bug you need to load the dataset .

i tested this times more than 30 times ....

Hi Mr. MAS,

Thank you for clearing up one of our questions.

So your dump is not in one file, but in two ..... So, before we run SELECTs we restore these two dumps one after another. That clears one of the questions that you asked.

Please, answer other questions , as well .....

You can test yourself , in my repo you have a bash script that will loop thru many mysql versions , create a new local docker , load data and run query to trigger the bug .

I do not use docker, nor Linux.

Your bug report is not Linux-specific, so it should not be a prerequisite.

SELECT @@SESSION.tx_isolation ;  

return REPEATABLE-READ

In step 0 you must wait  , the 2 dataset is loaded .

And after you open 2 or 3 mysql session to your test server .

Please, Mr. MAS, answer all of our questions ......

We do need all the answers in order to reproduce your test case correctly.

Try to answer all non-answered questions one by one. Skip those that you have answered already.

If you dont use linux/docker . 

Which stack i need to use to prove the bug ?

Mr. MAS,

No need to use any stack.

Use only SQL files and then give us the instructions on the exact steps that we should follow, one after the other, sequentially. Then, when you have to run several transactions in parallel, precise when and how to run them. 

Since these are all SQL files, we can use mysql CLI and other tools that come with our package.

> We still have several questions for you .....
> 
> * Both of your transactions should have repeatable read isolation level, not just one. That means that dump file should be run in the repeatable read, in parallel with those SELECT statements
>

This seem to be the default configuration .

> * Next, How can, on the first run, SELECT statement return 2.000 etc, when the table does not exist yet ??? 

Step 0  you must load the dataset ( 2 tables ) 

> * Next, none of your  dump files have BEGIN nor COMMIT statements , so how do you know that it is not run in a single transaction ??? How do you know that it is not run in the auto-commit mode ???

Step 0 we wait for completion .

> * Next, you have two dump files, so which one should be used ??? I guess that one is used before SELECT queries and the other is used while SELECTs are running. Please, confirm which is which ....

The first dump file will create the first table ( cited before ) 

The second table create a new table , this new table will be the source of data that i will use via a query insert into select from ( script bug_create_buggy.sql ) 

> * Next, are you sure that you have only two connections that are touching that table ??? In your further comment you are mentioning three concurrent transactions, which is very confusing

I am sure because i create a new mysql instance , the root password is dedicated to the test ( test1234 ) .

I was able to run on my mac with docker more that 30 times ... and one of my server .

You have 2 possible scenario to trigger the bug :
  scenario 1 ( described in section how to repeat ) 
  scenario 2 ( in my last comment where i use 3 sessions) 

> 
> * Next, you did not answer our question on why do you think that this query should always return round numbers ???
> 

Because of the dataset 

> * Last, I see that the mentioned SQL has only INSERTs, which further complicates the matters. We have two mysqldump files and a third file with INSERTs, which should be run when exactly ????

The 2 mysqldump is to populate tables to have a dataset .

The third file with insert must be run to trigger the bug .

I expect i answer all your questions

Hi Mr. MAS,

Yes, you have answered practically all of my questions.

First , we need one confirmation. After both dump files are loaded, you run two parallel transactions. One is running the script  bug_buggy_chunks.sql, while the other is running  bug_create_buggy.sql. Is that all ???

Also, bug_create_buggy.sql does not have BEGIN nor COMMIT, so how do you know it is not committing after reach INSERT ????

Also, confirm that until 5.7.18 (which means last good version was 5.7.17) you got great results. However, beginning with 5.7.18 and after, you get these buggy results. Let me explain. We require this info in order to catch the change in code that might have caused this behaviour.

Those are our last questions, after which  we shall proceed.

Thanks in advance.

About the question 

> 
> * Next, you did not answer our question on why do you think that this query should always return round numbers ???
> 

1) Because of the dataset and because i started a transaction  , my tx_isolation is REPEATABLE_READ 

2) in the case of scenario 2 ( with 3 sessions ) , because no commit was made in session C

Thank you Mr. MAS,

But, please answer our questions. Let me repeat those:

1. We need one confirmation. After both dump files are loaded, you
run TWO  parallel transactions. One is running the script 
bug_buggy_chunks.sql, while the other is running  bug_create_buggy.sql.
Is that all ??? And you run each of the scripts only once ???

2. A script bug_create_buggy.sql does not have BEGIN nor COMMIT, so how do you
know it is not committing after reach INSERT ????

We are asking only questions about scenario 1, no questions on scenario 2.

> Hi Mr. MAS,
> 
> Yes, you have answered practically all of my questions.
> 
> First , we need one confirmation. After both dump files are loaded, you run two parallel transactions. One is running the script  bug_buggy_chunks.sql,
 while the other is running  bug_create_buggy.sql. Is that all ???
>

yes for scenario 1 

instead of running bug_buggy_chunks.sql you can run only the first query .

> Also, bug_create_buggy.sql does not have BEGIN nor COMMIT, so how do you know it is not committing after reach INSERT ????

IN case of scenario 1 :
   the script is committing ( because of auto_commit ) 
   but the session B i open with START TRANSACTION WITH CONSISTEN SNAPSHOT , so  this transaction must not see transaction commited in others session after my START TRANSACTION .

IN case of scenario 2 :
   the script is not committing because when you START TRANSACTION , autocommit is set to 0

> Also, confirm that until 5.7.18 (which means last good version was 5.7.17) you got great results. However, beginning with 5.7.18 and after, you get these buggy results. Let me explain. We require this info in order to catch the change in code that might have caused this behaviour.

5.6.X with X>= 36 is affected
5.7.X with X>= 18 is affected

Sorry, but your new answers are only confusing the scenario 1.

You wrote:

"
instead of running bug_buggy_chunks.sql you can run only the first query
"

So, after we have restored both tables we just run (in a loop) first SELECT from bug_buggy_chunks.sql ??
This is understood and is quite clear. Just confirm. 

Then, you wrote:

"
  the script is committing ( because of auto_commit ) 
  but the session B i open with START TRANSACTION WITH CONSISTEN
SNAPSHOT , so  this transaction must not see transaction commited in
others session after my START TRANSACTION .
"
This is confusing. I explicitly wrote to you that START TRANSACTION WITH CONSISTENT SNAPSHOT can be used ONLY for dumping data with mysqldump or mysqlpump.

Hence, I can test by running bug_buggy_chunks.sql in one transaction in parallel with the other transaction running script bug_create_buggy.sql.

I will run BOTH scripts  WITHOUT  any START TRANSACTION WITH CONSISTENT SNAPSHOT, for the reasons explained above. I will run both with BEGIN as a first statement and COMMIT as a last statement in a repeatable-read isolation level.

If I get only the results that are expected, then this will be closed as "Not a bug". 

Is that OK for you ????

2 methods   to exhibit the bug ( lack of ISOLATION between session ) , both methods have the same requirements .

Requirements :

2 tables must be populated with the current mysqldumps ( from https://github.com/ErwanMAS/consistent-read-mysql-bug/tree/master/mysqldump )

i will reference the first query of bug_buggy_chunk.sql as QUERY_COUNT ( https://github.com/ErwanMAS/consistent-read-mysql-bug/blob/master/bug_buggy_chunks.sql )

i will reference the file bug_create_buggy.sql	as INSERT_DATA_FROM_FEED ( https://github.com/ErwanMAS/consistent-read-mysql-bug/blob/master/bug_create_buggy.sql )

method 1 :

 the writer will insert	data with auto-commit
 a reader will open a session  and start a transaction

step by step :

 1) start a session A
 2) execute QUERY_COUNT must return 2000
 3) start a session B
 4) start a transaction with BEGIN
 5) check your isolation level , by SELECT @@SESSION.tx_isolation ;   must be REPEATABLE-READ
 5) execute QUERY_COUNT must return 2000
 6) switch to session A
 7) execute INSERT_DATA_FROM_FEED ( source bug_create_buggy.sql )
 8) wait for completion
 9) switch to session B
 10) execute QUERY_COUNT must return 2000 , but because of the bug it return 1706 why ?

method 2  :
 the writer will open a session , and start a transaction
 insert data and rollback
 a reader will open a regular session

step by step :

 1) start a session A
 2) start a TRANSACTION with BEGIN
 3) execute QUERY_COUNT must return 2000
 4) check your isolation level , by SELECT @@SESSION.tx_isolation ;   must be REPEATABLE-READ
 5) start a session B
 5) execute QUERY_COUNT must return 2000
 6) switch to session A
 7) execute INSERT_DATA_FROM_FEED ( source bug_create_buggy.sql )
 8) wait for completion	( don't commit )
 9) switch to session B
 10) execute QUERY_COUNT must return 2000 , but because of the bug it return 1706 why ?
 11) switch to session A
 12) rollback
 13) wait for completion of rollback
 14) switch to session B
 15) execute QUERY_COUNT return 2000

Justification of the bug :

For method 1
 step 10 is a bug	because	my select is pollue by commit made after	my first SELECT

 justification  in https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

> Suppose that you are running in the default REPEATABLE READ isolation level. When you issue a consistent read (that is, an ordinary SELECT statement),
> InnoDB gives your transaction a timepoint according to which your query sees the database.
> If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

For method 2

 step 10 is a bug because concurent insert was not committed , and my select return less data

 and at step 15 after the rollback , the query return now 2000

> Sorry, but your new answers are only confusing the scenario 1.
> 
> You wrote:
> 
> "
> instead of running bug_buggy_chunks.sql you can run only the first query
> "
> 
> So, after we have restored both tables we just run (in a loop) first SELECT from bug_buggy_chunks.sql ??
> This is understood and is quite clear. Just confirm. 

no need to run a loop , only twice ( please see my previous comment ) 

> Then, you wrote:
> 
> "
>   the script is committing ( because of auto_commit ) 
>   but the session B i open with START TRANSACTION WITH CONSISTEN
> SNAPSHOT , so  this transaction must not see transaction commited in
> thers session after my START TRANSACTION .
> "
> This is confusing. I explicitly wrote to you that START TRANSACTION WITH CONSISTENT SNAPSHOT can be used ONLY for dumping data with mysqldump or mysqlpump.
>

bug appear with a simple BEGIN ( please see my previous comment ) 

> Hence, I can test by running bug_buggy_chunks.sql in one transaction in parallel with the other transaction running script bug_create_buggy.sql.
> I will run BOTH scripts  WITHOUT  any START TRANSACTION WITH CONSISTENT SNAPSHOT, for the reasons explained above. I will run both with BEGIN as a first
> statement and COMMIT as a last statement in a repeatable-read isolation level.

i never spoke about COMMIT , no need to use COMMIT ( please see my previous comment ) 

> If I get only the results that are expected, then this will be closed as "Not a bug". 
> Is that OK for you ????

i was able to have this bug more than 30 times with a virgin fresh database , so i will not understand , how 
you can not repeat the bug .

Hi Mr. MAS,

Let me tell you what did I do and did not repeat your bug.

I used both dump files to create data, which toold LOT'S of time, due to the size of data.

I ran in parallel both .sql files. While they were running in parallel, SELECT always returned round numbers.

Now, if I understand you correctly. you execute SELECTs .... AFTER INSERTs have finished. This is not a bug.

Simply, your data changes are now visible to the other transaction , with SELECTs, and dataset has changed so that you do not get round numbers.

If you think otherwise, please explain precisely why are data in the tables unchanged after INSERTs ????

Or why are the changes made in a manner that the output must be a round number. Please, make explanation in the very detailed manner.

> [24 Feb 13:51] Sinisa Milivojevic
> 
> Hi Mr. MAS,
> Let me tell you what did I do and did not repeat your bug.
>

Which mysql version did you test ? on which architecture ?    x86 ( 32 bits ) or amd64 ( 64 bits ) , or spark ? 
The bug is visible on 5.6.X and 5.7.X , i did not find a dataset that trigger the bug for mysql 8.0.X

> I used both dump files to create data, which toold LOT'S of time, due to the size of data.
> I ran in parallel both .sql files. While they were running in parallel, SELECT always returned round numbers.
>
>
> Now, if I understand you correctly. you execute SELECTs .... AFTER INSERTs have finished. This is not a bug.
> Simply, your data changes are now visible to the other transaction , with SELECTs, and dataset has changed so that you do not get round numbers.
>
> If you think otherwise, please explain precisely why are data in the tables unchanged after INSERTs ????
> Or why are the changes made in a manner that the output must be a round number. Please, make explanation in the very detailed manner.

In my comment of '[22 Feb 21:32] Erwan MAS ' , i spoke about 2 methods , this method is described step by step .

And in method 2 , i never COMMIT , so my INSERTS are not visible . i still have the bug , this bug occur at a very precise moment ( step 10 ) ,
before the ROLLBACK .

Hi Mr. MAS,

I have tested your bug report on macOS Catalina with i9 CPU.

I used both 5.7.30 and 8.0.20. I used only first method, because it is the one originally reported.

Also, I did not use ROLLBACK, since it would not change anything, particularly with INSERT's in auto-commit mode.

Can't repeat.

i was unable to have the dataset for 8.X , this why my focus for this bug is only 5.7.X or 5.6.X 

you tested on 5.7.30 , but this is not a public version so i can not test myself .

well the bug is easily repeatable on 5.7.28 on linux using just the initial description.

Sinisa, you wrote "I ran in parallel both .sql files. While they were running in parallel, SELECT always returned round numbers."

That's overly complicating. It's a completely manual and sequential testcase,
according to the "how to repeat".   Just switch to another console and run the steps manually in client for other connection.

I uploaded 2 videos to exhibit the bug ( these are files in my repository ) .

The docker image i used is the docker image made by oracle team .
source is https://hub.docker.com/r/mysql/mysql-server

So i was able to have the bug again , please take look on my video , and tell me what is wrong .

This is repro on 5.7.29 using test case provided by reporter. I'm copying the test case to the designated location in case reqrd

Attachment: 98642_5.7.29.results (application/octet-stream, text), 23.36 KiB.

# INTERNAL/PRI NOTE for our Sinisa

Test case copied to below location on hod03:

ls -l /usr/local/support/bugs/community/bug98642/
total 145645
-rwxrwxrwx 1 umshastr common 148984192 Feb 25 13:48 master.zip

Hi Mr. MAS,

I have changed the manner in which I ran the test case. Hence, I have managed to repeat it on 5.7.29 too ...

Verified as reported.

5.6.36 and after are affected too .

Can we change the Severity so S1 ?

The significant functionality that missing is ISOLATION , mysql is a ACID DB . 

And no workaround is possible .

And about the version affected 5.6.X with X >= 36 .

Mr. MAS,

Severity S1 is reserved for security and crashing bugs only.

From your website :

S1 (Critical): Represents a complete loss of service, a significant functionality is missing, a system that hangs indefinitely; and there is no available workaround.

S1 can be `a significant functionality is missing` 

ISOLATION between SESSION is ONE  OF FUNDAMENTAL PROPERTIES  ( A.C.I.D. ) 

If i dont have ISOLATION , this not any more a ACID database .

My guess :
   this bug is a regression from this 2 changes .
   these 2 changes was designed on MYSQL 8.0 and backported to 5.6 and 5.7
   the regression is relative to a innodb with a primary key build with  2 columns .

Changes in MySQL 5.6.36
InnoDB: The row_search_mvcc() function unnecessarily traversed the entire table for a range query, which occurred when the record was not in the transaction read view. (Bug #84202, Bug #23481444, Bug #25251375)

Changes in MySQL 5.6.38
InnoDB: A failure occurred during an end range comparison. (Bug #25669686)

By the way i shrink the dataset so now it take less than 1 min to test .

Thank you, Mr. Mas ...........

I will rephrase the bug description by :

>>>    A range query can have his result truncated , by inserts that are not commited .

This is proved by case 2 /method 2 

>>>    During a transaction a range query select can have his result truncated , by inserts committed after the begin of the transaction

This is proved by case 1 /method 1

I don't understand :

- why you consider this bug not CRITICAL ? 

A bug in the MVCC engine , is a lost of a significant functionality , and in this case you did not provide a work-around .

- version 5.6 are affected , why this was modified ?

Hi Mr. MAS,

We have our own definitions of the Severity and we are sticking to those.

Severity of S1 is reserved only for security vulnerability bugs and crashing bugs. Your bug is neither.

MySQL 5.6 will soon enter a maintenance stage when only S1 bugs will be fixed.

I tested with 5.6.44 and observed the same behaviour.

Added 5.6 to the list.

Posted by developer:
 
Fixed as of the upcoming 5.6.50, 5.7.32, 8.0.22 release, and here's the proposed changelog entry from the documentation team:

 In session started with START TRANSACTION WITH CONSISTENT SNAPSHOT, a
range query returned a truncated result. The end range flag was not reset
at the beginning of the index read resulting in an aborted read and
missing rows.

Thank you, Daniel.

The bug can happend in 2 case : 

   a regular session can have result for range query truncated ( because of a other  transaction  that have pending/no commmited insert ) .

   a session start with BEGIN can have  result for a range query truncated because of inserts made in a other transaction .