database upgraded with too new MySQL version.

[cisk]

Hi,
Even though my environment is not critical just the home lab being monitored with Zabbix, but still feel pain if I need to rebuild the database because when using the Zabbix docker container option I have upgraded MySQL with the latest version. I'm with alpine-6.4-latest. So find out that it's not supported by the newer version of MySQL 8. I have downgraded the container but the database has already been reformated. Obviously, NO BACKUPS done. Have I got any chances to recover at least configurations I'm not bothered with the monitoring history if this would be an option.
So at the moment, all looks normal, but no latest data coming in.

Fragment of Zabbix server log:

** Preparing Zabbix server
** Preparing database
** Using MYSQL_USER variable from ENV
** Using MYSQL_PASSWORD variable from ENV
********************
**** MySQL server is not available. Waiting 5 seconds...
** Database 'zabbix' already exists. Please be careful with database COLLATE!
** Table 'zabbix.dbversion' already exists.
** Preparing Zabbix server configuration file
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ListenIP": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ListenPort": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ListenBacklog": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "SourceIP": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "LogType": 'console'...updated
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "LogFile": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "LogFileSize": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "PidFile": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denie
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DebugLevel": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBHost": 'mysql'...updated
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBPort": '3306'...updated
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBSocket": ''...removed
sed: can't create temp file '/etc/zabbix/zabbix_server.confXXXXXX': Permission denied
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBName": 'zabbix'...updated


And a bit of MySQL log, to see the madness:

2023-11-09T21:25:49.196695483Z 2023-11-09 21:25:49+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.2.0-1.el8 started.
2023-11-09T21:25:49.888286956Z '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2023-11-09T21:25:50.981742697Z 2023-11-09T21:25:49.915130Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2023-11-09T21:25:50.981882538Z 2023-11-09T21:25:50.956081Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-11-09T21:25:50.981907408Z 2023-11-09T21:25:50.956170Z 0 [Warning] [MY-011069] [Server] The syntax '--character-set-client-handshake' is deprecated and will be removed in a future release.
2023-11-09T21:25:50.981929708Z 2023-11-09T21:25:50.958389Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2023-11-09T21:25:50.981952388Z 2023-11-09T21:25:50.958460Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.2.0) starting as process 1
2023-11-09T21:25:51.050292449Z 2023-11-09T21:25:51.049172Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-11-09T21:25:51.917682025Z 2023-11-09T21:25:51.917241Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-11-09T21:25:53.343410354Z 2023-11-09T21:25:53.342951Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-11-09T21:25:53.343485944Z 2023-11-09T21:25:53.343111Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-11-09T21:25:53.379103661Z 2023-11-09T21:25:53.378739Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2023-11-09T21:25:53.427692352Z 2023-11-09T21:25:53.427323Z 0 [Warning] [MY-015032] [Server] 'SET_USER_ID' (granted to 'root@%') is deprecated and will be removed in a future release.
2023-11-09T21:25:53.428284373Z 2023-11-09T21:25:53.428015Z 0 [Warning] [MY-015032] [Server] 'SET_USER_ID' (granted to 'root@localhost') is deprecated and will be removed in a future release.
2023-11-09T21:25:53.488287775Z 2023-11-09T21:25:53.487859Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-11-09T21:25:53.489370697Z 2023-11-09T21:25:53.489058Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.2.0' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2023-11-09T21:25:54.048143270Z 2023-11-09T21:25:54.047745Z 8 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-09T21:25:54.134930701Z 2023-11-09T21:25:54.134670Z 9 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-09T21:25:54.159622287Z 2023-11-09T21:25:54.159304Z 10 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-09T21:25:54.211232780Z 2023-11-09T21:25:54.210912Z 11 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-09T21:25:54.257315589Z 2023-11-09T21:25:54.256929Z 12 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-09T21:25:55.027164293Z 2023-11-09T21:25:55.026644Z 13 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'
2023-11-09T21:35:13.429071699Z 2023-11-09T21:35:13.428726Z 256 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'


Any options I have?
Thank you.

[tim.mooney]

Hi,
Even though my environment is not critical just the home lab being monitored with Zabbix, but still feel pain if I need to rebuild the database because when using the Zabbix docker container option I have upgraded MySQL with the latest version. I'm with alpine-6.4-latest. So find out that it's not supported by the newer version of MySQL 8. I have downgraded the container but the database has already been reformated. Obviously, NO BACKUPS done. Have I got any chances to recover at least configurations.

