Host upgraded PHP to 5 now UTF-8 vs Latin1 text encoding issues

[dharma]

My host recently upgraded to PHP 5 from 4. Since then I have been seeing this issue:

Special characters entered by customer :
Kübeckgasse goes into the db as it always has as latin1: K√ºbeckgasse but comes out UTF-8 displayed as: Kübeckgasse.

I have inquiried about this at my host who says that it is a Zen Cart issue.

So far I have not made any changes to my db or ZC files. This new data has appeared in the header of my db since the upgrade:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

which my host says is the way MySQL v5 client libraries dumps data vs. the old way MySQL 4 did (without the code above)


My site is running on:
Apache 1.3.41
PHP 5.2.6 linked against the MySQL 5.0.54 libraries
MySQL 4.0.27 database engine

They say they recently linked the PHP 5 to MySQL 5.0.54 libraries, not sure what that means but is this my issue?

My goal would be to fix what ever this issue is and then move my db onto their MySQL 5.0.44 server.

Do I need to change some ZC code to clear this up? Like change the CHARSET from western to UTF-8 for browsers? or will mess things up on later upgrades?

[Qdixon]

Interesting, done lots of upgrades from 4x to 5x and we have only experienced it a handful of times.

Upgrading MySQL on our servers we had no problem either.

Try doing a database repair and optimization via phpmyadmin. It may fix the encoding issue.

[Vger]

Yes, seen this lots of times. What you have to do is to backup the db, don't select a compatibility method, check the db offline to make sure the characters are correct and then re-import selecting Latin 1 in place of utf8 when importing. That should resolve it unless they specifically only allow utf8 encoding.

Whilst, in theory, utf8 gives you many more options with regard to the languages and characters you can use on your website it cannot handle non-English language characters which have already been saved in Latin1 or iso-8859-1 (which is the same thing). I've already outlined one workaround but another is to use ansi/ascii character codes for non-English characters in the db.

Vger

[dharma]

I am in the midst of a MYSQL 5 upgrade and to make a long story short:

http://www.zen-cart.com/forum/showth...highlight=Utf8
http://www.zen-cart.com/forum/showth...highlight=Utf8

I must either set my default character set to Latin1 in the db to avoid text encoding issues with ZC and MySQL 5 and PHP 5 (Default UTF-8).

Or change the Character set in ZC HTML to display UTF-8 instead of Western (latin1).

Since ZC 2.0 will require us to

re-encode language files as UTF-8-without-BOM format

Maybe now is the time to do this?

So if I convert my db to UTF-8-without-BOM and make that the CHARSET. What ZC files would I have to change to have browsers display in UTF-8? I do not have any languages but English.

I have seen some mention of this being done with fresh installs, but nothing about existing ZC with existing data.
http://www.zen-cart.com/forum/showth...highlight=Utf8

Is this a huge buggy undertaking? Is it best to go set the db CHARSET to latin1 for now and wait for the 2.0 update to convert?

Running ZC 1.38a
Apache 1.3.41
PHP 5.2.6 linked against the MySQL 5.0.54 libraries
MySQL 4.0.27 database engine for now, moving to MySQL 5.0.44 server.

[dharma]

Thank you both for your help, I have also gotten this bit of info from my host boards:

PHP 5 knows about different charsets/encodings. If no encoding is defined, and this is the crucial part, it will assume the data is encoded in utf-8. You connect to a MySQL 4.0 database with PHP 5, the MySQL server doesn't return information about the encoding and PHP will internally handle the data as utf-8.
It is down to the PHP application to handle the data correctly, either by setting the encoding of the database connection to latin1, or outputting HTML pages that define the charset as utf-8 so that browsers will display the page correctly.

With this info I have started a new thread:
http://www.zen-cart.com/forum/showth...268#post660268

[Vger]

