MailWatch unable to connect to database after upgrading to MariaDB 10.6.5

[hifihedgehog]

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

• MailWatch Version: stable 1.2.18
• Install type: Zip install (CyberPanel)
• Updated from an older MailWatch or fresh install: fresh install

Server configuration

• Operation System: CentOS 7
• PHP version: 7.4
• Database: MariaDB 10.6.5
• Mail Server: Postfix
• Used web server: OpenLiteSpeed

Client configuration

• Operation System: Windows 11
• Browser: Chromium Edge

[IKatsu]

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]

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]

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]

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]

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

[endelwar]

I've created a draft PR to begin tests with DBD:MariaDB
#1300

[shawniverson]

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