Collation Error after Upgrade

[merzi]

After i install the new Version 3.3.12 and made a SQL Upgrade from 1844 over 1845 to 1846 i got an mix of collations SQL error.

Maybe the same error in #688 but i got only in the virtual List.

I got this errors in the logs:

[Mon Dec  5 18:32:29 2022] 172.172.172.172:54568 Accepted
[Mon Dec  5 18:32:29 2022] Invalid query: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' caused by SELECT `mailbox`.* , `alias`.goto , `mailbox`.password_expiry as password_expiration , `vacation`.active AS v_active , `quota2`.bytes as current
 FROM `mailbox`
 LEFT JOIN `alias` ON `mailbox`.username=`alias`.address  LEFT JOIN `vacation` ON `mailbox`.username=`vacation`.email  LEFT JOIN `quota2` ON `mailbox`.username=`quota2`.username
 WHERE  `mailbox`.domain= :domain
 ORDER BY `mailbox`.username
 LIMIT 10 OFFSET 0
[Mon Dec  5 18:32:29 2022] PHP Fatal error:  Uncaught Exception: DEBUG INFORMATION: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='<br/> Check your error_log for the failed query in /postfixadmin/functions.inc.php:1872
Stack trace:
#0 /postfixadmin/functions.inc.php(1811): db_query()
#1 /postfixadmin/public/list-virtual.php(236): db_query_all()
#2 {main}
  thrown in /postfixadmin/functions.inc.php on line 1872
[Mon Dec  5 18:32:29 2022] 172.172.172.172:54568 [500]: GET /list-virtual.php - Uncaught Exception: DEBUG INFORMATION: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='<br/> Check your error_log for the failed query in /postfixadmin/functions.inc.php:1872
Stack trace:
#0 /postfixadmin/functions.inc.php(1811): db_query()
#1 /postfixadmin/public/list-virtual.php(236): db_query_all()
#2 {main}
  thrown in /postfixadmin/functions.inc.php on line 1872
[Mon Dec  5 18:32:29 2022] 172.172.172.172:54568 Closing

[DavidGoodwin]

#327 seems relevant.

ab496bd was added in Dec 2021 just after 3.3.11 was released.

I assume if you do a show create table mailbox and show create table alias you'll see different collations specified on both.

I think upgrade_1846 probably requires something to change the quota2.username field? If you do :

show create table quota2 what do you see for the username field?

e.g. I have :

CREATE TABLE `quota2` (
  `username` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `bytes` bigint(20) NOT NULL DEFAULT 0,
  `messages` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

If you aren't using quota, then you could just drop that table and recreate it?

[merzi]

the username column at the quota 2 table had a latin1 character set and a latin1_swedish_ci collate.

I changed this on the mysql Query command line with

alter table quota2 convert to character set latin1 COLLATE latin1_general_ci;

Now it works.

Maybe it make sense to add this to the database changes.

[DavidGoodwin]

@merzi thank you for confirming, and yes!

[DavidGoodwin]

This will be released in the upcoming 3.3.13 release

See also https://github.com/postfixadmin/postfixadmin/commits/postfixadmin_3.3