nddbc.html(5): <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
admin\includes\languages\english.php(18): // on FreeBSD 4.0 I use 'en_US.ISO_8859-1'
admin\includes\languages\english.php(20): setlocale(LC_TIME, 'en_US.ISO_8859-1');
admin\includes\languages\english.php(63): define('CHARSET', 'iso-8859-1');
bak_zc_install\techsupp.php(49): <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
bak_zc_install\includes\languages\english.php(23): define('CHARSET', 'iso-8859-1');
bak_zc_install\includes\modules\pages\database_setup\header_php.php(38): <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
docs\3.readme_paypal_ipn.html(12): <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
docs\4.readme_html_email_templates.html(12): <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
docs\index.html(37): <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
editors\htmlarea\lang\ee.js(3): // LANG: "ee", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\lang\en.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\lang\es.js(3): // LANG: "es", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\lang\it.js(3): // LANG: "it", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\lang\lv.js(3): // LANG: "lv", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\lang\nl.js(3): // LANG: "nl", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\lang\ru.js(3): // LANG: "ru", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\ContextMenu\lang\de.js(3): // LANG: "de", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\ContextMenu\lang\en.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\ContextMenu\lang\nl.js(3): // LANG: "nl", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\FullPage\lang\en.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\FullPage\lang\ro.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\SpellChecker\lang\da.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\SpellChecker\lang\de.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\SpellChecker\lang\en.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\SpellChecker\lang\it.js(3): // LANG: "it", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\TableOperations\lang\da.js(3): // LANG: "da", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\TableOperations\lang\de.js(3): // LANG: "de", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\TableOperations\lang\en.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\TableOperations\lang\it.js(3): // LANG: "it", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\TableOperations\lang\nl.js(3): // LANG: "nl", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\plugins\TableOperations\lang\no.js(3): // LANG: "en", ENCODING: UTF-8 | ISO-8859-1
editors\htmlarea\popups\old_insert_image.html(6): <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
includes\classes\class.phpmailer.php(44): var $CharSet = "iso-8859-1";
includes\functions\functions_email.php(164): $mail->CharSet = (defined('CHARSET')) ? CHARSET : "iso-8859-1";
includes\languages\english.php(20): // on FreeBSD try 'en_US.ISO_8859-1'
includes\languages\english.php(22): @setlocale(LC_TIME, 'en_US.ISO_8859-1');
includes\languages\english.php(49): define('CHARSET', 'iso-8859-1');
includes\templates\template_default\templates\tpl_zc_install_suggested_default.p hp(20): <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

Personally I'd reload the db as Latin 1.

Vger

[Kim]

Moderator's note - Merged 2 similar threads.

[dharma]

Holy Smokes! That is a lot of changes! Given the amount of editing above plus whatever mods/add-ons and APIs (Paypal) that might be affected, I think your suggestion is the best route for now:

Personally I'd reload the db as Latin 1.

and see what ZC has for us in future upgrades.

Thank you all for your help!

Merry Chrisümas!

[dharma]

Ok, So I tried to upgrade again to MySQL 5 from MySQL 4.0.27, setting the default CHARSET in my db to latin1: db defined as latin1 and all fields are latin1.

I still got text encoding issues when on a MySQL 5 server. So back down to MySQL 4 again.

According to my host:

ZenCart's code doesn't set the encoding of the client connection, so when connecting to a MySQL 5 server, the server will return utf-8 encoded text.

The database side is not the side that's the culprit. It actually doesn't matter at all what encoding is used in the database, whether it is UTF-8 or latin1/iso-8859-1. The collation is also of little importance, it only defines how fields are sorted (does 'ä' come before or after 'a' when sorted alphabetically, etc.).

It's all about the client side, i.e. PHP in this case. As of MySQL 4.1 a MySQL client can tell the MySQL server what it 'speaks', utf-8 or latin1, etc. When the client says it speaks utf-8, the server will send it data encoded in utf-8, even if the database uses another encoding, in which case the data is automatically converted.

In PHP5, the MySQL module (i.e. the MySQL client), will default to utf-8 if it's not told otherwise. When it connects to a MySQL 4.0 server, this information is discarded because the server doesn't know about character encodings. However, when it connects to a MySQL 5 server, the server will talk to the client in the 'language' the client speaks.

So, how do you get ZenCart to display the pages correctly if it receives utf-8 encoded text from the server. Either the client code needs to be configured to tell the server it speaks iso-8859-1/latin1, or the output (response headers plus the HTML pages) needs to be defined as utf-8. The first requires the PHP code to be modified, the second however appears to be fairly simple.
In the file english.php the following is defined;
// charset for web pages and emails
define('CHARSET', 'iso-8859-1');

Change 'iso-8859-1' to 'utf-8' and the pages and headers will be sent with the correct encoding. This might not be the complete solution, but it should go a very long way.

