Character sets mismatch after changing hosts

[sanderh]

Hello,

I have a problem with a ZEN cart site of one of our customers.
After migrating it to our servers, some characters (like the €-sign) are corrupted.
I think it's a mismatch in the character set, but I'm not sure on how to fix this.

For example when I create a coupon code with a €-sign in the couponcode field, I get a SQL error.
The customer states this worked at the previous server.
This is the error thrown (this is an update, but a create ends in the same error).

1366 Incorrect string value: '\x8050' for column 'coupon_code' at row 1
in:
[update coupons set coupon_code = '€50', coupon_amount = '10.0000', coupon_type = 'P', uses_per_coupon = '0', uses_per_user = '1', coupon_minimum_order = '0', restrict_to_products = '', restrict_to_categories = '', coupon_start_date = '2014-11-06', coupon_expire_date = '2015-11-06', date_created = now(), date_modified = now(), coupon_zone_restriction = '0' where coupon_id='12']

[RodG]

Does this help?

http://www.zen-cart.com/content.php?...8859-1-to-utf8

Cheers
RodG

Oh scrub that. You said you migrated, and this is only relevant if you upgraded. How did you export the original data? It look like it has been hex encoded. You may need to re-export the data from the original site and re-import it.

[DrByte]

It probably still is a problem with importing data based on a certain character set into a server which either doesn't support that character set or collation.

[sanderh]

Thank you for your replies.
Can you tell me how to check that and how I can fix it?

[RodG]

Thank you for your replies.
Can you tell me how to check that and how I can fix it?

Hopefully DrByte has a suggestion how to fix the problem if it is related to the import.

I'm working from the 'other end' and am thinking that the problem is/was caused during the export of the original data, in which case re-exporting without the HEX encoding would be my suggestion.

What program/software did you use for the export/import anyway? Most folk tend to use phpMyAdmin which in my experience doesn't usually cause an issue like this.

Cheers
RodG

[lat9]

One other thing to look at: When you created the database using your new webhost's cPanel, what collation did you specify on the database-create request?

[sanderh]

Hi, I exported the data from the other host, using phpmyadmin.
And I imported the data using HeidiSQL.
I cannot do this again, because the site is already in use on the new server.
Orders will disapear, so I have to fix this on the current server.

The charset on the OLD database was utf8_general_ci
The charset on the NEW database is also utf8_general_ci.

However the tables have latin1_swedish_ci, on both servers...

Does this make any sense to you guys?
Can webserver software be the cause of this?
I think the old server ran on LINUX/Apache, while the new one is WINDOWS/IIS

[lat9]

You could use the Convert db2utf8 plugin (http://www.zen-cart.com/downloads.php?do=file&id=1318), which will automatically convert those latin1_swedish_ci tables to utf8_general_ci. Be sure to place your site into maintenance mode and make a database backup before you run the script (just to be sure).

[RodG]

The charset on the OLD database was utf8_general_ci
The charset on the NEW database is also utf8_general_ci.
However the tables have latin1_swedish_ci, on both servers...

Interesting. Since the Database and the Tables are the same on both I wouldn't really be expecting you to be having the problems that you have.

What version of Zencart are you using?
Did you try to perform a ZenCart update at the same time as the migration?

Also, in your 1st post you wrote
"After migrating it to our servers, some characters (like the €-sign) are corrupted."
and
"For example when I create a coupon code with a €-sign in the couponcode field, I get a SQL error."

Can you verify that these are two different (albeit related) issues.

Issue#1) The €-sign is 'corrupted' in the pre-existing data that was imported (in what way is this particular symbol corrupted?). and
Issue#2) You can no longer enter data that contains the €-sign

Can webserver software be the cause of this?

The WEB server, no. A Webserver has nothing to do with SQL.

Can the SQL server be the cause.. Yes, most certainly, especially if the data was originally stored in a MySQL server and you are trying to import it into an MSSQL or MariaDB server, etc...

There can even be issues exporting/importing into different versions of the same SQL server

I think the old server ran on LINUX/Apache, while the new one is WINDOWS/IIS

The WebServer is irrelevant. The fact that you have downgraded to Windows based services *may* be relevant, but at the end of the day it is the DB server that is of most importance. A MySQL server on a Linux based machine generally doesn't behave any differently than the same version of a MySQL server running on a Windows based machine.

I suspect that a part (or all) of your problem relates to the fact that you exported with phpmyadmin, but imported with HeidiSQL.

I cannot do this again, because the site is already in use on the new server.
Orders will disapear, so I have to fix this on the current server.

Yoiks. This has somewhat limited your options, and a fix may not even be *easily* possible

I have just three suggestions for you. None of which I'm very confident about, so before trying either make sure you backup your database first.

Suggestion#1
Download and run this:
http://www.zen-cart.com/downloads.php?do=file&id=1318

Keep your fingers crossed. I've no idea what to expect if all of your tables and data get converted to UTF8 if your ZenCart version is still using LATIN1

Suggestion#2.
Export the data to a CSV file, load this file into a spreadsheet and use the Search-Replace functions to 'fix' the 'corruptions', then re-import the modified CSV.

The 3rd option would be to hire a database expert to see if they can restore things back to a sane condition. Databases can be very unforgiving, and if you don't fully understand what you are doing you can end up making a bad situation worse.

Don't forget the cardinal rule though... MAKE A BACKUP (and ideally you should test that you can restore from this backup *before* doing anything else). It's no good having a backup if you don't know how to use it to restore.

I wish you the best of luck. I think you are going to need it.

CHeers
RodG

[sanderh]

Hi,

Thank you all for your responses.
I think I have managed to solve it!
I had to do 2 steps:
1) convert the database using the suggested tool. http://www.zen-cart.com/downloads.php?do=file&id=1318
2) convert my v1.3.9 site PHP files to utf-8 using this guide: https://www.numinix.com/blog/2011/08...-3-9-to-utf-8/

I hope all is resolved, my customer is checking it now, if there are further questions, I will come back!