[Done v1.6.0] 1062:Duplicate entry for key 1 in db_cache

On V1.5.0 which was updated 2012-02-06 from freshly installed Version 1.3.9h (2011-12-18). Have multiple mods installed, only recently changed the cache from none to file for a few days, but then read up on the deprecation of the file option, so changed to database. It wasn't until I had installed one of the mods that allowed me to review the logs inside the cache folder that I too observed this issue. In my searches for resolution I tried to chase backwards what event was causing this error; however, I have not coded in anything to catch more detail when this error is logged. Review of server logs for yesterday where I had 11 instances of the the following summarized error: PHP Fatal error: 1062:Duplicate entry 'zc_xxxx' for key 'PRIMARY' :: insert into znc_db_cache set cache_entry_name = 'zc_xxxx' (Where xxxx is a 32 character length field).

I hadn't seen if there was a solution to be offered (if it is thought to be a bot related issue, not sure what can be done to restrict the bot's actions, also may not want to "slow" the bot down within ZenCart as that may result in bad information being collected), but wanted to chime in, especially since it seems that a thread has been found that relates to this issue.

Is it really a situation that is to be reported in our logs? Is there some way to logic through it? If still in the investigation phase, I can understand that those questions may not be answerable yet. It does seem that it would have some sort of impact if it is an "error", but is it something that the bots care about? Is there something that should be modified on our server, or possibly should the company that "issued" the request(s) be notified? Is it just that these errors crop up because the server isn't fast enough to fully process the incoming traffic before performing the same action on the next/other requester? Sorry so full of questions, would like to help resolve this issue, as I have been deleting these duplicate records after identifying that I can not identify the root cause. :)

Hope all are well and keep up the great support. I haven't had to post anything really until now, because I have been able to find most of what I need via search of the forums, the internet, or specific code.

Is there additional information needed/desired that may help resolve this? I have noticed that the file size is getting progressively larger for the same cache_entry_name. I have also found that I have gotten this error on a different cache_entry_name as well resulting in a rather small file compared to the other.

Predominantly, they have been occurring during bot like views as previously identified by Dr. Byte

Has someone fix this issue? I still have this errors logged from time to time in my logs folders. Also was trying to figure it out but no luck.

abiku:

Has someone fix this issue? I still have this errors logged from time to time in my logs folders. Also was trying to figure it out but no luck.

I have come to find out that ZC 1.5.1 predominantly prevents this from happening. I have not upgraded my own site yet; however, have helped someone else with theirs and the problem appeared to well, disappear. At least I have not received any further inquiries about generated log files with that information in over a month and they have a rather busy site.

Old thread, but I think I know what's causing this...

the error can be found at includes/classes/cache.php and the code goes as follows:

      case 'database':
      $sql = "select * from " . TABLE_DB_CACHE . " where cache_entry_name = '" . $zp_cache_name . "'";
      $zp_cache_exists = $db->Execute($sql);
      if ($zp_cache_exists->RecordCount() > 0) {
        return true;
      }
      $result_serialize = $db->prepare_input(base64_encode(serialize($zf_result_array)));
      $sql = "insert into " . TABLE_DB_CACHE . " set cache_entry_name = '" . $zp_cache_name . "',
                                                 cache_data = '" . $result_serialize . "',
                   cache_entry_created = '" . time() . "'";
      $db->Execute($sql);
      return true;
      break;

So it first queries DB to check if the record exists.

if it does, return true ... end of story.

If it doesn't, serialize and base64_encode the config data..... this can take a long time, so it's possible that a new page load happens while the first one is still serializing and encoding, thus there is no data in the database when the check is made and the second page load goes on to serialize and encode the data too.... eg. a race condition bug.

The only way to prevent this would be to add the exact same check just before the insert statement execute call or rewrite the query with something like "replace".

v1.5.3

36 MyDEBUG files of 100kb each with this error generated at various times during a PCI scan which consumed 4+GB server bandwidth since midnight.

There should be quite a list of serverlogfile entries to go along with the creation of these MyDEBUG files if it will help.

Try the correction posted here:

http://www.zen-cart.com/showthread.php?214335-Duplicate-Sessions&p=1257487#post1257487

Wasn't able to identify the specific individual to thank for creating/first posting the solution, but it has worked for the site tested on.

TNX mc123456578

The v1.5.3 sessions.php differs enough to where I do not trust my editing skills. I just realized that I posted to a v1.5.1 thread. OOOPS.

lhungil did mention changing cache from database to none in the configuration files. I have not done that yet since the error has only appeared during a PCI scan with 1500 'customers online'.

