Bug #96030 mysql query returns empty results intermittently under load
Submitted: 28 Jun 2019 14:48 Modified: 3 Jul 2019 18:09
Reporter: Chris Muench Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:mysql 5.6.40 OS:Linux (rds db.m4.large)
Assigned to: CPU Architecture:Any

[28 Jun 2019 14:48] Chris Muench
Description:
I have the following query that sometimes returns an **empty set** on the master but NEVER on the read replica and there is data that is there that match on both databases. It is random and am wondering if there is a mysql setting or something with query cache. Running mysql 5.6.40-log on rds.

I have tried doing `optimizer_switch="index_merge_intersection=off"` but it didn't work.

Once I do optimize table `phppos_items` it fixes it for a little bit (3 minutes) and then it goes back to being erratic (mostly empty sets). These are all innodb tables.

settings:

https://gist.github.com/blasto333/82b18ef979438b93e4c39624bbf489d7

Seems to return empty set more often during busy time of day. Server is rds m4.large with 500 databases with 100 tables each

Query:

    SELECT SUM( phppos_sales_items.damaged_qty ) AS damaged_qty, 
            SUM( phppos_sales_items.subtotal ) AS subtotal, 
            SUM( phppos_sales_items.total ) AS total, 
            SUM( phppos_sales_items.tax ) AS tax, 
            SUM( phppos_sales_items.profit ) AS profit
    FROM  `phppos_sales` 
        JOIN  `phppos_sales_items` ON  `phppos_sales_items`.`sale_id` =  `phppos_sales`.`sale_id` 
        JOIN  `phppos_items` ON  `phppos_sales_items`.`item_id` =  `phppos_items`.`item_id` 
    WHERE  `phppos_sales`.`deleted` =0
    AND  `sale_time` BETWEEN  '2019-01-01 00:00:00' AND  '2019-12-31 23:59:59'
    AND  `phppos_sales`.`location_id` IN ( 1 ) 
    AND  `phppos_sales`.`store_account_payment` =0
    AND  `suspended` <2
    AND  `phppos_items`.`deleted` =0
    AND  `phppos_items`.`supplier_id` = '485'
    GROUP BY  `phppos_sales_items`.`sale_id` 

Explain:

    +----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
    | id | select_type | table              | type        | possible_keys                                                                                 | key                         | key_len | ref                                                   | rows | Extra                                                                                                   |
    +----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
    |  1 | SIMPLE      | phppos_items       | index_merge | PRIMARY,phppos_items_ibfk_1,deleted,deleted_system_item                                       | phppos_items_ibfk_1,deleted | 5,4     | NULL                                                  |   44 | Using intersect(phppos_items_ibfk_1,deleted); Using where; Using index; Using temporary; Using filesort |
    |  1 | SIMPLE      | phppos_sales_items | ref         | PRIMARY,item_id,phppos_sales_items_ibfk_3,phppos_sales_items_ibfk_4,phppos_sales_items_ibfk_5 | item_id                     | 4       | phppoint_customer.phppos_items.item_id       |   16 | NULL                                                                                                    |
    |  1 | SIMPLE      | phppos_sales       | eq_ref      | PRIMARY,deleted,location_id,sales_search,phppos_sales_ibfk_10                                 | PRIMARY                     | 4       | phppoint_customer.phppos_sales_items.sale_id |    1 | Using where                                                                                             |
    +----+-------------+--------------------+-------------+-----------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+---------------------------------------------------------------------------------------------------------+
    3 rows in set (0.00 sec)

How to repeat:
This is hard to reproduce but happens under load of the database.
[28 Jun 2019 16:14] Chris Muench
I think it might be the same as #79675. I had to clear the query cache after I made the change and the problem has seem to gone away (been testing for 1 hour)
[1 Jul 2019 12:48] MySQL Verification Team
Hi Mr. Muench,

Thank you for this bug report.

I have to inform you that a query cache is a deprecated feature, so it will no longer get any bug fixes, except for the crashing or security bug.

It is not supported any more.
[3 Jul 2019 18:09] Chris Muench
Resetting the query cache was part of the fix. BUT the bug 79675 should be fixed for 5.6
[4 Jul 2019 12:49] MySQL Verification Team
These types of decisions are made internally by Development and we have no say on those matters.