[toc]

Do Not Upgrade to Any Version of MySQL After 8.0.37
https://www.percona.com/blog/do-not-upgrade-to-any-version-of-mysql-after-8-0-37/
最近都在传 谨慎升级到 MySQL 8.0.37 之后的版本,MySQL 8.0.38/8.4.1/9.0.0 数据库超过 10000个表之后,数据库实例重启之后,无法正常启动。
本篇文章测试验证 MySQL 8.0.38 表超过 10000 个之后的相关测试及解决方案

本次进行如下测试:

  1. MySQL 8.0.38 创建 10000 个表进行重启测试
  2. MySQL 8.0.37 创建 10000 个表进行重启测试
  3. MySQL 8.0.37 创建 10000 个表 然后升级到 MySQL 8.0.38 启动测试
  4. MySQL 8.0.38 设置共享表空间后创建 10000个表进行重启测试
  5. 设置 innodb_validate_tablespace_paths 参数为off,创建 10000个表进行重启测试
  6. 创建 10000个表后 crash 的 MySQL 8.0.38 降级到 8.0.37 测试

测试验证

MySQL 8.0.38 创建 10000 个表进行重启测试

1
2
3
4
5
6
7
8
9
yum install cmake make gcc gcc-c++ bison libaio ncurses-devel perl perl-DBI perl-DBD-MySQL perl-Time-HiRes readline-devel numactl zlib-devel -y
cd /data1/opt/software/
tar -xvf mysql-8.0.38-linux-glibc2.17-x86_64.tar.xz
ln -s mysql-8.0.38-linux-glibc2.17-x86_64 /usr/local/mysql
mkdir -p /data/opt/mysql_8038
mkdir -p /data/opt/mysql_8038/data
mkdir -p /data/opt/mysql_8038/log
mkdir -p /data/opt/mysql_8038/tmp
chown -R mysql:mysql /usr/local/mysql /data/opt/mysql_*

参数文件参考如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
[root@liups:/data/opt/mysql_8038/log]# cat /etc/my_8038.cnf
[mysql]
prompt="\u@db \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user = mysql
port = 8038
basedir = /usr/local/mysql
datadir = /data/opt/mysql_8038/data
socket = /data/opt/mysql_8038/mysql.sock
pid-file = /data/opt/mysql_8038/mysql.pid
character-set-server=utf8mb4
collation-server = utf8mb4_general_ci
sql_mode='NO_UNSIGNED_SUBTRACTION,NO_ENGINE_SUBSTITUTION'
open_files_limit = 65535
innodb_open_files = 65535
back_log=1024
max_connections = 512
max_connect_errors=1000000
interactive_timeout=300
wait_timeout=300
max_allowed_packet = 1024M
secure_file_priv=''
log-error=/data/opt/mysql_8038/log/error.log
slow_query_log=ON
slow_query_log_file=/data/opt/mysql_8038/log/slow_mysql.log
long_query_time=2
innodb_flush_log_at_trx_commit=1
innodb_log_file_size =1G
innodb_log_files_in_group=3
innodb_log_group_home_dir=./
log-bin-trust-function-creators=1
sync_binlog = 1
binlog_cache_size = 16M
max_binlog_cache_size = 1G
max_binlog_size=1G
log-bin= /data/opt/mysql_8038/log/binlog-mysql
binlog_row_image=full
server-id = 1
default_authentication_plugin =mysql_native_password

innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=1

初始化并启动数据库

1
2
3
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my_8038.cnf  --initialize
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf &

登录修改密码,确认版本

1
2
3
/usr/local/mysql/bin/mysql -uroot -p --socket=/data/opt/mysql_8038/mysql.sock
mysql> alter user root@localhost identified by "Password123";
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 /usr/local/mysql/bin/mysql -uroot -p --socket=/data/opt/mysql_8038/mysql.sock
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.38 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)

创建存储过程,用来创建10000个表,每个表插入1条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

DELIMITER //

CREATE PROCEDURE create_tables_and_insert()
BEGIN
DECLARE i INT DEFAULT 1;

