Issue with Special Characters in Fields

Printable View

11 Aug 2018, 07:02 PM
hc1501

Issue with Special Characters in Fields

Dear Team

I am facing an issue on my Zencart Website. I am getting error logs as pasted below. I have search on google and it says to change the Collation of the db to utf8. My current collation of db is latin1_swedish_ci. I tried to change the type to suggested one (utf8_swedish_ci) on my test site and then the below issues don't occur.

But I am not sure what role does this play on zencart and how it can affect my live site. Please someone guide here if it is suggested to take this step? Thanks in advance...

Quote:

[04-Aug-2018 09:04:13 UTC] PHP Fatal error: 1366:Incorrect string value: '\xF0\x9F\x99\x82' for column 'comments' at row 1 :: INSERT INTO zen_orders_status_history (orders_id, orders_status_id, date_added, customer_notified, comments) VALUES ('30540', '2', now(), '1', 'Happy rakhdi 🙂') ==> (as called by) /home/indiagi1/public_html/includes/functions/functions_general.php on line 965 <== in /home/indiagi1/public_html/includes/classes/db/mysql/query_factory.php on line 155

[04-Aug-2018 09:26:58 UTC] PHP Fatal error: 1366:Incorrect string value: '\xF0\x9F\x99\x82' for column 'comments' at row 1 :: INSERT INTO zen_orders_status_history (orders_id, orders_status_id, date_added, customer_notified, comments) VALUES ('30543', '2', now(), '1', 'Happy rakhdi 🙂') ==> (as called by) /home/indiagi1/public_html/includes/functions/functions_general.php on line 965 <== in /home/indiagi1/public_html/includes/classes/db/mysql/query_factory.php on line 155

myDEBUG-1533136493-46348.log:[01-Aug-2018 15:14:53 UTC] PHP Fatal error: 1366:Incorrect string value: '\xC4\xB7ll' for column 'entry_city' at row 1 :: UPDATE zen_address_book SET customers_id = '22614', entry_firstname = 'Deepak', entry_lastname = 'CHOWDHARY', entry_telephone = '9795496669', entry_street_address = '5555555ggggg', entry_postcode = '211003', entry_city = 'Allllllķll', entry_country_id = '99', address_book_id = '55075', entry_zone_id = '333', entry_state = '' WHERE address_book_id = 55075 ==> (as called by) /home/indiagi1/public_html/includes/functions/functions_general.php on line 965 <== in /home/indiagi1/public_html/includes/classes/db/mysql/query_factory.php on line 155

myDEBUG-1533298813-300093.log:[03-Aug-2018 12:20:13 UTC] PHP Fatal error: 1366:Incorrect string value: '\xE2\x80\xAD+61...' for column 'entry_telephone' at row 1 :: INSERT INTO zen_address_book (customers_id, entry_firstname, entry_lastname, entry_telephone, entry_street_address, entry_postcode, entry_city, entry_country_id, entry_zone_id, entry_state) VALUES (22745, 'Arshpreet Singh', 'Gill', '##+61 450 219 696‬', '10 Cobram street , Tarneit', 'VIC 3029', 'Melbourne', 13, 188, '') ==> (as called by) /home/indiagi1/public_html/includes/modules/quick_checkout.php on line 516 <== in /home/indiagi1/public_html/includes/classes/db/mysql/query_factory.php on line 155

myDEBUG-1533298996-369915.log:[03-Aug-2018 12:23:17 UTC] PHP Fatal error: 1366:Incorrect string value: '\xE2\x80\xAD+61...' for column 'entry_telephone' at row 1 :: INSERT INTO zen_address_book (customers_id, entry_firstname, entry_lastname, entry_telephone, entry_street_address, entry_postcode, entry_city, entry_country_id, entry_zone_id, entry_state) VALUES (22745, 'Arshpreet Singh', 'Gill', '##+61 450 219 696‬', '10 cobram street, Tarneit', 'VIC 3029', 'Melbourne', 13, 188, '') ==> (as called by) /home/indiagi1/public_html/includes/modules/checkout_new_address.php on line 167 <== in /home/indiagi1/public_html/includes/classes/db/mysql/query_factory.php on line 155
11 Aug 2018, 09:46 PM
lat9

Re: Issue with Special Characters in Fields

You could make a small modification to the Convert db2utf8 plugin (https://www.zen-cart.com/downloads.php?do=file&id=1318) so that the collation it uses to update is utf8_swedish_ci (its default is utf8_general_ci).
12 Aug 2018, 04:08 AM
hc1501

Re: Issue with Special Characters in Fields

Quote:

Originally Posted by lat9

You could make a small modification to the Convert db2utf8 plugin (https://www.zen-cart.com/downloads.php?do=file&id=1318) so that the collation it uses to update is utf8_swedish_ci (its default is utf8_general_ci).

Hi and thanks a lot for your response chief.

So you suggest to make these changes and it will not affect my site data and functionality anyway now and in future upgrades as well?

What if I do the default as done by the plugin to utf8_general_ci? Does it make any difference?
12 Aug 2018, 06:30 AM
hc1501

Re: Issue with Special Characters in Fields

Quote:

Originally Posted by hc1501

Hi and thanks a lot for your response chief.

So you suggest to make these changes and it will not affect my site data and functionality anyway now and in future upgrades as well?

What if I do the default as done by the plugin to utf8_general_ci? Does it make any difference?

Also what is the issue if I just change the db collation type to utf8_general_ci and not use the script. I did it on my test website and it seemed to have resolved the issue. Please throw some light...
12 Aug 2018, 01:53 PM
lat9

Re: Issue with Special Characters in Fields

There are, I assume, some subtleties in the character-set handling between utf8_swedish_ci and utf8_general_ci that a google-search would identify.
12 Aug 2018, 05:19 PM
hc1501

Re: Issue with Special Characters in Fields

Quote:

Originally Posted by lat9

There are, I assume, some subtleties in the character-set handling between utf8_swedish_ci and utf8_general_ci that a google-search would identify.

Thanks a lot for your response... Can u please also tell me if I just change the db collation and not use the script. Is it ok?
12 Aug 2018, 06:29 PM
lat9

Re: Issue with Special Characters in Fields

If you are making those collation changes to the database directly, be sure to change the default collation of the database itself, too. Otherwise, if you add a plugin that creates a new table that has a character-type field (e.g. varchar or text), that added table will not have the "correct" collation.
14 Aug 2018, 03:03 PM
hc1501

Re: Issue with Special Characters in Fields

Quote:

Originally Posted by lat9

If you are making those collation changes to the database directly, be sure to change the default collation of the database itself, too. Otherwise, if you add a plugin that creates a new table that has a character-type field (e.g. varchar or text), that added table will not have the "correct" collation.

Thanks. But my question is, if I change the main db collation and not inside the table, is that ok??
14 Aug 2018, 08:00 PM
lat9

Re: Issue with Special Characters in Fields

If you change only the main DB collation, that doesn't affect the collation(s) of any pre-existing tables. That's where the convert_db2utf8 script helps; it changes both the main DB and all included tables' collations to the value programmed (default: utf8_general_ci).