MySQL keeps crashing, corrupt DB?

Question

mysql Ver 8.0.20-0ubuntu0.19.10.1 for Linux on armv7l ((Ubuntu))

MySQL keeps crashing. I'm afraid the DB has been corrupted.

It crashes after a simple-ish query.

mysql> select count(*) from all_values;
ERROR 2013 (HY000): Lost connection to MySQL server during query

syslog:

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/error.log:

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.

When trying to backup my db, it fails:

$ 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

This is consistent, it fails at row 3835117 every time.

Current conf:

[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

I have tried everything with innodb_force_recovery set to all values (1-6), and also tried various values for innodb_buffer_pool_size, but no difference what so ever.

Questions:

I assume this means my DB is somehow corrupted, is this assumption correct?

Since it seems to fail at the same row every time, does this mean I have identified the row which is corrupt? If so, is there a way to remove this row only (without using a SQL query which will obviously make MySQL crash)?

Thanks in advance. And yes, I'll start backing up my DB regularly from now on..

Edit: Requested info:

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)

Answer 1

I managed to work around my issue.

1. Back up database by copying files from original system
2. Restore database files on a new system - all good
3. Backing up database on the new system (using mysqldump)
4. Dropping table in question on original system, restore backup from step 3.

All seems good. No data is missing from the database. I still don't know what caused the original issue though. Version on new system was 8.0.36 (8.0.20 on original system)