Mysql 因 performance_schema.innodb_redo_log_files 导致崩溃。

huangapple
go评论125阅读模式
英文:

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

  1. 2023-01-09T02:15:00.6NZ mysqld_safe 正在记录到 '/tmp/mysql_error.log'。
  2. 2023-01-09T02:15:00.6NZ mysqld_safe 使用来自 /opt/homebrew/var/mysql 的数据库启动 mysqld 守护程序
  3. 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 启动
  4. 2023-01-09T02:15:01.195324Z 0 [Warning] [MY-010159] [Server] 由于文件系统 /opt/homebrew/var/mysql/ 不区分大小写，设置 lower_case_table_names=2
  5. 2023-01-09T02:15:01.214104Z 1 [System] [MY-013576] [InnoDB] InnoDB 初始化已启动。
  6. 2023-01-09T02:15:01.551220Z 1 [System] [MY-013577] [InnoDB] InnoDB 初始化已完成。
  7. 2023-01-09T02:15:01.767660Z 0 [Warning] [MY-010068] [Server] CA 证书 ca.pem 是自签名的。
  8. 2023-01-09T02:15:01.767682Z 0 [System] [MY-013602] [Server] 配置为支持 TLS 的通道 mysql_main。此通道现在支持加密连接。
  9. 2023-01-09T02:15:01.790366Z 0 [System] [MY-011323] [Server] X 插件已准备好连接。绑定地址：'127.0.0.1' 端口：33060，套接字：/tmp/mysqlx.sock
  10. 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。
  11. 2023-01-09T02:15:02.760797Z 0 [ERROR] [MY-013183] [InnoDB] 断言失败：dict0dict.cc:3498:for_table || ref_table 线程 0x170fa3000
  12. InnoDB：我们故意生成内存陷阱。
  13. InnoDB：请向 http://bugs.mysql.com 提交详细的错误报告。
  14. InnoDB：如果在 mysqld 启动后立即获得重复的断言失败或崩溃，可能存在 InnoDB 表空间中的损坏。请参考
  15. InnoDB：关于强制恢复的信息。 http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
  16. 2023-01-09T02:15:02Z UTC - mysqld 收到信号 6；
  17. 最有可能是因为遇到了错误，但这个错误也可能是由于硬件故障引起的。
  18. 线程指针：0x12882a000
  19. 尝试回溯。您可以使用以下信息来查找
  20. mysqld 死机的位置。如果在此之后没有看到任何消息，说明发生了严重错误...
  21. stack_bottom = 170fa2ea8 thread_stack 0x100000
  22. 0   mysqld                              0x0000000100e0aff8 my_print_stacktrace(unsigned char const*, unsigned long) + 68
  23. 1   mysqld                              0x00000001005e4390 print_fatal_signal(int) + 572
  24. 2   mysqld                              0x00000001005e4594 my_server_abort() + 88
  25. 3   mysqld                              0x0000000100e05b14 my_abort() + 20
  26. 4   mysqld                              0x00000001010db8bc ut_dbg_assertion_failed(char const*, char const*, unsigned long long) + 400
  27. 5   mysqld                              0x0000000100eb4be0 dict_foreign_add_to_cache(dict_foreign_t*, char const**, bool, bool, dict_err_ignore_t) + 984
  28. 6   mysqld                              0x0000000100ec8688 dd_table_load_fk_from_dd(dict_table_t*, dd::Table const*, char const**, dict_err_ignore_t, bool) + 1516
  29. 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
  30. 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
  31. 9   mysqld                              0x0000000100ebd400 dict_table_t* dd_open_table<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*) + 60
  32. 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
  33. 11  mysqld                              0x0000000100ebdf04 dd_table_open_on_id_low(THD*, MDL_ticket**, unsigned long long) + 1100
  34. 12  mysqld                              0x0000000100ebd5d8 dd_table_open_on_id(unsigned long
  35. <details>
  36. <summary>英文:</summary>
  37. Was running last time it was used, upon rebooting mysql keeps restarting itself.
  38. Starting the server with the following to get output logs.
  39. `mysql.server start --general_log=1 --general_log_file=/tmp/mysql_output.log`
  40. Using `--innodb_force_recovery=2` or higher allows access to the databases.
  41. **mysql_error.log**
  42. 2023-01-09T02:15:00.6NZ mysqld_safe Logging to &#39;/tmp/mysql_error.log&#39;.
  43. 2023-01-09T02:15:00.6NZ mysqld_safe Starting mysqld daemon with databases from /opt/homebrew/var/mysql
  44. 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
  45. 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
  46. 2023-01-09T02:15:01.214104Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  47. 2023-01-09T02:15:01.551220Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  48. 2023-01-09T02:15:01.767660Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
  49. 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.
  50. 2023-01-09T02:15:01.790366Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: &#39;127.0.0.1&#39; port: 33060, socket: /tmp/mysqlx.sock
  51. 2023-01-09T02:15:01.790391Z 0 [System] [MY-010931] [Server] /opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld: ready for connections. Version: &#39;8.0.31&#39;  socket: &#39;/tmp/mysql.sock&#39;  port: 3306  Homebrew.
  52. 2023-01-09T02:15:02.760797Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: dict0dict.cc:3498:for_table || ref_table thread 0x170fa3000
  53. InnoDB: We intentionally generate a memory trap.
  54. InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
  55. InnoDB: If you get repeated assertion failures or crashes, even
  56. InnoDB: immediately after the mysqld startup, there may be
  57. InnoDB: corruption in the InnoDB tablespace. Please refer to
  58. InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
  59. InnoDB: about forcing recovery.
  60. 2023-01-09T02:15:02Z UTC - mysqld got signal 6 ;
  61. Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
  62. Thread pointer: 0x12882a000
  63. Attempting backtrace. You can use the following information to find out
  64. where mysqld died. If you see no messages after this, something went
  65. terribly wrong...
  66. stack_bottom = 170fa2ea8 thread_stack 0x100000
  67. 0   mysqld                              0x0000000100e0aff8 my_print_stacktrace(unsigned char const*, unsigned long) + 68
  68. 1   mysqld                              0x00000001005e4390 print_fatal_signal(int) + 572
  69. 2   mysqld                              0x00000001005e4594 my_server_abort() + 88
  70. 3   mysqld                              0x0000000100e05b14 my_abort() + 20
  71. 4   mysqld                              0x00000001010db8bc ut_dbg_assertion_failed(char const*, char const*, unsigned long long) + 400
  72. 5   mysqld                              0x0000000100eb4be0 dict_foreign_add_to_cache(dict_foreign_t*, char const**, bool, bool, dict_err_ignore_t) + 984
  73. 6   mysqld                              0x0000000100ec8688 dd_table_load_fk_from_dd(dict_table_t*, dd::Table const*, char const**, dict_err_ignore_t, bool) + 1516
  74. 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&lt;char const*, ut::allocator&lt;char const*, ut::detail::allocator_base_pfs&lt;char const*&gt; &gt; &gt;*) + 108
  75. 8   mysqld                              0x0000000100ecc3e8 dict_table_t* dd_open_table_one&lt;dd::Table&gt;(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*, std::__1::deque&lt;char const*, ut::allocator&lt;char const*, ut::detail::allocator_base_pfs&lt;char const*&gt; &gt; &gt;&amp;) + 5644
  76. 9   mysqld                              0x0000000100ebd400 dict_table_t* dd_open_table&lt;dd::Table&gt;(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*) + 60
  77. 10  mysqld                              0x0000000100ebd254 dd_table_open_on_dd_obj(THD*, dd::cache::Dictionary_client*, dd::Table const&amp;, dd::Partition const*, char const*, dict_table_t*&amp;, TABLE const*) + 1376
  78. 11  mysqld                              0x0000000100ebdf04 dd_table_open_on_id_low(THD*, MDL_ticket**, unsigned long long) + 1100
  79. 12  mysqld                              0x0000000100ebd5d8 dd_table_open_on_id(unsigned long long, THD*, MDL_ticket**, bool, bool) + 392
  80. 13  mysqld                              0x00000001010746ec row_purge_step(que_thr_t*) + 560
  81. 14  mysqld                              0x0000000101046b64 que_run_threads(que_thr_t*) + 536
  82. 15  mysqld                              0x000000010109ad28 srv_worker_thread() + 660
  83. 16  mysqld                              0x00000001010a8834 void Detached_thread::operator()&lt;void (*)()&gt;(void (*&amp;&amp;)()) + 132
  84. 17  mysqld                              0x00000001010a8724 void* std::__1::__thread_proxy&lt;std::__1::tuple&lt;std::__1::unique_ptr&lt;std::__1::__thread_struct, std::__1::default_delete&lt;std::__1::__thread_struct&gt; &gt;, Detached_thread, void (*)()&gt; &gt;(void*) + 60
  85. 18  libsystem_pthread.dylib             0x00000001a1dab878 _pthread_start + 320
  86. 19  libsystem_pthread.dylib             0x00000001a1da65e0 thread_start + 8
  87. Trying to get some variables.
  88. Some pointers may be invalid and cause the dump to abort.
  89. Query (0): 
  90. Connection ID (thread ID): 0
  91. Status: NOT_KILLED
  92. The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
  93. information that should help you find out what is causing the crash.
  94. 2023-01-09T02:15:02.6NZ mysqld_safe mysqld restarted
  95. 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
  96. **mysql_output.log**
  97. /opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld, Version: 8.0.31 (Homebrew). started with:
  98. Tcp port: 3306  Unix socket: /tmp/mysql.sock
  99. Time                 Id Command    Argument
  100. 2023-01-09T02:15:01.751199Z	    0 Execute	CREATE TABLE performance_schema.innodb_redo_log_files(
  101. `FILE_ID` BIGINT NOT NULL COMMENT &#39;Id of the file.&#39;,
  102. `FILE_NAME` VARCHAR(2000) NOT NULL COMMENT &#39;Path to the file.&#39;,
  103. `START_LSN` BIGINT NOT NULL COMMENT &#39;LSN of the first block in the file.&#39;,
  104. `END_LSN` BIGINT NOT NULL COMMENT &#39;LSN after the last block in the file.&#39;,
  105. `SIZE_IN_BYTES` BIGINT NOT NULL COMMENT &#39;Size of the file (in bytes).&#39;,
  106. `IS_FULL` TINYINT NOT NULL COMMENT &#39;1 iff file has no free space inside.&#39;,
  107. `CONSUMER_LEVEL` INT NOT NULL COMMENT &#39;All redo log consumers registered on smaller levels than this value, have already consumed this file.&#39;
  108. )engine = &#39;performance_schema&#39;
  109. /opt/homebrew/Cellar/mysql/8.0.31/bin/mysqld, Version: 8.0.31 (Homebrew). started with:
  110. Tcp port: 3306  Unix socket: /tmp/mysql.sock
  111. Time                 Id Command    Argument
  112. 2023-01-09T02:15:04.124392Z	    0 Execute	CREATE TABLE performance_schema.innodb_redo_log_files(
  113. `FILE_ID` BIGINT NOT NULL COMMENT &#39;Id of the file.&#39;,
  114. `FILE_NAME` VARCHAR(2000) NOT NULL COMMENT &#39;Path to the file.&#39;,
  115. `START_LSN` BIGINT NOT NULL COMMENT &#39;LSN of the first block in the file.&#39;,
  116. `END_LSN` BIGINT NOT NULL COMMENT &#39;LSN after the last block in the file.&#39;,
  117. `SIZE_IN_BYTES` BIGINT NOT NULL COMMENT &#39;Size of the file (in bytes).&#39;,
  118. `IS_FULL` TINYINT NOT NULL COMMENT &#39;1 iff file has no free space inside.&#39;,
  119. `CONSUMER_LEVEL` INT NOT NULL COMMENT &#39;All redo log consumers registered on smaller levels than this value, have already consumed this file.&#39;
  120. I&#39;ve back up all my databases, *innodb_redo_log_files* is not readable
  121. &gt; An error occurred while retrieving the information for table &#39;innodb_redo_log_files&#39;. Please try again.
  122. &gt; MySQL said: Table &#39;performance_schema.innodb_redo_log_files&#39; doesn&#39;t exist
  123. 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.
  124. </details>
  125. # 答案1
  126. **得分**: 0
  127. 在第5次重新安装后，它又开始工作了。我按照[coderwall](https://coderwall.com/p/os6woq/uninstall-all-those-broken-versions-of-mysql-and-re-install-it-with-brew-on-mac-mavericks)上的说明进行操作。
  128. **步骤**
  129. - 运行以下命令以查找 MySQL 进程：ps -ax | grep mysql
  130. - 停止并结束所有 MySQL 进程
  131. - 卸载 MySQL：brew remove mysql
  132. - 清理不需要的文件：brew cleanup
  133. - 删除以下文件和文件夹：
  134. - sudo rm /usr/local/mysql
  135. - sudo rm -rf /usr/local/var/mysql
  136. - sudo rm -rf /usr/local/mysql*
  137. - sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
  138. - sudo rm -rf /Library/StartupItems/MySQLCOM
  139. - sudo rm -rf /Library/PreferencePanes/My*
  140. - 卸载 LaunchAgent：launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
  141. - 编辑 /etc/hostconfig 并删除包含 MYSQLCOM=-YES- 的行
  142. - 删除 PreferencePanes 文件夹中的任何 MySQL 相关文件：rm -rf ~/Library/PreferencePanes/My*
  143. - 删除以下文件和文件夹：
  144. - sudo rm -rf /Library/Receipts/mysql*
  145. - sudo rm -rf /Library/Receipts/MySQL*
  146. - sudo rm -rf /private/var/db/receipts/*mysql*
  147. - 重启计算机以确保所有 MySQL 进程都被终止
  148. - 尝试运行 mysql，它不应该工作
  149. **重新安装 mysql**
  150. - 运行 brew doctor 并修复任何错误
  151. - 更新 brew：brew update
  152. - 安装 MySQL：brew install mysql
  153. - 取消设置 TMPDIR
  154. - 运行以下命令以启动 MySQL 服务器：
  155. - mysqld --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/opt/homebrew/var/mysql --tmpdir=/tmp
  156. - 启动 MySQL 服务器：mysql.server start
  157. 如果你使用的是 M1 芯片，你的 `--datadir=` 路径会不同。如果使用不同的芯片，请将 datadir 设置为 /usr/local/var/mysql。
  158. <details>
  159. <summary>英文:</summary>
  160. After the 5th reinstall it&#39;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).
  161. **Steps**
  162. - ps -ax | grep mysql
  163. - stop and kill any MySQL processes
  164. - brew remove mysql
  165. - brew cleanup
  166. - sudo rm /usr/local/mysql
  167. - sudo rm -rf /usr/local/var/mysql
  168. - sudo rm -rf /usr/local/mysql*
  169. - sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
  170. - sudo rm -rf /Library/StartupItems/MySQLCOM
  171. - sudo rm -rf /Library/PreferencePanes/My*
  172. - launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
  173. - edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
  174. - rm -rf ~/Library/PreferencePanes/My*
  175. - sudo rm -rf /Library/Receipts/mysql*
  176. - sudo rm -rf /Library/Receipts/MySQL*
  177. - sudo rm -rf /private/var/db/receipts/*mysql*
  178. - restart your computer just to ensure any MySQL processes are killed
  179. - try to run mysql, it shouldn&#39;t work
  180. **Reinstall mysql**
  181. - brew doctor and fix any errors
  182. - brew update
  183. - brew install mysql
  184. - unset TMPDIR
  185. - mysqld --verbose --user=`whoami` --basedir=&quot;$(brew --prefix mysql)&quot; --datadir=/opt/homebrew/var/mysql --tmpdir=/tmp
  186. - mysql.server start
  187. I&#39;m on an M1 chip to my `--datadir=` path is different. If using a different chip `datadir=/usr/local/var/mysql`
  188. </details>

huangapple
  • 本文由 发表于 2023年1月9日 10:31:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/75052700.html
  • innodb
  • macos
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定