Solution to MySQL restarting automatically

Preface

Recently, a problem occurred in the test environment where the MySQL database kept restarting automatically. The cause was that the database process was forcibly killed by kill -9. The error message was 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
The 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_KILLED

You 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. Initial exploration process

When a similar situation occurred before, it was because of insufficient memory, as 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 indeed not large, and the remaining memory is insufficient. Moreover, it is a slave library of another test environment, so the memory allocation is also small.

Similar situations have occurred in some environments before. After adjusting parameters and releasing memory, the system can start normally. So I tried to close some temporary programs and adjust the values ​​of the above parameters of MySQL, such as:

[mysqld]
max_connections = 50

Then restart MySQL, and it will continue to restart.

Initial treatment was unsuccessful.

2. Add innodb_force_recovery to solve the problem of continuous restart

Add innodb_force_recovery to the configuration file my.cnf to deal with the problem of continuous restarts first

[mysqld]
innodb_force_recovery = 4

After adding, start MySQL again, and the repeated restarts will no longer occur.

Check the database log, there is a prompt [Note] InnoDB: !!! innodb_force_recovery is set to 4 !!! as follows:

Because the database can be opened at this time, I try to start the slave library, but an error is reported, prompting Table 'mysql.slave_relay_log_info' is read only.

Now look at the error log, as follows

Therefore, during this startup, innodb_force_recovery is set to 4. After MySQL 5.6.15, when the value of innodb_force_recovery is greater than or equal to 4, the InnoDB table is in read-only mode. Because information needs to be written to the table when starting replication, an error is reported at this time.

Note: Since it still does not work when set to 1-3, I set it to 4 when processing (values ​​above 4 may permanently damage the data file. If a similar problem occurs in the production environment, be sure to copy a test first, and then process it in the production environment after the test passes). At this point you can dump all the data and restore it later.

3. innodb_force_recovery parameter

innodb_force_recovery can be set to 1-6, with larger values ​​including the effects of all previous values ​​smaller than it.

1 (SRV_FORCE_IGNORE_CORRUPT): Ignore detected corrupt pages. Force the service to run despite detection of corrupted pages. Generally, you can set it to this value, and then dump the table to rebuild it.

2 (SRV_FORCE_NO_BACKGROUND): Prevents the main thread from running. If the main thread needs to perform a full purge operation, it will cause a crash. Prevents the master thread and any purge threads from running. This value is used if the crash occurs during the purge phase.

3 (SRV_FORCE_NO_TRX_UNDO): Do not perform a transaction rollback.

4 (SRV_FORCE_NO_IBUF_MERGE): Do not perform an insert buffer merge. Don't do these operations if they might 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 need to be dropped and recreated in the future.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): Without looking in the redo log, the InnoDB storage engine treats uncommitted transactions as committed. At this time, InnoDB even treats unfinished transactions as committed. This value may permanently corrupt the data file.

6 (SRV_FORCE_NO_LOG_REDO): Do not perform a rollforward operation. No redo log roll-forward is performed during recovery. This puts database pages in an invalid state, which may cause further damage to B-trees or other database structures.

Notice:

1. For safety reasons, when the parameter value is set to greater than 0, select, create, and drop operations can be performed on the table, but insert, update, or delete operations are not allowed.
2. After MySQL 5.6.15, when the value of innodb_force_recovery is greater than or equal to 4, the InnoDB table is in read-only mode.
3. When the value is less than or equal to 3, you can dump the table by selecting, dropping or creating the table.
4. MySQL 5.6.27 and later also supports DROP TABLE for values ​​greater than 3; if you know in advance which table caused the crash, you can drop the table.
5. If you encounter a runaway rollback caused by a failed large-scale import or a large number of ALTER TABLE operations, you can kill the mysqld thread and set innodb_force_recovery = 3 to prevent the database from rolling back after restart. Then delete the table that caused the runaway rollback; if the data in the table is damaged, the entire table content cannot be dumped. Then a query with an order by primary_key desc clause might be able to dump some of the data after the damaged part;

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.