| Bug #77603 | prepared_statements_instances table does not update SUM_* columns | ||
|---|---|---|---|
| Submitted: | 2 Jul 2015 17:25 | Modified: | 8 Aug 2015 19:30 |
| Reporter: | Sveta Smirnova (OCA) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 5.7.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Jul 2015 17:25]
Sveta Smirnova
[3 Jul 2015 7:57]
MySQL Verification Team
Hello Sveta,
Thank you the bug report.
May be this is fixed internally as I cannot repeat with latest daily 5.7.8 builds. I checked few randomly e.g SUM_SELECT_SCAN,SUM_SORT_SCAN, SUM_SORT_ROWS, SUM_NO_INDEX_USED, SUM_ROWS_AFFECTED etc and all reflecting in corresponding SUM* values.
mysql> select count(*) from employees where hire_date > '1995-01-01';
+----------+
| count(*) |
+----------+
| 34004 |
+----------+
1 row in set (0.09 sec)
mysql> select * from performance_schema.events_statements_history\G
*************************** 2. row ***************************
THREAD_ID: 26
EVENT_ID: 12
END_EVENT_ID: 12
EVENT_NAME: statement/sql/select
SOURCE: socket_connection.cc:98
TIMER_START: 291494761429000
TIMER_END: 291584276481000
TIMER_WAIT: 89515052000
LOCK_TIME: 114000000
SQL_TEXT: select count(*) from employees where hire_date > '1995-01-01'
DIGEST: cad93d30d44cbf142d0952b861c10635
DIGEST_TEXT: SELECT COUNT ( * ) FROM `employees` WHERE `hire_date` > ?
CURRENT_SCHEMA: employees
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 300024
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
*************************** 3. row ***************************
.
.
10 rows in set (0.00 sec)
mysql> prepare stmt from 'select count(*) from employees where hire_date > ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> set @hd='1995-01-01';
Query OK, 0 rows affected (0.00 sec)
mysql> execute stmt using @hd;
+----------+
| count(*) |
+----------+
| 34004 |
+----------+
1 row in set (0.08 sec)
mysql> select * from performance_schema.prepared_statements_instances\G
*************************** 1. row ***************************
OBJECT_INSTANCE_BEGIN: 139798098605552
STATEMENT_ID: 1
STATEMENT_NAME: stmt
SQL_TEXT: select count(*) from employees where hire_date > ?
OWNER_THREAD_ID: 26
OWNER_EVENT_ID: 15
OWNER_OBJECT_TYPE: NULL
OWNER_OBJECT_SCHEMA: NULL
OWNER_OBJECT_NAME: NULL
TIMER_PREPARE: 510249000
COUNT_REPREPARE: 0
COUNT_EXECUTE: 1
SUM_TIMER_EXECUTE: 82310575000
MIN_TIMER_EXECUTE: 82310575000
AVG_TIMER_EXECUTE: 82310575000
MAX_TIMER_EXECUTE: 82310575000
SUM_LOCK_TIME: 124000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 1
SUM_ROWS_EXAMINED: 300024
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: 1
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 1
SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)
Thanks,
Umesh
[3 Jul 2015 8:00]
MySQL Verification Team
//
truncate performance_schema.prepared_statements_instances;
truncate performance_schema.events_statements_history;
mysql> select * from keyvalue order by id desc limit 1;
+---------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| id | name1 | name2 | name3 | name4 |
+---------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| 1000004 | 3df319259ff1e39a7a2969d87d63d1b2 | fe828400d6991779ef73c5c2819cefc8 | f1889f83e0566a8764881ade82b2f533 | da685eef142f2e984c2ae6e73300ba2c |
+---------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (0.62 sec)
*************************** 3. row ***************************
THREAD_ID: 26
EVENT_ID: 34
END_EVENT_ID: 34
EVENT_NAME: statement/sql/select
SOURCE: socket_connection.cc:98
TIMER_START: 3328526851049000
TIMER_END: 3329148319209000
TIMER_WAIT: 621468160000
LOCK_TIME: 60000000
SQL_TEXT: select * from keyvalue order by id desc limit 1
DIGEST: 4f37b23308ba0691cc64c8f98b9c7eb7
DIGEST_TEXT: SELECT * FROM `keyvalue` ORDER BY `id` DESC LIMIT ?
CURRENT_SCHEMA: test
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 1000005
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 1
SORT_SCAN: 1
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
3 rows in set (0.00 sec)
mysql> prepare stmt from 'select * from keyvalue order by id desc limit ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> set @lm=1;
Query OK, 0 rows affected (0.00 sec)
mysql> execute stmt using @lm;
+---------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| id | name1 | name2 | name3 | name4 |
+---------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
| 1000004 | 3df319259ff1e39a7a2969d87d63d1b2 | fe828400d6991779ef73c5c2819cefc8 | f1889f83e0566a8764881ade82b2f533 | da685eef142f2e984c2ae6e73300ba2c |
+---------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (0.61 sec)
mysql> select * from performance_schema.prepared_statements_instances\G
*************************** 2. row ***************************
OBJECT_INSTANCE_BEGIN: 139747699948816
STATEMENT_ID: 3
STATEMENT_NAME: stmt
SQL_TEXT: select * from keyvalue order by id desc limit ?
OWNER_THREAD_ID: 26
OWNER_EVENT_ID: 36
OWNER_OBJECT_TYPE: NULL
OWNER_OBJECT_SCHEMA: NULL
OWNER_OBJECT_NAME: NULL
TIMER_PREPARE: 109311000
COUNT_REPREPARE: 0
COUNT_EXECUTE: 1
SUM_TIMER_EXECUTE: 619428076000
MIN_TIMER_EXECUTE: 619428076000
AVG_TIMER_EXECUTE: 619428076000
MAX_TIMER_EXECUTE: 619428076000
SUM_LOCK_TIME: 107000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 1
SUM_ROWS_EXAMINED: 1000005
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: 1
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 1
SUM_SORT_SCAN: 1
SUM_NO_INDEX_USED: 1
SUM_NO_GOOD_INDEX_USED: 0
2 rows in set (0.00 sec)
[3 Jul 2015 12:06]
Sveta Smirnova
Hi Umesh, I cannot check 5.7.8 yet, so I can only believe you if this was fixed internally. I use 5.7.7 version from GitHub.
[3 Jul 2015 12:14]
MySQL Verification Team
Hi Sveta, Thanks, will check internally on which exact bug fixed this and keep you posted further on this. Thanks, Umesh
[8 Aug 2015 19:30]
Sveta Smirnova
Fixed in version 5.7.8.
