Broken MySQL 8 – after upgrade 8.0.22_1 -> 8.0.23

This morning, I tried to upgrade MySQL 8:

mysql80-server upgraded: 8.0.22_1 -> 8.0.23

But then after upgrading. MySQL didn't start anymore !
It was broken:

2021-02-22T06:20:33.856568Z 4 [System] [MY-013381] [Server] Server upgrade from '80022' to '80023' started.
2021-02-22T06:20:34.351272Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Create slow_log CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), user_host MEDIUMTEXT NOT NULL, query_time TIME(6) NOT NULL, lock_time TIME(6) NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMBLOB NOT NULL, thread_id BIGINT UNSIGNED NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"; ' failed with error code = 13, error message = 'Can't get stat of './mysql/slow_log.CSV' (OS errno 2 - No such file or directory)'.
2021-02-22T06:20:34.354494Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2021-02-22T06:20:34.354946Z 0 [ERROR] [MY-010119] [Server] Aborting

Googling didn't solve my issue directly. So I tried to create this missing CSV file. (in the mysql database directory)
After this error I also received a missing slog_log.CSV.
Did the same thing.

touch ./mysql/general_log.CSV
chown mysql:mysql  ./mysql/general_log.CSV
touch ./mysql/slow_log.CSV
chown mysql:mysql  ./mysql/slow_log.CSV

Then it works again!

This seems to happen if you skip a certain upgrade. (from now on these CSV files are required). And these CSV files are only created in that particular update.. (not very robust!)

FreeBSD, MySQL out of swap space

My FreeBSD server is giving a lot of swap errors. Every day the daily reporting email was filled with kernel messages like these:

my.server.com kernel log messages:
+swap_pager_getswapspace(31): failed
+swap_pager_getswapspace(32): failed
 ... and many more ...

After figuring out which process was causing this, it seemed MySQL was to blame.
That was odd, because these are the number of mysqltuner is reporting about my configuration:

[--] Physical Memory     : 8.0G
[--] Max MySQL memory    : 845.4M

Max 850 MB

After a lot of googling, reading MySQL, it seemd the performance schema could be to blame. Because is is completely stored in memory.

Now for several days in a row, I don't have the swaperrors ☺️
To disable the MySQL permance schema place the following in /usr/local/etc/mysql/my.cnf

[mysqld]
performance_schema = 0

Btw. I'm not sure if I need the performance scheme ..

⚠️ Followup a few weeks later

Unfortunately this isn't the solution. MySQL is still running out of swap space. I simply don't get it, because the memory usage should be limited

Followup more weeks later ;-)

ZFS ARC is taking a lot of memory...
Somehow MySQL checks the 'free' space and decides by itself to use swap. So the ZFS ARC memory is never released and MySQL .

To solve it limit the zfs arc memory /boot/loader.conf

vfs.zfs.arc_max="4G"

MySQL database dump without system tables

candidates=$(echo "show databases" | mysql -u root | grep -Ev "^(Database|mysql|performance_schema|sys|information_schema)$")
mysqldump -u root --databases $candidates &amp;gt; full-backup-without-system-tables-$(date +%F).sql

Installing Ruby on Rails with Mysql on Windows

No I don't like working on Windows, but unfortunately my co-workers haven't seen the light yet.

To make the rails setup a bit easier I needed to document the installation of Ruby on Rails on Windows with a Mysql database.
The description is about Ruby 1.9.?, Rails 3.0, Mysql 5.5 with the mysql2 gem.. and a lot of gems that got installed and compiled via "bundle install"

Here's a raw extract of the document I build:

1. Install MySQL

In our situation MySQL was installed. But you should install MySQL with development files. (C++ headers and Libraries).
Just download the Community Server at http://mysql.com/.

2. Install Ruby

Install the base Ruby version. Download the installer at http://rubyinstaller.org/
Run the installer and check the following items:

Add Ruby executables to your PATH
Associate .rb and .rbw files with this Ruby Installation

3. Install DevKit

Because a lot of Ruby plugins make use of Native extensions you need a compiler. For windows there's devkit to the rescue. Download it at rubyinstaller: http://rubyinstaller.org/.

Extract this to (for example) c:/devkit (this text assumes this location)
Open a command prompt: (cmd.exe) and enter the following commands.

