50 replies to this topic

[jpweber]

Hi all. I've researched this to the hilt, and I want to post a fix to the error I've been getting all along, which I've finally put an end to. It's the ol' 1062 Duplicate Entry error, and I have a fix for all. Everybody seems to get these, eventually, but very few people post about 'em in here.

The Problem -- Duplicate Entry 1062 Errors:
I kept having a duplicate error report for sessions.  It is a problem with sessions. I was having this error everytime I selected create_account, or wishlist_help.  

The Fix:
1) Go to your phpmyadmin, and select the sessions table

2) Select sesskey

3) Click edit

4) It's probably set at the OSC default, which is varchar(32).  This is a mistake!  Change the type to INT, the length/values should stay at 32.

5)  Scroll all the way across to where you see extras and select AUTOINCREMENT from the drop down box.

6) Hit save button

All 1062 Duplicate Enty errors will not be resolved.  You won't get 'em again!


If I am correct, OsCommerce creates sessions id for every visit to your website, in case a visitor has cookies disabled.  Somehow during configuration, the sessions table probably wasn't configured to autoincrement.  This was a simple fix that you wouldn't even need to change any of the values for your test runs. Just fix the sesskey and you're good to go!

J

[enigma1]

the session key is alphanumeric and it should be generated by not following a specific pattern. By changing it to an integer with the autoincrement attribute you allow others to predict easily sessions of customers and even manufacture session cookies. Not a good thing.

[sharplab]

enigma1, on Jul 16 2006, 12:54 AM, said:

the session key is alphanumeric and it should be generated by not following a specific pattern. By changing it to an integer with the autoincrement attribute you allow others to predict easily sessions of customers and even manufacture session cookies. Not a good thing.

Had SIMILIAR PROBLEM!

Thanka!

[jpweber]

Okay, I hear what you're trying to say .... but then why did all my problems go away when I switched the sesskey to INT?  I researched it, and many people felt the same way.  All I know is that all my problems ... duplicate entry 1062 errors, etc., all disappeared.  

J

[enigma1]

jpweber, on Jul 21 2006, 10:58 AM, said:

Okay, I hear what you're trying to say .... but then why did all my problems go away when I switched the sesskey to INT?  I researched it, and many people felt the same way.  All I know is that all my problems ... duplicate entry 1062 errors, etc., all disappeared.  

J

maybe you should check your sessions table with phpmyadmin to see what's going on.

And one other thing. Even if you add an extra auto-increment column to get around it (and that will work) you basically try to hide a problem instead of troubleshooting it. The tep_session_start should generate a unique session via the core php functions. Theoretically and with the default oscommerce there is no issue. So something is going on with your store that causes it.

[jpweber]

Okay, thanks .. I'll look into it and see what I can figure out,

J

[jackrabbit]

jpweber, on Jul 21 2006, 01:33 PM, said:

Okay, thanks .. I'll look into it and see what I can figure out,

J

Did you find out anything? I am having a similar issue and it's a little frustrating as I'm sure you know.

;-j

[jpweber]

jackrabbit, on Aug 2 2006, 03:47 PM, said:

Did you find out anything? I am having a similar issue and it's a little frustrating as I'm sure you know.

;-j

You can truncate table sessions when there are no customers online, but really ... the more I've researched, the more I've come up with the fact that you should just go into your phpmyadmin, and change the value of your sesskey in the sessions table to INT, not VarChar.  You can always change it back.  Just try it; I bet your problems go away.

Go into phpmyadmin and edit the sesskey.  Set it to int(32) and set it to auto-increment.  Problems solved, and no more problems arise.  Worked for me (and several others).  Let me know,

J

[jackrabbit]

jpweber, on Aug 2 2006, 03:53 PM, said:

You can truncate table sessions when there are no customers online, but really ... the more I've researched, the more I've come up with the fact that you should just go into your phpmyadmin, and change the value of your sesskey in the sessions table to INT, not VarChar.  You can always change it back.  Just try it; I bet your problems go away.

Go into phpmyadmin and edit the sesskey.  Set it to int(32) and set it to auto-increment.  Problems solved, and no more problems arise.  Worked for me (and several others).  Let me know,

J

Hey J,
I appreciate your working on a solution to this, but I have to say that Enigma's comment seems quite relevant. Don't you think that, regardless of the results of the fix you propose, you are opening up a potential security risk with it? I have to admit that I don't know enough to be able to say one way or another.