I don't really understand what your question is, but it seems like you think there's a problem when there might not be any problem at all.

So I understand that your version of MySQL got upgraded accidentally. Oops. But at least at this point, your problem is recoverable.

You next say that "it's not supported by the newer version of MySQL 8"? What's not supported? The MySQL database that got upgraded accidentally isn't supported by MySQL 8? That doesn't make any sense.

What version (exactly) did your MySQL get upgraded to? Even though the upgrade was accidental, why not just run that version of MySQL? Does Zabbix complain about the "too new" version of MySQL?

In general, with MySQL or MariaDB or Percona, the binary database and support files can only be upgraded, not downgraded. The binary files usually don't change if you do a micro-version upgrade (like from 8.0.15 to 8.0.19). It's not impossible that there would be file changes in a micro-version upgrade, but it's not common. It's much more common for there to be file changes between something like a MySQL 8.0.15 to 8.2.10 upgrade, where the minor version changes (8.0 -> 8.2). If the file format was upgraded for the database, then you can't just downgrade the packages (or container) and assume they'll be able to read the newer-than-expected file format.

Let's say you accidentally upgraded to MySQL 8.2.10 and there were binary file format changes, so installing older 8.0.x packages (or container) can't read those binary files. You still have an easy(ish) fall-back: Run the accidental 8.2.10 version long enough to do a mysqldump of all the data. If you back up the database under 8.2.10 to an SQL export, you will probably be able to import that SQL into an 8.0.x container (or packages). Just make sure you have a good SQL export (mysqldump) of everything you need before you shut down the 8.2.10 install and remove the binary database files. You can (and should) test loading the SQL export on a different system (or in a different container) before you take the step of removing the binary database files on your production system.

Before doing any of that, though, I would check to see if you can't just run the accidental MySQL version. You might not need to do any dump/load to go back to an old version, the new version might be fine.

[tim.mooney]

Hi,
Even though my environment is not critical just the home lab being monitored with Zabbix, but still feel pain if I need to rebuild the database because when using the Zabbix docker container option I have upgraded MySQL with the latest version. I'm with alpine-6.4-latest. So find out that it's not supported by the newer version of MySQL 8. I have downgraded the container but the database has already been reformated. Obviously, NO BACKUPS done. Have I got any chances to recover at least configurations.

I don't really understand what your question is, but it seems like you think there's a problem when there might not be any problem at all.

So I understand that your version of MySQL got upgraded accidentally. Oops. But at least at this point, your problem is recoverable.

You next say that "it's not supported by the newer version of MySQL 8"? What's not supported? The MySQL database that got upgraded accidentally isn't supported by MySQL 8? That doesn't make any sense.

What version (exactly) did your MySQL get upgraded to? Even though the upgrade was accidental, why not just run that version of MySQL? Does Zabbix complain about the "too new" version of MySQL?

In general, with MySQL or MariaDB or Percona, the binary database and support files can only be upgraded, not downgraded. The binary files usually don't change if you do a micro-version upgrade (like from 8.0.15 to 8.0.19). It's not impossible that there would be file changes in a micro-version upgrade, but it's not common. It's much more common for there to be file changes between something like a MySQL 8.0.15 to 8.2.10 upgrade, where the minor version changes (8.0 -> 8.2). If the file format was upgraded for the database, then you can't just downgrade the packages (or container) and assume they'll be able to read the newer-than-expected file format.

Let's say you accidentally upgraded to MySQL 8.2.10 and there were binary file format changes, so installing older 8.0.x packages (or container) can't read those binary files. You still have an easy(ish) fall-back: Run the accidental 8.2.10 version long enough to do a mysqldump of all the data. If you back up the database under 8.2.10 to an SQL export, you will probably be able to import that SQL into an 8.0.x container (or packages). Just make sure you have a good SQL export (mysqldump) of everything you need before you shut down the 8.2.10 install and remove the binary database files. You can (and should) test loading the SQL export on a different system (or in a different container) before you take the step of removing the binary database files on your production system.

Before doing any of that, though, I would check to see if you can't just run the accidental MySQL version. You might not need to do any dump/load to go back to an old version, the new version might be fine.