Heads-up: MySQL default Version will switch to 8.0

SirDice

SirDice

Administrator
Staff member
Administrator
Moderator
This is important enough to also post here. I'm sure it'll still catch people by surprise.

Moin moin

MySQL 5.7 is already for some time EOL. For this reason MySQL 5.6 will
be deleted from the ports on 6/30/2023 and MySQL 5.7 on 12/31/2023.
I have requested an exp-run today to change the default version to 8.0
as exp-run [1].
It would be good to make sure when updating the ports that used MySQL
that they are also compatible with MySQL 8.0.

Cheers
Jochen

[1] https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=268586
Click to expand...

 
If you want to stay on MySQL 5.7 (which will be possible until the actual removal of the port) you will have to build from ports (or set up your own repository) and set DEFAULT_VERSIONS+= mysql=5.7 in make.conf(5).
 
Some observations from a migration of a database from 5.7 to 8.0.

Certain options in my.cnf may throw errors and would need to be removed or else MySQL is going to refuse to start, notably various query_cache_* settings. Check the error log, MySQL will complain loudly if you have any options that need to be removed or changed.

mysql_upgrade(1) doesn't need to be run after the upgrade. The migration is now automagically done by MySQL server itself the first time it starts.
 
SirDice said:
MySQL is going to refuse to start, notably various query_cache_* settings
Click to expand...
That one caught me (when testing) and also there were some encoding issues with PHP - MySQL 8.0 defaults to utf8mb4 on the connection which may cause you issues.

That utf8mb4 change also affects indexes - things that were 1 byte can now be 4 bytes so you might hit index column length limitations.

If haven't started already then you should start now - checking your databases against MySQL 8.
 
Some of my notes during 5.7 to 8.0 upgrade to hundreds of servers:

Don't forget to take backups:

mysqldump --login-path=local -uroot --add-drop-table --routines --events --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats --force > /home/mysql-before-upgrade.sql

