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!)

Share

Cron Job to clear Rails Cache

The rails file cache is something that keeps growing.
Here are some 'handy' snippets.

Delete all files older then 7 days. (replace -delete with -print to test it)

find ./tmp/cache/ -type f -mtime +7 -delete

Delete all empty folders. (mindepth prevents the deletion of the root folder)

find ./tmp/cache/ -type d -empty -delete -mindepth 1

The whenever.rb I use

job_type :command_in_path, "cd :path && :task :output"

every :day, at: "0:40" do
  command_in_path "find ./tmp/cache/ -type f -mtime +7 -delete"
end

every :day, at: "1:10" do
  command_in_path "find ./tmp/cache/ -type d -empty -delete -mindepth 1"
end
Share

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"
Share

FreeBSD Set Timezone and Time to chmos clock

The following commands can be used to change time zone.
When the file /etc/wall_chmos_clock exists, it wil interpret the chmod time as local time.
Else it interprets the systemtime as UTC time

cp /usr/share/zoneinfo/Europe/Amsterdam /etc/localtime
touch '/etc/wall_cmos_clock'
adjkerntz -i
Share