Bug #79009 Duplicate records in perf schema digests table
Submitted: 28 Oct 2015 21:44 Modified: 28 Sep 2016 8:43
Reporter: Trey Raymond Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.6.24 OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[28 Oct 2015 21:44] Trey Raymond
Description:
seeing the following on some installs.  it doesn't happen with all digests, just a few out of the bunch.  timestamps show that it was written when the query pattern was first seen, and the other row got all the updates, while the duplicate just sat with count=0.

*************************** 1. row ***************************
                SCHEMA_NAME: [db]
                     DIGEST: [digest 1]
                DIGEST_TEXT: [digest text 1]
                 COUNT_STAR: 11222
             SUM_TIMER_WAIT: 3502903096000
             MIN_TIMER_WAIT: 129067000
             AVG_TIMER_WAIT: 312146000
             MAX_TIMER_WAIT: 33191536000
              SUM_LOCK_TIME: 1590981000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 11223
          SUM_ROWS_EXAMINED: 11223
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2015-08-05 13:42:51
                  LAST_SEEN: 2015-08-19 12:12:38
*************************** 2. row ***************************
                SCHEMA_NAME: [db]
                     DIGEST: [digest 1]
                DIGEST_TEXT: [digest text 1]
                 COUNT_STAR: 0
             SUM_TIMER_WAIT: 0
             MIN_TIMER_WAIT: 0
             AVG_TIMER_WAIT: 0
             MAX_TIMER_WAIT: 0
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2015-08-05 13:42:51
                  LAST_SEEN: 2015-08-05 13:42:51
*************************** 3. row ***************************
                SCHEMA_NAME: [db]
                     DIGEST: [digest 2]
                DIGEST_TEXT: [digest text 2]
                 COUNT_STAR: 549
             SUM_TIMER_WAIT: 295066494000
             MIN_TIMER_WAIT: 292858000
             AVG_TIMER_WAIT: 537461000
             MAX_TIMER_WAIT: 905152000
              SUM_LOCK_TIME: 142504000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 549
          SUM_ROWS_EXAMINED: 1098
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2015-09-23 14:52:19
                  LAST_SEEN: 2015-10-01 10:53:05
*************************** 4. row ***************************
                SCHEMA_NAME: [db]
                     DIGEST: [digest 2]
                DIGEST_TEXT: [digest text 2]
                 COUNT_STAR: 0
             SUM_TIMER_WAIT: 0
             MIN_TIMER_WAIT: 0
             AVG_TIMER_WAIT: 0
             MAX_TIMER_WAIT: 0
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2015-09-23 14:52:19
                  LAST_SEEN: 2015-09-23 14:52:19
4 rows in set (0.05 sec)

How to repeat:
Can't find a deterministic way to reproduce, we get anywhere from zero to a dozen of these on prod systems.  The following query will display them:

select *
from performance_schema.events_statements_summary_by_digest zero_rows
left join performance_schema.events_statements_summary_by_digest real_rows on real_rows.schema_name=zero_rows.schema_name and real_rows.digest=zero_rows.digest and real_rows.count_star>0
where zero_rows.count_star=0;

Suggested fix:
find out why the second row gets inserted at first_seen date, and make sure it only inserts one row.  the tuple (schema_name,digest) should always be unique for the data to make sense.
[27 Jul 2016 17:53] Trey Raymond
It's getting worse!  check this out (5.6.29 host):

mysql> select * from events_statements_summary_by_digest where schema_name='sbtest1' and digest='f68bce38a0814cfc93da84c55aa7ed26'\G
*************************** 1. row ***************************
                SCHEMA_NAME: sbtest1
                     DIGEST: f68bce38a0814cfc93da84c55aa7ed26
                DIGEST_TEXT: SELECT DISTINCTROW `c` FROM `sbtest3` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
                 COUNT_STAR: 0
             SUM_TIMER_WAIT: 0
             MIN_TIMER_WAIT: 0
             AVG_TIMER_WAIT: 0
             MAX_TIMER_WAIT: 0
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2016-07-26 19:00:01
                  LAST_SEEN: 2016-07-26 19:00:01
*************************** 2. row ***************************
                SCHEMA_NAME: sbtest1
                     DIGEST: f68bce38a0814cfc93da84c55aa7ed26
                DIGEST_TEXT: SELECT DISTINCTROW `c` FROM `sbtest3` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
                 COUNT_STAR: 0
             SUM_TIMER_WAIT: 0
             MIN_TIMER_WAIT: 0
             AVG_TIMER_WAIT: 0
             MAX_TIMER_WAIT: 0
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2016-07-26 19:00:01
                  LAST_SEEN: 2016-07-26 19:00:01
*************************** 3. row ***************************
                SCHEMA_NAME: sbtest1
                     DIGEST: f68bce38a0814cfc93da84c55aa7ed26
                DIGEST_TEXT: SELECT DISTINCTROW `c` FROM `sbtest3` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
                 COUNT_STAR: 54226
             SUM_TIMER_WAIT: 103364227384000
             MIN_TIMER_WAIT: 1230608000
             AVG_TIMER_WAIT: 1906174000
             MAX_TIMER_WAIT: 10798202000
              SUM_LOCK_TIME: 2175628000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 5422800
          SUM_ROWS_EXAMINED: 16268400
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 54228
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 54228
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 5422800
              SUM_SORT_SCAN: 54228
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2016-07-27 17:45:02
                  LAST_SEEN: 2016-07-27 17:50:14
3 rows in set (0.00 sec)
[28 Sep 2016 8:43] Marc Alff
Duplicate with:
Bug #79533 	events_statements_summary_by_digest: no unique rows by DIGEST/SCHEMA_NAME

Please track 79533 instead.