And for extra safety in case upgrade fails and you have to "rollback" to the older version (I wasn't using ZFS that time as it would be faster to use a snapshot):

mysql --login-path=local -uroot -e "SET GLOBAL innodb_fast_shutdown=0"
rsync --delete -a -v /var/db/mysql/ /home/mysql-before-upgrade/
service mysql-server stop
rsync --delete -a -v /var/db/mysql/ /home/mysql-before-upgrade/

In the above commands we run 2 times rsync (first one while MySQL is running) to minimise the downtime if you have big databases or slow disks.

Remove old version (it will remove mysql57-server too):

yes | pkg del mysql57-client

Replace old settings with new settings:

fetch -o /usr/local/etc/mysql/my.cnf https://example.com/mysql/my.cnf

I had build the new version to another server because portupgrade can take a lot of time to rebuild MySQL:

cd /tmp
fetch https://example.com/mysql/mysql80-server-8.0.28_2.pkg
fetch https://example.com/mysql/mysql80-client-8.0.28_1.pkg
pkg add mysql80-client-8.0.28_1.pkg
pkg add mysql80-server-8.0.28_2.pkg

Start MySQL server (it will take some time to finish so you can use tail -f /var/db/mysql/*.err to check how it proceeds):

service mysql-server start
 
I migrated all instances of it to 8.0 almost two weeks ago.
There are a few other options, in addition to *query_cache, that prevent it from running. Keep an eye on logs. Quite a smooth upgrade now compared to a year or so ago.
 
Awesome, please add more information if you've already done a 5.7 to 8.0 migration. This information will be useful for anyone using the standard FreeBSD repositories. The switching of the default hasn't happened yet, I'll try to keep an eye on it and report on when it happens. I'm assuming this will only be done in latest. If you're on quarterly packages (the default on all -RELEASE versions) I don't expect this change to happen until 2023Q2.

In Dutch we have an adage; "een gewaarschuwd mens telt for twee", roughly translated "A warned person counts as two". A better translation would be; "forewarned is forearmed".
 
I say good luck to you.

MySQL 8.0 is a wild ride. New features are being added continuously in patch releases - it's "evergreen" - no more semantic versioning here.

Additionally, downgrading to a previous patch release is no longer supported. Part of the time it might still work, but not e.g. if the file format has been modified. So if you're stuck on a new bug, which could happen when new features are added willy-nilly - there is no way to downgrade unless you have a logical backup (mysqldump, mydumper etc) that you can restore. But then you'll obviously lose data/changes since the backup was done.

MariaDB still follows the sane approach of supporting downgrades within a minor release (e.g. within 10.6), and new features are only very rarely added in patch releases.
 
dbdemon said:
I say good luck to you.

MySQL 8.0 is a wild ride. New features are being added continuously in patch releases - it's "evergreen" - no more semantic versioning here.

Additionally, downgrading to a previous patch release is no longer supported. Part of the time it might still work, but not e.g. if the file format has been modified. So if you're stuck on a new bug, which could happen when new features are added willy-nilly - there is no way to downgrade unless you have a logical backup (mysqldump, mydumper etc) that you can restore. But then you'll obviously lose data/changes since the backup was done.

MariaDB still follows the sane approach of supporting downgrades within a minor release (e.g. within 10.6), and new features are only very rarely added in patch releases.
Click to expand...
Sadly, some pkgs still only support 5.7 e.g. Zoneminder. It was the price to pay for the upgrade.
 
Something out of real life, that caught us on a hop some 2 years ago, when upgrading from 5.7 to 8.0:
Beware that 8.0 has introduced new keywords!

The particular case causing us some nosebleeds:
A Frontend-Software was reading/writing to a table called "groups", which worked without issue on 5.7
On 8.0 the Software crashed everytime.

Root cause: The dev didn't escape the table-names in his queries.

Bottom line: Check your queries in regards to newly introduced keywords
 
If someone still uses Joomla 1.5 this command will make it work:

sed -i "" -e "s|AS groups|AS `groups`|g" ./components/com_content/models/category.php ./components/com_content/models/archive.php ./components/com_content/models/article.php ./components/com_content/models/frontpage.php ./components/com_content/models/section.php
 
Datapanic said:
What do you think will happen with the default version for databases/percona57-server? There is no percona80 in the ports.
Click to expand...
This page (scroll down a bit) appears to say Persona Server for MySQL 5.7 will be EOL on 1. October this year. So I assume the FreeBSD package/port will remain available at least until then.

Percona Software Support Lifecycle Overview

This document is intended to clarify the impact of the Percona Release Lifecycle Overview and the Percona Services Lifecycle Policy on software which Percona produces and the Linux-based operating system platforms which we provide services and software builds for.
www.percona.com www.percona.com
 
Regarding the claim that "MySQL 5.7 is already for some time EOL":

I've been trying to find a source for this, because if true then surely there would have been more people talking about it and being upset?

It turns out the people on the mailing list quoted in the OP were of course talking specifically about the FreeBSD binary versions provided by Oracle (via the FreeBSD ports). According to this page:

October 14, 2019

Support EOL for MySQL 5.7 on FreeBSD​

Due to very low demand, MySQL has stopped development and support for MySQL 5.7 on FreeBSD. Users of FreeBSD are requested to upgrade to recent versions of MySQL. Source and binaries for previously released versions will continue to be available from the archives.
Click to expand...

For Linux it is not officially EOL until October 2023. Indeed a patch release - 5.7.41 - was released only last month (January 2023).

I assume if you really wanted you could still build your own FreeBSD MySQL 5.7 from the same source code.
 
dbdemon said:
For Linux it is not officially EOL until October 2023. Indeed a patch release - 5.7.41 - was released only last month (January 2023).

I assume if you really wanted you could still build your own FreeBSD MySQL 5.7 from the same source code.
Click to expand...
Look at the date when the 5.7 port will get removed. That's planned for 23 December 2023. It's the default that gets changed some time soon, the databases/mysql57-server port will continue to be available until December 2023. Then check post #2.
 
Ok, change was made 10 days ago, it has probably trickled down to the package repositories now too.

ports - FreeBSD ports tree

cgit.freebsd.org cgit.freebsd.org

Note that only latest has changed, quarterly is still on 5.7.
 
Here are some (all?) of the changes you must make to the "sample" my.cnf file, which, as
shipped, is broken. (This on a FreeBSD 12.3 system, though I don't think it matters.)

These are unknown: comment them out:
query_cache_type query_cache_size

This is "deprecated" but the log messages will tell you what value to use for the new variable:
innodb_log_file_size <- deprecated
innodb_redo_log_capacity = <new value from log file>

Example:
My innodb_log_file_size was 256M, that's 268435456, multiply by 2, and you get 536870912, which
is the recommended value. My changes are:

# innodb_log_file_size = 256M innodb_redo_log_capacity = 536870912

Warnings about deprecated variables:

The syntax 'slave_load_tmpdir' is deprecated and will be removed in a future release. Please use replica_load_tmpdir instead. The syntax '--master-info-repository' is deprecated and will be removed in a future release. The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release. The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead. 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
To fix those warnings, you can just comment (or delete) them from my.cnf:

#master-info-repository #relay-log-info-repository #skip-symbolic-links

Fix for 'expire-logs-days':

# expire_logs_days = 30 binlog_expire_logs_seconds = 2592000 <- 30 * 24 * 60 * 60

Bill Dudley
 
You must log in or register to reply here.
Back
Top