utf8mb4 + emoji owes again.

[chibipaw]

No. It does not mean/include/imply the language file(s) as that/those define CHARSET which is not DB_CHARSET and are used for the interface between the data and html not specifically the database and seeing the follow-on message about database conversion to utf8 not being enough is true. If the conversion program was only converting the database to utf8 and not to utf8mb4, then it hasn't/hadn't gone far enough.

The change made to/in the language file(s) (admin and catalog sides) should be reverted back to utf-8 regardless of using the database collation/character set of utf8 or utf8mb4.

Got it. And thank you for the clarification. With the changes made, I've tested the system in our dev server. Looks like we got our asian characters as well as emoji issues resolved. This hasn't been really an issue until lately after running zen cart for so many year.. signs of how the consumers are changing these days. Thanks again.

[mc12345678]

Got it. And thank you for the clarification. With the changes made, I've tested the system in our dev server. Looks like we got our asian characters as well as emoji issues resolved. This hasn't been really an issue until lately after running zen cart for so many year.. signs of how the consumers are changing these days. Thanks again.

Guess I should have asked earlier, the script that did the database conversion, could you please identify as much as you know/understand about it, such as the link to it, the version number used, whether you know or suspect that it converted to utf8mb4. While re-reading the thread based on the recent posting it is clear that the specific field(s) had not been converted, trying to identify a more accurate picture of what had been done leading up to the original post. :)

[chibipaw]

Guess I should have asked earlier, the script that did the database conversion, could you please identify as much as you know/understand about it, such as the link to it, the version number used, whether you know or suspect that it converted to utf8mb4. While re-reading the thread based on the recent posting it is clear that the specific field(s) had not been converted, trying to identify a more accurate picture of what had been done leading up to the original post. :)

Sure thing. I've used convert_db2utf8-v4.1 script, and it was successful in converted from I believe Latin-1 to utf-8. Just to note, there is nothing
in the script itself containing the string of utf8mb4. So your original assessment was correct from the previous thread, but the script only converted to utf-8. I remember trying to modify the script to handle utf8mb4, but ran into some other issues. Possibly character length errors. Cant remember specifically since its been a few months.

Anyhoo, Im happy that this is finally figured out. But I guess since this is an installation that has been around since that 1.3x days, the original db encoding was probably Latin-1 by default.

[generikz]

I'm so glad I found this thread!

These Incorrect string value: '\x... errors have been driving me crazy for some time.

Basically it boil down to customer-input of emojis that will either:

• crash at checkout (email can't be recorded in the archives, order is create but is empty)
• crash at Paypal payment (payment arrives, order is not created)

Each time an emoji was either added in an order comment or in the Paypal payment comments, crashing the whole thing on a database error.

Found this thread + the ut8mb4 script but of course I was on 1.5.5f + ZC dates back to 1.3.8 in 2009!

Double fail: pre-v1.5.6 + old v1.3.8 database structure

v1.5.5e   [2017-03-19 17:11:48]   (Version Update 1.5.4->1.5.5e)
v1.5.4   [2017-03-19 17:11:44]   (Version Update 1.5.3->1.5.4)
v1.5.3   [2017-03-19 17:11:40]   (Version Update 1.5.2->1.5.3)
v1.5.2   [2017-03-19 17:11:34]   (Version Update 1.5.1->1.5.2)
v1.5.1   [2017-03-19 17:11:28]   (Version Update 1.5.0->1.5.1)
v1.5.0   [2017-03-19 17:11:20]   (Version Update 1.3.9->1.5.0)
v1.3.9h   [2010-11-04 22:35:18]   (Version Update 1.3.9g->1.3.9h)
v1.3.9g   [2010-10-01 12:17:54]   (Version Update 1.3.9f->1.3.9g)
v1.3.9f   [2010-09-10 23:38:40]   (Version Update 1.3.8->1.3.9f)
v1.3.8   [2009-07-04 20:22:05]   (Fresh Installation)

I brought my ZC up to speed:

v1.5.6c   [2021-01-07 15:05:13]   (Version Update 1.5.5->1.5.6c)

And now all I need to do is get these emojis to work without damaging the accents in the existing fields.
My ZC is in FRENCH, tons of accents lying around.

I tried a dry-run of the utf8mb4 script and it completed successfully but after that most of the Admin accentuated contents located in the DB (menus, controls, product names, categories) would show up weird even after updating the CHARSET.

Everything in UTF-8 */includes/languages/french* shows correctly.

Did anyone experience such issues when upgrading to utf8/utf8mb4?

Julien

[generikz]

Good evening,

Tried the utfmb4 script again but I get:

74 times "MySQL Error: Incorrect string value: '\x..." in various tables
2 times "MySQL Error: Specified key was too long; max key length is 1000 bytes"

I think the script is not able to treat pre-v1.5.0 that stored accentuated characters in latin1?

So I took another path:

1/ Full EXPORT of the database with drop/create tables and I replaced all COLLATE=latin1 with COLLATE=utf8mb4
2/ Full IMPORT, basically recreating everything with new charset
3/ Run the utf8mb4 script to upgrade what can be upgraded

Accents are back to normal, the script only shows these errors now (update stop at the first fail, sorry in France we can have REALLY lengthy addresses!):

MySQL Error: Data too long for column 'entry_suburb' at row 33
MySQL Error: Data too long for column 'entry_city' at row 1265
MySQL Error: Data too long for column 'categories_name' at row 6
MySQL Error: Data too long for column 'customers_suburb' at row 910
MySQL Error: Data too long for column 'delivery_suburb' at row 910
MySQL Error: Data too long for column 'billing_suburb' at row 910
MySQL Error: Data too long for column 'products_name' at row 10456

That's manageable manually, re-rerunning the script until there is no more errors.

I hope this can help someone else with an existing legacy ZC DB with accents!

Julien

[DrByte]

Hey Julien,
Thanks for posting these details.

Can you confirm that the utf8mb4 script you're using is this one? https://github.com/zencart/utf8mb4-converter

Do you recall which tables triggered the "2 times "MySQL Error: Specified key was too long; max key length is 1000 bytes" messages?

[generikz]

Hey Julien,
Thanks for posting these details.

Can you confirm that the utf8mb4 script you're using is this one? https://github.com/zencart/utf8mb4-converter

Do you recall which tables triggered the "2 times "MySQL Error: Specified key was too long; max key length is 1000 bytes" messages?

Hello!

I deleted the logs/notes but I can reload a backup of the database and go through this again.
I wanted to do it again anyway to re-validate the whole updating process before applying it to the live shop.

Processing table [get_terms_to_filter]:
MySQL Error: Specified key was too long; max key length is 1000 bytes
Altered field `get_term_name`: `varchar(255)`
Altered field `get_term_table`: `varchar(64)`
Altered field `get_term_name_field`: `varchar(64)`
Table collation updated.

Processing table [media_manager]:
Temporarily dropped index idx_media_name_zen.
Altered field `media_name`: `varchar(255)`
MySQL Error: Specified key was too long; max key length is 1000 bytes
Recreated index idx_media_name_zen on media_manager (media_name).
Table collation updated.

All the "MySQL Error: Data too long for column" where accentuated strings of length X reaching exactly the VARCHAR(X) value or the field.
Some of them were truncated from entries dating back to 2009~2017 (email_html, page_desc and mostly adresses-related fields) when MySQL was more forgiving than MariaDB with its default settings.

• For addresses and categories/product names, I just manually edited them to shorten them in order for the utf8mb4 script to work its magic.
• For page_desc in user_tracking I just got rid of them:
  

  delete from user_tracking where length(page_desc) > 64;

Note the script will give the row # and not an unique ID.
In that case if, for example, the row is 77, the quick way to find it with phpMyAdmin or other tools is to use a SELECT LIMIT with the ID-1:

SELECT * from <the offending table> WHERE 1 LIMIT 76,1;

Julien

[DrByte]

Accents are back to normal, the script only shows these errors now (update stop at the first fail, sorry in France we can have REALLY lengthy addresses!):

MySQL Error: Data too long for column 'entry_suburb' at row 33
MySQL Error: Data too long for column 'entry_city' at row 1265
MySQL Error: Data too long for column 'categories_name' at row 6
MySQL Error: Data too long for column 'customers_suburb' at row 910
MySQL Error: Data too long for column 'delivery_suburb' at row 910
MySQL Error: Data too long for column 'billing_suburb' at row 910
MySQL Error: Data too long for column 'products_name' at row 10456

That's manageable manually, re-rerunning the script until there is no more errors.

What are the new field lengths you've used to accommodate your typically longer addresses etc (and the increased mb4 length requirements)?

Processing table [get_terms_to_filter]:
MySQL Error: Specified key was too long; max key length is 1000 bytes
Altered field `get_term_name`: `varchar(255)`
Altered field `get_term_table`: `varchar(64)`
Altered field `get_term_name_field`: `varchar(64)`
Table collation updated.

Processing table [media_manager]:
Temporarily dropped index idx_media_name_zen.
Altered field `media_name`: `varchar(255)`
MySQL Error: Specified key was too long; max key length is 1000 bytes
Recreated index idx_media_name_zen on media_manager (media_name).
Table collation updated.

Those tables shouldn't have had an issue if the v156 upgrade ran:
https://github.com/zencart/zencart/b....sql#L204-L210

Did it run into errors running those upgrade scripts related to data in those tables?

[generikz]

Hello!

(I sent you a PM with the logs and more details)

What are the new field lengths you've used to accommodate your typically longer addresses etc (and the increased mb4 length requirements)?

I didn't modify the max length, I just edited or trimmed the addresses/categories/etc. so they don't create issues anymore.

Those tables shouldn't have had an issue if the v156 upgrade ran:
https://github.com/zencart/zencart/b....sql#L204-L210

Did it run into errors running those upgrade scripts related to data in those tables?

I did see a brief error notification because the script announced the upgrade was successful.

On reloading a more recent backup and trying again this time it didn't appear and the MAX KEY LENGTH errors disappeared.
It could have been a left-over from a 1.5.6, 1.5.6a or 1.5.6b SQL previous update?

Anyway the issue is gone and the latin1 => utf8mb4 upgrade was a success.

Testing a little more and will upgrade the production server as well following the same steps:

• Upgrade ZC v1.5.5f (or older) to 1.5.6c
• Full Export of the DB including drop/create tables
• Edit/Replace all =latin1 with =utf8mb4
• Full Import of the modified DB
• Run the script from https://github.com/zencart/utf8mb4-converter (with errors)
• Edit/trim/modify the offending fields/values until the script runs error-free
• Update the 2 configure.php to define('DB_CHARSET', 'utf8mb4'); to allow emojis and other extended characters

Julien

[DrByte]

Note to future readers of this discussion: avoid editing the exported SQL file. It's very easy to break things doing that, and should not be necessary.