search

ZoneMinder / zoneminder

ZoneMinder is a free, open source Closed-circuit television software application developed for Linux which supports IP, USB and Analog cameras.
http://www.zoneminder.com/
GNU General Public License v2.0
5.13k stars 1.22k forks source link

Character set 'utf8' is not a compiled character set ... #3514

Closed Rayn0r closed 1 year ago

Rayn0r commented 2 years ago

I'm running Ubuntu 18.04 x86_64 and have installed Zoneminder (1.37.17~20220626221127-bionic) via iconnor's ppa. Ever since upgrading to MariaDB 10.6, Zoneminder is not able to connect to the DB anymore. The error message reads as follows:

Character set 'utf8' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
DBI connect('database=zm;host=localhost','zmuser',...) failed: Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
Character set 'utf8' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
DBI connect('database=zm;host=localhost','zmuser',...) failed: Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
06/27/2022 07:20:26.849032 zmupdate[25710].ERR [ZoneMinder::Database:114] [Error reconnecting to db: errstr:Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) error val:]
06/27/2022 07:20:26.849264 zmupdate[25710].ERR [ZoneMinder::Database:114] [Error reconnecting to db: errstr:Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) error val:]
Character set 'utf8' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
DBI connect('database=zm;host=localhost','zmuser',...) failed: Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
06/27/2022 07:20:26.849727 zmupdate[25710].ERR [ZoneMinder::Database:114] [Error reconnecting to db: errstr:Can't initialize character set utf8 (path: /usr/share/mysql/charsets/) error val:]
Unable to connect to DB. ZM Cannot continue.
BEGIN failed--compilation aborted at /usr/share/perl5/ZoneMinder/Config.pm line 150.
Compilation failed in require at /usr/bin/zmupdate.pl line 75.
BEGIN failed--compilation aborted at /usr/bin/zmupdate.pl line 75.
Error updating db.

I tried setting the default-character-set=utf8 inside the [client] section for MariaDB as well as character-set-server = utf8 for [mysqld], but that did not solve the problem. I can add this:

<charset name="utf8">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
  <collation name="utf8_general_ci"     id="33">
   <flag>primary</flag>
   <flag>compiled</flag>
  </collation>
  <collation name="utf8_bin"            id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>

to /usr/share/mysql/charsets/Index.xml but it gets overwritten on each MariaDB upgrade. Besides that, it collides with an existing entry inside the Index.xml that looks like this:

<charset name="utf8mb3">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
  <collation name="utf8mb3_general_ci"  id="33">
   <flag>primary</flag>
   <flag>compiled</flag>
  </collation>
  <collation name="utf8mb3_bin"     id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

How do I cope with the situation correctly?

Thank you very much in advance.

welcome[bot] commented 2 years ago

Thanks for opening your first issue here! Just a reminder, this forum is for Bug Reports only. Be sure to follow the issue template!

MoHf7f4 commented 2 years ago

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

I think it's a poor fix. I think this is caused because you're using the official mariadb install and not a distro one. It look like mariadb removed automatically mapping utf8 to one of the higher utf8s and has no plans to change that back for compatibility reasons.

MoHf7f4 commented 2 years ago

I'm guessing this was introduced in 1.36.13, there is a comment about adding utf8 for Chinese characters.

utf8 was an alias for utf8mb3. Newer release wants you to explicitly use utf8mb3 (or 4).

MoHf7f4 commented 2 years ago

I think I've gotten to the root of this. Whatever code zoneminder is using to parse that xml for utf8 it's ignoring the alias attributes.

UTF-8 as a charset has been deprecated by mysql and mariadb. So they'll need to get it changed before folks migrate to them.

I also tried to find a way to get zoneminder pointed at a different charset folder with no luck.

I used this, made a backup and will overwrite it whenever I upgrade.

<charset name="utf8">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
  <alias>utf8mb3</alias>
  <collation name="utf8mb3_general_ci"  id="33">
   <flag>primary</flag>
   <flag>compiled</flag>
  </collation>
  <collation name="utf8mb3_bin"         id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>
connortechnology commented 2 years ago

I wonder if it should just be mysql_enable_utf8mb4 instead of mysql_enable_utf8

MoHf7f4 commented 2 years ago

I'm not sure. When you look at the charset files there is no utf8mb4. I'm trying to make sense of that. Mysql seems to suggest utf8mb4 (saw something where utf8mb3 would be deprecated), and MariaDB utf8mb3.

I don't know how that driver handles it. There's a line where those charsets changed during the versions. MariaDB 10.8 has utf8mb3 with utf8 as the alias. 10.5 only has utf-8/utf8 with no mention of utf8mb3 or utf8mb4.

I've been looking through mysql and mariadb changelogs and docs trying to figure out when this was planned. Installed a couple different mysql and mariadbs on some test vms.

On Fri, Jul 22, 2022, 4:00 PM Isaac Connor @.***> wrote:

I wonder if it should just be mysql_enable_utf8mb4 instead of mysql_enable_utf8

— Reply to this email directly, view it on GitHub https://github.com/ZoneMinder/zoneminder/issues/3514#issuecomment-1192931387, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEIA5XSMG5NNH7JOL5HRBQTVVMDX3ANCNFSM5Z6SYMYA . You are receiving this because you commented.Message ID: @.***>

dvsingletary commented 2 years ago

I wonder if it should just be mysql_enable_utf8mb4 instead of mysql_enable_utf8

mysql_enable_utf8mb4 is a supported option (https://metacpan.org/pod/DBD::mysql) - I changed the attribute in ZoneMinder/Database.pm:107 from mysql_enable_utf8 to mysql_enable_utf8mb4 and it works.

connortechnology commented 2 years ago

I made this change in a16e7aad62116a0b4909079b255af52ee062438d to master and have cherry-picked it to release-1.36. Will be included in 1.36.32.