MySQL Forums :: InnoDB :: Strange issue with count(*) with where clause


Advanced Search

Strange issue with count(*) with where clause
Posted by: Frank Feingold ()
Date: March 16, 2017 08:11AM

I don't know that this is InnoDB related, but it's as good a place as anywhere to start.

Background:

MySQL 5.6.35 Community edition

All default MySQL settings except:

innodb_file_per_table=1
max_allowed_packet=500M
open_files_limit=10000

Basic table structure:

create table ticket (
id int(1) unsigned not null auto_increment,
department_id int(10) unsigned not null,
status_id int(10) unsigned not null)

Foreign key on department_id to a department table
Foreign key on status_id to a status table

The SQL is very simple:

select count(*) from ticket where department_id = 1 and status_id = 1

If I run this SQL multiple tickets sometimes I get the correct results and sometimes I get zero. While the data could be changing in between queries, it's really never going to go to zero.

This one really has me stumped as it's a very basic query.

Thanks in advance and sorry if this is posted in the wrong place

Options: ReplyQuote


Subject Views Written By Posted
Strange issue with count(*) with where clause 51 Frank Feingold 03/16/2017 08:11AM
Re: Strange issue with count(*) with where clause 30 Peter Brawley 03/16/2017 10:38AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.