MySQL Bugs: #104406: The result of querying through the secondary index is incorrect

Bug #104406	The result of querying through the secondary index is incorrect
Submitted:	24 Jul 2021 8:11	Modified:	25 Aug 2023 13:44
Reporter:	Zhang JiYang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Looking only for the secondary index on the same data will give an incorrect result. The root cause was not identified, but it may be related to the following factors:

1. Character set. If the table does not specify a character set of UTF8MB4, this is not triggered.
2. Long transaction. There must be a long transaction to block the purge process.
3. Secondary index. There is no error if the query is by primary key index.

Or for some other unknown reason.

How to repeat:
CREATE TABLE `foo_t` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `schema_id` bigint unsigned NOT NULL,
  `name` varchar(64),
  `type` enum('BASE TABLE','VIEW','SYSTEM VIEW'),
  `engine` varchar(64),
  PRIMARY KEY (`id`),
  UNIQUE KEY `schema_id` (`schema_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

--disable_query_log
connect (foo,localhost,root,,);
connection foo;
begin;
select count(*) from mysql.user;

connection default;

let $j = 5;
while ($j)
{
let $i = 1000;
while ($i)
{
  dec $i;
  eval insert into foo_t (schema_id, name, type, engine)
  values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));
}
delete from foo_t where id < 1000000;
dec $j;
}

--enable_query_log

let $i = 670;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

let $i = 675;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

connect (foo1,localhost,root,,);
connection foo1;

# ERROR. The result cannot be read. But, in fact, it should be readable.
select id from foo_t force index(schema_id) where name like 'name_670';

# If the query uses a primary key index, the data is read correctly.
select * from foo_t where name like 'name_670';

Hi Mr. zjy,

Thank you for your bug report.

Your report is quite interesting, but it leaves many questions unanswered, which means that we require other info from you.

First of all, it seems that what you have sent to us as a test case is, actually, MTR case. If that is so, we do not have a complete test case, because dropping and creation of the table is totally missing. 

Next, there is no explanation of what you mean by the result of the query is not readable. We have ran your test out of MTR context and got readable result.

Next, you are forcing an index that can NOT be used. This is because a column `name` is a second column in the index. In cases like this one, it is MUCH faster to scan the table then to use the index, particularly since the column `id` is not a part of the index that is being forced.

We get the following output from the same query as yours:

select id from t1 force index(schema_id) where name like 'name_670';
+------+
| id.      |
+------+
|  670   |
+------+
1 row in set (0.01 sec)

Hence, we can not repeat your test case .......

Sorry for that I didn't make it clear.

> First of all, it seems that what you have sent to us as a test case is, actually, MTR case. If that is so, we do not have a complete test case, because dropping and creation of the table is totally missing. 

Yes, it's a MTR case. But it won't cause a crash, so the error is not obvious. 

However, the test case is wrong, so the error can't be repeated. Here's the right test case that causes an abnormality:

```
CREATE TABLE `foo_t` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `schema_id` bigint unsigned NOT NULL,
  `name` varchar(64),
  `type` enum('BASE TABLE','VIEW','SYSTEM VIEW'),
  `engine` varchar(64),
  PRIMARY KEY (`id`),
  UNIQUE KEY `schema_id` (`schema_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 STATS_PERSISTENT=0;

--disable_query_log
connect (foo,localhost,root,,);
connection foo;
begin;
select count(*) from mysql.user;

connection default;

let $j = 5;
while ($j)
{
let $i = 1000;
while ($i)
{
  dec $i;
  eval insert into foo_t (schema_id, name, type, engine)
  values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));
}
delete from foo_t where id < 1000000;
dec $j;
}

--enable_query_log

let $i = 670;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

let $i = 675;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

connect (foo1,localhost,root,,);
connection foo1;

# ERROR. The result cannot be read. But, in fact, it should be readable.
select id from foo_t force index(schema_id) where name like 'name_670';

# If the query uses a primary key index, the data is read correctly.
select * from foo_t where name like 'name_670';

sleep 3000000;
```

My test method is to use MTR execution, then stop in the end. 

You can see that the query ("select id from foo_t force index(schema_id) where name like 'name_670'") cannot get a correct result by checking the log of the test case (which might be placed in 'mysql-test/var/log/XXXXX.LOG', XXXXX is the name of the test case.)

I have already repeated this bug on MySQL-8.0.26.

Hi Mr. zjy,

Thank you for your feedback.

However, things are still not clear enough and your test case is not exactly built like it should.

First of all, creating and dropping of the table is not contained in your step, not according to our MTR rules.

Next, you have a very long sleep, that could actually suffice to make query out of the query log.

Next, your run your read-only query from a totally different connection, which might also explain the inability to read.

Next, MTR is not designed to be stopped in its execution. Its correctness could be judged only if it runs all of its way through ......

We had other questions for you in our previous comments, so please reply to all of those and change your test case so that it complies with basic premises of our MTR design.

We are waiting on your feedback.

Sorry for an ambiguous description made from me. Let me make it more accurate. 

>> First of all, creating and dropping of the table is not contained in your step, not according to our MTR rules.
>> Next, you have a very long sleep, that could actually suffice to make query out of the query log.
>> Next, MTR is not designed to be stopped in its execution. Its correctness could be judged only if it runs all of its way through ......

This MTR-like test is just to let me better describe the steps that reproduce the bug. Of course, a complete MTR test case should have a cleanup process，that is, what you said "dropping of the table". 

And also usually there is no longer sleep in a MTR testcase. My original intent is to let MTR to stop to make some debugging work. This "sleep" is not a key point, and in fact the table definition is different from my previous comments.

If I don't take this way, we may describe the reproduction method:
Step-1. Open a session (con0), create a table:
CREATE TABLE `foo_t` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `schema_id` bigint unsigned NOT NULL,
  `name` varchar(64),
  `type` enum('BASE TABLE','VIEW','SYSTEM VIEW'),
  `engine` varchar(64),
  PRIMARY KEY (`id`),
  UNIQUE KEY `schema_id` (`schema_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 STATS_PERSISTENT=0;

Step-2. Open a new session (con1), and perform a read-only transaction that is not commited to block the PURGE system.

Step-3. Open a new session (con2): load data: 
let $j = 5;
while ($j)
{
let $i = 1000;
while ($i)
{
  dec $i;
  eval insert into foo_t (schema_id, name, type, engine)
  values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));
}
delete from foo_t where id < 1000000;
dec $j;
}

let $i = 670;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

let $i = 675;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

Step-4. Open a new seesion (con3): Execute two queries:
# ERROR. The result cannot be read. But, in fact, it should be readable.
select id from foo_t force index(schema_id) where name like 'name_670';

# If the query uses a primary key index, the data is read correctly.
select * from foo_t where name like 'name_670';

>> Next, your run your read-only query from a totally different connection, which might also explain the inability to read.
In fact, two queries are performed on the same connection (See the Step-4).

>> We had other questions for you in our previous comments, so please reply to all of those and change your test case so that it complies with basic premises of our MTR design.
I believe that my new comments can answer the previous questions. If not, please let me know.

Thanks a lot.

Hi Mr. zjy,

Sorry, but your test case is not self-sufficient.

Can you create a MTR test or a pure multi-threaded test case, which would be complete.

Hence, we need a MTR test that is complete and self-sufficient, without ANY descriptions and explanations in it. You can explain what is the bug about and what happens, outside of the MTR test or classical test case. Hence, MTR test should contain the preparation stage, like table creation, variables setting etc ..., creation of different concurrent sessions, sql command and MTR syntax and the cleanup stage.

Descriptive explanations of how to see what happens do not constitute a manner of exact, 100 % reproducible MTR test.

Thanks in advance.

I slightly modified JiYang's case to meet your requirements.

The problem is obvious and serious, and I hope to pay attention to it.

```
CREATE TABLE `foo_t` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `schema_id` bigint unsigned NOT NULL,
  `name` varchar(64),
  `type` enum('BASE TABLE','VIEW','SYSTEM VIEW'),
  `engine` varchar(64),
  PRIMARY KEY (`id`),
  UNIQUE KEY `schema_id` (`schema_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 STATS_PERSISTENT=0;

--disable_query_log
connect (foo,localhost,root,,);
connection foo;
begin;
select count(*) from mysql.user;

connection default;

let $j = 5;
while ($j)
{
let $i = 1000;
while ($i)
{
  dec $i;
  eval insert into foo_t (schema_id, name, type, engine)
  values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));
}
delete from foo_t where id < 1000000;
dec $j;
}

--enable_query_log

let $i = 670;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

let $i = 675;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

connect (foo1,localhost,root,,);
connection foo1;

# ERROR. The result cannot be read. But, in fact, it should be readable.
let $cnt1 = `select count(1) from foo_t force index(schema_id) where name like 'name_670'`;
# If the query uses a primary key index, the data is read correctly.
let $cnt2 = `select count(1) from foo_t force index(primary) where name like 'name_670'`;

--let $assert_text= Assert value should be same
--let $assert_cond = ($cnt1 == $cnt2)
--source include/assert.inc

drop table foo_t;

```

Hi,

Thank you Mr. Huang, but with 8.0.30 results are quite readable on my setup.

It just reports a syntax error on one of your selects ...... second or third ......

Otherwise everything else works just fine .......

Finally I found that how to fix it:
set session optimizer_switch = "skip_scan=off";

I think this is because row_search_end_range_check is not aware of "skip scan range". 

1. IndexSkipScanIterator::Read --> ... --> handler::set_end_range, set end_rnage.
2. row_serach_mvcc --> ... --> row_search_end_range_check, found out of range.
3. return error: DB_RECORD_NOT_FOUND, then the query ended early.

In fact, records that are not searched later may still meet the conditions. In my test:
page_no=48, schema_id=4, name=name_538
page_no=48, supremum # Found 'out of range', return DB_RECORD_NOT_FOUND prematurely.
...
page_no=49, schema_id=4, name=name_670 # Records that meet the condition are omitted

How to repeat:

```
CREATE TABLE `foo_t` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `schema_id` bigint unsigned NOT NULL,
  `name` varchar(64),
  `type` enum('BASE TABLE','VIEW','SYSTEM VIEW'),
  `engine` varchar(64),
  PRIMARY KEY (`id`),
  UNIQUE KEY `schema_id` (`schema_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 STATS_PERSISTENT=0;

--disable_query_log
connect (foo,localhost,root,,);
connection foo;
begin;
select count(*) from mysql.user;

connection default;

let $j = 5;
while ($j)
{
let $i = 1000;
while ($i)
{
  dec $i;
  eval insert into foo_t (schema_id, name, type, engine)
  values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));
}
delete from foo_t where id < 1000000;
dec $j;
}

--enable_query_log

let $i = 670;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

let $i = 675;
eval insert into foo_t (schema_id, name, type, engine)
values ($i % 6, CONCAT("name_", $i), 'SYSTEM VIEW', repeat('a', 60));

connect (foo1,localhost,root,,);
connection foo1;

# ERROR. The result cannot be read. But, in fact, it should be readable.
let $cnt1 = `select count(1) from foo_t force index(schema_id) where name like 'name_670'`;
# If the query uses a primary key index, the data is read correctly.
let $cnt2 = `select count(1) from foo_t force index(primary) where name like 'name_670'`;

--let $assert_text= Assert value should be same
--let $assert_cond = ($cnt1 = $cnt2)
--source include/assert.inc

drop table foo_t;
```

Also, Bug #112180 (one of which should be marked duplicate)

Hi Mr. Jiyang,

First of all, we could not repeat your test case. 

We did not change anything in CREATE or INSERT and got these results, with direct SELECTs:

-----------------
elect count(*) from foo_t force index(schema_id) where name LIKE 'name_67%';
count(*)
1
explain select count(*) from foo_t force index(schema_id) where name like 'name_67%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	foo_t	NULL	range	schema_id	schema_id	267	NULL	544	100.00	Using where; Using index for skip scan
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`foo_t` FORCE INDEX (`schema_id`) where (`test`.`foo_t`.`name` like 'name_67%')
select count(*) from foo_t force index(primary) where name LIKE 'name_67%';
count(*)
2
select count(*) from foo_t  where name like 'name_67%';
count(*)
1
select name from foo_t  where name LIKE 'name_67%';
name
name_675

----------------

Hence, it was 675.

Next, we added this switch:

set session optimizer_switch = "skip_scan=off";

And got exact results.

The above switch is ABSOLUTELY necessary, because column `name` is second in the index, so scanning for the second index entry is recommended only with the above switch.

This is described in our Reference Manual here:

https://dev.mysql.com/doc/refman/8.1/en/optimizer-hints.html

Hi Mr. Jiyang,

This is just to inform you that this bug is a duplicate of an internal bug report, not visible to the public.

Thus, it fails with 8.0.26 and 8.0.30 and is OK with 8.0.34.

Hence, you should not have any problems with 8.0.34. If you have, try latest release of 8.0 and 8.1.