My site is working right now, so I don't feel like I have to put it at risk. My problem is just that when I set products to special I recieve the error once then it goes away until I set another product to special. I would like to know more about troubleshooting this error, but I'm afraid I dont' know where to start.

[jpweber]

No, I don't see the security risk.  I mean, there are imaginable scenarios in everything we do.  I mean, there's a loophole in things where you can physically type in checkout_success.php into your browser and never even enter your credit card information, and your order goes through.  I'm sure there are imaginable loopholes.  But I'm leaving my sesskey as INT auto incremented becaused based on my research, it's the right thing to do.  And it solved all my problems.

J

[boxtel]

jpweber, on Aug 3 2006, 09:34 AM, said:

No, I don't see the security risk.  I mean, there are imaginable scenarios in everything we do.  I mean, there's a loophole in things where you can physically type in checkout_success.php into your browser and never even enter your credit card information, and your order goes through.  I'm sure there are imaginable loopholes.  But I'm leaving my sesskey as INT auto incremented becaused based on my research, it's the right thing to do.  And it solved all my problems.

J

I totally agree with Mark on this.

First of all, I am not sure those sessions will still function but even if they do it is now so much easier to guess an active session id in use by somebody else.
If I go to your store with cookies disabled and get session id 2345 in my url's then chances are pretty high that if I alter that to 2344 I get the information from the previous customer.

With random session id's (alphanumeric or even plain numeric)  that is not such a straightforward issue.

And Mark is correct, you are eliminating symptoms not the root cause. if you get duplicate entries then your table is either not cleared, you are generating session id's like wildfire and thus increasing the chances of session id collision or for some other reason you are trying to insert the same session id (instead of updating the existing one).

[jpweber]

Well, I have read SO many articles that say sessions should be INT and auto incremented .... data analysts, programmers, etc. ....

But there's no doubt, even before I added a contribution, I was getting 1062 duplicate error messages.  And that's on a clean installation of OSC.  I had to constantly truncate the sessions table.  So *something* had to be fixed.  Through my research, that was my fix.  With my customer base, it's highly unlikely that an end-user is going to know how to alter his session -- and even if he does know (which he won't, because I don't even know how to do it), I'm still doubtful he'll get the information from a previous session.  As for now, my OSC, 6 contributions later, is functioning well.  

J

[boxtel]

jpweber, on Aug 3 2006, 10:23 PM, said:

Well, I have read SO many articles that say sessions should be INT and auto incremented .... data analysts, programmers, etc. ....

But there's no doubt, even before I added a contribution, I was getting 1062 duplicate error messages.  And that's on a clean installation of OSC.  I had to constantly truncate the sessions table.  So *something* had to be fixed.  Through my research, that was my fix.  With my customer base, it's highly unlikely that an end-user is going to know how to alter his session -- and even if he does know (which he won't, because I don't even know how to do it), I'm still doubtful he'll get the information from a previous session.  As for now, my OSC, 6 contributions later, is functioning well.  

J

well, sure, it can very well be that it is an error that comes with the default install.
But if you do it with INT and Autoincrement, at least make sure that the session id is NEVER shown in the url's by forcing cookies. That way they are ALWAYS stored in cookies and not readily visible.
Then again forcing cookies has its own pro's and con's.

Still, that is only a small improvement as people who want to look for other peoples data are bound to know how to read that cookie and still know what likely active id's are.

It is basically like giving all the people in your street a combination lock to their cars and make the combinations sequential. So you know, only if he wants to ofcourse, that your neighbour can have a joy-ride in yours any time.

[enigma1]

what you have there is going to break sooner or later. You changed the session key to integer/auto-increment. Now as the auto-index increases so the chances of passing a key (from the actual session) to the _sess_write function with the first character as a digit (0-9) and that digit will be already in the database which will give you a duplicate entry for the auto-increment field.

[jpweber]

So then what's the fix to the ol' duplicate entry error 1062 in the sessions table?  Just keep truncating the table when no customers are online every day?

J

[enigma1]

