Support

Hi. I tried to restore a site to a staging subdomain in order to test some changes, but the system replied with the following (seems to affect the Sobipro third-party XTDir search module):


Database server error reply:
ErrNo #1267

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
SQL=CREATE VIEW `rfx4t_xtdir_promosection_dynamic_view` AS select `t`.`section` AS `section_id`,`t`.`id` AS `entry_id`,`promo`.`ordering` AS `ordering`,`promo`.`promotype_id` AS `promotype_id`,`promo`.`tiertype_id` AS `tiertype_id`,NULL AS `promoentry_id`,NULL AS `promoorder_id` from (((`rfx4t_xtdir_trees` `t` join `rfx4t_sobipro_object` `o` on(((`t`.`id` = `o`.`id`) and (`o`.`oType` = 'entry')))) join `rfx4t_xtdir_promotions` `promo` on(((`t`.`section` = `promo`.`section_id`) and (`promo`.`category_id` = 0) and (`promo`.`published` = 1) and (`promo`.`tiertype_id` = 'D')))) left join `rfx4t_xtdir_promosection_paid_view` `psp` on((`t`.`id` = `psp`.`entry_id`))) where (isnull(`psp`.`entry_id`) and ((`t`.`id` = (select `opt`.`sid` from `rfx4t_sobipro_field_option_selected` `opt` where ((`opt`.`sid` = `t`.`id`) and (`opt`.`fid` = `promo`.`field_id`) and (convert(`opt`.`optValue` using utf8mb4) = `promo`.`field_value`)) limit 1)) or (`t`.`id` = (select `d`.`sid` from `rfx4t_sobipro_field_data` `d` where ((`d`.`sid` = `t`.`id`) and (`d`.`fid` = `promo`.`field_id`) and (convert(`d`.`baseData` using utf8mb4) = `promo`.`field_value`)) limit 1))))


Raw query text:

CREATE VIEW `#__xtdir_promosection_dynamic_view` AS select `t`.`section` AS `section_id`,`t`.`id` AS `entry_id`,`promo`.`ordering` AS `ordering`,`promo`.`promotype_id` AS `promotype_id`,`promo`.`tiertype_id` AS `tiertype_id`,NULL AS `promoentry_id`,NULL AS `promoorder_id` from (((`#__xtdir_trees` `t` join `#__sobipro_object` `o` on(((`t`.`id` = `o`.`id`) and (`o`.`oType` = 'entry')))) join `#__xtdir_promotions` `promo` on(((`t`.`section` = `promo`.`section_id`) and (`promo`.`category_id` = 0) and (`promo`.`published` = 1) and (`promo`.`tiertype_id` = 'D')))) left join `#__xtdir_promosection_paid_view` `psp` on((`t`.`id` = `psp`.`entry_id`))) where (isnull(`psp`.`entry_id`) and ((`t`.`id` = (select `opt`.`sid` from `#__sobipro_field_option_selected` `opt` where ((`opt`.`sid` = `t`.`id`) and (`opt`.`fid` = `promo`.`field_id`) and (convert(`opt`.`optValue` using utf8mb4) = `promo`.`field_value`)) limit 1)) or (`t`.`id` = (select `d`.`sid` from `#__sobipro_field_data` `d` where ((`d`.`sid` = `t`.`id`) and (`d`.`fid` = `promo`.`field_id`) and (convert(`d`.`baseData` using utf8mb4) = `promo`.`field_value`)) limit 1))))


I read online that using Admintools to change the database collation to “UTF-8 Multibyte” will solve this issue. I have AdminTools installed too.

Is this the solution?

I've never done this before and fear I may break the site and not be able to restore it from backups!

What do you advise?

Thank you

Btw, I read this:
https://www.akeebabackup.com/support/admin-tools/Ticket/28745-admin-tools-and-database-collation-error.html

Besides your advise on my issue, can you also help to suggest the change I can make in the sql export file (see the sample code I pasted earlier) so I can test it on the production site? I'll use phpmyadmin to import it. Thanks.

Davide's advice in #28745 about making sure you have a backup is good.

First, make sure you're using the most recent version to do your backup. There was some work done regarding this sort of problem, but I'm not sure if it is still in development or if it was released.

Next, it seems there is a 3rd party extension creating a view on Sobipro tables. If you can take another backup, you should fix the collation on your original database. Otherwise you will have to manually edit the SQL file to avoid such query and re-create the view from scratch.

Hi. Thanks for the response. This is what I did to resolve the issue and create a clone of the live site (in case someone else has a similar issue):

Created an Akeeba Backup of the live site (tho I know the error will prevent it from restoring). Also did a cpanel backup. Just in case these are needed later.
Downloaded the database file from Phpmyadmin. Keep it safe.
Created a copy of the database for editing purposes.
Identified the lines that cause the mixed collation error. These show up as CREATE VIEW in the error messages, but in the .sql file they start with CREATE ALGORITHM=TEMPTABLE DEFINER=. In my case, these related to the extensions: Docman and XTDir.
In Phpmyadmin, import the edited .sql file. The process should now complete without errors.
Move over the Joomla files and folders and edit the DB lines in the configuration.php file.
The website should show up. It did in my case :)
Log into the backend and reinstall Docman and XTDir or whatever extensions caused the errors.
That's it!
All seems fine on the frontend. Hopefully I did it correctly and no issue is lurking unnoticed.