MySQL Bugs: #100795: join performance_schema.events_statements_history

Bug #100795	join performance_schema.events_statements_history_long with
Submitted:	10 Sep 2020 9:41	Modified:	9 Oct 2020 1:48
Reporter:	feng wang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.29	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
this sql has no resultset, event it should have in fact;
select  THREAD_ID,EVENT_ID,EVENT_NAME,CURRENT_SCHEMA,SQL_TEXT from performance_schema.events_statements_history_long cn  where cn.thread_id in  (select  c.thread_id  from information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c where w.blocking_trx_id = b.trx_id and b.trx_mysql_thread_id = c.PROCESSLIST_ID ) order by THREAD_ID,EVENT_ID;

How to repeat:
start a session:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set id = 2 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

start another session:
update t set id=3 where id=1;

a new session:
query:
mysql> (select  c.thread_id  from information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c where w.blocking_trx_id = b.trx_id and b.trx_mysql_thread_id = c.PROCESSLIST_ID ) ;
+-----------+
| thread_id |
+-----------+
|        66 |
+-----------+
1 row in set, 1 warning (0.00 sec)

this query has results:
select  THREAD_ID,EVENT_ID,EVENT_NAME,CURRENT_SCHEMA,SQL_TEXT from performance_schema.events_statements_history_long cn  where exists (select  1  from information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c where w.blocking_trx_id = b.trx_id and b.trx_mysql_thread_id = c.PROCESSLIST_ID and  cn.THREAD_ID = c.THREAD_ID );
+-----------+----------+------------------------------+----------------+----------------------------------+
| THREAD_ID | EVENT_ID | EVENT_NAME                   | CURRENT_SCHEMA | SQL_TEXT                         |
+-----------+----------+------------------------------+----------------+----------------------------------+
|        66 |        1 | statement/sql/select         | NULL           | select @@version_comment limit 1 |
|        66 |        2 | statement/sql/show_databases | NULL           | show databases                   |
|        66 |        3 | statement/sql/error          | NULL           | creat database wftest            |
|        66 |        4 | statement/sql/create_db      | NULL           | create database wftest           |
|        66 |        5 | statement/sql/select         | NULL           | SELECT DATABASE()                |
|        66 |        6 | statement/com/Init DB        | NULL           | NULL                             |
|        66 |        7 | statement/sql/show_databases | wftest         | show databases                   |
|        66 |        8 | statement/sql/show_tables    | wftest         | show tables                      |
|        66 |        9 | statement/sql/create_table   | wftest         | create table t(id int)           |
|        66 |       10 | statement/sql/insert         | wftest         | insert into t values(1)          |
|        66 |       11 | statement/sql/commit         | wftest         | commit                           |
|        66 |       12 | statement/sql/begin          | wftest         | start transaction                |
|        66 |       13 | statement/sql/update         | wftest         | update t set id = 2 where id=1   |
+-----------+----------+------------------------------+----------------+----------------------------------+
13 rows in set, 289 warnings (0.02 sec)

but this not:
mysql>  select  THREAD_ID,EVENT_ID,EVENT_NAME,CURRENT_SCHEMA,SQL_TEXT from performance_schema.events_statements_history_long cn  where cn.thread_id in  (select  c.thread_id  from information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c where w.blocking_trx_id = b.trx_id and b.trx_mysql_thread_id = c.PROCESSLIST_ID ) order by THREAD_ID,EVENT_ID;
Empty set (0.01 sec)

Hi Mr. wang,

Thank you for your bug report.

However, we can not process this report, since it has insufficient info, plus some inconsistencies.

First of all, you are not using latest 5.7 release. It is necessary to use last patch-fix release, so that we do not chase old bugs.

Next, you have provided insufficient test case. No CREATE statements, no full test case that could be repeated and so on .......

Last, but not least, those two statements, with nested queries, are completely different. One only asks that the nested query returns ANY result set, while the other one has an additional condition on matching the thread_id.

We can't repeat your report.

Thanks for your response.
sorry for incomplete bug report, I will do some tests on mysql-5.7.31-el7-x86_64.
as follows:

1, prepare environment
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.31-log |
+------------+
1 row in set (0.00 sec)

mysql> create database wftest;
Query OK, 1 row affected (0.04 sec)

mysql> use wftest;
Database changed
mysql> create table t(id int auto_increment primary key);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

2, start new session for blocking
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.31-log |
+------------+
1 row in set (0.00 sec)

mysql> update t set id=4 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

mysql>

3, start another session, for blocked sql
mysql> use wftest;
Database changed

mysql> set session innodb_lock_wait_timeout=600;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

mysql> update t set id=3 where id=1;

here ,this session must be blocking for 10 minutes.

4, start new session, to query some performance views
mysql> select  c.thread_id  from
    -> information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c
    -> where w.blocking_trx_id = b.trx_id
    -> and b.trx_mysql_thread_id = c.PROCESSLIST_ID ;
+-----------+
| thread_id |
+-----------+
|        34 |
+-----------+
1 row in set, 1 warning (0.01 sec)