well this error should not happen in the first place. Check the sessions logic when a new session is to be inserted.

	  $check_query = tep_db_query("select count(*) as total from " . TABLE_SESSIONS . " where sesskey = '" . tep_db_input($key) . "'");
	  $check = tep_db_fetch_array($check_query);

	  if ($check['total'] > 0) {
		return tep_db_query("update " . TABLE_SESSIONS . " set expiry = '" . tep_db_input($expiry) . "', value = '" . tep_db_input($value) . "' where sesskey = '" . tep_db_input($key) . "'");
	  } else {
		return tep_db_query("insert into " . TABLE_SESSIONS . " values ('" . tep_db_input($key) . "', '" . tep_db_input($expiry) . "', '" . tep_db_input($value) . "')");
	  }

Notice the code performs a check query with the session key passed and inserts this as a new key only when the key is not found in the database.

Now it could be something else like a null key. Have you implement the latest osc upgrade?
http://www.oscommerce.com/solutions/downloads

It has a specific check filtering the session id with the tep_session_start() function. Make sure you have those changes integrated with your store.

[ehong33234]

enigma1, on Aug 3 2006, 11:57 AM, said:

well this error should not happen in the first place. Check the sessions logic when a new session is to be inserted.

	  $check_query = tep_db_query("select count(*) as total from " . TABLE_SESSIONS . " where sesskey = '" . tep_db_input($key) . "'");
	  $check = tep_db_fetch_array($check_query);

	  if ($check['total'] > 0) {
		return tep_db_query("update " . TABLE_SESSIONS . " set expiry = '" . tep_db_input($expiry) . "', value = '" . tep_db_input($value) . "' where sesskey = '" . tep_db_input($key) . "'");
	  } else {
		return tep_db_query("insert into " . TABLE_SESSIONS . " values ('" . tep_db_input($key) . "', '" . tep_db_input($expiry) . "', '" . tep_db_input($value) . "')");
	  }

Notice the code performs a check query with the session key passed and inserts this as a new key only when the key is not found in the database.

Now it could be something else like a null key. Have you implement the latest osc upgrade?
http://www.oscommerce.com/solutions/downloads

It has a specific check filtering the session id with the tep_session_start() function. Make sure you have those changes integrated with your store.

I'm sorry I am new to all of this and am getting this error.  What is the solution? Thanks in advance...

[xiaoge]

ehong33234, on Aug 18 2006, 05:29 PM, said:

I'm sorry I am new to all of this and am getting this error.  What is the solution? Thanks in advance...

I have just met that problem.and now i've removed it.

according to enigma1, osc has made sure that the key item can't be insert twice. but for mysql,there is such a condition that if the string is too long ,it will be cut short to the certain length.

for example: 123456789 exists in the database, if you insert 123456789123,it won't find 123456789123 in the table,but when you insert 123456789123 into the table ,mysql cut it to 123456789 automatically,so,there shows an error.

so,to solve the problem .

first when you have klicked on one category. make sure that in the address  there is like:".../index.php?cPath=44&osCsid=0983152dd2ebf0ad25171fbfbdef3c31", and there is nothing after this string. if there is ,the error occurs. and then you should exam you file "./includes/boxes/categories",make sure that the href link is right.

this is my first threat here. sorry for my pool english.

[Vger]

Its' not just the sessions table that you'll get the 1062 error on.  Just a few days ago I came across a site that was getting that on the orders table - load one order fine, next order comes up with the 1062 error.

Whilst everything looked fine on the surface, dropping and reinstalling all 'orders' tables fixed the problem.  Basically it was the database tables that were the problem.

Vger

[enigma1]

xiaoge, on Aug 19 2006, 04:50 AM, said:

I have just met that problem.and now i've removed it.

according to enigma1, osc has made sure that the key item can't be insert twice. but for mysql,there is such a condition that if the string is too long ,it will be cut short to the certain length.

for example: 123456789 exists in the database, if you insert 123456789123,it won't find 123456789123 in the table,but when you insert 123456789123 into the table ,mysql cut it to 123456789 automatically,so,there shows an error.

so,to solve the problem .

first when you have klicked on one category. make sure that in the address  there is like:".../index.php?cPath=44&osCsid=0983152dd2ebf0ad25171fbfbdef3c31", and there is nothing after this string. if there is ,the error occurs. and then you should exam you file "./includes/boxes/categories",make sure that the href link is right.

this is my first threat here. sorry for my pool english.

This is actually valid. It can happen with the default functions. I have an extra filter to check the key length specifically for this reason. However the default osc sessions.php doesn't. So you do need to check the length in tep_session_start() where it flags the sane id. So the key should be 24<= key <= 32 digits. Or in the _sess_write() handler

Edited by enigma1, 19 August 2006 - 02:33 PM.