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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.7.29 | OS: | CentOS |
Assigned to: | CPU Architecture: | Any |
[10 Sep 2020 9:41]
feng wang
[22 Sep 2020 12:49]
MySQL Verification Team
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.
[27 Sep 2020 6:18]
feng wang
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
[27 Sep 2020 6:20]
feng wang
[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
[28 Sep 2020 13:28]
MySQL Verification Team
Hi, Thank you Mr. wang, I have ran your query and I have got empty results for both of your queries. Can't repeat.
[9 Oct 2020 1:48]
feng wang
Did you set setup_consumers like this: UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history_long';
[9 Oct 2020 12:22]
MySQL Verification Team
Yes, we have .......