So in a nutshell my host is telling me to change the php coding on my Zen Cart to UTF-8.

If I performed the all the edits suggested by Vger above, and followed CrystalKoi's tutorials (for fresh installs):

http://www.srw2d.com/content/utf-8-zen-cart
http://www.zen-cart.com/forum/showthread.php?t=93439

Would I be be free of this issue, or would the issues just begin? paypal express, ZC upgrades etc...+ I don't see a lot of posts on this issue which makes me think maybe this is only my host?

Running ZC 1.38a
Apache 1.3.41
PHP 5.2.6 linked against the MySQL 5.0.54 libraries
MySQL 4.0.27 database engine for now, trying to move to MySQL 5.0.44 server.
English Only but I get many foreign nämes and äddresseś

[dharma]

Many pots of coffee later...Here is an update to my issue, maybe this will help so folk...:

I am not sure if this just on my host only or what but:

On MySQL 5 - PHP 5 the default connection is utf8. Since ZC does not specify what the connection should be in (latin1) the data transmitted from the db is utf8.

If I add this code:

   mysql_set_charset('latin1'); 

to line 36 in

includes/classes/db/mysql/query_factory.php

That fixes that issue.

-----------

My other issue was moving from MySQL 4 to 5:

Since MySQL 4 has no collations for utf8/latin1.

I exported my db on the command line dump and added the /*40101's below to the header of the db:

-- MySQL dump 9.11
--
-- Host: mysql.domain.com    Database: domainuser-db-test
-- ------------------------------------------------------
-- Server version	4.0.27-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES latin1 */;
--
-- Table structure for table `address_book`
--

then I created a new db and set the mysql collation in PHPMYADMIN to latin1_swedish_ci for that db and imported the old db into the new MySQL 5 db

on the import of the db, I did add --default-character-set=latin1 to one db test but the above but PHPMYADMIN seem to do that so I left it out on the last go, also removed the /*!40101's except for /*!40101 SET NAMES latin1 */; that seem to work too (should I leave them out?)

--------
So with the ZC query_factory and the import in/as latin1 my ZC looks ok in browsers auto-detect : (Western) ISO latin1/(Western ISO-8859-1)

When viewing ZC db data in PHPMYADMIN 3.1.1 the browser is set to

<meta http-equiv="Content-Type"
    content="text/html; charset=utf-8" />

so I set the connection to utf8_general_ci for PHPMYADMIN and all data is displayed correctly.

When dumping the db from PHPMYADMIN if the PHPMYADMIN collation is utf8 the dump will be in Utf-8, no BOM if you change it to swedish they will be mac os roman (in my text editor BBE edit for MAC which is the default for files that have no clear encoding), switching the view to Western (ISO latin 1) all the characters in the db look ok.

This is my first dive into anything related to a db or mysql, so I do not know if this the best way to go or not.

Please post there is a better way!

Because I do not know this will create future db issues if you take this route (if you had to restore your site from a mysql dump or ran a future ZC Uprade.sql script for 1.3.9 etc)

anyway here is some of what my last dump looked like :

-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: MySQL.domain.net
-- Generation Time: Jan 11, 2009 at 11:43 AM
-- Server version: 5.0.70
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `db-setlatin1inheaders-setlatin1collationCHARSET`
--

-- --------------------------------------------------------

--
-- Table structure for table `address_book`
--

DROP TABLE IF EXISTS `address_book`;
CREATE TABLE IF NOT EXISTS `address_book` (
  `address_book_id` int(11) NOT NULL auto_increment,
  `customers_id` int(11) NOT NULL default '0',
  `entry_gender` char(1) NOT NULL default '',
  `entry_company` varchar(32) default NULL,
  `entry_firstname` varchar(32) NOT NULL default '',
  `entry_lastname` varchar(32) NOT NULL default '',
  `entry_street_address` varchar(64) NOT NULL default '',
  `entry_suburb` varchar(32) default NULL,
  `entry_postcode` varchar(10) NOT NULL default '',
  `entry_city` varchar(32) NOT NULL default '',
  `entry_state` varchar(32) default NULL,
  `entry_country_id` int(11) NOT NULL default '0',
  `entry_zone_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`address_book_id`),
  KEY `idx_address_book_customers_id_zen` (`customers_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=135 ;