Alert: 不要升级到 MySQL 8.0.38( 8.4.1, 9.0.0) 任何版本?

首页 » MySQL » Alert: 不要升级到 MySQL 8.0.38( 8.4.1, 9.0.0) 任何版本?

Alert: 不要升级到 MySQL 8.0.38( 8.4.1, 9.0.0) 任何版本?

2024/07/17
MySQL
162 views
没有评论

上周，Percona 发布了一篇警告博客，提醒用户<不要升级到 MySQL 8.0.37 之后的任何版本>。然而，现在访问 MySQL 官方下载页面，依然可以在主推的下拉列表中看到这些版本。
Percona 在文章中提到，Jean-François Gagné 在 bug.mysql.com 上报告了一个编号为 #115517 的 bug。不幸的是，这个 bug 目前处于私密状态。简而言之，如果您创建大量表（例如 10000 个），MySQL 守护进程在重新启动时会崩溃。当然，也有人认为这是危言耸听，因为单个数据库实例中有超过1万张表的用户可能不足0.0000001%。

然而，有人反馈认为这是实例级限制，而非单库限制，对于多租户环境来说并不罕见。此外，国内也有不少数据库单库数据量达到上 TB 级的 MySQL 实例，所以这种情况也并非完全不可能。

已确定受影响的版本如下：

MySQL 8.0.38
MySQL 8.4.1
MySQL 9.0.0

目前已确认该问题的提交是Bug #33398681 – ibd 表空间文件从 · mysql/mysql-server@28eb1ff 移回，实际测试中仅8K个tablespace/tables就能复现该问题。

Meng-Hsiu Chiang分析的较全面

When the database has more than 8k tables (defined in storage/innobase/include/fil0fil.h:64, and I can reproduce the issue with 8001 tables instead of 10k)), it will create an additional detached OS thread to check data dictionaries (Validate_files::validate() -> par_for() -> Validate_files::check()).

MySQL uses a thread_local THD* object current_thd to track current MYSQL_thread. But when in a detached OS thread context current_thd is not set and remains nullptr. I observed this behavior from 8.0.36 to 8.0.38.

8.0.38 then introduce commit 28eb1ff1 that causes the issue, because the commit uses current_thd->dd_client() to do cleanup in Validate_files::check(). Since it’s called in the detached OS thread it causes segment fault.

There may be 3 solution:

Revert commit 28eb1ff1

“Properly” use a MYSQL_thread in par_for(). However we don’t know if the behavior (current_thd not being set in detached OS thread) is expected or it can be done

Improve/Fix commit 28eb1ff1 so it doesn’t segfault

测试（8.0.38）

测试脚本1

#!/bin/bash

# MySQL connection details
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_USER="root"
MYSQL_PASSWORD="mysql"
MYSQL_DATABASE="test"

# Number of tables to create
NUM_TABLES=12000
THREADS=16

# MySQL command to execute
MYSQL_CMD="mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD"

# Check MySQL connection
echo "Checking MySQL connection..."
echo "SELECT 1;" | $MYSQL_CMD 2>/dev/null
if [ $? -ne 0 ]; then
    echo "Error: Unable to connect to MySQL. Please check your connection details."
    exit 1
fi

# Create database if it doesn't exist
echo "Creating database if it doesn't exist..."
echo "CREATE DATABASE IF NOT EXISTS $MYSQL_DATABASE;" | $MYSQL_CMD

# Use the created or existing database
MYSQL_CMD="$MYSQL_CMD $MYSQL_DATABASE"

# Function to create a table
create_table() {
    local table_name="table_$1"
    local sql="CREATE TABLE IF NOT EXISTS $table_name (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(255));"
    echo "$sql" | $MYSQL_CMD
    if [ $? -eq 0 ]; then
        echo "success"
    else
        echo "failure"
    fi
}

export -f create_table
export MYSQL_CMD

# Generate a sequence of table numbers and run the create_table function in parallel
success_count=$(seq 1 $NUM_TABLES | parallel -j $THREADS create_table | grep -c "success")

echo "Completed creating $success_count tables out of $NUM_TABLES."

测试脚本2

 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> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.38    |
+-----------+

--使用的测试脚本2
mysql>  CALL create_tables_and_insert();
Query OK, 0 rows affected (8 min 6.38 sec)

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

# 重启

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.

Note:
重启后再次crash. 位置mysqld(Validate_files::check，同样的测试在8.0.37不存在问题。如果先存在这么多对象，再升级到8.0.38后，此问题依旧可以复现。

MySQL 8.0.38 innodb_file_per_table = 0

增加参数文件设置： innodb_file_per_table = 0

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

Note：
这种情况下，重启是正常的。

innodb_validate_tablespace_paths 参数为off

参数文件设置： innodb_validate_tablespace_paths = off

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    |
+-----------------------+-------+

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

# 日志

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 > > const&, __gnu_cxx::__normal_iterator > > const&, unsigned long)+0x8ae) [0x2170c8e]
/usr/local/mysql/bin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, std::function > > const&, __gnu_cxx::__normal_iterator > > const&, unsigned long)>, __gnu_cxx::__normal_iterator

Note：
重启问题依旧crash. 错误日志相同，而发问题后，降级到8.0.37再重启问题不再存在。

总结：

在MySQL最近的版本8.0.38（8.4.1, 9.0.0）中当创建对象近1w（8k）后，实例重启后，在Validate_files::check环节会crash，如果您MySQL库中没有这么多对象没有影响，如果遇到了该bug解决方法是降级到8.0.37(后期应该会在新版本修复)，或使用共享/通用表空间的方式(innodb_file_per_table = 0)（不推荐）

— update 2024/7/24

MySQL 也在今天做出了回应，紧急下架了这三个版本，于是在 7 月 23 日重新发布了新版本

MySQL 9.0.1
MySQL 8.4.2
MySQL 8.0.39

MySQL 9.0.0 和 MySQL 8.4.1 和 MySQL 8.0.38 版本已经无法从历史归档中下载

打赏

115517, mysql bug, Validate_files::check

目前这篇文章还没有评论(Rss)

我要评论

上一篇： Troubleshooting MySQL ERROR 1709 (HY000): Index column size too large.

下一篇： Oracle Controlfile Corrupted ORA-27048: skgfifi: file header information is invalid

ANBOB™