WHILE i <= 10000 DO
SET @sql = CONCAT('CREATE TABLE t_', i, ' (
`id` int NOT NULL,
PRIMARY KEY (`id`)
)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT('INSERT INTO t_', i, ' SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET i = i + 1;
END WHILE;
END //

DELIMITER ;

CALL create_tables_and_insert();


mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE create_tables_and_insert()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
->
-> WHILE i <= 10000 DO
-> SET @sql = CONCAT('CREATE TABLE t_', i, ' (
'> `id` int NOT NULL,
'> PRIMARY KEY (`id`)
'> )');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
->
-> SET @sql = CONCAT('INSERT INTO t_', i, ' SELECT 1');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
->
-> SET i = i + 1;
-> END WHILE;
-> END //
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> DELIMITER ;
mysql> CALL create_tables_and_insert();
Query OK, 0 rows affected (8 min 6.38 sec)

mysql>
image-20240715171726563
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show processlist;
+----+-----------------+-----------+-----------+---------+------+------------------------+-------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+-----------+---------+------+------------------------+-------------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 329 | Waiting on empty queue | NULL |
| 9 | root | localhost | crashtest | Query | 0 | creating table | CREATE TABLE t_3211 (
`id` int NOT NULL,
PRIMARY KEY (`id`)
) |
| 10 | root | localhost | crashtest | Query | 0 | init | show processlist |
+----+-----------------+-----------+-----------+---------+------+------------------------+-------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
mysql> select count(*) from information_schema.tables where TABLE_SCHEMA='crashtest';
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.06 sec)

mysql>

重启 MySQL,发生 crash

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
2024-07-15T09:23:03.000000Z 0 [System] [MY-013951] [Server] 2024-07-15T09:23:03Z UTC - mysqld got signal 11 ;
2024-07-15T09:23:03.000001Z 0 [System] [MY-013951] [Server] Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
2024-07-15T09:23:03.000002Z 0 [System] [MY-013951] [Server] BuildID[sha1]=96a3ee772d1d213bc36262ae5f5c23c7b9f61b30
2024-07-15T09:23:03.000003Z 0 [System] [MY-013951] [Server] Thread pointer: 0x0
2024-07-15T09:23:03.000004Z 0 [System] [MY-013951] [Server] Attempting backtrace. You can use the following information to find out
2024-07-15T09:23:03.000005Z 0 [System] [MY-013951] [Server] where mysqld died. If you see no messages after this, something went
2024-07-15T09:23:03.000006Z 0 [System] [MY-013951] [Server] terribly wrong...
2024-07-15T09:23:03.000007Z 0 [System] [MY-013951] [Server] stack_bottom = 0 thread_stack 0x100000
2024-07-15T09:23:03.000008Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x212497d]
2024-07-15T09:23:03.000009Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(print_fatal_signal(int)+0x37f) [0xfe3fff]
2024-07-15T09:23:03.000010Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(handle_fatal_signal+0xa5) [0xfe40b5]
2024-07-15T09:23:03.000011Z 0 [System] [MY-013951] [Server] /lib64/libpthread.so.0(+0xf630) [0x7f36ab733630]
2024-07-15T09:23:03.000012Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(Validate_files::check(__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)+0x8ae) [0x2170c8e]
2024-07-15T09:23:03.000013Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, std::function<void (__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)>, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespa/usr/local/mysql/bin/mysqld() [0x2ae5974]
2024-07-15T09:23:03.000014Z 0 [System] [MY-013951] [Server] /lib64/libpthread.so.0(+0x7ea5) [0x7f36ab72bea5]
2024-07-15T09:23:03.000015Z 0 [System] [MY-013951] [Server] /lib64/libc.so.6(clone+0x6d) [0x7f36a9b40b0d]
2024-07-15T09:23:03.000016Z 0 [System] [MY-013951] [Server] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
2024-07-15T09:23:03.000017Z 0 [System] [MY-013951] [Server] information that should help you find out what is causing the crash.
2024-07-15T09:23:03.000018Z 0 [System] [MY-013951] [Server] =======
2024-07-15T09:24:43.538037Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-07-15T09:24:43.538080Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-07-15T09:24:43.538097Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.38) starting as process 29227
2024-07-15T09:24:43.541258Z 0 [System] [MY-013951] [Server] A backtrace was processed and added to the main error-log in the appropriate format.
2024-07-15T09:24:43.543515Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=3221225472. Please use innodb_redo_log_capacity instead.
2024-07-15T09:24:43.545635Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-15T09:24:51.470088Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-15T09:24:51Z UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=96a3ee772d1d213bc36262ae5f5c23c7b9f61b30
Thread pointer: 0x0
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 = 0 thread_stack 0x100000
/usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x212497d]
/usr/local/mysql/bin/mysqld(print_fatal_signal(int)+0x37f) [0xfe3fff]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0xa5) [0xfe40b5]
/lib64/libpthread.so.0(+0xf630) [0x7f81611c9630]
/usr/local/mysql/bin/mysqld(Validate_files::check(__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)+0x8ae) [0x2170c8e]
/usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, std::function<void (__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)>, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespa/usr/local/mysql/bin/mysqld() [0x2ae5974]
/lib64/libpthread.so.0(+0x7ea5) [0x7f81611c1ea5]
/lib64/libc.so.6(clone+0x6d) [0x7f815f5d6b0d]
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.

