mysql Ver 8.0.20-0ubuntu0.19.10.1 for Linux on armv7l ((Ubuntu))
MySQL 不断崩溃。恐怕数据库已损坏。
它在一个简单的查询后崩溃。
mysql> select count(*) from all_values;
ERROR 2013 (HY000): Lost connection to MySQL server during query
系统日志:
ubuntu kernel: [240423.997644] audit: type=1400 audit(1707221926.282:9988): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/19377/task/19441/mem" pid=19377 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=111 ouid=111
ubuntu systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
ubuntu systemd[1]: mysql.service: Failed with result 'exit-code'.
ubuntu systemd[1]: mysql.service: Service RestartSec=100ms expired, scheduling restart.
ubuntu systemd[1]: mysql.service: Scheduled restart job, restart counter is at 1.
ubuntu systemd[1]: Stopped MySQL Community Server.
mysql/错误日志:
2024-02-06T12:18:46.282748Z 8 [ERROR] [MY-012153] [InnoDB] Trying to access page number 16964 in space 11, space name tsl/all_values, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2024-02-06T12:18:46.282848Z 8 [ERROR] [MY-012154] [InnoDB] Server exits.
2024-02-06T12:18:46.282890Z 8 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:7192 thread 463119312
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
12:18:46 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0xeb04900
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 = 1b9a9d68 thread_stack 0x36000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2d) [0x1a93e7a]
/usr/sbin/mysqld(handle_fatal_signal+0x1c5) [0xfdb706]
/lib/arm-linux-gnueabihf/libc.so.6(+0x2aaa0) [0xb6972aa0]
/lib/arm-linux-gnueabihf/libc.so.6(+0x1ad56) [0xb6962d56]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (b997900): is an invalid pointer
Connection ID (thread ID): 8
Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
当尝试备份我的数据库时,它失败了:
$ mysqldump -u root -p tsl > tsl-bu4.sql
Enter password:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `all_values` at row: 3835117
这是一致的,每次都在第 3835117 行失败。
当前配置:
[mysqld]
log_error = /var/log/mysql/error.log
general_log = on
general_log_file=/var/log/mysql/general.log
innodb_force_recovery = 0
innodb_buffer_pool_size=1G
我已经尝试了将 innodb_force_recovery 设置为所有值(1-6)的所有操作,并且还尝试了 innodb_buffer_pool_size 的各种值,但没有任何区别。
问题:
我认为这意味着我的数据库已损坏,这个假设正确吗?
由于它似乎每次都在同一行失败,这是否意味着我已经识别出损坏的行?如果是这样,有没有办法只删除这一行(不使用 SQL 查询,这显然会使 MySQL 崩溃)?
提前致谢。是的,从现在开始我将开始定期备份我的数据库。
编辑:请求的信息:
mysql> SHOW CREATE TABLE all_values;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| all_values | CREATE TABLE `all_values` (
`sensor_id` int DEFAULT NULL,
`value` double DEFAULT NULL,
`timestamp` int DEFAULT NULL,
KEY `sensor_id` (`sensor_id`,`timestamp`),
KEY `value_index` (`value`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS WHERE name LIKE "all_values";
+------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| all_values | InnoDB | 10 | Dynamic | 41503787 | 44 | 1866465280 | 0 | 2297348096 | 0 | NULL | 2021-12-13 08:12:06 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
+------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.01 sec)
我设法解决了我的问题。
一切看起来都不错。数据库中没有丢失任何数据。我仍然不知道是什么导致了最初的问题。新系统上的版本是8.0.36(原系统上是8.0.20)