search

mailwatch / MailWatch

MailWatch for MailScanner is a web-based front-end to MailScanner
http://mailwatch.org/
GNU General Public License v2.0
118 stars 66 forks source link

MailWatch unable to connect to database after upgrading to MariaDB 10.6.5 #1246

Closed hifihedgehog closed 3 months ago

hifihedgehog commented 2 years ago

Issue summary

MailWatch is unable to connect to the MariaDB database, emitting the error. As a workaround, you can edit the '/usr/share/mysql/charsets/Index.xml' file and make a copy of "utf8mb3" block and change this new block's charset name to "utf8" as described here (https://jira.mariadb.org/browse/MDEV-26863). It appears that RoundCube has fixed a similar issue in a later release (also noted here, in the comment section at bottom: https://jira.mariadb.org/browse/MDEV-26863).

Steps to reproduce

  1. Upgrade to MariaDB 10.6.5
  2. Run "MailScanner --lint" in terminal

Expected result

No error should be emitted and it should connect to the MariaDB database.

Actual result

Error is generated stating "Character set 'utf8' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file" and it cannot connect to the MariaDB database.

Installation

Version and method

Server configuration

Client configuration

IKatsu commented 2 years ago

I ran into this as well after noticing the MariaDB 10.2 repo was gone and subsequently trying to upgrade to 10.9. (ended up rolling back to 10.2 via the mariadb archive repo) I don't know enough about perl to create a patch but a new future proof 'utf8' standard should probably be added depending on mariadb version ?

endelwar commented 2 years ago

Due to the backward compatibility promise of 1.2 branch MailWatch must support old version of MySQL and their choice to support an incomplete set of UTF8 back in the days. ATM the workaround reported by OP is a quick fix of this issue that doesn't nedd a complete rewrite of the UTF8 stack.

Fonant commented 6 months ago

The workaround of copying the utf8mb3 block and calling it utf8 worked here, although the utf8 block seems to need to be after the utf8mb3 one.

UPDATE: this seems to break mysqldump backups.

UPDATE: removed the duplicated section in /usr/share/mysql/charsets/Index.xml, and instead used phpMyAdmin to change the charset of the mailwatch/mailscanner database to be utf8mb4_unicode_ci. That seems to also fix the connection problem.

UPDATE: No it doesn't... Fixed by changing mysql_enable_utf8 to mysql_enable_utf8mb4 in /usr/share/MailScanner/perl/custom/MailWatch.pm.

endelwar commented 6 months ago

We should consider migrating to DBD::MariaDB in perl module to fully support MariaDB and utfmb4, but extensive tests needs to be done to assure compatibility with MySQL 5.7 and 8.x

UPDATE: No it doesn't... Fixed by changing mysql_enable_utf8 to mysql_enable_utf8mb4 in /usr/share/MailScanner/perl/custom/MailWatch.pm.

mysql_enable_utf8 vs mysql_enable_utf8mb4 in DBD:mysql is needed to support MySQL < 5.5.3 (DBD:MariaDB threats everything as UFT8 AFAIK), should we bump minimum MySQL version to 5.7?

shawniverson commented 6 months ago

@endelwar I think it is reasonable to bump MySQL minimum to 5.7. Even 5.7 is now EOL.

endelwar commented 6 months ago

I've created a draft PR to begin tests with DBD:MariaDB https://github.com/mailwatch/MailWatch/pull/1300

shawniverson commented 6 months ago

I'll deploy this change to my instance running MariaDB and see about setting up some instances with various versions of MariaDB and MySQL.