Your database appears to need patching to a higher level.

kobra:

Did you do a test install and look at the "DEFAULT" DB collation before importing any existing DB backup??

No...there was no instruction I saw to do that in the three step upgrade process or how to do that.

No...there was no instruction I saw to do that in the three step upgrade process or how to do that.
I did not reference any instruction

Just install ZenCart with a new DB as a test and view through phpmyadmin what the collation is if you want to know is the server config is overuling the default collation

kobra:

I did not reference any instruction

Just install ZenCart with a new DB as a test and view through phpmyadmin what the collation is if you want to know is the server config is overuling the default collation

The new installation went fine...everything worked at that point. The error occurred when restoring to place all the products back in place. We seem to be talking in a circle without a solution to the problem. I think the error confirmed there is a discrepancy but knowing that is not a solution.

Update requires restoring the products and customers etc. from the old database and this seems to be the source of the problem. I did NOT see any option on new install or upgrade to resolve that conflict while updating or restoring from the old.

I don't need to confirm again what is known. I need to know how to fix it while not losing all the products and settings from the prior version.

The other thing that since crossed my mind was to ignore the upgrade instructions and instead, use EasyPopulate to export from the old and import to the new and avoid this instruction to restore from the old version database.

Thoughts?

EP will not get the customer stuff imported

Again, if a stock install is latin1_general_ci
And the DB to be imported is something else then it was changed

I think the error confirmed there is a discrepancy but knowing that is not a solution.
You were already provided the solution earlier by haredo

http://www.zen-cart.com/forum/showthread.php?t=168872

kobra:

Again, if a stock install is latin1_general_ci
And the DB to be imported is something else then it was changed

I am still trying to determine how it was changed? I have a lot of these to upgrade and knowing that in advance might help me see in advance if the others were changed. Perhaps change it back from the CP and do a fresh backup to use with utf8 set in place.

Is this a function in the Zen control panel that could do this? That is the ONLY place any of the users would have available to them to make such a change. I never made any such changes myself so if it was changed the customer has to have done it.

Just fixing this one then running into the same problem for others is not what I am trying to do if I can avoid this error on the rest.

:smile:

OK, maybe Kobra is not getting through...so let me try....

What I haven't seen you answer is the question about what the collation is when you install a stock 1.3.9h by installing it and going and looking in phpmyadmin to see what it says. If it is anything other than latin1_general_ci then the server is set up to create the databases in another collation method. That would be why the older sites are a different collation method from what the zc_installer expects and you get the error when upgrading the databases.

Stating that you are the host doesn't help much if you don't know what your doing when it comes to server administration and being able to test something as simple as what collation method your server creates databases in by default....

Sorry for being blunt but this thread keeps going over the same ground because you are sure somebody must have changed the collation, so apparently have not checked the default......so hopefully I have clarified what Kobra was trying to get you to do....

barco57:

OK, maybe Kobra is not getting through...so let me try....

What I haven't seen you answer is the question about what the collation is when you install a stock 1.3.9h by installing it and going and looking in phpmyadmin to see what it says. If it is anything other than latin1_general_ci then the server is set up to create the databases in another collation method. That would be why the older sites are a different collation method from what the zc_installer expects and you get the error when upgrading the databases.

Stating that you are the host doesn't help much if you don't know what your doing when it comes to server administration and being able to test something as simple as what collation method your server creates databases in by default....

Sorry for being blunt but this thread keeps going over the same ground because you are sure somebody must have changed the collation, so apparently have not checked the default......so hopefully I have clarified what Kobra was trying to get you to do....

I just moved to a brand new server with cPanel and had always used Plesk in the past so I am still wandering in the woods when it comes to fining everything in my CP.

I know from looking at the DB for this particular install it shows utf8 until the very bottom line (summary) where is shows swedish?

I am going to ask my SysAdmin for his input into this. I need to know how to determine what problems I may have upgrading ALL of the zen installs, not just this first test case.