MySQL 8.0.37 创建 10000 个表进行重启测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mkdir -p /data/opt/mysql_8037
mkdir -p /data/opt/mysql_8037/data
mkdir -p /data/opt/mysql_8037/log
mkdir -p /data/opt/mysql_8037/tmp

cd /data1/opt/software/
tar -xvf mysql-8.0.37-linux-glibc2.17-x86_64.tar.xz -C /usr/local/
ln -s mysql-8.0.37-linux-glibc2.17-x86_64 /usr/local/mysql8037
chown -R mysql:mysql /usr/local/mysql8037 /data/opt/mysql_8037/
/usr/local/mysql8037/bin/mysqld --defaults-file=/etc/my_8037.cnf --initialize
/usr/local/mysql8037/bin/mysqld_safe --defaults-file=/etc/my_8037.cnf &
cat error.log |grep -i password
2024-07-15T09:39:24.689800Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: OkTIjdIQq7%B
/usr/local/mysql8037/bin/mysql -uroot -p --socket=/data/opt/mysql_8037/mysql.sock

登录修改密码,确认版本

1
2
mysql>  alter user root@localhost  identified by "Password123";
Query OK, 0 rows affected (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.37 |
+-----------+
1 row in set (0.03 sec)

mysql> show variables like '%per_tabl%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.03 sec)

创建存储过程,用来创建10000个表,每个表插入1条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

mysql>
mysql> use m8037t1w;
Database changed
mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE create_tables_and_insert()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
->
-> WHILE i <= 10000 DO
-> SET @sql = CONCAT('CREATE TABLE t_', i, ' (
'> `id` int NOT NULL,
'> PRIMARY KEY (`id`)
'> )');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
->
-> SET @sql = CONCAT('INSERT INTO t_', i, ' SELECT 1');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
->
-> SET i = i + 1;
-> END WHILE;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql> CALL create_tables_and_insert();

Query OK, 0 rows affected (7 min 37.74 sec)
mysql> select count(*) from information_schema.tables where TABLE_SCHEMA='m8037t1w';
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.05 sec)

mysql>

