[Done v1.3.9b] Counter History issue in 1.3.9a

Hi there,

I recently upgraded a site from 1.3.8a to 1.3.9a. The admin counter history looked like this after upgrading:

04/26/2010 1-1
04/26/2010 1-1
04/26/2010 1-1
04/26/2010 1-1
04/26/2010 1-1
04/26/2010 1-1
04/26/2010 1-1
04/26/2010 1-1
04/26/2010 1-1
04/26/2010 1-1

I compared the includes/counter.php file from v1.3.8a and v1.3.9a and found this code change:

1.3.8a

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

1.3.9a

$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);

I put 1.3.8a code back in, and now the counter works fine. I'm not sure what the change in code was for, but it doesn't seem to be working correctly.

Thanks!

The code is working properly, but it requires that your database tables be updated properly first.

Thus, the symptoms you describe suggest that you didn't upgrade your database structure properly, so your database isn't properly handling the duplicates anymore.

It's important that you run zc_install to upgrade your database as part of the upgrade process.

DrByte:

The code is working properly, but it requires that your database tables be updated properly first.

Thus, the symptoms you describe suggest that you didn't upgrade your database structure properly, so your database isn't properly handling the duplicates anymore.

It's important that you run zc_install to upgrade your database as part of the upgrade process.

Yes I did do that already, and in the admin it does say the Database Patch Level is 1.3.9a.

When you did the database-upgrade, did it tell you that it skipped anything?

What messages are listed in your database's upgrade_exceptions table?

DrByte:

When you did the database-upgrade, did it tell you that it skipped anything?

What messages are listed in your database's upgrade_exceptions table?

The only things skipped were the things mentioned in the upgrade thread, I can't remember what they were offhand, something to do with Paypal I believe?

All the messages in the upgrade_exceptions table are related to add-ons. There were no messages about the counter_history table.

Use phpMyAdmin and Export the structure-only of your counter_history table. Output to screen. Paste it here.

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE counter_history (
startdate char(8) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
counter int(12) DEFAULT NULL,
session_counter int(12) DEFAULT NULL,
PRIMARY KEY (startdate)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Danielle:

I'm not sure what the change in code was for
http://www.zen-cart.com/forum/showthread.php?t=122543

DrByte:

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

Yes, I saw that thread already, but we didn't have any issue with duplicates until the upgrade to 1.3.9a. There were literally thousands of records per day since the upgrade, all with 1-1. Anyways, it seems to be working fine with the old code for now.

Thanks :)

What version of MySQL?

The only reason it should be making those duplicates is if your primary key isn't actually being enforced properly to prevent duplicates.
In fact, it must not be enforced properly, because if it was, then the duplicates you reported would be impossible.

Either that, or you're using a very old version of MySQL. Thus the question about which version of MySQL you're using.

Try replacing the code with this:```
$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);
}

Would appreciate your feedback, as well as an indicator of which MySQL version you're using.

I had the same problem. Here is how I fixed it.

The new code won't work unless the startdate field is primary or indexed, and it was neither. When I tried to make it primary, it failed, due to duplicate entries for the same date, that occurred before upgrading zen cart. After deleting all duplicates, I was able to make the startdate field primary, and the counter history started working correctly.

loves2loop:

I had the same problem. Here is how I fixed it.

The new code won't work unless the startdate field is primary or indexed, and it was neither. When I tried to make it primary, it failed, due to duplicate entries for the same date, that occurred before upgrading zen cart. After deleting all duplicates, I was able to make the startdate field primary, and the counter history started working correctly.
Okay, but, the zc_install upgrade process already takes care of fixing all those duplicate problems AND setting the primary key properly.
So, clearly you didn't use the published upgrade process.

I have the same issue I used the simple script in my cp panel to upgrade. My hosting tech suggested this as a way of upgrading. I do now have version 1.39c but the duplicate problems in the history.

DrByte:

Use phpMyAdmin and Export the structure-only of your counter_history table. Output to screen. Paste it here.

Where do I find the structure -only of the counter_history table?

Thanks

rjspomer:

I have the same issue I used the simple script in my cp panel to upgrade. My hosting tech suggested this as a way of upgrading. I do now have version 1.39c but the duplicate problems in the history.

Where do I find the structure -only of the counter_history table?

Thanks

Apparently your hosting tech has greater wisdom on upgrading than we do. Feel free to ask them for help since you followed their advice instead of ours.

DrByte:

Apparently your hosting tech has greater wisdom on upgrading than we do. Feel free to ask them for help since you followed their advice instead of ours.

OK I made a mistakes in using the simple script but I did not expect such a smartass remark. A lot of us are small business owners who do not have a lot of time or knowledge of scripts and not PHP gurus like you. Please don't talk down to us.

I know I am not worthy of your advise but can you try to help. :smile:

Sorry, I've just been dealing with too many cases where people did automated upgrades and broke things, instead of following the published upgrade instructions. I should have counted to 10 before replying.

No Problem :smile:

I appreciate the help and all you guys and gals do for nothing but a thank you.

Now that I have this messed up where do I start. I went into my counter history db and empty it. I had hundreds of duplicates over that last few days.

Thanks again

Given that you've already deleted all the data in the table, just recreate it:```
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;

Yes I have 1.3.9c

Please bear with me where do I paste this code?