Is that a clean install or one of the ones you have to upgrade?

I just moved to a brand new server with cPanel and had always used Plesk in the past
That would have been important information you could have provided earlier

A default CP config will accept the script DB collation

If these ZenCarts were on plesk there is no telling if it was configured to force UTF8 or not

Unless you still have access to it - - if so, then get off the dime and do a test install there and CHECK the collation

If not - - Get off the dime and check the DB's that you intend upgrading BEFORE you upgrade

You can create "2" installers
One default
One where you edit it for UTF8
ie: thange the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

Now you can do either

I have that problem too now, only just upgraded from 138a to 139h.

Got the charset error message during upgrade:-

1253 COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
in:
[CREATE TABLE counter_history_clean as SELECT * FROM counter_history WHERE 1 GROUP BY startdate COLLATE utf8_general_ci;]

Although I had set the installer_params.php to accept 'utf8' and "ignore_db_charset" to "TRUE" in the hope these would by-pass the error messages or force the issue.

My database host is set for utf8 on all tables.

Now entering the Admin section and receiving the message to patch the databases to a higher level.

I've already gone through the "3 Easy Steps" - creating a new DB, exporting the contents of the old DB, importing the contents into the new DB moving the entire site folder to a new location, as a test run, altering all configure files and others to reflect the new location, the new db, etc., etc. So my end appears to be fully-functing, but the interaction with the db isn't working and needs a corrective fix.

What and where it is?

I've followed a few threads over the last 18 hours and found no clear, effective answer to the question of correcting the CHARSET and DB issue.

Current database patch level on the cart's admin section reads 1.3.8

I've also tried running zc_install a couple of times to upgrade the database, and back to the [1253 COLLATION 'utf8_general_ci'...] ERROR message.

If the cart is defaulting to 'latin1' and still offers the option to override to 'utf8' then how do we resolve this conflict?

1. Alter source code?
2. Change all the database, creating new ones in 'latin1' format?
   The current DB settings include:-
   1 . MySQL charset: UTF-8 Unicode (utf8)
   2 . MySQL connection collation: utf8_unicode_ci

From an amateur's point of view this is not something I want to be meddling with unless there is no alternative and there are clear instructions, guidelines and safety hats provided.

:wacko:

worldmaker:

I've followed a few threads over the last 18 hours and found no clear, effective answer to the question of correcting the CHARSET and DB issue.

As latin1_general_ci is the default and yours is utf8 - - it was altered by something

To continue using the utf8 DB I reiterate - -

Do the following before running the zc_install to upgrade the DB

change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

kobra:

As latin1_general_ci is the default and yours is utf8 - - it was altered by something

To continue using the utf8 DB I reiterate - -

Do the following before running the zc_install to upgrade the DB

change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

You didn't read my post - I had already made that change in advance of running the upgrade.

Then received the Error message: "1253 COLLATION 'utf8_general_ci' ..."

Then the internal, Admin, message regarding the need to patch the database.

I am now seeking clarification and answers to resolve the latter issue - how to resolve the patch issue given that my databases are in utf8 format.

:clap:

I appear to have solved the problem, thus:-

1 . Using my test database I dropped all the tables, and cleared it for a fresh start on solving this issue.

2 . I copied then edited the copy of the .sql file from the master store database - the export file. Edited using simple text editor, not word processor. And saved back in simple text format as a .sql file.

3 . In this editing I replaced any references made to any character set other than "latin1_general_ci".

4 . Saved this edited sql file under new name.

5 . Used this new, edited .sql file as the import source for the new database.

