Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20
  1. #11
    Join Date
    Jul 2011
    Posts
    135
    Plugin Contributions
    4

    Default Re: utf8mb4 + emoji owes again.

    Quote Originally Posted by mc12345678 View Post
    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.

  2. #12
    Join Date
    Jul 2012
    Posts
    15,564
    Plugin Contributions
    17

    Default Re: utf8mb4 + emoji owes again.

    Quote Originally Posted by chibipaw View Post
    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. :)
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...

  3. #13
    Join Date
    Jul 2011
    Posts
    135
    Plugin Contributions
    4

    Default Re: utf8mb4 + emoji owes again.

    Quote Originally Posted by mc12345678 View Post
    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.

  4. #14
    Join Date
    Sep 2011
    Location
    Tokyo
    Posts
    10
    Plugin Contributions
    1

    Default Re: utf8mb4 + emoji owes again.

    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

    Code:
    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:

    Code:
    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

  5. #15
    Join Date
    Sep 2011
    Location
    Tokyo
    Posts
    10
    Plugin Contributions
    1

    Default Re: utf8mb4 + emoji owes again.

    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

  6. #16
    Join Date
    Jan 2004
    Posts
    66,143
    Blog Entries
    7
    Plugin Contributions
    268

    Default Re: utf8mb4 + emoji owes again.

    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?
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  7. #17
    Join Date
    Sep 2011
    Location
    Tokyo
    Posts
    10
    Plugin Contributions
    1

    Default Re: utf8mb4 + emoji owes again.

    Quote Originally Posted by DrByte View Post
    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.

    Code:
    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:
      Code:
      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:
    Code:
    SELECT * from <the offending table> WHERE 1 LIMIT 76,1;
    Julien

  8. #18
    Join Date
    Jan 2004
    Posts
    66,143
    Blog Entries
    7
    Plugin Contributions
    268

    Default Re: utf8mb4 + emoji owes again.

    Quote Originally Posted by generikz View Post
    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)?

    Quote Originally Posted by generikz View Post
    Code:
    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?
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  9. #19
    Join Date
    Sep 2011
    Location
    Tokyo
    Posts
    10
    Plugin Contributions
    1

    Default Re: utf8mb4 + emoji owes again.

    Hello!

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

    Quote Originally Posted by DrByte View Post
    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.

    Quote Originally Posted by DrByte View Post
    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
    Last edited by DrByte; 22 Jan 2021 at 07:38 PM. Reason: Edit: recommend not doing the steps shown in light-grey, eg: don't edit the export.

  10. #20
    Join Date
    Jan 2004
    Posts
    66,143
    Blog Entries
    7
    Plugin Contributions
    268

    Default Re: utf8mb4 + emoji owes again.

    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.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

 

 
Page 2 of 2 FirstFirst 12

Similar Threads

  1. URGENT: Customers are being forced to log in again and again
    By dmm2020 in forum General Questions
    Replies: 26
    Last Post: 23 Feb 2009, 01:05 AM
  2. Gift Certificate fully redeemed but customer still owes money
    By brianosc in forum Discounts/Coupons, Gift Certificates, Newsletters, Ads
    Replies: 8
    Last Post: 9 Sep 2008, 04:55 AM
  3. H Again
    By hells1614 in forum Installing on a Windows Server
    Replies: 2
    Last Post: 12 Apr 2008, 02:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR