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 = “Елена.xxxxx@yahoo.com”
(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.
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: https://discuss.phplist.org/t/bulk-clear-name-fields/658?u=ibpeter
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 ??
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 !
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?
thanks,
dug
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.
thanks!
dug