Exim4U General Discussion

MySQL issue with blocklists

Classic

List

Threaded

4 messages Options

Odhiambo Washington

MySQL issue with blocklists

I am using MySQL-8.x

I realized that some lookups on the blocklists table were stopping mails from being delivered.

MYSQL: query failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

So I had to comment out all blocklists lookups (my people don't even create custom ones though) pending advise from here.

Anyone dealt with this issue before, or it's only me on MySQL-8.x??

TIA

Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft.", grep ^[^#] :-)

_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users

Helmut Fritz

Re: MySQL issue with blocklists

Odhiambo,
Have you tried changing the collation of the affected tables? MySQL has some odd default collations, I have changed a lot of mine to just plain UTF8_bin across the board, and try to remember to do this whenever I set up a new database. However, I have not had issues with changing the collation after the fact, but your mileage may vary.

A couple articles on the subject:

fixing mysql collation error

stackoverflow thread

regardless, you might want to set up a separate database for testing and import your data there before trying it on your live data. of course make a backup of the database before you start anything, not sure if you can quiesce your service while you work on this but that would help prevent new entries into the database.

Hope this helps you some. Personally I have not run into this error myself (that I know of!), but the default mysql collations have always bugged me.

Helmut

On 3/26/2019 1:32 AM, Odhiambo Washington wrote:

I am using MySQL-8.x

I realized that some lookups on the blocklists table were stopping mails from being delivered.

MYSQL: query failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

So I had to comment out all blocklists lookups (my people don't even create custom ones though) pending advise from here.

Anyone dealt with this issue before, or it's only me on MySQL-8.x??

TIA

--

Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft.", grep ^[^#] :-)
_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users

_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users

Odhiambo Washington

Re: MySQL issue with blocklists

Hi Helmut,

Thank you abundantly.

I will look into this and see if it helps.

PS: This list is too silent lately. Seems guys are all contented - except myself who was late to upgrade to the latest version of exim4u :)

On Tue, 26 Mar 2019 at 20:06, Helmut Fritz <[hidden email]> wrote:

Odhiambo,
Have you tried changing the collation of the affected tables? MySQL has some odd default collations, I have changed a lot of mine to just plain UTF8_bin across the board, and try to remember to do this whenever I set up a new database. However, I have not had issues with changing the collation after the fact, but your mileage may vary.

A couple articles on the subject:

fixing mysql collation error

stackoverflow thread

regardless, you might want to set up a separate database for testing and import your data there before trying it on your live data. of course make a backup of the database before you start anything, not sure if you can quiesce your service while you work on this but that would help prevent new entries into the database.

Hope this helps you some. Personally I have not run into this error myself (that I know of!), but the default mysql collations have always bugged me.

Helmut

On 3/26/2019 1:32 AM, Odhiambo Washington wrote:
I am using MySQL-8.x

I realized that some lookups on the blocklists table were stopping mails from being delivered.

MYSQL: query failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

So I had to comment out all blocklists lookups (my people don't even create custom ones though) pending advise from here.

Anyone dealt with this issue before, or it's only me on MySQL-8.x??

TIA

--

Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft.", grep ^[^#] :-)
_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users
_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users

Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft.", grep ^[^#] :-)

_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users

Helmut Fritz

Re: MySQL issue with blocklists

i may be asking for the list's help soon. i am updating my OS soonish, and I need to see what versions of the dependencies are default. i hope not to have too many issues.

On 3/26/2019 12:51 PM, Odhiambo Washington wrote:

Hi Helmut,

Thank you abundantly.

I will look into this and see if it helps.

PS: This list is too silent lately. Seems guys are all contented - except myself who was late to upgrade to the latest version of exim4u :)
On Tue, 26 Mar 2019 at 20:06, Helmut Fritz <[hidden email]> wrote:
Odhiambo,
Have you tried changing the collation of the affected tables? MySQL has some odd default collations, I have changed a lot of mine to just plain UTF8_bin across the board, and try to remember to do this whenever I set up a new database. However, I have not had issues with changing the collation after the fact, but your mileage may vary.

A couple articles on the subject:

fixing mysql collation error

stackoverflow thread

regardless, you might want to set up a separate database for testing and import your data there before trying it on your live data. of course make a backup of the database before you start anything, not sure if you can quiesce your service while you work on this but that would help prevent new entries into the database.

Hope this helps you some. Personally I have not run into this error myself (that I know of!), but the default mysql collations have always bugged me.

Helmut

On 3/26/2019 1:32 AM, Odhiambo Washington wrote:
I am using MySQL-8.x

I realized that some lookups on the blocklists table were stopping mails from being delivered.

MYSQL: query failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

So I had to comment out all blocklists lookups (my people don't even create custom ones though) pending advise from here.

Anyone dealt with this issue before, or it's only me on MySQL-8.x??

TIA

--

Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft.", grep ^[^#] :-)
_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users
_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users
--

Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft.", grep ^[^#] :-)
_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users

_______________________________________________
users mailing list
[hidden email]
https://exim4u.org/mailman/listinfo/users