Alert: 不要升级到 MySQL 8.0.38( 8.4.1, 9.0.0) 任何版本?
上周,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*
objectcurrent_thd
to track current MYSQL_thread. But when in a detached OS thread contextcurrent_thd
is not set and remainsnullptr
. 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 inValidate_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 版本已经无法从历史归档中下载
目前这篇文章还没有评论(Rss)