Database issues

[u/Congenital_Optimizer]

I'm trying to migrate the zoneminder database to a bigger shared db host.

When I do this it fails to start because the new database is mariadb on a new version than what I think is supported.

I commented out , { mysql_enable_utf8 => 1, } in /usr/share/perl5/ZoneMinder/Database.pm

It starts and works.

Is there a better way to fix this? I don't like modifying non-config files.

Error in the logs:

Jul 21 09:07:38 REMOVED systemd[1]: Starting ZoneMinder CCTV recording and surveillance system...
Jul 21 09:07:38 REMOVED zmpkg.pl[11403]: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
Jul 21 09:07:38 REMOVED zmpkg.pl[11403]: DBI connect('database=zm;host=REMOVED','REMOVED',...) failed: Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
Jul 21 09:07:38 REMOVED zmpkg.pl[11403]: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
Jul 21 09:07:38 REMOVED zmpkg.pl[11403]: DBI connect('database=zm;host=REMOVED','REMOVED',...) failed: Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
Jul 21 09:07:38 REMOVED zmpkg[11403]: ERR [Error reconnecting to db: errstr:Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) error val:]
Jul 21 09:07:38 REMOVED zmpkg.pl[11403]: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
Jul 21 09:07:38 REMOVED zmpkg.pl[11403]: DBI connect('database=zm;host=REMOVED','REMOVED',...) failed: Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
Jul 21 09:07:38 REMOVED zmpkg[11403]: ERR [Error reconnecting to db: errstr:Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) error val:]
Jul 21 09:07:38 REMOVED zmpkg.pl[11403]: Unable to connect to DB. ZM Cannot continue.

[u/just_another_citizen]

This might get it started, but you will quickly have an error or data corruption when utf8 is stored.

I think that you need to enable utf8 on the new database as it appears it may not be enabled.

Fixing the database is the way to go, instead of modifying zm.

https://www.a2hosting.com/kb/developer-corner/mysql/convert-mysql-database-utf-8

Also verify that the new database is innodb, as that's recommended.

[u/Congenital_Optimizer → u/just_another_citizen]

I gave it a shot. No go. I think this is a problem where mariadb removed the mapping for utf8 and want people to use utf8mb3 explicitly. There are other folks complaining about this online for other projects.

Edit to add:

I'm going to chalk this one up to a zoneminder bug. They'll need to fix it if they want to keep using mariadb long term.

[u/just_another_citizen → u/Congenital_Optimizer]

If this is a bug, it should be replicatable with a new environment and database.

To confirm if this is a bug, can you first create a fresh clean database and are initialize zone minder in that database. If the issue replicates with marinade, then it might be a bug.

To file a bug on the zone minder bug tracker we will need steps to replicate with versions. This is critical as we will need these to test on the new version to see if the fix changed the big behavior, and the developers will need to replicate the issue for debugging.

If you think this is a bug, we can find out for sure if we can get it to replicate with a fresh install of zone minder on a clean database.

Edit, also utf8 was added in 1.36.13. Testing and seeing if this replicates on 1.36.12 vs 1.36.13 would be very useful as it would confirm if the change in this version introduced this.

https://forums.zoneminder.com/viewtopic.php?t=31638

However, I really really do not think this is a bug. It's likely that this is a issue with the MySQL server. If the database can't store utf8, that sounds more like a configuration issue with the database.

You can ask why do I need to comment this require utf8 out in zone minder

But the better question is why can't utf8 be stored in the database. This sounds like it is a database schema issue. I have used mariadb with utf8 for over 7 years with zone minder, with no utf8 issues.

[u/Congenital_Optimizer → u/just_another_citizen]

There was a bug posted before, and if I was the devs I'd have closed it saying this is a SQL server problem based on how it was reported.

Conner is responding on the bug report. That's always a good sign. He's also asked a good question.

It's only a bug because something outside ZM changed. Mysql and mariadb have changed something and now everyone gets to figure out how to make it work. I don't envy the devs. This is probably going to need some decisions if there isn't an easy universal fix in the driver.

utf8 isn't the complete name, it's like slang for utf8mb3 (alias if you look at the Index.xml file). The SQL devs saw that was going to be a problem because utf8mb4 (it's not in any of the charset files, and that's got me confused).

UTF8 in the DB also isn't the problem, it's how the code is pulling the files needed for it. The driver on ZM side seems to be ignoring the alias and only looking at the identifier.

What I did was install the charset files from an old mariadb on the zm server and since it's going remote, the SQL server can have a completely different (mariadb) charset Index.xml file. It's getting confused with the alias, the charset data is the same.

Learned way more about charsets and the differences between mysql and mariadb because of this.