Database "Illegal mix of collations" error

Summary

After upgrading from Debian 10 with MariaDB 10.3 to Debian 12 with MariaDB 10.11, Postfix began experiencing database query failures when processing emails containing special or Unicode characters. These failures caused temporary Postfix rejections (451 4.3.0 errors) of all incoming emails for 1-2 minutes.

Steps to reproduce

1. Upgrade server from Debian 10 (MariaDB 10.3) to Debian 12 (MariaDB 10.11).
2. Send or receive an email from an address containing special or Unicode characters.
3. Observe logs in /var/log/mail.log or equivalent Postfix log file.

Observed Behavior: Postfix logs an error similar to:

postfix/proxymap[1212780]: warning: mysql:/etc/postfix/mysql-virtual_transports.cf: query failed: Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
postfix/trivial-rewrite[1203903]: warning: proxy:mysql:/etc/postfix/mysql-virtual_transports.cf lookup error for "info---??????????????????????????????????----????????kefu@chsi.com.cn"
postfix/trivial-rewrite[1203903]: warning: transport_maps lookup failure

During this period, all emails receive a temporary rejection error:

451 4.3.0 Temporary lookup failure

Environment

• Server OS + version: Debian 12
• ISPConfig version: 3.2.12p1
• Postfix: 3.7.11-0+deb12u1
• MariaDB: 10.11

Proposed fix

Expected Behavior: Postfix should handle addresses containing special characters gracefully, without database collation errors or affecting the delivery of unrelated incoming messages.

Root Cause: The upgrade from MariaDB 10.3 to 10.11 introduced stricter handling of mixed collations (utf8mb3_general_ci vs. utf8mb4_general_ci), causing database query failures.

Workaround/Solution: Convert all database tables consistently to UTF-8 encoding (utf8mb4_general_ci):

ALTER TABLE `table` ENGINE=InnoDB;
ALTER TABLE `table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;