mysql> select  THREAD_ID,EVENT_ID,EVENT_NAME,CURRENT_SCHEMA,SQL_TEXT
    -> from performance_schema.events_statements_history_long cn
    -> where cn.thread_id in  (
    -> select  c.thread_id  from
    -> information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c
    -> where w.blocking_trx_id = b.trx_id
    -> and b.trx_mysql_thread_id = c.PROCESSLIST_ID
    -> );
Empty set (0.00 sec)

mysql> select  THREAD_ID,EVENT_ID,EVENT_NAME,CURRENT_SCHEMA,SQL_TEXT
    -> from performance_schema.events_statements_history_long cn
    -> where exists (
    -> select  1  from information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c
    -> where w.blocking_trx_id = b.trx_id
    -> and b.trx_mysql_thread_id = c.PROCESSLIST_ID
    -> and  cn.THREAD_ID = c.THREAD_ID
    -> );
+-----------+----------+------------------------------+----------------+----------------------------------+
| THREAD_ID | EVENT_ID | EVENT_NAME                   | CURRENT_SCHEMA | SQL_TEXT                         |
+-----------+----------+------------------------------+----------------+----------------------------------+
|        34 |        1 | statement/sql/select         | NULL           | select @@version_comment limit 1 |
|        34 |        2 | statement/sql/select         | NULL           | SELECT DATABASE()                |
|        34 |        3 | statement/com/Init DB        | NULL           | NULL                             |
|        34 |        4 | statement/sql/show_databases | wftest         | show databases                   |
|        34 |        5 | statement/sql/show_tables    | wftest         | show tables                      |
|        34 |        6 | statement/com/Field List     | wftest         | NULL                             |
|        34 |        7 | statement/sql/begin          | wftest         | start transaction                |
|        34 |        8 | statement/sql/select         | wftest         | select @@version                 |
|        34 |        9 | statement/sql/update         | wftest         | update t set id=4 where id=1     |
+-----------+----------+------------------------------+----------------+----------------------------------+

In sub-query and exists sub-query have different forms , but they serve the same purpose here. 

this is my config file:
  =3306
basedir                         =/usr/local/mysql/mysql-5.7.31-el7-x86_64
plugin-dir                      =/usr/local/mysql/mysql-5.7.31-el7-x86_64/lib/plugin
datadir                         =/database/my3306/data
pid_file                        =/database/my3306/var/3306.pid
socket                          =/database/my3306/var/3306.socket
character-set-server            =utf8mb4
server-id                       =3
innodb_file_per_table           =1
innodb_log_buffer_size          =64M
innodb_buffer_pool_size         =2048M
innodb_log_files_in_group       =2
innodb_thread_concurrency       =32
innodb_flush_log_at_trx_commit  =0
sync_binlog                     =1
thread_cache_size               =32

[root@mysql2 my3306]# more my.cnf
[mysql]
default-character-set           =utf8mb4
[mysqld]
port                            =3306
basedir                         =/usr/local/mysql/mysql-5.7.31-el7-x86_64
plugin-dir                      =/usr/local/mysql/mysql-5.7.31-el7-x86_64/lib/plugin
datadir                         =/database/my3306/data
pid_file                        =/database/my3306/var/3306.pid
socket                          =/database/my3306/var/3306.socket
character-set-server            =utf8mb4
server-id                       =3
innodb_file_per_table           =1
innodb_log_buffer_size          =64M
innodb_buffer_pool_size         =2048M
innodb_log_files_in_group       =2
innodb_thread_concurrency       =32
innodb_flush_log_at_trx_commit  =0
sync_binlog                     =1
thread_cache_size               =32
event_scheduler                 =off
slow-query-log                  =ON
slow_query_log_file             =/database/my3306/log/slow.log
long_query_time                 =2
log_slow_slave_statements       =ON
binlog_format                   =row
expire_logs_days                =60
log_bin                         =/database/my3306/log/binlog
log_bin_index                   =/database/my3306/log/binlog.index
log_error                       =/database/my3306/log/log.err
relay_log                       =/database/my3306/log/relay_log
relay_log_index                 =/database/my3306/log/relay_log.index
relay_log_info_file             =/database/my3306/log/relay-log.info
general_log_file                =/database/my3306/log/general.log
master_info_repository          =TABLE
relay_log_info_repository       =TABLE
log_slave_updates               =ON
gtid_mode                       =1
enforce_gtid_consistency        =1
max_binlog_size                 =512M
innodb_log_file_size            =2048M
innodb_log_files_in_group       =2
skip-slave-start
query_cache_size                =0
tmp_table_size                  =64M
myisam_max_sort_file_size       =10G
myisam_sort_buffer_size         =30M
key_buffer_size                 =0
read_buffer_size                =64K
read_rnd_buffer_size            =256K
sort_buffer_size                =512K
bulk_insert_buffer_size         =64M
max_allowed_packet              =64M
sql_mode                        =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY
autocommit=0
transaction_isolation           =READ-COMMITTED
innodb_buffer_pool_instances=2
innodb_page_size=65536
innodb_write_io_threads=8
innodb_io_capacity=400
innodb_io_capacity_max=4000

Hi,

Thank you Mr. wang,

I have ran your query and I have got empty results for both of your queries.

Can't repeat.

Did you set setup_consumers like this:
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history_long';

Yes, we have .......