Troubleshooting : Accidentally Shutdown MySQL 5.7 Once a Month

Infrastructure,MySQLMySQL,Troubleshooting

Problem: MySQL 5.7 is down.

MySQL server is shutdown unexpectedly once a month. It happened when we updated MySQL to 5.7.24. In the /var/mysql.log, the following logs are shown. What is wrong?

2020-07-09T05:54:41.056589Z 0 [Note] Giving 0 client threads a chance to die gracefully
2020-07-09T05:54:41.056621Z 0 [Note] Shutting down slave threads
2020-07-09T05:54:41.058220Z 0 [Note] Forcefully disconnecting 0 remaining clients
2020-07-09T05:54:41.059854Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2020-07-09T05:54:41.087530Z 0 [Note] Binlog end
2020-07-09T05:54:41.274087Z 0 [Note] Shutting down plugin 'ngram'
2020-07-09T05:54:41.275539Z 0 [Note] Shutting down plugin 'partition'
2020-07-09T05:54:41.276189Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2020-07-09T05:54:41.276200Z 0 [Note] Shutting down plugin 'ARCHIVE'
2020-07-09T05:54:41.276204Z 0 [Note] Shutting down plugin 'CSV'
2020-07-09T05:54:41.277020Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-07-09T05:54:41.281866Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2020-07-09T05:54:41.281878Z 0 [Note] Shutting down plugin 'MEMORY'
2020-07-09T05:54:41.282858Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2020-07-09T05:54:41.284104Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-07-09T05:54:41.284115Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-07-09T05:54:41.284119Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-07-09T05:54:41.284122Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-07-09T05:54:41.284125Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-07-09T05:54:41.284128Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-07-09T05:54:41.284621Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-07-09T05:54:41.284627Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-07-09T05:54:41.284630Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-07-09T05:54:41.284633Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-07-09T05:54:41.284636Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-07-09T05:54:41.284639Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-07-09T05:54:41.284642Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-07-09T05:54:41.284645Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-07-09T05:54:41.284648Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-07-09T05:54:41.284651Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2020-07-09T05:54:41.284654Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2020-07-09T05:54:41.284657Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-07-09T05:54:41.284660Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-07-09T05:54:41.284663Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-07-09T05:54:41.284666Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-07-09T05:54:41.284668Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-07-09T05:54:41.284671Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-07-09T05:54:41.284674Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-07-09T05:54:41.284677Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-07-09T05:54:41.284680Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2020-07-09T05:54:41.284683Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-07-09T05:54:41.284686Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-07-09T05:54:41.284689Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2020-07-09T05:54:41.284692Z 0 [Note] Shutting down plugin 'InnoDB'
2020-07-09T05:54:41.295977Z 0 [Note] InnoDB: FTS optimize thread exiting.
2020-07-09T05:54:41.298858Z 0 [Note] InnoDB: Starting shutdown...
2020-07-09T05:54:41.416427Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool

Solution

According to the following 2 bug reports, this problem is fixed at version 5.7.31 , 8.0.21.

Also, as a workaround, the following setting is introduced.

[mysqld]
internal_tmp_disk_storage_engine=MYISAM

The parameter can be changed dynamically with the following MySQL command.

mysql> SET GLOBAL internal_tmp_disk_storage_engine=MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%internal_tmp%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| internal_tmp_disk_storage_engine | MyISAM |
+----------------------------------+--------+
1 row in set (0.00 sec)

Infrastructure,MySQLMySQL,Troubleshooting

Posted by Kei