Jump to content
thirty bees forum

Normalizing collation


Recommended Posts

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?

Link to comment
Share on other sites

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 by musicmaster
Link to comment
Share on other sites

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 


Link to comment
Share on other sites

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:

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...