Normalizing collation

[musicmaster]

I am moving a shop from PS 1.6 to a new TB installation. When I try to copy the content of the ps_search_word table I get a duplicate key problem. 

The problem is related to the collation. In the old shop it was utf8_general_ci. In the new one it is utf8mb4_unicode_ci. The problem is to be in the unicode vs general part (copying to utf8mb4_general_ci works ok). As I understand it some data normalization is needed to solve this. But how do I do that?

[datakick]

How about restoring data to utf8mb4_general_ci, and then convert table to utf8mb4_unicode_ci (using core updater or manually)?

[musicmaster]

When in Phpmyadmin I change utf8mb4_general_ci into utf8mb4_unicode_ci for the column I get a unique key error message.

When I use Core Updater it doesn't show any reaction to my click on the "apply fix" button. When i look in the browser console I see that the system returned a "success" response. However, the warning is still in its list.

Edited December 14, 2022 by musicmaster

[datakick]

Not sure. You can try to select/group from the table with collation, that should show you offending words that map into the same key in utf8mb4_unicode_ci, but not in utf8mb4_general_ci

SELECT id_shop, id_lang, `word` COLLATE utf8mb4_unicode_ci, COUNT(*), GROUP_CONCAT(id_word) as duplicate_ids
FROM ps_search_word
GROUP BY id_shop, id_lang, `word` COLLATE utf8mb4_unicode_ci 
HAVING COUNT(*) > 1;

 

[datakick]

Also, @musicmaster, could you send me the sql script to populate this ps_search_word so I can try to reproduce this issue, and test how coreupdater handles the situation?

[janoo]

If I understand right, problem lies in converting data to TB collation.
For converting I use script developed for Wordpress plugin, but working also as standalone file:
https://interconnectit.com/search-and-replace-for-wordpress-databases/
https://github.com/interconnectit/Search-Replace-DB

I had never any issue with using it. It also helps to search and replace data records, and you can run it in "demo" mode without any changes, just see logs.
Please don´t say it´s old  🙂 it´s not necessary upload something what works reliably already