6 . During the import I carefully selected the "latin1" character set for the process (at least I think that's what that button meant!), because the service provider defaults to "utf8".

7 . Returned to zc_install/includes/installer_params.php and edited out the "utf8" and restored the "latin1".

8 . Returned through browser to begin installation again.

9 . After a little hiccup or two (had to make configure.php file writable), and carefully going through the steps to upgrade - everything went smoothly.

Conclusion
So far, so good.

Back into the Admin section.
No RED WARNING MESSAGES.
No other alerts that I can see on a preliminary glance.

Will now have to check that all the products, etc., etc., are back in place, to reinstall the payment and shipping modules, add others and see what happens.

I'm sure if something goes wrong you'll be hearing from me again, but on this issue - SOLVED IT!

:smartalec:

worldmaker:

3 . In this editing I replaced any references made to any character set other than "latin1_general_ci".
If your store's products, categories, customers, ez-pages, etc, use ANY non-english-characters, then DO NOT DO THAT, else you'll find all your database content will contain odd annoying symbols where those characters were originally found.

If your database contains strictly english-only characters and no extended-character symbols, (ie: not even TM or (c) symbols), then the raw data-hacking approach you described may work if executed perfectly.

USE WITH EXTREME CAUTION!!!!

Thanks for the addition Doc.

It did cross my mind briefly, to warn everyone but it's been a long couple of days and things are getting blurry around here (it's now past midnight) and I have to assume a little common sense out there, don't I, please?

I'm sure to find a problem later, especially with extended characters, but for now, this worked on my test site.

:unsure:

Since I am not a programmer I have decided to not upgrade in the hopes that a future release will contain a more developed upgrade path that includes a solution to this.

I still don't understand why there is this discrepancy since I never did anything to change from UTF8 prior to the upgrades..what ever it is before the upgrades was the default from the original Zen installation. I always use the defaults for everything other than the obvious site-specifics like the DB info and admin information.

Someone kept insisting I changed something related to the UTF8 but I would not know how nor would I have a reason to do so. If there is a method to change that in the prior version then the upgrade instructions need to warn users and add a 4th step to go to the CP and make sure the utf8 is changed prior to trying the upgrade. :mellow:

**SUGGESTION
**
Can someone who understands all of this write a new Upgrade instruction to include this issue and how to avoid it from the start?

It does not appear to be either isolated or rare that others are finding the same errors when trying to upgrade. It seems more logical to prevent others from wading through dozens of posts to just include the necessary steps as part of the documentation to do the upgrade.

If I understood this myself I'd try to post it but I am confused which may have to do with my advancing years and desire for a nap :laugh:

stuffdone:

I always use the defaults for everything
You might BUT it may be a mysql default set by your host to only use utf8
Something then to open with your host

stuffdone:

**SUGGESTION
**
Can someone who understands all of this write a new Upgrade instruction to include this issue and how to avoid it from the start?

...

If I understood this myself I'd try to post it but I am confused which may have to do with my advancing years and desire for a nap :laugh:

Yes, so many comments on this subject floating around, but our hard-working programmers, all volunteers, can't be everywhere at once. It has crossed my mind to write something but the best I could do was the previous note offering one solution and how to do it, I haven't the time for more (struggling with other problems and relearning everything).

However, I did upload my new cart with the edited (export).sql file as I noted above, only this time I tested editing the (export).sql (that's the file exported from the working source database) for "utf8" instead of "latin1" and switched the "zc_install/includes/installer_params.php" to favour "utf8" and had no trouble upgrading at all. This makes my db ready for the next major upgrade when the cart shifts to the new improved model.

Most of it can be done in a plain text editor, if you're language pack is not radically off the mainstream.

As DrByte recommends, this ought to be done with GREAT CARE, and I suggest creating a new separate test database and site first before running a full upgrade to your new cart. And editing a copy of your .sql files not the original export.

:lookaroun

You could try editing /zc_install/includes/functions/general.php on line 150, changing this:$collateSuffix = ' COLLATE ' . DB_CHARSET . '_general_ci'; to this:```
$collateSuffix = (strtoupper($param[3]) == 'AS' || (isset($param[6]) && strtoupper($param[6]) == 'AS')) ? '' : ' COLLATE ' . DB_CHARSET . '_general_ci';