[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 国际许可协议