Support

Hello,

I'm wondering about using Admin Tools to fix a database collation error I've encountered. I know the Change Database Collation feature is there, and was thinking of using it. However, in the course of researching the error, I found advice against using Admin Tools to fix it.

Background - I recently restored a site backed up with Akeeba Backup (Pro) to a new location, and at the new location, then updated from J! 3.6.5 to 3.8.2, and also updated half a dozen components. Everything seems to have gone swimmingly except for the following error, which appears on both the User menu for Fields and Field Groups, and the Content menu for Fields and Field Groups (apparently it's related to com_fields):

500 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

Also, as additional background, on J!'s "System Information" screen I notice the "Database Collation" is "latin1_swedish_ci", although the "Database Connection Collation" is utf8mb4_unicode_ci.

I googled the 500 error, above, and found the following thread regarding the exact same error on the J! Issues Tracker:

https://issues.joomla.org/tracker/joomla-cms/16230

There, one person reports using Admin Tools to fix this problem, and another (seemingly knowledgeable) person advises against that, saying "Changing collation for everything is not really a solution. It fixes the problem for now, but it may appear again when in future with some Joomla schema update some new table or column is added which is joined to one of the tables from com_menu."

This individual ultimately suggests running a handful of SQL statements to change selected tables to a binary collation (these statements are reproduced at the end of this post).

I have various questions about this problem and the right (and/or easiest) way to fix it, but the only one which is really Admin Tools related is, is it OK to use the Admin Tools Change Database Collation feature to fix this problem, or should I pursue another course?

Thanks,

Sam

PS - here are the SQL statements I mentioned:

ALTER TABLE #__banners MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__categories MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__contact_details MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__content MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__languages MODIFY lang_code char(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
ALTER TABLE #__menu MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'The SEF alias of the menu item.';
ALTER TABLE #__newsfeeds MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__tags MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__ucm_content MODIFY core_alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

Hello Davide,

Thank you for the suggestion. I did that, and unfortunately, it didn't solve the issue. The results, for what that's worth, are:

Database table structure is up to date.

Database schema version (in #__schemas): 3.8.2-2017-10-14.
Update version (in #__extensions): 3.8.2.
Database driver: mysqli.
148 database changes were checked.
183 database changes did not alter table structure and were skipped.

I very much welcome any further suggestions you have ... otherwise I will post at Joomla!'s forum and/or try the SQL queries I referenced in my first post.

Thanks,

Sam

Thank you so much regarding the _bin collation ... I was planning to try that. I know enough to be able to somewhat understand why it would cause the kinds of problems you describe, but would not have thought about that. I had just posted @ Joomla! forum about it, but will not proceed now even if advised to do so ...

I'm not using Smart Search (to my knowledge - perhaps something is turned on by default? - I will look), so that shouldn't be an issue.

I think you're saying that the ideal solution is to look for fields in tables in third-party plugins (as the most likely culprits) which use utf8mb4_unicode_ci, and change only those columns' collation to utf8mb4_unicode_ci ... but if I want to be lazy, it's OK to brute force the whole works to _unicode_ci with Admin Tools. Is that correct?

Thanks again.

Davide,

Thank you ... I have spent a little time looking at table structures, and I see that while the tables associated with com_fields (which seems to be generating my error) have utf8mb4_unicode_ci collation, many other core tables (#__languages, #__categories, et al.) actually have utf8mb4_unicode_ci. I'm not sure what is going on, but I'm fairly sure I didn't do this, so I assume it's Joomla! itself. I'm wondering if the issue might go beyond third-party plugins.

So, I think I will "press the button" in Admin Tools to make all columns utf8mb4_unicode_ci, but I have one more question for you. In your reply to my original post you wrote that "... I think looking at the code, yes, you shouldn't use Admin Tools to change the database collation, since it will apply the same collation to every column, while there are some exceptions. We are working on this issue."

So, per your comment, I assume there's some kind of drawback to applying utf8mb4_unicode_ci to all (or almost all) columns, as Admin Tools will do ... Can you please tell me a bit more about what that drawback is? Will it create any issues down the road on further Joomla! updates, etc.?

Also, will Admin Tools change the database collation from "latin1_swedish_ci"? If not, should I do something about that, or is leaving that OK?

I'm sorry to have drawn out this ticket ... I am just trying to make sure I don't inadvertently cause further / worse issues.

Thanks,

Sam

Just letting you know that I did this today (used Admin Tools to set all columns to utf8mb4_unicode_ci) and then Repaired and Optimized tables, and it seems to have worked great. Thank you very much for your help with this. The database collation was also fixed, so permissions must be adequate for that. Please feel free to close the ticket.

Thanks again,

Sam