Database error 1267 while doing query Illegal mix of collations

I migrated from phplist 2.10.5 to the latest (3.0.12)

I’m sending out emails now after a few issues, which were resolved due to the kind help in this forum.

Looking a bit deeper in the log I notice this error:

Database error 1267 while doing query Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
Sql error select id from phplist_user_user where email = “Елена”

(PS. I edited the email address, it is not the original anymore, for obvious reasons)

I take it this is the result of the conversion to UTF8.

So I figured I delete this address from the database, but when going through the UI, to look up the address I also run into the same issue: Database error 1267 while doing query Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for
operation ‘like’

My question, what can I do to fix or otherwise remove this type of addresses in the database ?
And will they cause problems during sending (other than the warning in a log file) ?

Access your phpMyAdmin, find your phpList database, MAKE A BACKUP. Then select Operations, then look for the Collation drop down, make sure it’s set to latin1_swedish_ci and cklck the Go button below.

This will reset your database to the one setting and should resolve the error message you are receiving.

Thanks, I shall do that once current email has been sent

1 Like

I checked, it is latin1_swedish_ci and I clicked Go anyway, which appeared to be successful, but the same error still exists when I do the same operations.

I could change it to something else and then back to latin1_swedish_ci but I’m scared I may loose data this way.

I’m also confused as to how the text data is stored in the database ?
Does this mean the names are stored in latin1_swedish_ci and not in a unicode format (e.g. utf8) ?
This is somewhat related to my other post:
I want to make sure I can properly store ‘exotic’ names as well.
With 2.x I could not import utf8 lists successfully, so I always imported ascii formatted text.
I wonder now if this was because of certain settings, or because of 2.x vs. 3.x (mind you I haven’t tried this yet with version 3.x)

The collation refers to the ordering of text fields not to the actual content of them. The collation can apply at several levels - database, table and field. Changing the collation at one level will not automatically change the lower levels, it is only a default for when new tables or fields are created.

From your original error message you need to look at the collation for the email field on the phplist_user_user table and change it to utf8_general_ci.

The migration to phplist 3 should have taken care of this so I guess that there might be a bug in the conversion process.

That seems to have done it.
When I now do a search for that email address, there is no error, but I can’t find the address either, which is of course also not ideal. Not sure how to get to it now, or what to look for.

[EDIT] I may have deleted the entry … I forgot.

I just tried to add the email address to the database and got the same error again.
So I don’t know anymore …
PS. It was also immediately blacklisted as I did this ??

Was this issue resolved because I have it in version 3.2.1 ?

Sorry, I missed your message somehow.
I haven’t looked at it in a while and I haven’t upgraded to a later version.
As far as I know the problem is still there yes !

I just updated from 3.0 to 3.3 and I have this issue. More specifically, I see this error on the web page when processing bounces. encoding is set to cp1252 West European and collation is set to latin1_swedish_ci

In Sequel Pro GUI app on mac, utf8_general_ci is not an option in the Collation popup. I imagine this is because the encoding is not UTF8.

Please advise what I should do to fix.


I believe this should still work @swwdug

Thank you for the speedy reply.
So, you are suggesting that in the email field I change both the encoding to UTF8 and the collation to utf8_general_ci?

If that works for you, then okay. My suggestion was to update the whole database so if there are any other incorrect fields set, this would do a global change.

BUT MAKE A BACKUP first, just in case, that way you can simply restore the db from your backup.

Definitely made a backup already:)
That is what I was I suspected you meant. I need to convert all the encodings at once. I don’t have myphpadmin installed, so I’ll have to use the command line to do it.