重启 MySQL,数据库正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
killall mysqld
[root@liups:/data/opt/mysql_8037/log]# ps -ef |grep m2024-07-15T09:50:59.385755Z mysqld_safe mysqld from pid file /data/opt/mysql_8037/mysql.pid ended
ysql
root 1861 6336 0 17:50 pts/2 00:00:00 grep --color=auto mysql
[1]+ Done /usr/local/mysql8037/bin/mysqld_safe --defaults-file=/etc/my_8037.cnf (wd: /data/opt/mysql_8037/tmp)
(wd now: /data/opt/mysql_8037/log)
[root@liups:/data/opt/mysql_8037/log]#
[root@liups:/data/opt/mysql_8037/log]#
[root@liups:/data/opt/mysql_8037/log]# killall mysqld
mysqld: no process found
[root@liups:/data/opt/mysql_8037/log]# ps -ef |grep mysql
root 1941 6336 0 17:51 pts/2 00:00:00 grep --color=auto mysql
[root@liups:/data/opt/mysql_8037/log]# /usr/local/mysql8037/bin/mysqld_safe --defaults-file=/etc/my_8037.cnf &
[1] 2056
[root@liups:/data/opt/mysql_8037/log]# 2024-07-15T09:51:17.710034Z mysqld_safe Logging to '/data/opt/mysql_8037/log/error.log'.
2024-07-15T09:51:17.732459Z mysqld_safe Starting mysqld daemon with databases from /data/opt/mysql_8037/data

[root@liups:/data/opt/mysql_8037/log]#
[root@liups:/data/opt/mysql_8037/log]#
[root@liups:/data/opt/mysql_8037/log]#
[root@liups:/data/opt/mysql_8037/log]# ps -ef |grep mysqld
root 2056 6336 0 17:51 pts/2 00:00:00 /bin/sh /usr/local/mysql8037/bin/mysqld_safe --defaults-file=/etc/my_8037.cnf
mysql 2593 2056 9 17:51 pts/2 00:00:01 /usr/local/mysql8037/bin/mysqld --defaults-file=/etc/my_8037.cnf --basedir=/usr/local/mysql --datadir=/data/opt/mysql_8037/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/opt/mysql_8037/log/error.log --open-files-limit=65535 --pid-file=/data/opt/mysql_8037/mysql.pid --socket=/data/opt/mysql_8037/mysql.sock --port=8037
root 2876 6336 0 17:51 pts/2 00:00:00 grep --color=auto mysqld
[root@liups:/data/opt/mysql_8037/log]# /usr/local/mysql8037/bin/mysql -uroot -p --socket=/data/opt/mysql_8037/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| m8037t1w |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)

mysql> select count(*) from information_schema.tables where TABLE_SCHEMA='m8037t1w';
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.05 sec)

mysql>

日志:

1
2
3
4
5
6
7
8
9
10
11
12
13
2024-07-15T09:50:52.689640Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.37).
2024-07-15T09:50:59.265025Z 0 [System] [MY-010910] [Server] /usr/local/mysql8037/bin/mysqld: Shutdown complete (mysqld 8.0.37) MySQL Community Server - GPL.
2024-07-15T09:51:17.954258Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-07-15T09:51:17.954302Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-07-15T09:51:17.954319Z 0 [System] [MY-010116] [Server] /usr/local/mysql8037/bin/mysqld (mysqld 8.0.37) starting as process 2593
2024-07-15T09:51:17.965485Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=3221225472. Please use innodb_redo_log_capacity instead.
2024-07-15T09:51:17.973170Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-15T09:51:23.917646Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-15T09:51:24.720539Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-07-15T09:51:24.720594Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-07-15T09:51:24.760067Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-07-15T09:51:24.760133Z 0 [System] [MY-010931] [Server] /usr/local/mysql8037/bin/mysqld: ready for connections. Version: '8.0.37' socket: '/data/opt/mysql_8037/mysql.sock' port: 8037 MySQL Community Server - GPL.
2024-07-15T09:51:48.234027Z 8 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

MySQL 8.0.37 创建 10000 个表 然后升级到 MySQL 8.0.38 启动测试

用 MySQL 8.0.38 的软件启动 MySQL 8.0.37 ,无法正常启动,升级失败。

1
2
3
4
5
6
7
8
9
10
 /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8037.cnf &
[1] 3545
[root@liups:/usr/local]# 2024-07-15T10:03:17.697965Z mysqld_safe Logging to '/data/opt/mysql_8037/log/error.log'.
2024-07-15T10:03:17.717718Z mysqld_safe Starting mysqld daemon with databases from /data/opt/mysql_8037/data

