Install SQL is latin1; adjustment needed to install to UTF-8

**conor** · 5 May 2010, 12:27 PM

Hi,

Just tried to install 1.3.9b on a local server which uses MySQL 5.1 with a default charset of

UTF-8 Unicode (utf8)

I'm disappointed to find that Zen Cart 1.3.9b isn't UTF-8 compatible.

The following error occurs when trying to install the software on a "modern" UTF-8 MySQL setup:

Code:

1366 Incorrect string value: '\xA3:&eur...' for column 'configuration_value' at row 1
in:
[INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, use_function, set_function) VALUES ('Convert currencies for Text emails', 'CURRENCIES_TRANSLATIONS', '£,£:€,€', 'What currency conversions do you need for Text emails?
Default = &pound;,£:&euro;,€', 12, 120, NULL, '2003-11-21 00:00:00', NULL, 'zen_cfg_textarea_small(');]

Of course I understand that Zen Cart has to support lots of users on old servers but I hope that 2.0.0 will be UTF compatible... I'd suggest that a quick check at the installation stage for the correct character set to use in database connections would be a worthwhile inclusion.

If anyone wants to install Zen Cart 1.3.9b on a server that uses the UTF-8 character set as its default character set you'll need to modify

includes/classes/db/mysql/query_factory.php

Just after (line 38):
$this->link = @mysql_connect($zf_host, $zf_user, $zf_password, true);

put in the lines:

if (version_compare(mysql_get_server_info(), '4.1.0', '>=')) {
mysql_query('SET NAMES "latin1"', $this->link);
}

Of course, set the character set to whichever character set you'd like to use.

I've created a database using the latin1 character set then added the lines above to set the connection to MySQL to use "latin1" so that latin1 is used instead of the server's default of UTF-8. If this isn't done the SQL error mentioned above occurs, messing up the installation.

If you are still using a UTF-8 MySQL character set you'd use "utf8" as the character set in the line above.

Hope that helps anyone else who has the same problems!

All the best..

Conor

**DrByte** · 5 May 2010, 02:26 PM

Or instead of HACKING UP YOUR query_factory.php script, you could do it much simpler and use the built-in new DB_CHARSET capability added in v1.3.9.

**DrByte** · 5 May 2010, 02:29 PM

Originally Posted by conor

I'm disappointed to find that Zen Cart 1.3.9b isn't UTF-8 compatible.

That statement is NOT entirely correct.
Simply setting the appropriate DB_CHARSET define will make even the most "modern", as you call it, installs happen easily.

Originally Posted by conor

I hope that 2.0.0 will be UTF compatible

And, yes, we have ALWAYS STATED that v2.0.0 will be UTF-8 compatible.

There is NO point breaking prior v1.3.x installs by forcing everything to change to UTF-8 in a minor bugfix release. Changing character sets is NOT something to treat lightly. Hence the logical decision to wait until 2.0 for such a change.

**conor** · 5 May 2010, 03:06 PM

Hi,

Thanks for the reply!

Originally Posted by DrByte

Or instead of HACKING UP YOUR query_factory.php script, you could do it much simpler and use the built-in new DB_CHARSET capability added in v1.3.9.

That's exactly what was needed, thank you!

Why is this information only included in a changelog? It is important enough that it should be part of the installation process.

That statement is NOT entirely correct.
Simply setting the appropriate DB_CHARSET define will make even the most "modern", as you call it, installs happen easily.

If the installation process won't run without people having to make manual code edits that they don't know about then I personally would say that the software isn't "UTF-8 compatible" as standard. Nevertheless, no point arguing about terms, I don't really care, the fact that it can be made to be compatible so easily is a great boon.

Again though, I would suggest that there should be an option in the installation process to set this directly or else some information in the installation process about this so people don't have the same problems I did. (Hopefully this thread helps with that a bit! ;) ).

There is NO point breaking prior v1.3.x installs by forcing everything to change to UTF-8 in a minor bugfix release. Changing character sets is NOT something to treat lightly. Hence the logical decision to wait until 2.0 for such a change.

I agree, but I wasn't asking for that.. :)

All the best..

Conor
ceon

**nagelkruid** · 11 May 2010, 06:01 PM

Originally Posted by DrByte

Or instead of HACKING UP YOUR query_factory.php script, you could do it much simpler and use the built-in new DB_CHARSET capability added in v1.3.9.

Can you elaborate please how to work around this error?
I have the same problem but do not understand how to implement your proposed solution?