Database Patch

I'm seeing a lot of people with the same error and haven't seen any solutions. My site says

"Your database appears to need patching to a higher level. See Admin->Tools->Server Information to review patch levels."

at the top currently. I have ran zc_install Database Upgrade only twice now and each time I get this:

"253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8'
in:
[CREATE TABLE counter_history_clean as SELECT * FROM counter_history WHERE 1 GROUP BY startdate COLLATE latin1_general_ci;]"

Server Information says:

Zen Cart 1.3.9b
Database Patch Level: 1.3.8

How can I get this off my site or upgrade properly?

Thanks.

I had the same problem. Here are two links discussing this issue.

http://www.zen-cart.com/forum/showthread.php?t=157128&page=2

http://www.zen-cart.com/forum/showthread.php?t=153617&highlight=counter+history

The code discussed on the 2nd link worked for me. Although I would have sworn it was unrelated.

I hope that helps!

I did the upgrade through fantastico and got this problem. I cant seem to get it fixed either. I ran the ZC_install and it still doesnt work right.

Also somehow it put a bunch of default adds and headers :shocking: i need help :(

2kredz3:

I did the upgrade through fantastico and got this problem. I cant seem to get it fixed either. I ran the ZC_install and it still doesnt work right.

Also somehow it put a bunch of default adds and headers :shocking: i need help :(

NEVER upgrade via Fantastico or other automated piece of server facility.

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

completespray - Thanks, but that didn't seem to help me. Just to make sure I followed it correctly here's what I tried. Here's my current/changed counter.php:

if (!defined('IS_ADMIN_FLAG')) {

  die('Illegal Access');

}

if (isset($_SESSION['session_counter']) && $_SESSION['session_counter'] == true) {

  $session_counter = 0;

} else {

  $session_counter = 1;

  $_SESSION['session_counter'] = true;

}
$date_now = date('Ymd');
$sql = "insert into " . TABLE_COUNTER_HISTORY . " (startdate, counter, session_counter) values ('" . $date_now . "', '1', '1')
        on duplicate key update counter = counter + 1, session_counter = session_counter + " . (int)$session_counter;
$db->Execute($sql);

$counter_query = "select startdate, counter from " . TABLE_COUNTER;
$counter = $db->Execute($counter_query);
if ($counter->RecordCount() <= 0) {
  $date_now = date('Ymd');
  $sql = "insert into " . TABLE_COUNTER . " (startdate, counter) values ('" . $date_now . "', '1')";
  $db->Execute($sql);
  $counter_startdate = $date_now;
  $counter_now = 1;
} else {
  $counter_startdate = $counter->fields['startdate'];
  $counter_now = ($counter->fields['counter'] + 1);
  $sql = "update " . TABLE_COUNTER . " set counter = '" . $counter_now . "'";
  $db->Execute($sql);
}


$counter_query = "select startdate, counter from " . TABLE_COUNTER;

$counter = $db->Execute($counter_query);

if ($counter->RecordCount() <= 0) {

  $date_now = date('Ymd');

  $sql = "insert into " . TABLE_COUNTER . " (startdate, counter) values ('" . $date_now . "', '1')";

  $db->Execute($sql);

  $counter_startdate = $date_now;

  $counter_now = 1;

} else {

  $counter_startdate = $counter->fields['startdate'];

  $counter_now = ($counter->fields['counter'] + 1);

  $sql = "update " . TABLE_COUNTER . " set counter = '" . $counter_now . "'";

  $db->Execute($sql);

}



$counter_startdate_formatted = strftime(DATE_FORMAT_LONG, mktime(0, 0, 0, substr($counter_startdate, 4, 2), substr($counter_startdate, -2), substr($counter_startdate, 0, 4)));

and I ran this:

DROP TABLE IF EXISTS counter_history;
CREATE TABLE counter_history (
  startdate char(8) default NULL,
  counter int(12) default NULL,
  session_counter int(12) default NULL,
  PRIMARY KEY  (startdate)
) ENGINE=MyISAM;

in the admin's Install SQL Patches. Then I ran the Upgrade Database in zc_install. Still got this:

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

Admin still says Zen Cart 1.3.9b
Database Patch Level: 1.3.8

but when I ran the zc_install it says Database appears to be Zen Cart™ v1.3.9.

Do I need to upgrade Zen Cart?

buzzfever:

1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8'
Evidently you've created the table with UTF8 character set. If that's what you want to continue using, then change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

DrByte:

Evidently you've created the table with UTF8 character set. If that's what you want to continue using, then change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

Thank you DrByte! That worked for me, and I'm sure it would work for anyone else getting that same error. You saved me from doing a fresh install of my site. :D

OH MY GOSH, DrByte you are amazing! Thank you so much for your time and help. I can't believe it was so simple.

DrByte:

Evidently you've created the table with UTF8 character set. If that's what you want to continue using, then change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

Thank you! A very easy fix. :smile:

DrByte:

Evidently you've created the table with UTF8 character set. If that's what you want to continue using, then change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

Thanks Heaps!!

DrByte:

Evidently you've created the table with UTF8 character set. If that's what you want to continue using, then change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

HI Dr, Byte,

Did this:

<?php /** * @package Installer * @access private * @copyright Copyright 2003-2010 Zen Cart Development Team * @license <http://www.zen-cart.com/license/2_0.txt> GNU Public License V2.0 * @version $Id: installer_params.php 16886 2010-07-09 16:26:42Z drbyte $ */ /** * Runtime Parameters used by browser interface */ // $session_save_path = (@ini_get('session.save_path') && is_writable(ini_get('session.save_path')) ) ? ini_get('session.save_path') : realpath('../cache'); $session_save_path = (is_writable(realpath('../cache')) ) ? realpath('../cache') : ini_get('session.save_path'); define('SESSION_WRITE_DIRECTORY', $session_save_path); define('DEBUG_LOG_FOLDER', realpath('../cache')); // Set the following to TRUE if having problems (blank pages, etc). Best to leave at FALSE for normal use. define('STRICT_ERROR_REPORTING', FALSE); // optionally set this to 'utf8': define('utf8', 'latin1'); // optionally uncomment the following line if choosing 'utf8' or 'latin1' above are causing problems: // define('IGNORE_DB_CHARSET', TRUE); And I am still getting this: 1253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8' in: [CREATE TABLE counter_history_clean as SELECT * FROM counter_history WHERE 1 GROUP BY startdate COLLATE latin1_general_ci;] What am I doing wrong here?? Thanks.

PinkLeopard:

What am I doing wrong here??
Read post #6

And please refrain from posting this same issue every where

Not sure if this is the same problem, but it is related... the footer of my Admin area right now says "Zen Cart v1.3.9h/v1.3.9c" and when I looked up my server info, sure enough it says that my version of Zen Cart is 1.3.9h and my database patch level is 1.3.9c. Is this something I should try to fix, or is this ok? I haven't seen any warning messages about there being a problem, and everything appears to be operating fine.

Hello!

I have a UTF-8 database and I'm getting this error on upgrade:

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

I have edited installer_params.php to try utf8 and to uncomment the "IGNORE_DB_CHARSET" line - no luck for either!

I've also added a my_db_charset.php in the extra_configures folders. Is there somewhere else I need this info? I'm thinking I inadvertently lost a needed file change when preparing the upgrade files.

DrByte:

Evidently you've created the table with UTF8 character set. If that's what you want to continue using, then change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

It would have been better if the install/upgrade routine would look at the database to see which format was used so it would know and not have to throw this error, rather than making a poor incorrect assumption and having it hard coded in the upgrade scripts.

Besides, in these days, don't most people use UTF8? The original install of Zencart uses UTF8 as we never chose that. Why would the install script just assume that someone changed it?

New database installs these days mostly use UTF8, but a large majority of existing ZC installs still have latin1 character sets. We can't just assume that everyone will delete their database and start new using a new UTF8 database and not keep any of their history. Converting between charactersets is not a trivial matter, and is hardly something you should expect to be forced to do between minor version updates.
Things will be different with v2.0, since it's based on using UTF8 by default. And there's enough headache already in trying to prepare for that conversion. If you're an expert in the area of automating the conversion flawlessly for every possible server configuration and character set out there and every possible skill level of storeowner, please share your expertise.

hookah:

The original install of Zencart uses UTF8 as we never chose that.
UTF8 has **never **been the default character set since I have been involved: circa 2005

If yours is UTF8 it was altered by someone

DrByte:

Evidently you've created the table with UTF8 character set. If that's what you want to continue using, then change the DB_CHARSET in /zc_install/includes/installer_params.php to 'utf8' before using zc_install.

If not utf8, then what is the default character set that zencart uses? And why?

pricediscrimination:

If not utf8, then what is the default character set that zencart uses? And why?
Prior to v1.3.9, Zen Cart has never specified a character set as far as the database goes. It has always just used whatever character-set the database was created with, or whatever it was changed to after creation.

However, some of the country/state/zone names do contain some multibyte characters, and those were encoded in latin1 format.

Starting with v1.3.9 the ability to set DB_CHARSET was added as discussed above and in other threads.
v1.5 will probably use UTF8 by default.

buzzfever:

I'm seeing a lot of people with the same error and haven't seen any solutions. My site says

"Your database appears to need patching to a higher level. See Admin->Tools->Server Information to review patch levels."

at the top currently. I have ran zc_install Database Upgrade only twice now and each time I get this:

"253 COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8'
in:
[CREATE TABLE counter_history_clean as SELECT * FROM counter_history WHERE 1 GROUP BY startdate COLLATE latin1_general_ci;]"

Server Information says:

Zen Cart 1.3.9b
Database Patch Level: 1.3.8

How can I get this off my site or upgrade properly?

Thanks.
I too keep getting a message that the database needs a higher patch. I did a clean install, then imported data only from the old database into the new database (per instruction on Zen) then I ran the Database update program but got an error about Latin language?? and still get the error message. The server info states:
Zen Cart 1.3.9h

Database Patch Level: 1.3.8

v1.3.8 [2008-01-27 14:24:40] (Fresh Installation)
v1.3.8 [2008-01-27 14:24:40] (Fresh Installation)

Anyone got any great ideas on how to solve this issue?