[root@liups:/usr/local]#
[root@liups:/usr/local]#
[root@liups:/usr/local]# 2024-07-15T10:03:24.561220Z mysqld_safe mysqld from pid file /data/opt/mysql_8037/mysql.pid ended

[1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8037.cnf

查看日志如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2024-07-15T10:01:33.393697Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.37).
2024-07-15T10:01:34.218636Z 0 [System] [MY-010910] [Server] /usr/local/mysql8037/bin/mysqld: Shutdown complete (mysqld 8.0.37) MySQL Community Server - GPL.
2024-07-15T10:03:18.004612Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-07-15T10:03:18.004673Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-07-15T10:03:18.004692Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.38) starting as process 4072
2024-07-15T10:03:18.014338Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=3221225472. Please use innodb_redo_log_capacity instead.
2024-07-15T10:03:18.020079Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-15T10:03:23.975962Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-15T10:03:24Z UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=96a3ee772d1d213bc36262ae5f5c23c7b9f61b30
Thread pointer: 0x0
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 = 0 thread_stack 0x100000
/usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x212497d]
/usr/local/mysql/bin/mysqld(print_fatal_signal(int)+0x37f) [0xfe3fff]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0xa5) [0xfe40b5]
/lib64/libpthread.so.0(+0xf630) [0x7f380add6630]
/usr/local/mysql/bin/mysqld(Validate_files::check(__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)+0x8ae) [0x2170c8e]
/usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, std::function<void (__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)>, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespa/usr/local/mysql/bin/mysqld() [0x2ae5974]
/lib64/libpthread.so.0(+0x7ea5) [0x7f380adceea5]
/lib64/libc.so.6(clone+0x6d) [0x7f38091e3b0d]
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.

MySQL 8.0.38 设置共享表空间后创建 10000个表进行重启测试

参数文件设置: innodb_file_per_table = 0

清理之前 8.0.38 的数据目录,重新初始化,并创建 10000个表,重启测试

