英文:
Mysql crashing due to performance_schema.innodb_redo_log_files
问题
上次使用时运行正常，但在重新启动后，MySQL一直在重新启动。
使用以下命令启动服务器以获取输出日志。
mysql.server start --general_log=1 --general_log_file=/tmp/mysql_output.log
使用--innodb_force_recovery=2或更高版本允许访问数据库。
mysql_error.log
2023-01-09T02:15:00.6NZ mysqld_safe 正在记录到 '/tmp/mysql_error.log'。
2023-01-09T02:15:00.6NZ mysqld_safe 使用来自 /opt/homebrew/var/mysql 的数据库启动 mysqld 守护程序
2023-01-09T02:15:01.188215Z 0 [System] [MY-010116] [Server] /opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld (mysqld 8.0.31) 以进程 27318 启动
2023-01-09T02:15:01.195324Z 0 [Warning] [MY-010159] [Server] 由于文件系统 /opt/homebrew/var/mysql/ 不区分大小写，设置 lower_case_table_names=2
2023-01-09T02:15:01.214104Z 1 [System] [MY-013576] [InnoDB] InnoDB 初始化已启动。
2023-01-09T02:15:01.551220Z 1 [System] [MY-013577] [InnoDB] InnoDB 初始化已完成。
2023-01-09T02:15:01.767660Z 0 [Warning] [MY-010068] [Server] CA 证书 ca.pem 是自签名的。
2023-01-09T02:15:01.767682Z 0 [System] [MY-013602] [Server] 配置为支持 TLS 的通道 mysql_main。此通道现在支持加密连接。
2023-01-09T02:15:01.790366Z 0 [System] [MY-011323] [Server] X 插件已准备好连接。绑定地址：'127.0.0.1' 端口：33060，套接字：/tmp/mysqlx.sock
2023-01-09T02:15:01.790391Z 0 [System] [MY-010931] [Server] /opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld：已准备好连接。版本：'8.0.31' 套接字：'/tmp/mysql.sock' 端口：3306 Homebrew。
2023-01-09T02:15:02.760797Z 0 [ERROR] [MY-013183] [InnoDB] 断言失败：dict0dict.cc:3498:for_table || ref_table 线程 0x170fa3000
InnoDB：我们故意生成内存陷阱。
InnoDB：请向 http://bugs.mysql.com 提交详细的错误报告。
InnoDB：如果在 mysqld 启动后立即获得重复的断言失败或崩溃，可能存在 InnoDB 表空间中的损坏。请参考
InnoDB：关于强制恢复的信息。 http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
2023-01-09T02:15:02Z UTC - mysqld 收到信号 6；
最有可能是因为遇到了错误，但这个错误也可能是由于硬件故障引起的。
线程指针：0x12882a000
尝试回溯。您可以使用以下信息来查找
mysqld 死机的位置。如果在此之后没有看到任何消息，说明发生了严重错误...
stack_bottom = 170fa2ea8 thread_stack 0x100000
0 mysqld 0x0000000100e0aff8 my_print_stacktrace(unsigned char const*, unsigned long) + 68
1 mysqld 0x00000001005e4390 print_fatal_signal(int) + 572
2 mysqld 0x00000001005e4594 my_server_abort() + 88
3 mysqld 0x0000000100e05b14 my_abort() + 20
4 mysqld 0x00000001010db8bc ut_dbg_assertion_failed(char const*, char const*, unsigned long long) + 400
5 mysqld 0x0000000100eb4be0 dict_foreign_add_to_cache(dict_foreign_t*, char const**, bool, bool, dict_err_ignore_t) + 984
6 mysqld 0x0000000100ec8688 dd_table_load_fk_from_dd(dict_table_t*, dd::Table const*, char const**, dict_err_ignore_t, bool) + 1516
7 mysqld 0x0000000100ec8834 dd_table_load_fk(dd::cache::Dictionary_client*, char const*, char const**, dict_table_t*, dd::Table const*, THD*, bool, bool, std::__1::deque<char const*, ut::allocator<char const*, ut::detail::allocator_base_pfs<char const*> > >*) + 108
8 mysqld 0x0000000100ecc3e8 dict_table_t* dd_open_table_one<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*, std::__1::deque<char const*, ut::allocator<char const*, ut::detail::allocator_base_pfs<char const*> > >&) + 5644
9 mysqld 0x0000000100ebd400 dict_table_t* dd_open_table<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*) + 60
10 mysqld 0x0000000100ebd254 dd_table_open_on_dd_obj(THD*, dd::cache::Dictionary_client*, dd::Table const&, dd::Partition const*, char const*, dict_table_t*&, TABLE const*) + 1376
11 mysqld 0x0000000100ebdf04 dd_table_open_on_id_low(THD*, MDL_ticket**, unsigned long long) + 1100
12 mysqld 0x0000000100ebd5d8 dd_table_open_on_id(unsigned long
<details>
<summary>英文:</summary>
Was running last time it was used, upon rebooting mysql keeps restarting itself.
Starting the server with the following to get output logs.
`mysql.server start --general_log=1 --general_log_file=/tmp/mysql_output.log`
Using `--innodb_force_recovery=2` or higher allows access to the databases.
**mysql_error.log**
2023-01-09T02:15:00.6NZ mysqld_safe Logging to '/tmp/mysql_error.log'.
2023-01-09T02:15:00.6NZ mysqld_safe Starting mysqld daemon with databases from /opt/homebrew/var/mysql
2023-01-09T02:15:01.188215Z 0 [System] [MY-010116] [Server] /opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld (mysqld 8.0.31) starting as process 27318
2023-01-09T02:15:01.195324Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /opt/homebrew/var/mysql/ is case insensitive
2023-01-09T02:15:01.214104Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-01-09T02:15:01.551220Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-01-09T02:15:01.767660Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-01-09T02:15:01.767682Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-01-09T02:15:01.790366Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /tmp/mysqlx.sock
2023-01-09T02:15:01.790391Z 0 [System] [MY-010931] [Server] /opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld: ready for connections. Version: '8.0.31' socket: '/tmp/mysql.sock' port: 3306 Homebrew.
2023-01-09T02:15:02.760797Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: dict0dict.cc:3498:for_table || ref_table thread 0x170fa3000
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2023-01-09T02:15:02Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x12882a000
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 = 170fa2ea8 thread_stack 0x100000
0 mysqld 0x0000000100e0aff8 my_print_stacktrace(unsigned char const*, unsigned long) + 68
1 mysqld 0x00000001005e4390 print_fatal_signal(int) + 572
2 mysqld 0x00000001005e4594 my_server_abort() + 88
3 mysqld 0x0000000100e05b14 my_abort() + 20
4 mysqld 0x00000001010db8bc ut_dbg_assertion_failed(char const*, char const*, unsigned long long) + 400
5 mysqld 0x0000000100eb4be0 dict_foreign_add_to_cache(dict_foreign_t*, char const**, bool, bool, dict_err_ignore_t) + 984
6 mysqld 0x0000000100ec8688 dd_table_load_fk_from_dd(dict_table_t*, dd::Table const*, char const**, dict_err_ignore_t, bool) + 1516
7 mysqld 0x0000000100ec8834 dd_table_load_fk(dd::cache::Dictionary_client*, char const*, char const**, dict_table_t*, dd::Table const*, THD*, bool, bool, std::__1::deque<char const*, ut::allocator<char const*, ut::detail::allocator_base_pfs<char const*> > >*) + 108
8 mysqld 0x0000000100ecc3e8 dict_table_t* dd_open_table_one<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*, std::__1::deque<char const*, ut::allocator<char const*, ut::detail::allocator_base_pfs<char const*> > >&) + 5644
9 mysqld 0x0000000100ebd400 dict_table_t* dd_open_table<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*) + 60
10 mysqld 0x0000000100ebd254 dd_table_open_on_dd_obj(THD*, dd::cache::Dictionary_client*, dd::Table const&, dd::Partition const*, char const*, dict_table_t*&, TABLE const*) + 1376
11 mysqld 0x0000000100ebdf04 dd_table_open_on_id_low(THD*, MDL_ticket**, unsigned long long) + 1100
12 mysqld 0x0000000100ebd5d8 dd_table_open_on_id(unsigned long long, THD*, MDL_ticket**, bool, bool) + 392
13 mysqld 0x00000001010746ec row_purge_step(que_thr_t*) + 560
14 mysqld 0x0000000101046b64 que_run_threads(que_thr_t*) + 536
15 mysqld 0x000000010109ad28 srv_worker_thread() + 660
16 mysqld 0x00000001010a8834 void Detached_thread::operator()<void (*)()>(void (*&&)()) + 132
17 mysqld 0x00000001010a8724 void* std::__1::__thread_proxy<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct> >, Detached_thread, void (*)()> >(void*) + 60
18 libsystem_pthread.dylib 0x00000001a1dab878 _pthread_start + 320
19 libsystem_pthread.dylib 0x00000001a1da65e0 thread_start + 8
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0):
Connection ID (thread ID): 0
Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2023-01-09T02:15:02.6NZ mysqld_safe mysqld restarted
2023-01-09T02:15:02.960389Z 0 [System] [MY-010116] [Server] /opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld (mysqld 8.0.31) starting as process 27347
**mysql_output.log**
/opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld, Version: 8.0.31 (Homebrew). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2023-01-09T02:15:01.751199Z 0 Execute CREATE TABLE performance_schema.innodb_redo_log_files(
`FILE_ID` BIGINT NOT NULL COMMENT 'Id of the file.',
`FILE_NAME` VARCHAR(2000) NOT NULL COMMENT 'Path to the file.',
`START_LSN` BIGINT NOT NULL COMMENT 'LSN of the first block in the file.',
`END_LSN` BIGINT NOT NULL COMMENT 'LSN after the last block in the file.',
`SIZE_IN_BYTES` BIGINT NOT NULL COMMENT 'Size of the file (in bytes).',
`IS_FULL` TINYINT NOT NULL COMMENT '1 iff file has no free space inside.',
`CONSUMER_LEVEL` INT NOT NULL COMMENT 'All redo log consumers registered on smaller levels than this value, have already consumed this file.'
)engine = 'performance_schema'
/opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld, Version: 8.0.31 (Homebrew). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2023-01-09T02:15:04.124392Z 0 Execute CREATE TABLE performance_schema.innodb_redo_log_files(
`FILE_ID` BIGINT NOT NULL COMMENT 'Id of the file.',
`FILE_NAME` VARCHAR(2000) NOT NULL COMMENT 'Path to the file.',
`START_LSN` BIGINT NOT NULL COMMENT 'LSN of the first block in the file.',
`END_LSN` BIGINT NOT NULL COMMENT 'LSN after the last block in the file.',
`SIZE_IN_BYTES` BIGINT NOT NULL COMMENT 'Size of the file (in bytes).',
`IS_FULL` TINYINT NOT NULL COMMENT '1 iff file has no free space inside.',
`CONSUMER_LEVEL` INT NOT NULL COMMENT 'All redo log consumers registered on smaller levels than this value, have already consumed this file.'
I've back up all my databases, *innodb_redo_log_files* is not readable
> An error occurred while retrieving the information for table 'innodb_redo_log_files'. Please try again.
> MySQL said: Table 'performance_schema.innodb_redo_log_files' doesn't exist
Running `DESCRIBE innodb_redo_log_files;` does however show the structure that was defined in the `CREATE TABLE performance_schema.innodb_redo_log_files()` above.
</details>
# 答案1
**得分**: 0
在第5次重新安装后，它又开始工作了。我按照[coderwall](https://coderwall.com/p/os6woq/uninstall-all-those-broken-versions-of-mysql-and-re-install-it-with-brew-on-mac-mavericks)上的说明进行操作。
**步骤**
- 运行以下命令以查找 MySQL 进程：ps -ax | grep mysql
- 停止并结束所有 MySQL 进程
- 卸载 MySQL：brew remove mysql
- 清理不需要的文件：brew cleanup
- 删除以下文件和文件夹：
- sudo rm /usr/local/mysql
- sudo rm -rf /usr/local/var/mysql
- sudo rm -rf /usr/local/mysql*
- sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
- sudo rm -rf /Library/StartupItems/MySQLCOM
- sudo rm -rf /Library/PreferencePanes/My*
- 卸载 LaunchAgent：launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
- 编辑 /etc/hostconfig 并删除包含 MYSQLCOM=-YES- 的行
- 删除 PreferencePanes 文件夹中的任何 MySQL 相关文件：rm -rf ~/Library/PreferencePanes/My*
- 删除以下文件和文件夹：
- sudo rm -rf /Library/Receipts/mysql*
- sudo rm -rf /Library/Receipts/MySQL*
- sudo rm -rf /private/var/db/receipts/*mysql*
- 重启计算机以确保所有 MySQL 进程都被终止
- 尝试运行 mysql，它不应该工作
**重新安装 mysql**
- 运行 brew doctor 并修复任何错误
- 更新 brew：brew update
- 安装 MySQL：brew install mysql
- 取消设置 TMPDIR
- 运行以下命令以启动 MySQL 服务器：
- mysqld --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/opt/homebrew/var/mysql --tmpdir=/tmp
- 启动 MySQL 服务器：mysql.server start
如果你使用的是 M1 芯片，你的 `--datadir=` 路径会不同。如果使用不同的芯片，请将 datadir 设置为 /usr/local/var/mysql。
<details>
<summary>英文:</summary>
After the 5th reinstall it's working again. I followed the instructions from [coderwall](https://coderwall.com/p/os6woq/uninstall-all-those-broken-versions-of-mysql-and-re-install-it-with-brew-on-mac-mavericks).
**Steps**
- ps -ax | grep mysql
- stop and kill any MySQL processes
- brew remove mysql
- brew cleanup
- sudo rm /usr/local/mysql
- sudo rm -rf /usr/local/var/mysql
- sudo rm -rf /usr/local/mysql*
- sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
- sudo rm -rf /Library/StartupItems/MySQLCOM
- sudo rm -rf /Library/PreferencePanes/My*
- launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
- edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
- rm -rf ~/Library/PreferencePanes/My*
- sudo rm -rf /Library/Receipts/mysql*
- sudo rm -rf /Library/Receipts/MySQL*
- sudo rm -rf /private/var/db/receipts/*mysql*
- restart your computer just to ensure any MySQL processes are killed
- try to run mysql, it shouldn't work
**Reinstall mysql**
- brew doctor and fix any errors
- brew update
- brew install mysql
- unset TMPDIR
- mysqld --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/opt/homebrew/var/mysql --tmpdir=/tmp
- mysql.server start
I'm on an M1 chip to my `--datadir=` path is different. If using a different chip `datadir=/usr/local/var/mysql`
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论