Hello everyone, After logging into the admin panel, I was met with a blank white page, and the /admin route wasn’t working. However, I could access /debug, where I found the following error in the Mailcow UI: ["mysql_error",{"errorInfo":["HY000",1267,"Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='"]}] This issue appeared suddenly, and I haven’t made any changes to Mailcow for months. After some research, I tried changing the collation settings for the database and tables to utf8mb4_unicode_ci, but that didn’t resolve the issue. Neither switching everything to utf8mb4_unicode_ci nor utf8mb4_general_ci worked. As a last resort, I modified the my.conf file, changing the line: from collation-server = utf8mb4_unicode_ci to collation-server = utf8mb4_general_ci if(window.hljsLoader && !document.currentScript.parentNode.hasAttribute('data-s9e-livepreview-onupdate')) { window.hljsLoader.highlightBlocks(document.currentScript.parentNode); } This made the UI more responsive, but now Postfix is throwing errors such as: 27.09.2024, 12:53:04 warning: mysql:/opt/postfix/conf/sql/mysql_virtual_alias_domain_maps.cf: query failed: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' And the Mailcow UI log has slightly changed to: ["mysql_error",{"errorInfo":["HY000",1267,"Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='"]}] I feel like I’m making things worse, so before I dig an even deeper hole for my Mailcow instance, I’d like to ask for your help. Any advice would be greatly appreciated! 😊 German and English are both welcome!

Okay, I wrote a custom script which will just update batchwise by copying the current imagetags from the docker compose on github. But do you guys have any clue how to fix this mismatch? [unknown] I looked up the commit from renovate and mariadb has been on 11.x for 4 month and it worked flawlessly until last week, that is why I did not combine those facts [unknown] I looked up the commit from renovate and mariadb has been on 11.x for 4 month and it worked flawlessly until last week, that is why I did not combine those facts [unknown] I looked up the commit from renovate and mariadb has been on 11.x for 4 month and it worked flawlessly until last week, that is why I did not combine those facts [unknown] I looked up the commit from renovate and mariadb has been on 11.x for 4 month and it worked flawlessly until last week, that is why I did not combine those facts [unknown] I looked up the commit from renovate and mariadb has been on 11.x for 4 month and it worked flawlessly until last week, that is why I did not combine those facts

Sudden collation mismatches in mariadb

ck98

Hello everyone,

After logging into the admin panel, I was met with a blank white page, and the /admin route wasn’t working. However, I could access /debug, where I found the following error in the Mailcow UI:

["mysql_error",{"errorInfo":["HY000",1267,"Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='"]}]

This issue appeared suddenly, and I haven’t made any changes to Mailcow for months.

After some research, I tried changing the collation settings for the database and tables to utf8mb4_unicode_ci, but that didn’t resolve the issue. Neither switching everything to utf8mb4_unicode_ci nor utf8mb4_general_ci worked. As a last resort, I modified the my.conf file, changing the line:

from

collation-server = utf8mb4_unicode_ci

to

collation-server = utf8mb4_general_ci

This made the UI more responsive, but now Postfix is throwing errors such as:

27.09.2024, 12:53:04 warning: mysql:/opt/postfix/conf/sql/mysql_virtual_alias_domain_maps.cf: query failed: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

And the Mailcow UI log has slightly changed to:

["mysql_error",{"errorInfo":["HY000",1267,"Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='"]}]

I feel like I’m making things worse, so before I dig an even deeper hole for my Mailcow instance, I’d like to ask for your help. Any advice would be greatly appreciated! 😊

German and English are both welcome!

DocFraggle

ck98 I haven’t made any changes to Mailcow for months

So you didn’t update your mailcow either? I suggest you update to the latest version and check how things come along

This is the only match in GitHub:

mailcow/mailcow-dockerizedblame/master/data/Dockerfiles/dovecot/quarantine_notify.py#L52

DocFraggle

Just for clarification: do you use an external database or the container?

mailcow/mailcow-dockerizedblob/master/data/conf/mysql/my.cnf#L4

states that utf8mb4_unicode_ci should be the one…

And did you see which table used utf8mb4_general_ci beforehand?

ck98

I’m using the default compose file from Mailcow, which includes the provided MySQL instance.

Yes, the my.cnf file is set to utf8mb4_unicode_ci, and that was also the configuration on my end. However, the collation mismatch occurred without any changes on my part, and I’m confused as to why some tables switched to general. The UI was completely blocked only my change to utf8mb4_general_ci (in the my.cnf file) made the UI somewhat responsive.

I can’t recall exactly, but I came across a discussion online where someone recommended switching all collations to utf8mb4_unicode_ci. I followed that advice, but unfortunately, it didn’t resolve the issue.

DocFraggle

ck98 I’m using the default compose file from Mailcow, which includes the provided MySQL instance.

You never told us you are using renovate! What exactly did you configure for renovate to update? The docker-compose.yaml?

ck98

I’ve aligned all version to the one from the docker-compose, everything is fine except the mysql since my renovate bot already upgraded that once and a downgrade seems unpossible. But I also would be suprised when a newer mariadb version changes any collation, also the newer mysql was running for over 3 months.

I’m actually pretty clueless right now.

ck98

I’m sorry but I just could not think that updating images would make collation mismatch issues.

Yes the renovate is updating the images in the docker-compose.yml file.

esackbauer

ck98 I just could not think that updating images would make collation mismatch issues.

This is the reason why you NEVER should update containers independently from the docker compose stack!
Let the docker compose do the job, and mailcow takes care of it with the update.sh
Never use renovate or watchtower for anything that is deployed with docker-compose.

DocFraggle

But why would you do that? Each mailcow release is tested with and depends on the exact versions set in docker-compose.yaml…

I guess the update of mariadb from 10.5 to 11.x broke something, as I can find quite some issues with changed collation via Google.

ck98

Okay, I wrote a custom script which will just update batchwise by copying the current imagetags from the docker compose on github. But do you guys have any clue how to fix this mismatch?

[unknown] I looked up the commit from renovate and mariadb has been on 11.x for 4 month and it worked flawlessly until last week, that is why I did not combine those facts

DocFraggle

Do you have a regular backup in place? This way you could find out which container version was updated by renovate last week
Did you check every table and the columns within the tables for the current collation?