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.
After the 5th reinstall it's working again. I followed the instructions from coderwall.
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