1
2
3
4
rm -rf  /data/opt/mysql_8038/data/*
rm -rf /data/opt/mysql_8038/log/*
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my_8038.cnf --initialize
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf &
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.38 |
+-----------+
1 row in set (0.00 sec)


mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> create database m8038pertoff;
Query OK, 1 row affected (0.01 sec)

mysql> use m8038pertoff;
Database changed
mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE create_tables_and_insert()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
->
-> WHILE i <= 10000 DO
-> SET @sql = CONCAT('CREATE TABLE t_', i, ' (
'> `id` int NOT NULL,
'> PRIMARY KEY (`id`)
'> )');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
->
-> SET @sql = CONCAT('INSERT INTO t_', i, ' SELECT 1');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
->
-> SET i = i + 1;
-> END WHILE;
-> END //
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> DELIMITER ;
mysql> CALL create_tables_and_insert();
Query OK, 0 rows affected (5 min 24.84 sec)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from information_schema.tables where TABLE_SCHEMA='m8038pertoff';
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.04 sec)

mysql>

重启数据库,正常启动。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
[root@liups:/data/opt/mysql_8038/log]# killall mysqld
[root@liups:/data/opt/mysql_8038/log]# killall mysqld2024-07-15T10:30:34.889057Z mysqld_safe mysqld from pid file /data/opt/mysql_8038/mysql.pid ended
ps -ef |grep mysqld
root 3250 4310 0 18:30 pts/0 00:00:00 grep --color=auto mysqld
[1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf
[root@liups:/data/opt/mysql_8038/log]# ps -ef |grep mysqld
root 4096 4310 0 18:30 pts/0 00:00:00 grep --color=auto mysqld
[root@liups:/data/opt/mysql_8038/log]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf &
[1] 8256
[root@liups:/data/opt/mysql_8038/log]# 2024-07-15T10:30:49.487879Z mysqld_safe Logging to '/data/opt/mysql_8038/log/error.log'.
2024-07-15T10:30:49.511707Z mysqld_safe Starting mysqld daemon with databases from /data/opt/mysql_8038/data

[root@liups:/data/opt/mysql_8038/log]#
[root@liups:/data/opt/mysql_8038/log]#
[root@liups:/data/opt/mysql_8038/log]# ps -ef |grep mysqld
root 8256 4310 0 18:30 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf
mysql 8915 8256 14 18:30 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my_8038.cnf --basedir=/usr/local/mysql --datadir=/data/opt/mysql_8038/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/opt/mysql_8038/log/error.log --open-files-limit=65535 --pid-file=/data/opt/mysql_8038/mysql.pid --socket=/data/opt/mysql_8038/mysql.sock --port=8038
root 10689 4310 0 18:30 pts/0 00:00:00 grep --color=auto mysqld
[root@liups:/data/opt/mysql_8038/log]# /usr/local/mysql/bin/mysql -uroot -p --socket=/data/opt/mysql_8038/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.38 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from information_schema.tables where TABLE_SCHEMA='m8038pertoff';
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.04 sec)

或者设置通用表空间。

1
2
3
 通用表空间方案
> CREATE TABLESPACE test ADD DATAFILE 'test.ibd';
> CREATE TABLE t_1(...) TABLESPACE=test;

设置 innodb_validate_tablespace_paths 参数为off,创建 10000个表进行重启测试

参数文件设置: innodb_validate_tablespace_paths = off

清理之前 8.0.38 的数据目录,重新初始化。

1
2
3
4
rm -rf  /data/opt/mysql_8038/data/*
rm -rf /data/opt/mysql_8038/log/*
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my_8038.cnf --initialize
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf &
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

mysql> show variables like 'innodb_validate_tablespace_paths';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| innodb_validate_tablespace_paths | OFF |
+----------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%per_tab%'
-> ;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> create database tbsoff;
Query OK, 1 row affected (0.00 sec)

mysql> use tbsoff;
Database changed
mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE create_tables_and_insert()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
->
-> WHILE i <= 10000 DO
-> SET @sql = CONCAT('CREATE TABLE t_', i, ' (
'> `id` int NOT NULL,
'> PRIMARY KEY (`id`)
'> )');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
->
-> SET @sql = CONCAT('INSERT INTO t_', i, ' SELECT 1');
-> PREPARE stmt FROM @sql;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
->
-> SET i = i + 1;
-> END WHILE;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql> CALL create_tables_and_insert();

Query OK, 0 rows affected (7 min 50.46 sec)

mysql>
mysql> select count(*) from information_schema.tables where TABLE_SCHEMA='tbsoff';
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.05 sec)

mysql> exit
Bye

[root@liups:/data/opt/mysql_8038/log]# killall mysqld
[root@liups:/data/opt/mysql_8038/log]# ps -ef |grep mysqld
root 16015 4310 0 18:57 pts/0 00:00:00 grep --color=auto mysqld
root 21161 4310 0 18:46 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf
mysql 21752 21161 11 18:46 pts/0 00:01:16 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my_8038.cnf --basedir=/usr/local/mysql --datadir=/data/opt/mysql_8038/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/opt/mysql_8038/log/error.log --open-files-limit=65535 --pid-file=/data/opt/mysql_8038/mysql.pid --socket=/data/opt/mysql_8038/mysql.sock --port=8038
[root@liups:/data/opt/mysql_8038/log]# ps -ef |grep mysqld
root 16053 4310 0 18:57 pts/0 00:00:00 grep --color=auto mysqld
root 21161 4310 0 18:46 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf
mysql 21752 21161 11 18:46 pts/0 00:01:16 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my_8038.cnf --basedir=/usr/local/mysql --datadir=/data/opt/mysql_8038/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/opt/mysql_8038/log/error.log --open-files-limit=65535 --pid-file=/data/opt/mysql_8038/mysql.pid --socket=/data/opt/mysql_8038/mysql.sock --port=8038
[root@liups:/data/opt/mysql_8038/log]# killall mysqld
[root@liups:/data/opt/mysql_8038/log]# killall mysqld
[root@liups:/data/opt/mysql_8038/log]# killall mysqld
[root@liups:/data/opt/mysql_8038/log]# 2024-07-15T10:57:39.649371Z mysqld_safe mysqld from pid file /data/opt/mysql_8038/mysql.pid ended

[1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf
[root@liups:/data/opt/mysql_8038/log]#
[root@liups:/data/opt/mysql_8038/log]# ps -ef |grep mysqld
root 16206 4310 0 18:57 pts/0 00:00:00 grep --color=auto mysqld
[root@liups:/data/opt/mysql_8038/log]# ps -ef |grep mysqld
root 17062 4310 0 18:58 pts/0 00:00:00 grep --color=auto mysqld
[root@liups:/data/opt/mysql_8038/log]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf &
[1] 17456
[root@liups:/data/opt/mysql_8038/log]# 2024-07-15T10:58:45.532197Z mysqld_safe Logging to '/data/opt/mysql_8038/log/error.log'.
2024-07-15T10:58:45.550952Z mysqld_safe Starting mysqld daemon with databases from /data/opt/mysql_8038/data
2024-07-15T10:58:52.831574Z mysqld_safe mysqld from pid file /data/opt/mysql_8038/mysql.pid ended

[1]+ Done /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf

重启之后,发生crash,日志如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
----------
2024-07-15T10:58:06.405707Z 0 [System] [MY-013951] [Server] 2024-07-15T10:58:06Z UTC - mysqld got signal 11 ;
2024-07-15T10:58:06.405708Z 0 [System] [MY-013951] [Server] Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
2024-07-15T10:58:06.405709Z 0 [System] [MY-013951] [Server] BuildID[sha1]=96a3ee772d1d213bc36262ae5f5c23c7b9f61b30
2024-07-15T10:58:06.405710Z 0 [System] [MY-013951] [Server] Thread pointer: 0x0
2024-07-15T10:58:06.405711Z 0 [System] [MY-013951] [Server] Attempting backtrace. You can use the following information to find out
2024-07-15T10:58:06.405712Z 0 [System] [MY-013951] [Server] where mysqld died. If you see no messages after this, something went
2024-07-15T10:58:06.405713Z 0 [System] [MY-013951] [Server] terribly wrong...
2024-07-15T10:58:06.405714Z 0 [System] [MY-013951] [Server] stack_bottom = 0 thread_stack 0x100000
2024-07-15T10:58:06.405715Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x212497d]
2024-07-15T10:58:06.405716Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(print_fatal_signal(int)+0x37f) [0xfe3fff]
2024-07-15T10:58:06.405717Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(handle_fatal_signal+0xa5) [0xfe40b5]
2024-07-15T10:58:06.405718Z 0 [System] [MY-013951] [Server] /lib64/libpthread.so.0(+0xf630) [0x7f4875280630]
2024-07-15T10:58:06.405719Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(Validate_files::check(__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)+0x8ae) [0x2170c8e]
2024-07-15T10:58:06.405720Z 0 [System] [MY-013951] [Server] /usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, std::function<void (__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)>, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespa/usr/local/mysql/bin/mysqld() [0x2ae5974]
2024-07-15T10:58:06.405721Z 0 [System] [MY-013951] [Server] /lib64/libpthread.so.0(+0x7ea5) [0x7f4875278ea5]
2024-07-15T10:58:06.405722Z 0 [System] [MY-013951] [Server] /lib64/libc.so.6(clone+0x6d) [0x7f487368db0d]
2024-07-15T10:58:06.405723Z 0 [System] [MY-013951] [Server] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
2024-07-15T10:58:06.405724Z 0 [System] [MY-013951] [Server] information that should help you find out what is causing the crash.
2024-07-15T10:58:06.405725Z 0 [System] [MY-013951] [Server] ----------
2024-07-15T10:58:45.764356Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2024-07-15T10:58:45.764400Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2024-07-15T10:58:45.764417Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.38) starting as process 17990
2024-07-15T10:58:45.769297Z 0 [System] [MY-013951] [Server] A backtrace was processed and added to the main error-log in the appropriate format.
2024-07-15T10:58:45.771145Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=3221225472. Please use innodb_redo_log_capacity instead.
2024-07-15T10:58:45.772799Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-15T10:58:52.318151Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-15T10:58:52Z UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=96a3ee772d1d213bc36262ae5f5c23c7b9f61b30
Thread pointer: 0x0
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 = 0 thread_stack 0x100000
/usr/local/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x212497d]
/usr/local/mysql/bin/mysqld(print_fatal_signal(int)+0x37f) [0xfe3fff]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0xa5) [0xfe40b5]
/lib64/libpthread.so.0(+0xf630) [0x7fe66a5a0630]
/usr/local/mysql/bin/mysqld(Validate_files::check(__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)+0x8ae) [0x2170c8e]
/usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, std::function<void (__gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespace const*, std::allocator<dd::Tablespace const*> > > const&, unsigned long)>, __gnu_cxx::__normal_iterator<dd::Tablespace const* const*, std::vector<dd::Tablespa/usr/local/mysql/bin/mysqld() [0x2ae5974]
/lib64/libpthread.so.0(+0x7ea5) [0x7fe66a598ea5]
/lib64/libc.so.6(clone+0x6d) [0x7fe6689adb0d]
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.

创建 10000个表后 crash 的 MySQL 8.0.38 降级到 8.0.37 测试

使用 MySQL 8.0.37 的软件启动 MySQL 8.0.38

1
2
3
4
5
6
7
8
[root@liups:/usr/local]#  /usr/local/mysql8037/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf &
[1] 18869
[root@liups:/usr/local]# 2024-07-16T02:48:22.503355Z mysqld_safe Logging to '/data/opt/mysql_8038/log/error.log'.
2024-07-16T02:48:22.522283Z mysqld_safe Starting mysqld daemon with databases from /data/opt/mysql_8038/data
[root@liups:/usr/local]# ps -ef |grep mysqld
root 18869 18554 0 10:48 pts/0 00:00:00 /bin/sh /usr/local/mysql8037/bin/mysqld_safe --defaults-file=/etc/my_8038.cnf
mysql 19399 18869 12 10:48 pts/0 00:00:01 /usr/local/mysql8037/bin/mysqld --defaults-file=/etc/my_8038.cnf --basedir=/usr/local/mysql --datadir=/data/opt/mysql_8038/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/opt/mysql_8038/log/error.log --open-files-limit=65535 --pid-file=/data/opt/mysql_8038/mysql.pid --socket=/data/opt/mysql_8038/mysql.sock --port=8038
root 19578 18554 0 10:48 pts/0 00:00:00 grep --color=auto mysqld

可以正常启动

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@liups:/usr/local]#/usr/local/mysql8037/bin/mysql -uroot -p  --socket=/data/opt/mysql_8038/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.37 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.tables where TABLE_SCHEMA='tbsoff';
+----------+
| count(*) |
+----------+
| 10000 |
+----------+

也就是从 crash 的 MySQL 8.0.38 降级到 8.0.37 是可以的。

知识总结

  1. MySQL 8.0.38 数据库中,表的数量达到 10000 个后,重启数据库会出现 crash
  2. MySQL 8.0.37 数据库中,表的数量达到 10000 个后,重启数据库会不出现 crash,这个时候升级到 8.0.38 会升级失败
  3. 当 MySQL 8.0.38 数据库中,表的数量达到 10000 个,crash 之后,可以通过降级到 8.0.37 来解决
  4. MySQL 8.0.38 如果要创建超过 10000个表,可以通过 共享/通用表空间的方式(不推荐)
  5. 建议不要将数据库升级到 8.0.38 ,如果目前已经升级,建议将数据库降级到升级前的版本或者8.0.37版本。

参考资料

https://www.modb.pro/db/1812669074343813120

原文作者: liups.com

原文链接: http://liups.cn/posts/f24e3443/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议