Recently, there was a problem of automatic restart of MySQL database in the test environment. The reason was that kill-9 was forced to kill the database process. The error message is as follows:
2019-07-24T01:14:53.769512Z 0 [Note] Executing ‘SELECT * FROM INFORMATION_SCHEMA.TABLES;’ to get a list of tables using the deprecated partition engine. You may use the startup option ‘–disable-partition-engine-check’ to skip this check.
2019-07-24T01:14:53.769516Z 0 [Note] Beginning of list of non-natively partitioned tables
01:14:53 UTC – mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/key_buffer_size=33554432
read_buffer_size=8388608
max_used_connections=0
max_threads=501
thread_count=4
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4478400 K bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.Thread pointer: 0x7f486900e000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7f4846172820 thread_stack 0x80000
/usr/local/mysql5.7/bin/mysqld(my_print_stacktrace+0x2c)[0xed481c]
/usr/local/mysql5.7/bin/mysqld(handle_fatal_signal+0x461)[0x7a15a1]
/lib64/libpthread.so.0(+0xf7e0)[0x7f498697c7e0]
/usr/local/mysql5.7/bin/mysqld(_ZN12ha_federated7rnd_posEPhS0_+0x2f)[0x12bcc3f]
/usr/local/mysql5.7/bin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x172)[0x804a12]
/usr/local/mysql5.7/bin/mysqld(_ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x1e3)[0xe50e23]
/usr/local/mysql5.7/bin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x716)[0xe50196]
/usr/local/mysql5.7/bin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x6e)[0xe48fde]
/usr/local/mysql5.7/bin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x1f0)[0xe8d6f0]
/usr/local/mysql5.7/bin/mysqld(handle_slave_sql+0x163d)[0xe9a0fd]
/usr/local/mysql5.7/bin/mysqld(pfs_spawn_thread+0x1b4)[0x1209414]
/lib64/libpthread.so.0(+0x7aa1)[0x7f4986974aa1]
/lib64/libc.so.6(clone+0x6d)[0x7f4984c6bc4d]Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 2
Status: NOT_KILLEDYou may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
1. Preliminary exploration process
When there was a similar situation before, it was due to insufficient memory, because there was a corresponding prompt in the log:
key_buffer_size=33554432
read_buffer_size=8388608
max_used_connections=0
max_threads=501
thread_count=4
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4478400 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.The physical memory of this test environment is really small, and the remaining memory is insufficient. Moreover, as a slave Library of another test environment, the memory allocation is also small.
Similar situations have occurred in some environments before. After adjusting parameters and releasing memory, it can be started normally. So I try to close some temporary programs and adjust the values of the above parameters in mysql, such as:
[mysqld]
max_connections = 50Then restart mysql, the result is still restart.
Preliminary treatment failed.
2. Add InnoDB_ force_ Recovery solution: restart continuously
In the configuration file my.cnf Add InnoDB_ force_ Recovery first deals with the problem of constant restart
[mysqld]
innodb_force_recovery = 4After adding, start MySQL again, and there will be no repeated restart at this time.
Check the database log, there is a prompt [note] InnoDB:!! InnoDB_ force_ Recovery is set to 4
Because the database can be opened at this time, we tried to start the slave database, but an error was reported, and the prompt table ‘ mysql.slave_ relay_ log_ info’ is read only.
Now look at the error log as follows
Therefore, this time, InnoDB_ force_ Recovery is set to 4. After MySQL 5.6.15, when InnoDB_ force_ When the value of recovery is greater than or equal to 4, the InnoDB table is in read-only mode. Because the information needs to be written to the table when starting replication, an error is reported at this time.
Note: since it is still not effective when it is set to 1-3, the value I set to 4 (4 or above) during processing may permanently damage the data file. If there are similar problems in the production environment, make sure to copy a test first, and then deal with it in the production environment after passing the test). At this point, you can dump all the data and then restore it.
3. innodb_ force_ Recovery parameter
innodb_ force_ Recovery can be set to 1-6, and a large value contains all the effects of the previous values smaller than it.
1 (SRV_ FORCE_ IGNORE_ Corrupt: ignores the checked corrupt page. The service is forced to run even though a corrupt page is detected. Generally, it can be set to this value, and then the dump issue table is rebuilt.
2 (SRV_ FORCE_ NO_ Background: prevent the main thread from running. If the main thread needs to perform full purge operation, it will cause crash. Prevent master thread and any purge thread from running. This value is used if the crash occurs in the purge phase.
3 (SRV_ FORCE_ NO_ TRX_ Undo: does not perform the transaction rollback operation.
4 (SRV_ FORCE_ NO_ IBUF_ Merge: does not perform merge operation of insert buffer. Do not do this if it is possible to cause a crash. Do not perform statistical operations. This value may permanently corrupt the data file. If this value is used, the secondary index will be deleted and rebuilt in the future.
5 (SRV_ FORCE_ NO_ UNDO_ LOG_ Scan: if the redo log is not viewed, the InnoDB storage engine will treat uncommitted transactions as committed. At this point, InnoDB even processes the unfinished transaction as a commit. This value may permanently damage the data file.
6 (SRV_ FORCE_ NO_ LOG_ Redo: do not roll forward. No forward log roll. If the database page is disabled, it may cause more damage to the B-tree or other database structures.
be careful:
- For security, when the setting parameter value is greater than 0, you can select, create and drop the table, but insert, update or delete are not allowed.
- After MySQL 5.6.15, when InnoDB_ force_ When the value of recovery is greater than or equal to 4, the InnoDB table is in read-only mode.
- When the value is less than or equal to 3, you can dump the table through select, drop or create the table.
- Drop table is also supported for values greater than 3 after MySQL 5.6.27. If you know in advance which table caused the crash, you can drop the table.
- If you encounter runaway rollback caused by failed large-scale import or large number of alter table operations, you can kill mysqld thread and set InnoDB_ force_ Recovery = 3 makes the database restart without rollback. Then delete the table that causes runaway rollback; if the data in the table is damaged, the entire table content cannot be dumped. So order by primary_ The query of key desc clause may dump some data after the damaged part;
summary
The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developer.
source : https://developpaper.com/the-solution-of-mysql-auto-restart/