JDBC Persistence (MariaDB) unable to log into MySQL-Server after Upgrade to 5.1 (wrong charset)

Add-ons Persistence Services
1

I just upgraded from OH4 to OH5.1 with a clean install of openhabian and restoring a backup. The configuration of the MariaDB persistence was present after the restore, so this is 1:1 to the working configuration of the OH4 installation.

I had no persistence strategy after the upgrade and configured a simple one. But it does not work, as the persistence is obviously unable to connect to the MySQL server on my NAS. The error is:

“Error: 1115-42000: Unknown character set: ‘utf8mb4’“, followed by “no connection to database” whenever data shall be written through the persistence.

I checked my database and it says it is uft8_general_ci coded, so the error is not that wrong.

But I cannot find any configuration for the correct charset and never needed a special configuration of the charset in OH4.

I read about problems with JDBC and “restore on startup” and disabled it without any changes. I found information that adding “?serverTimezone=Europe/Berlin“ to the server string in the configuration could help, but no changes, too.

As I directly upgraded from OH4 I did not use the upgrade thread.

And ideas?

2

Why do you use MariaDB persistence rather than MySQL persistence for MySQL Server?

3

I’m also using JDBC Persistance (MariaDB) against a MariaDB instance running on a WD nas. No issues before or after OH 5.1 so I’d say it is most likely not related to OH or the upgrade process directly, although the utf character set could have changed I suppose.

A quick google search gives this related to MariaDB version and that specific UTF error, indicating that utf8mb4 was introduced in mariadb 5.5.3 (from 2013, ie. really old).

Perhaps a stupid question but what version of mariadb are you using? For comparison I’m at Mariadb 10.5.19 with no issues.

4

Good question, easy to answer:

Short answer: It works / worked since 2022/2023 until I upgraded now.

Long answer: When I decided to use the MySQL-Server on my QNAP-NAS for persistence I tried with the MySQL Persistence without success. I spend hours and hours, ignoring several hints to use the MariaDB persistence instead, which helped other people in the same situation. Finally I gave up with the MySQL persistence and tried the “unreasoable” approach myself, and it worked instantly. So I never spent a second on the MySQL persistance since then.

I think that there is an undocumented change of the charset in the MariaDB persistance after OH4 which is incompatible with my MySQL server/database.

In the meantime I found out, that it is possible to add a charset parameter to a database URL in JDBC and tried it out with “?characterEncoding=utf8”. But it did not work and I still get the same error. Looks like utf8mb4 is added to the sting as a hard coded parameter by the persistence now. So you cannot write it over by adding the correct parameter manually.

BTW: As I do not run a MariaDB server at all, there is no version of it :wink:

5

It’s an unsupported setup. Since MariaDB is a fork of MySQL, it probably worked fine initially, but I don’t know how compatible they are today. Could you try to see if MySQL Persistence works for you today? It received many fixes and upgrades since 2023, so maybe you’d have better luck now.

Which version of MySQL are you using? Personally I’m running MySQL Persistence against MySQL Server 8.0.36.

6

My server is quite old. The version is 5.1.73. That is always the disadvantage with these NAS systems. They do not get a lot of updates on the software components and it is always risky to do a manual update besides the official packages for the system, as you never know all the dependencies with core components of the system.

7

I understand. Unfortunately that version is from 03.12.2013 (more than 12 years ago), and openHAB no longer supports versions < 8.0 - see https://dev.mysql.com/doc/relnotes/connector-j/en/news-9-0-0.html. So this setup is not supported either.

8

Then I will have to look for something completely different. The NAS is still doing a good job here and is not connected to the internet, so there is no need to buy a newer model for the moment.

Perhaps I will buy a SSD for the raspberry and use a database server there. Have to look if that all fits in my housing together with the RazBerry. Thanks for the information!

9

Don’t know if this information helps. I have always been confused by the mySQL/mariaDB. Debian always steers me toward mariaDB, but it seems to be considered mySQL. I use the mySQL Workbench 8.0 and OH jdbc looks like mysql

service.pid=“org.openhab.jdbc”
url=“jdbc:mysql://127.0.0.1:3306/rpi4datanew?serverTimezone=America/New_York”
user=“openhab”,

but the mariaDB is running in the container

services:
  mysql:
    image: mariadb:11.5.2
    network_mode: host
    restart: always
    environment:
      MYSQL_DATABASE: rpi5data
      MYSQL_ROOT_PASSWORD: xxxxxxxxxxx
      MYSQL_USER: openhab
      MYSQL_PASSWORD: xxxxxxxxxxx
      TZ: America/New_York
    volumes:
      - ./database:/var/lib/mysql
      - ./datadump:/docker-entrypoint-initdb.d
      - ./config:/etc/mysql/conf.d

Also at least for me it is running comfortably (about 20GB) on a Rpi5 together with OH, MQTT and ZUI containers, but I do have a 250GB SSD on a PCIe base.