MySQL upgrade from 4 to 5 Issues

[dharma]

I upgraded from MySQL Version: 4.0.27 to MySQL 5.0.44 today. Everything runs ok but I am having some issues.

If I cut and paste text that includes ' or " in a product description or ez page, My ZC does not display any text after those symbols.

ex:
There have been no statewide opinion polls published on the bailout request, though with Michigan’s economy so tied to the industry.

ZC displays in browser :

There have been no statewide opinion polls published on the bailout request, though with Michigan

because :

’s economy so tied to the industry. is not put into the db.


other issues are:

Foreign characters.... from a little digging it seems to be ZC and the text encoding:

ex.: in old db : V‰nersborg
in new db: Vänersborg

browsers display : Vänersborg

if I change the view in firefox's browser: View->Character Encoding-> from western charset=iso-8859-1 to UTF-8 the browsers displays ok:

Vänersborg

Did I miss some upgrade step with ZC when upgrading to 5?

Should I revert back to MySQL Version: 4.0.27 ? Im a bit worried here.

I am running Zen Cart 1.38a ENGLISH only on PHP Module Version: 5.2.6

[DrByte]

If I cut and paste text that includes ' or " in a product description or ez page, My ZC does not display any text after those symbols.

ex:
There have been no statewide opinion polls published on the bailout request, though with Michigan’s economy so tied to the industry.

ZC displays in browser :

There have been no statewide opinion polls published on the bailout request, though with Michigan

because :

’s economy so tied to the industry. is not put into the db.

That sounds like a bug in an addon you've got on your site. The original 1.3.8 code properly handles ' marks in product descriptions.

other issues are:

Foreign characters.... from a little digging it seems to be ZC and the text encoding:

ex.: in old db : V‰nersborg
in new db: Vänersborg

browsers display : Vänersborg

if I change the view in firefox's browser: View->Character Encoding-> from western charset=iso-8859-1 to UTF-8 the browsers displays ok:

Vänersborg

Sounds like you used different character sets when exporting and importing your database.

Should I revert back to MySQL Version: 4.0.27 ? Im a bit worried here.

No, downgrading is unlikely to solve anything. But maybe you need to change your ZC CHARSET to utf-8 ?

[dharma]

According to my host about the MySQL upgrade from 4 to 5:

"The reason that we do the conversion is that MySQL v4.0.x doesn't bother to specify a character set while using latin1. v5.0.x on the other hand uses utf-8 by default if no type is specified. Therefore if we simply copy a database from 4.0.x to 5.0.x the data will be corrupted as it will be wrongly assumed to be utf-8 instead of latin1. We chose to convert data to utf-8 instead of telling v5.0.x we were importing latin1 because utf-8 is the standard now and because doing that requires less massaging of the data."

So I guess this means I have a db in UTF-8 and a ZC in latin1?

They suggest I dump my db as latin1 and reimport, I guess there is a way to do that via phpmyadmin? I changed MySQL collation in the phpmyadmin home screen's dropdown menu from utf8_general_ci to latin1_general_ci and did and export to file. The special characters have returned to the way they were in the original db (V‰nersborg), but I see mention of utf8 in this dump:

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=123 ;

old version from MySQL 4:
) TYPE=MyISAM AUTO_INCREMENT=122 ;

does this mean this dump is still utf8? and is so, will new entries from my latin1 ZC to a utf8 db still be messed up or good to go?

[dharma]

Ok, so I downgraded back to MySQL 4 and had my host restore the db backup before the upgrade.

My site is working ok, with no dropped text or issues with ', " or special characters.

Everything is back to normal.

Now how to upgrade this then? Given the posts above issues?

Are there any steps to be taken? in exporting/converting the db before placing on a MySQL 5 server?

regarding ZC's db is latin1 on 4 and MySQL 5's character set defaults?

or do I need to change something in ZC's php files?

[DrByte]

The problems you're running into with defaults etc are imposed by your host, not by Zen Cart.

I suggest some practice and experimentation, hopefully with the assistance of your hosting company, to determine the best approach for your hosting environment. Surely they can provide you with access to a 2nd database for testing your unique conversion needs in a test shop ...

[dharma]

Ok, I will look into testing and working with my host however, I am weary of doing any "custom" alterations to my db, that might mess up future ZC upgrades.

All I need to do is get my db (currently in latin1) on to a MySQL5 server in latin1 with the default character set to latin1.
ex.
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=123 ;

If I do this I should be ok for upgrades and such, correct?

[2004g35]

I have the same problem - my store is in mysql 4.0.27 and I need to get it into mysql 5.x My host will not assist me - they just said Export the old DB and then Import into the new DB. Sounds great on paper, but I get errors and nothing shows up. I am trying to export in SQL, save as file, export type - INSERT. I know enough to be able to say - I don't know what I am doing. Could you please advise the proper settings to be able to export the 4.0.27 and Import into 5.0.81 MySql - Thanks in advance for your help.

[DrByte]

Recommended settings for Exporting: https://www.zen-cart.com/tutorials/i...hp?article=103

[dharma]

Hello!

as I recall I exported & re- imported the db to mysql 5 via telenet and command line...but before you do anything please note:


Upgrading the Mysql from 4 to 5 was for me my host a horrible long nightmare stemming from issues due to a latin1 zc/db and the hosts mysql 5 being defaulted to UTF-8.

My best advice is to hire someone, or you will go through agonizing weeks of struggle, frustration and end up hiring someone anyway. If your lucky, you will find someone who knows both zc and your hosts set up.

sorry to be so pessimistic...but my upgrade almost killed me and its still not 100% (Paypal UTF-8 vs. zc latin1 db) issues

[dharma]

Having said all that above, my issues again may have been due to my hosts sever configs, and your upgrade may be simpler and less complicated.

Follow dr. Bytes links and go from there...