v1.5.3 sessions.php lines 60-68

    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;

    $qid = "select count(*) as total
            from " . TABLE_SESSIONS . "
            where sesskey = '" . zen_db_input($key) . "'";
    $total = $db->Execute($qid);

    if ($total->fields['total'] > 0) {

Sorry for taking solong to get back to this, but when I compared the same area of code between 1.5.1 and 1.5.3, they had equivalent code requiring the same replacement as described in the link. The replacement involves moe than what was provided above. Take a look at the thead again, but the replacement of the if and else statements is to be included. Ie, everything quoted in the first part of the post with the content posted by lat9.

mc12345678:

Sorry for taking solong to get back to this, but when I compared the same area of code between 1.5.1 and 1.5.3, they had equivalent code requiring the same replacement as described in the link. The replacement involves moe than what was provided above. Take a look at the thead again, but the replacement of the if and else statements is to be included. Ie, everything quoted in the first part of the post with the content posted by lat9.

TNX,

I think I'm just going to leave things as-is for now and just add it to the list of database issues to ignore (but watch).

Since this MyDebug is mainly generated during a PCI scan, it doesn't make much sense to make edits that are outside of my skillset.

RixStix:

TNX,

I think I'm just going to leave things as-is for now and just add it to the list of database issues to ignore (but watch).

Since this MyDebug is mainly generated during a PCI scan, it doesn't make much sense to make edits that are outside of my skillset.

Can understand the feeling. It is a copy and paste/replace though.

This (all of this):

   $expiry = time() + $SESS_LIFE;

    $qid = "select count(*) as total
            from " . TABLE_SESSIONS . "
            where sesskey = '" . zen_db_input($key) . "'";

    $total = $db->Execute($qid);

    if ($total->fields['total'] > 0) {
      $sql = "update " . TABLE_SESSIONS . "
              set expiry = '" . zen_db_input($expiry) . "', value = '" . zen_db_input($val) . "'
              where sesskey = '" . zen_db_input($key) . "'";

      $result = $db->Execute($sql);

    } else {
      $sql = "insert into " . TABLE_SESSIONS . "
              values ('" . zen_db_input($key) . "', '" . zen_db_input($expiry) . "', '" .
                       zen_db_input($val) . "')";

      $result = $db->Execute($sql);

    }

Is to be replaced by this:

 $expiry = time() + $SESS_LIFE;
 $sql = "insert IGNORE into " . TABLE_SESSIONS . "
           values ('" . zen_db_input($key) . "', '" . zen_db_input($expiry) . "', '" . zen_db_input($val) . "')";

 $db->Execute($sql);

 $sql = "update " . TABLE_SESSIONS . "
           set expiry = '" . zen_db_input($expiry) . "', value = '" . zen_db_input($val) . "'
           where sesskey = '" . zen_db_input($key) . "'";

 $result = $db->Execute($sql);

And done...

mc12345678:

Can understand the feeling. It is a copy and paste/replace though.

This (all of this):

$expiry = time() + $SESS_LIFE;

$qid = "select count(*) as total
        from " . TABLE_SESSIONS . "
        where sesskey = '" . zen_db_input($key) . "'";

$total = $db->Execute($qid);

if ($total->fields['total'] > 0) {
  $sql = "update " . TABLE_SESSIONS . "
          set expiry = '" . zen_db_input($expiry) . "', value = '" . zen_db_input($val) . "'
          where sesskey = '" . zen_db_input($key) . "'";

  $result = $db->Execute($sql);

} else {
  $sql = "insert into " . TABLE_SESSIONS . "
          values ('" . zen_db_input($key) . "', '" . zen_db_input($expiry) . "', '" .
                   zen_db_input($val) . "')";

  $result = $db->Execute($sql);

}

> 
> Is to be replaced by this:
> 
> ```
 $expiry = time() + $SESS_LIFE;
 $sql = "insert IGNORE into " . TABLE_SESSIONS . "
           values ('" . zen_db_input($key) . "', '" . zen_db_input($expiry) . "', '" . zen_db_input($val) . "')";

 $db->Execute($sql);

 $sql = "update " . TABLE_SESSIONS . "
           set expiry = '" . zen_db_input($expiry) . "', value = '" . zen_db_input($val) . "'
           where sesskey = '" . zen_db_input($key) . "'";

 $result = $db->Execute($sql);

And done...

Tried that. The code in red is what was confusing me. I've come to assume red text is the edit when included with other code since I did not make a connection of "replace this" with "this".

That copy/paste/replace just causes a blank white page.

Assuming the file is includes\functions\sessions.php (lines 61-78) since the file was never listed by complete name.

First time, I missed including the last brace in the replace. (blank white page)
Second time, I made sure to include the last brace in the replace. (blank white page)

Blank white page causes myDebug

[27-Nov-2014 06:41:41 America/Los_Angeles] PHP Parse error:  syntax error, unexpected end of file in /home/XXXXXXXX/public_html/includes/functions/sessions.php on line 168

The 1062: error happened twice last night, which is one of the rare occasions seeing it not related to a PCI scan.

RixStix:

Tried that. The code in red is what was confusing me. I've come to assume red text is the edit when included with other code since I did not make a connection of "replace this" with "this".

That copy/paste/replace just causes a blank white page.

Assuming the file is includes\functions\sessions.php (lines 61-78) since the file was never listed by complete name.

First time, I missed including the last brace in the replace. (blank white page)
Second time, I made sure to include the last brace in the replace. (blank white page)

Blank white page causes myDebug

[27-Nov-2014 06:41:41 America/Los_Angeles] PHP Parse error: syntax error, unexpected end of file in /home/XXXXXXXX/public_html/includes/functions/sessions.php on line 168

> 
> The 1062: error happened twice last night, which is one of the rare occasions seeing it not related to a PCI scan.

I hate to say it, RixStix, but something else is going on with the replacement. The code being removed has balanced curly parentheses, and the code being inserted doesn't have any...  Yes the file is the includes/functions/sessions.php. Yes in ZC 1.5.3, the lines of code to be replaced are between 61 and 78 (inclusive), this results in the function _sess_write looking like this:

function _sess_write($key, $val) {
global $db;
if (!is_object($db)) return;
$val = base64_encode($val);

global $SESS_LIFE;
$expiry = time() + $SESS_LIFE;
$sql = "insert IGNORE into " . TABLE_SESSIONS . "
       values ('" . zen_db_input($key) . "', '" . zen_db_input($expiry) . "', '" . zen_db_input($val) . "')";

$db->Execute($sql);

$sql = "update " . TABLE_SESSIONS . "
       set expiry = '" . zen_db_input($expiry) . "', value = '" . zen_db_input($val) . "'
       where sesskey = '" . zen_db_input($key) . "'";

$result = $db->Execute($sql);

return (!empty($result) && !empty($result->resource));

}

The above has been specifically tested on ZC 1.5.3 with no white page.. Again, there must have been something awry with the replace/save.

Got it finally.

Actually, lines to be replaced were:

55 through 81 inclusive.

That is what was throwing things off when I just used the code to replace lines 61-78 inclusive by just looking at line numbers. Replacing 61-78 does not cause a white page, but it does generate a MyDebug file.

TNX mc12345678. We'll see what happens over the next week. Hopefully no 100k MyDebug files related to 1062:

Umm, thing is no where in the suggestion of replacing code that I posted the first time around were line numbers provided. Line numbers truly mean nothing to php code execution, and are provided for reference to those that are trying to generally locate something. In the case of error messages, it does provide useful information as that is specifically where the problem was identified.

Gone are the days of commands such as goto 20, or gosub 110 where those numbers represent line numbers.

I'd be interested in knowing whether using "REPLACE INTO" instead of "INSERT INTO" would give the desired results as "INSERT IGNORE INTO".
This article suggests it might be the more desirable approach: http://shiflett.org/articles/storing-sessions-in-a-database

DrByte:

I'd be interested in knowing whether using "REPLACE INTO" instead of "INSERT INTO" would give the desired results as "INSERT IGNORE INTO".
This article suggests it might be the more desirable approach: http://shiflett.org/articles/storing-sessions-in-a-database

Be glad to give it a shot. Until incorporating the above I was getting a mydebug message at least once a day when there was heavy traffic on the site (usually some form of a spider).

Looking over the article, the only thing I see that might be an issue is that: However, although REPLACE works with MySQL, it is not standard and does not work with all databases.

This. Might be an issue with some of the flexibility of ZC unless the databases with which it is compatible are confirmed to function with the REPLACE command.

mc12345678:

Looking over the article, the only thing I see that might be an issue is that: However, although REPLACE works with MySQL, it is not standard and does not work with all databases.

This. Might be an issue with some of the flexibility of ZC unless the databases with which it is compatible are confirmed to function with the REPLACE command.
Only if you rewrite the entire database infrastructure to use some other engine besides MySQL ... in which case you'd be rewriting syntax in lots of places including this one. So basically a non-issue at present.

DrByte:

Only if you rewrite the entire database infrastructure to use some other engine besides MySQL ... in which case you'd be rewriting syntax in lots of places including this one. So basically a non-issue at present.

Yeah, didn't exactly have my mind fully engaged at that point to understand that the warning offered by the author was more about using the REPLACE syntax on other types of databases than the mysql for which this code is written.

Have had the revision of using REPLACE instead of INSERT IGNORE for about a week, I have also just now realized that the use of the REPLACE command would do away with the UPDATE that is provided in the above code replacement as in the article it describes the REPLACE command to be something that will either insert if it doesn't exist or update if it does. Therefore the entire function could look like this:

  function _sess_write($key, $val) {
    global $db;
    if (!is_object($db)) return;
    $val = base64_encode($val);
    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;
    $sql = "replace into " . TABLE_SESSIONS . " (sesskey, expiry, value)
               values ('" . zen_db_input($key) . "', '" . zen_db_input($expiry) . "', '" . zen_db_input($val) . "')";
    $result = $db->Execute($sql);
    
    return (!empty($result) && !empty($result->resource));
   }

Which was something I missed before when considering to "replace insert ignore into with update into". I'll try the above and see if there is anything that appears wrong when using it. One thing from other sources is that supposedly the replace into command is slower than using equivalent checks, but the areas where I saw that discussion seemed like they might have been using function incorrectly or for the wrong things. There may be a reason to do a select first then a replace to prevent creating the race condition, at the moment it is purely a replace on the tested system. Not sure that I will be able to get "timing" statistics off of it, just merely go/no-go with its use. So if someone else would like to also try and obtain statistical data for analysis that would be great. :)