Error on changing encoding of hikashop_product table

  • Posts: 57
  • Thank you received: 1
  • Hikashop Business
2 months 2 weeks ago #340953

-- HikaShop version -- : 4.5.0
-- Joomla version -- : 3.10.8

Hello,

I'm trying to convert the encoding of hikashop_product table to utf8mb4 support emoticons through phpMyAdmin but I get the following error:
ALTER TABLE `seemy_hikashop_product` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
#1071 - Specified key was too long; max key length is 1000 bytes

What steps should I take to proceed with the conversion?

Thank you,
Yannis

Please Log in or Create an account to join the conversation.

  • Posts: 75905
  • Thank you received: 11855
  • MODERATOR
2 months 2 weeks ago #340956

Hi,

In your hikashop_product table, you have several indexes and a unique key.
By default these are:
UNIQUE KEY `product_code` (`product_code`),
KEY `product_parent_id` (`product_parent_id`),
KEY `product_main_index` (`product_type`,`product_quantity`,`product_published`)
These indexes allow MySQL to more rapidely find elements in the hikashop_product table, especially when there are hundreds of thousands or millions of entries.
As you can see, these indexes are made on specific columns of the hikashop_product column.
Out of these, 2 are varchar(255) columns: product_type and product_code
So that means that each one can have up to 255 characters.
With the default UTF8 collation on these, they go up to 3 byes per character, so they never go over the 1000 bytes threshold.
If you want to convert these columns to use utf8mb4 it means that the maximum size of a text in these columns would be 4*255 (since utf8mb4 means that the characters are encoded with 4 bytes) which equals to 1020.
However, this means that the indexes based on these columns would exceed the 1000 bytes key length limit of your MySQL and that's why you get that error message.
To avoid that, you should edit these indexes and add a limit on the number of characters to index. For the product_type column, 20 characters will be enough. And for the product_code column, it depends on the length of your product codes but I doubt you would have codes bigger than 100 characters on a normal shop.
So before changing to utf8mb4, you need first to reduce the size of the indexes on these two columns:
stackoverflow.com/questions/50345159/wha...-index-in-phpmyadmin

More information on that issue is available here:
stackoverflow.com/questions/8746207/1071...length-is-1000-bytes

Please Log in or Create an account to join the conversation.

Time to create page: 0.058 seconds
Powered by Kunena Forum