MySQL Bugs: #81031: count(*) on innodb sometimes returns 0

Bug #81031	count(*) on innodb sometimes returns 0
Submitted:	11 Apr 2016 13:00	Modified:	24 May 2017 13:58
Reporter:	Ashraf Amayreh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.6.28	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	index merge, index_merge_intersection, wrong results

Description:
Doing a count(*) on innodb table SOMETIMES returns 0, remove count, you get results. Notice that the query is fired against mysql client, not through any code layers!

Here's the exact query

select count(*) from books where publisher_id = 46 AND deleted = 0 AND status_id = 3;

Just remove the count (and replace it with *), and you get results!

Explain shows the following

Using intersect(IDX_cms_books_publisher_id,IDX_cms_books_status_id,IDX_cms_books_deleted); Using where; Using index

If I turn off index merge intersection, problem disappears. So it must be related to the intersect merge feature

SET SESSION optimizer_switch="index_merge_intersection=off";
select count(*) from books where publisher_id = 46 AND deleted = 0 AND status_id = 3;

Working fine

How to repeat:
Create table with same indexes? Please note that this doesn't ALWAYS happen. So there's no sure way to repeat. But in general, it's 0 (wrong) 80% of the time.

Suggested fix:
This is a quick fix for now

SET SESSION optimizer_switch="index_merge_intersection=off";

Hi Ashraf,

Thank you for the report.
I'm not able to reproduce with the dummy schema/data set. Could you please provide exact test case to reproduce this issue at our end(table schema and  subset of data to trigger the issue preferably mysqldump file ). If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh

Hello there 

I use Mysql 5.6.33 on my portal www.reedb.com and this bug still exists.
This problem is very critical!

example: 

select count(*) from <table> where active=1 and obj=10 
result 0

select count(<table.update>) from <table> where active=1 and obj=10 
result 8805

SET SESSION optimizer_switch="index_merge_intersection=off"
select count(*) from <table> where active=1 and obj=10 
result 8805

If you want, i can send you a dump of a table for tests.

thanks to Ashraf Amayreh - you made my day

best regards n.Olivier

www.reedb.com - real estate online

TESTCASE:
---------

drop table if exists t;
create table t (id int,a int,b int,key (a),key (b)
) engine=innodb partition by hash (id) partitions 4;

insert into t values (0,4,1);
insert into t values (6,2,5);
insert into t values (6,7,5);
insert into t values (6,8,2);
insert into t values (1,7,2);
insert into t values (2,4,6);

select sql_no_cache * from t;
set session optimizer_switch="index_merge_intersection=off";
select sql_no_cache count(*) from t where a=4 and b=6;
set session optimizer_switch="index_merge_intersection=on";
select sql_no_cache count(*) from t where a=4 and b=6;

Verified on 5.6.35.  

5.5.52, 5.7.17, 8.0.1 are not affected.

Also verified this bug exists in MySQL 5.6.34 via AWS RDS and is not consistent, sometimes appropriate number of results are returned, but after 2-3 weeks of uptime DB starts to return zero results for a majority of count(*) queries. 

index_merge_intersection=off

Fixes the issue.

Posted by developer:
 
Fixed as of the upcoming 5.6.37, 5.7.19, 8.0.2 release, and here's the changelog entry:

When using an index merge optimizer switch, a SELECT COUNT(*) operation
sometimes returned 0. Partitioning code incorrectly performed a memcpy
instead of a column copy of columns read by the index, causing the wrong
records to be copied.

This issue still occurs for me, I am using the Percona release, so maybe I should open it with them, but according to them they are using your latest branch.

https://www.percona.com/doc/percona-server/5.6/release-notes/Percona-Server-5.6.37-82.2.ht...

When i run the select count with index () it returns the correct amount.

I have also temporarily set index_merge_intersection off which has resolved the error.

Hi Steven, 

Since bug report is closed, please, open a new bug report with a reproducible test case for us to look at.

Mysql 5.6.39 Innodb table has the same issue on my system.

The database

Attachment: tmp_projectdays.sql (application/octet-stream, text), 313.56 KiB.

"Index merge intersect" is usually a clue that you have multiple single-row indexes.  Almost always, you could add a multi-column ("composite") index to make it run faster _and_ avoid the bug with "intersect".