Bug #108343 | mysql 5.7.25 sql stuck in closing table | ||
---|---|---|---|
Submitted: | 31 Aug 2022 7:10 | Modified: | 2 Sep 2022 10:38 |
Reporter: | lei Liu | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.7.25 | OS: | Linux (CentOS release 6.6) |
Assigned to: | CPU Architecture: | x86 (Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz) |
[31 Aug 2022 7:10]
lei Liu
[31 Aug 2022 12:54]
MySQL Verification Team
Hi Mr. Liu, Thank you for your bug report. However .... Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php. In short, we need a totally repeatable test case, in the form of the set of SQL statements that always lead to the situation that you described. If you can provide more information, feel free to add it to this bug and, in the case that supplied information is sufficient, we shall change the status back to 'Open'. Thank you for your interest in MySQL.
[1 Sep 2022 3:27]
lei Liu
hi,one update sql is stucking in closing tables state for 4306389 seconds,we try to kill it ,but it doesnt work ,then it turn into killed state ,i think we encounter a mysql bug. show process list as below: *************************** 1. row *************************** ID: 2683458065 USER: [xxxx] HOST: [xxxx]:50729 DB: [xxxx] COMMAND: Killed TIME: 4306389 STATE: closing tables INFO: update [xxxx] set CallerNo=[xxxx],CalledNo=[xxxx] where SerialNo=[xxxx] 1 row in set (0.00 sec)
[1 Sep 2022 3:28]
lei Liu
pstack mysqld
Attachment: 11478_3.log (application/octet-stream, text), 1.07 MiB.
[1 Sep 2022 11:37]
MySQL Verification Team
Hi Mr. Liu, That behaviour is not a bug. You have not made settings for our server as well as you should. Like number of open tables, sizes of some caches as well as maximum allowable handles in your Linux. Can't repeat.
[2 Sep 2022 10:38]
lei Liu
hi,our table_open_cache variables is 20000,it is big enough ,all of cache variables as below +--------------------------------+------------+ | Variable_name | Value | +--------------------------------+------------+ | binlog_cache_size | 262144 | | binlog_stmt_cache_size | 32768 | | have_query_cache | YES | | host_cache_size | 1603 | | innodb_disable_sort_file_cache | OFF | | innodb_ft_cache_size | 8000000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_total_cache_size | 640000000 | | key_cache_age_threshold | 300 | | key_cache_block_size | 4096 | | key_cache_division_limit | 20 | | max_binlog_cache_size | 4294967296 | | max_binlog_stmt_cache_size | 268435456 | | metadata_locks_cache_size | 1024 | | query_cache_limit | 0 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | stored_program_cache | 256 | | table_definition_cache | 10000 | | table_open_cache | 20000 | | table_open_cache_instances | 16 | | thread_cache_size | 512 | +--------------------------------+------------+ our database is not busy,qps 400/s,cpu 10%,os server is the same. os parameter fs.file-max is 6815744 ,i think is big enough. ,i dont think so that there some settings not properly for our server
[2 Sep 2022 11:28]
MySQL Verification Team
Hi Mr. Liu, This is a forum for the bug reports, that include totally repeatable test cases in the form of the batch of SQL statements. This is not a support forum.