cd c:/devkit 
ruby dk.rb init

DevKit wil probably find your ruby installation. But to make sure check the file c:/devkit/config.yml. It should contain the path of your Ruby installation:

--- 
- C:/Ruby192

This step installs (or updates) an operating_system.rb file into the relevant directory needed to implement a RubyGems pre_install hook and a devkit.rb helper library file into \lib\ruby\site_ruby. NOTE: you may need to use the –force option to update (with backup of the originals) the above mentioned files as discussed at the SFX DevKit upgrade FAQ entry.

Test the Devkit installation Confirm your Ruby environment is correctly using the DevKit by running.

gem install rdiscount --platform=ruby

RDiscount should install correctly and you should see:

Temporarily enhancing PATH to include DevKit... in the screen messages.

After this double check if it works by running:

ruby -rubygems -e "require 'rdiscount'; puts RDiscount.new('**Hello RubyInstaller**').to_html"

to confirm that the rdiscount gem is working. (This should generate HTML)
(More info: https://github.com/oneclick/rubyinstaller/wiki/Development-Kit )

4. Install Git

If you not already have installed git, please do it. You will like it!
Git is required for many plugins so you need it.

download git at http://code.google.com/p/msysgit/ (choose a version I don't know :P, the latest .exe ?? )
execute the installer, select the following options:
- check “Run Git and included tools from the windows command prompt” (Select the option with the red letters that scream "don't pick me" :P )
- checkout-as-is and commit-as-it.

5. Install Rails with the mysql2 plugin

Next install Ruby on Rails. (We don't install/generate the docs)

gem install rails --no-ri --no-rdoc

6. Install the mysql2 gem

To build mysql2 support do this: (Point the paths to your MySQL install directory!)

NOTE: When using a 64bit version of windows you probably run into issues. Thanks for mentioning Luis! (Read more about it: http://blog.mmediasys.com/2011/07/07/installing-mysql-on-windows-7-x64-and-using-ruby-with-it/ ).

gem install mysql2 -- '--with-mysql-lib="c:\Program Files\MySQL\MySQL Server 5.5\lib" --with-mysql-include="c:\Program Files\MySQL\MySQL Server 5.5\include"'

(Mysql5.1 seems to have the path: c:\Program Files\MySQL\MySQL Server 5.1\lib\opt)

Fix up Bundler (the gem management tool) to know what the mysql2 gem build settings need to be..
Else you're "bundle install" will fail when installing or updating the mysql2 gem!!

bundle config build.mysql2 '--with-mysql-lib="c:\Program Files\MySQL\MySQL Server 5.5\lib" --with-mysql-include="c:\Program Files\MySQL\MySQL Server 5.5\include"'

As soon as you start using this installation you could get errors like 'libmysql.dll not found.
To solve this copy this file from the mysql-installation/lib directory to the ruby/bin directory.

Slightly Unrelated, install the Rails app

Pull the rails app from git. (Or copy it). And run bundler install to install all gems..

cd /rails/app
bundle install

I've recieved the following error when doing this:

Resolving deltas: 100% (3434/3434), done.
Checking out files: 100% (85/85), done.
Fetching source index for http://rubygems.org/

Could not find devise-1.4.0 in any of the sources

I seems that devise was updated and the specific version in Gemfile.lock wasn't available anymore.
You can Force the installation of this updated package

bundle update devise

These were my steps to build a working rails environment under windows...
Enough of windows, Now back to my Mac :P

MySQL Concat method can make string searching case-sensitive

I have the following table-structure in the database.
An integer column for the number and a varchar as the prefix.

billings:

billing_prefix VARCHAR(200)
billing_nr     INTEGER NOT NULL
.. and more field, but unimportant for this example

The following record is in the database:

billing_prefix: FatOrders-
billing_nr    : 42

The complete billing number is represented to the client as "FatOrders-42".
Now I have a application that enables the user to search the list with a string. (on several fields)
A client searching for 'fat' results in the following query:

SELECT * FROM billings WHERE CONCAT( billing_prefix, billing_nr ) LIKE '%fat%'

No results..
This is not the behaviour I need :P

Mysql tells me this:

If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form

An simple solution is to modify the query to this one:

SELECT * FROM billings WHERE CONCAT( billing_prefix, CAST( billing_nr AS char) ) LIKE '%fat%'