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:
None 
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
Description:
sql stuck in closing table last 48 days ,sql as below:

2683458065      xxxxxx   xxxxx:xxxxx    xxxxxx     4180037 20220831000041  Query   closing tables  update xxxxxx set xxxxx='xxxxxx',CalledNo='xxxxx' where xxxxxx='xxxxxxx'

pstack information as below:

Thread 1444 (Thread 0x2ad6657ff700 (LWP 216944)):
#0  0x000000396160f00d in nanosleep () from /lib64/libpthread.so.0
#1  0x0000000001000ad0 in os_thread_sleep(unsigned long) ()
#2  0x0000000000f96a9b in TrxInInnoDB::enter(trx_t*, bool) ()
#3  0x0000000000f837e1 in ha_innobase::update_thd(THD*) ()
#4  0x0000000000f8890a in ha_innobase::external_lock(THD*, int) ()
#5  0x0000000000819672 in handler::ha_external_lock(THD*, int) ()
#6  0x0000000000e4b065 in mysql_unlock_tables(THD*, st_mysql_lock*) ()
#7  0x0000000000cc598f in close_thread_tables(THD*) ()
#8  0x0000000000d17568 in mysql_execute_command(THD*, bool) ()
#9  0x0000000000d1c3fd in mysql_parse(THD*, Parser_state*) ()
#10 0x0000000000d1d625 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#11 0x0000000000d1e4d4 in do_command(THD*) ()
#12 0x0000000000defb14 in handle_connection ()
#13 0x000000000125d3f4 in pfs_spawn_thread ()
#14 0x0000003961607aa1 in start_thread () from /lib64/libpthread.so.0
#15 0x00000039612e8c4d in clone () from /lib64/libc.so.6

How to repeat:
can not repeat
[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.