My MySQL appears to not be running correctly on my MacOS Catalina (10.15) according to Homebrew and according to my wordpress app, it cannot make a connection to my MySQL server. When I try to read a database in a SQL Pro SQL Pro cannot establish a connection either. However, I can log into MySQL using my terminal shell which is confusing to me.
What is wrong with my MySQL process? MySQL was working fine for my wordpress app yesterday. The only difference was that I restarted my computer and now, I cannot seem to start it successfully with homebrew and wordpress cannot establish a connection to it. Here is a break down of details and logs with regards to wordpress and homebrew
Wordpress
Yesterday, I restarted my laptop running on MacOS and when after restarting my computer I tried to run my wordpress app locally, but wordpress complained about an error establishing a database connection:
Warning: mysqli_real_connect(): Unexpected server response while doing caching_sha2 auth: 109 in /Users/robskrob/code/projects/myblog/wordpress/web/wp/wp-includes/wp-db.php on line 1633
Warning: mysqli_real_connect(): (HY000/2006): MySQL server has gone away in /Users/robskrob/code/projects/kingandpartners/myblog/wordpress/web/wp/wp-includes/wp-db.php on line 1633
MySQL server has gone away
Error establishing a database connection
This either means that the username and password information in your wp-config.php file is incorrect or we can’t contact the database server at 127.0.0.1. This could mean your host’s database server is down.
Are you sure you have the correct username and password?
Are you sure you have typed the correct hostname?
Are you sure the database server is running?
If you’re unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums.
MySQL Homebrew logs
I checked what I believe to be my log file for the mysql homebrew installation in /usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err -- these are the logs from today:
2020-01-17T23:59:48.010069Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 2098009ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T00:32:58.010992Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 1937613ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T02:26:25.009708Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6753733ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T03:15:04.012850Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 2868086ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T03:17:10.403347Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 113439ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-01-18T04:19:50.025641Z 0 [Note] Giving 0 client threads a chance to die gracefully
2020-01-18T04:19:50.026067Z 0 [Note] Shutting down slave threads
2020-01-18T04:19:50.026406Z 0 [Note] Forcefully disconnecting 0 remaining clients
2020-01-18T04:19:50.026859Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2020-01-18T04:19:50.028486Z 0 [Note] Binlog end
2020-01-18T04:19:50.266455Z 0 [Note] Shutting down plugin 'ngram'
2020-01-18T04:19:50.266534Z 0 [Note] Shutting down plugin 'partition'
2020-01-18T04:19:50.266547Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2020-01-18T04:19:50.266558Z 0 [Note] Shutting down plugin 'ARCHIVE'
2020-01-18T04:19:50.266566Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-01-18T04:19:50.278568Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2020-01-18T04:19:50.278607Z 0 [Note] Shutting down plugin 'MyISAM'
2020-01-18T04:19:50.278712Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2020-01-18T04:19:50.284742Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-01-18T04:19:50.284768Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-01-18T04:19:50.284780Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-01-18T04:19:50.284789Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-01-18T04:19:50.284796Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-01-18T04:19:50.284803Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-01-18T04:19:50.284810Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-01-18T04:19:50.284817Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-01-18T04:19:50.284824Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-01-18T04:19:50.284831Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-01-18T04:19:50.284838Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-01-18T04:19:50.284846Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-01-18T04:19:50.284855Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-01-18T04:19:50.284863Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-01-18T04:19:50.284870Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-01-18T04:19:50.284877Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2020-01-18T04:19:50.284884Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2020-01-18T04:19:50.284891Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-01-18T04:19:50.284898Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-01-18T04:19:50.284906Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-01-18T04:19:50.284913Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-01-18T04:19:50.284920Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-01-18T04:19:50.284930Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-01-18T04:19:50.284937Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-01-18T04:19:50.284944Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-01-18T04:19:50.284964Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2020-01-18T04:19:50.284980Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-01-18T04:19:50.284987Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-01-18T04:19:50.284994Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2020-01-18T04:19:50.285009Z 0 [Note] Shutting down plugin 'InnoDB'
2020-01-18T04:19:50.302153Z 0 [Note] InnoDB: FTS optimize thread exiting.
2020-01-18T04:19:50.302324Z 0 [Note] InnoDB: Starting shutdown...
2020-01-18T04:19:50.448900Z 0 [Note] InnoDB: Dumping buffer pool(s) to /usr/local/var/mysql/ib_buffer_pool
2020-01-18T04:19:50.455916Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 200117 23:19:50
2020-01-18T04:19:51.352113Z 0 [Note] InnoDB: Shutdown completed; log sequence number 26072066420
2020-01-18T04:19:51.354829Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-01-18T04:19:51.354892Z 0 [Note] Shutting down plugin 'MEMORY'
2020-01-18T04:19:51.355317Z 0 [Note] Shutting down plugin 'CSV'
2020-01-18T04:19:51.355373Z 0 [Note] Shutting down plugin 'sha256_password'
2020-01-18T04:19:51.355402Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-01-18T04:19:51.356443Z 0 [Note] Shutting down plugin 'binlog'
2020-01-18T04:19:51.364593Z 0 [Note] /usr/local/opt/mysql/bin/mysqld: Shutdown complete
2020-01-18T04:21:20.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T04:21:21.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2020-01-18T04:21:22.464837Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.18) starting as process 773
2020-01-18T04:21:23.267083Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2020-01-18T04:21:24.347624Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2020-01-18T04:22:10.697989Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2020-01-18T04:22:12.302757Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80018' started.
2020-01-18T04:22:20.760953Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80018' completed.
2020-01-18T04:22:20.869345Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-18T04:22:20.901909Z 0 [System] [MY-010931] [Server] /usr/local/opt/mysql/bin/mysqld: ready for connections. Version: '8.0.18' socket: '/tmp/mysql.sock' port: 3306 Homebrew.
2020-01-18T04:22:21.087121Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-01-18T18:40:40.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T18:40:40.6NZ mysqld_safe A mysqld process already exists
2020-01-18T18:40:42.139017Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.18) Homebrew.
2020-01-18T18:40:50.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T18:40:50.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2020-01-18T18:40:50.703294Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.18) starting as process 15000
2020-01-18T18:40:50.709793Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2020-01-18T18:40:51.867098Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-18T18:40:51.893469Z 0 [System] [MY-010931] [Server] /usr/local/opt/mysql/bin/mysqld: ready for connections. Version: '8.0.18' socket: '/tmp/mysql.sock' port: 3306 Homebrew.
2020-01-18T18:40:52.023790Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-01-18T18:53:44.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T18:53:44.6NZ mysqld_safe A mysqld process already exists
2020-01-18T18:53:45.316518Z 0 [System] [MY-010910] [Server] /usr/local/opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.18) Homebrew.
2020-01-18T18:53:54.6NZ mysqld_safe Logging to '/usr/local/var/mysql/Roberts-MBP-4.fios-router.home.err'.
2020-01-18T18:53:54.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2020-01-18T18:53:55.004674Z 0 [System] [MY-010116] [Server] /usr/local/opt/mysql/bin/mysqld (mysqld 8.0.18) starting as process 17779
2020-01-18T18:53:55.011065Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2020-01-18T18:53:57.547543Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-01-18T18:53:57.573755Z 0 [System] [MY-010931] [Server] /usr/local/opt/mysql/bin/mysqld: ready for connections. Version: '8.0.18' socket: '/tmp/mysql.sock' port: 3306 Homebrew.
2020-01-18T18:53:57.706901Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
Brew Services List
When I run brew services list I see that mysql has the status error:
$ brew services list
Name Status User Plist
chromedriver stopped
docker-machine stopped
elasticsearch started robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.elasticsearch.plist
elasticsearch@2.4 stopped
httpd stopped
mysql error robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.mysql.plist
nginx stopped
php started robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.php.plist
postgresql stopped
rabbitmq stopped
redis started robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.redis.plist
MySQL Terminal Session
I am able to enter a mysql console session in my zshell:
$ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.18 Homebrew
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> use myblog_development;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
This tells me that to a certain extent it appears that mysql is running, but my wordpress app cannot establish a connection to it.
System.log
When I printed out the system log on my machine, I see these strange logs regarding mysql:
Jan 18 13:53:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql[14913]): Service did not exit 5 seconds after SIGTERM. Sending SIGKILL.
Jan 18 13:53:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql): This service is defined to be constantly running and is inherently inefficient.
Jan 18 13:53:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql[17411]): Service exited with abnormal code: 1
Jan 18 13:53:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql): Service only ran for 0 seconds. Pushing respawn out by 10 seconds.
Homebrew forum
There appears to be a similarly logged issue in homebrew forums.
Update 1
Following the instructions here I ran these commands to see if mysql's running state would improve:
$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
/Users/robskrob/Library/LaunchAgents/homebrew.mxcl.mysql.plist: Operation now in progress
$ launchctl remove ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
As a result when I run brew services list I now see mysql in a better status -- mysql went from error to started and has stayed at started:
mysql started robskrob /Users/robskrob/Library/LaunchAgents/homebrew.mxcl.mysql.plist`
However, wordpress still cannot make a connection to mysql.
The new logs for mysql from System.log are:
Jan 18 14:36:05 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql): This service is defined to be constantly running and is inherently inefficient.
Jan 18 14:48:44 Roberts-MBP-4 com.apple.xpc.launchd[1] (homebrew.mxcl.mysql): This service is defined to be constantly running and is inherently inefficient.
