Yet another issue with mysql

Seems I had corrupted database again. Most likely because I continue to use beta releases of MacOS and have to keep updating Homebrew to keep up. But maybe not…

This time, even with recovery from timemachine backup, I still had trouble with the Aria plugin engine. It had a corruped log file that I couldn’t seem to lick.

Finally found a reference to a routine to run on all my MIA files – 

aria_chk -r *.MIA

When followed with deletion of all the aria_log.xxx files, this seems to resolve all the remaining issues with the tables. All of this predicated with…

brew services stop mariadb

Watching the log file (Mac-Pro.lan.err) found the errors and all clean now…

2020-04-05 7:47:50 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-04-05 7:47:50 0 [Note] InnoDB: Uses event mutexes
2020-04-05 7:47:50 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-04-05 7:47:50 0 [Note] InnoDB: Number of pools: 1
2020-04-05 7:47:50 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-04-05 7:47:50 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-04-05 7:47:50 0 [Note] InnoDB: Completed initialization of buffer pool
2020-04-05 7:47:50 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-04-05 7:47:50 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-04-05 7:47:50 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait …
2020-04-05 7:47:50 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-04-05 7:47:50 0 [Note] InnoDB: Waiting for purge to start
2020-04-05 7:47:50 0 [Note] InnoDB: 10.4.11 started; log sequence number 1383411434; transaction id 1238187
2020-04-05 7:47:50 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/var/mysql/ib_buffer_pool
2020-04-05 7:47:50 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-04-05 7:47:50 0 [Note] InnoDB: Buffer pool(s) load completed at 200405 7:47:50
2020-04-05 7:47:50 0 [Note] Server socket created on IP: '::'.
2020-04-05 7:47:51 0 [Note] Reading of all Master_info entries succeeded
2020-04-05 7:47:51 0 [Note] Added new Master_info '' to hash table
2020-04-05 7:47:51 0 [Note] /usr/local/opt/mariadb/bin/mysqld: ready for connections.
Version: '10.4.11-MariaDB' socket: '/tmp/mysql.sock' port: 3306 Homebrew

All golden!

Another fix…

Again necessary to fix mariadb by using TimeMachine. Log showed a bad shutdown this morning corrupted tables and couldn’t be fixed. So, back to TimeMachine and got yesterdays backup for the folder:

/usr/local/var/mysql

Then, restarted with

mysql.server start

… and all was good with the world again.

Then decided to make sure and ran the database upgrade to make sure everything looked good.

mysql_upgrade -u root -p [password]

Everything good with OK all the way down the page. Nice!!

Brew doctor says all is good!

Time Machine to the Rescue

When MariaDB went into a tailspin, I spent hours trying to understand the InnoDB error that emerged, likely because of corruption in the database. Best guess, some write error to phpservermon table due to cron function that was writing during a restart or crash.

So, trying to fix the error was fruitless and going nowhere. I couldn’t get the SQL engine to start, no matter what the process or changes to mysql.cnf. The event went to the point of MariaDB reinstall.

It was then that I realized that Time Machine might be my friend. Since this is not a high use site, recovering databases would be simple, right?

So, I headed over to /usr/local/var and, thanks to HomeBrew, eureka! Time Machine had dutifully backed up for over three weeks and beyond. Recover the MySQL folder in its entirety and everything back up and running except for one database. Not sure why on the database, but deleted everything in the offending directory and then used Sequel Pro to delete the database from the directory. Then run mysqlcheck -A and all gets OK. Reload WordPress and update to 5.3, upgrade the database and all is good with the world.

I make it sound easy, but this was a few hours work in between things trying to search the internet for solutions for naught — and trying more than a few tinkering options. So, we are updated and living high on the internet hog with Catalina 10.15.2 and Homebrew bubbling along with it.

Catalina was a struggle, but I’m here

Was difficult getting Homebrew stable on Catalina after upgrade to 10.15. My greatest hurdle was mysql (mariadb). Ended up having to delete mysql entirely and reinstall.

May have been:

  • forgot to run mysql_upgrade on previous iteration – that messed up some key tables in mysql database
  • something messed up socket and re-installation mucked this up requiring rm -rf of the socket to clean things up.
  • needed update to mariadb that I didn’t catch – still a few bugs in homebrew that I noted in updates

So, what I did:

  • got it running long enough to dump my databases to safe folder – Innodb safe mode = 2
  • deleted everything – all folders associated with mysql at all levels
  • service uninstall in brew
  • remove remaining files across system
  • service reinstall in brew
  • reset root password on newly reinstalled maraidb
  • Used Sequel Pro to return databases from sql files
  • restarted everything

And…. except for a few password issues, up and running again. Frustrating and took two days, but got it figured out.

Still may have remaining issue on wordpress and my ssl installation. I’ll have to go back and check getgrav documentation on Catalina upgrade.