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!

    Have something to say?

    Join the community by quickly registering to participate in this discussion. We'd like to see you joining our great moo-community!

    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.

      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 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?

        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.

          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 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.

            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

            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?

            No one is typing