Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
User avatar
qspypl
Registered User
Posts: 149
Joined: Wed Dec 20, 2006 7:15 pm

Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by qspypl » Fri Oct 10, 2008 4:47 pm

Our forum is very popular and i think this causing problems.
If we look on code below screenshot, we will see $search_id is randomized.
I think this error come's when mt_rand() give same result at some point.
Error comes randomly from time to time (what proves above theory) and dissapear when search again/relog.

Image
https://camo.phpbb.com/93a48569d9461dc14adfe77e722591a287b284b7/687474703a2f2f696d6167657332382e666f746f73696b2e706c2f3238332f323235343436386530373034306637632e706e67

Code: Select all

                mt_srand ((double) microtime() * 1000000);
                $search_id = mt_rand();

                $sql = "UPDATE " . SEARCH_TABLE . "
                        SET search_id = $search_id, search_time = $current_time, search_array = '" . str_replace("\'", "''", $result_array) . "'
                        WHERE session_id = '" . $userdata['session_id'] . "'";
                if ( !($result = $db->sql_query($sql)) || !($db->sql_affectedrows()) )
                {
                        $sql = "INSERT INTO " . SEARCH_TABLE . " (search_id, session_id, search_time, search_array)
                                VALUES($search_id, '" . $userdata['session_id'] . "', $current_time, '" . str_replace("\'", "''", $result_array) . "')";
                        if ( !($result = $db->sql_query($sql)) )
                        {
                                message_die(GENERAL_ERROR, 'Could not insert search results', '', __LINE__, __FILE__, $sql);
                        }
                }
This

Code: Select all

                if ( !($result = $db->sql_query($sql)) || !($db->sql_affectedrows()) )
should theoretically prevent duplicate, but its not.
Any ideas?

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by espicom » Mon Oct 13, 2008 12:01 am

Repair the tables giving you this problem, because the indexes are damaged. If you use phpmyadmin, select them and look for the "With Selected" pull-down box, and select "Repair tables".

Alternatively, the first link in my signature will help you.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

User avatar
qspypl
Registered User
Posts: 149
Joined: Wed Dec 20, 2006 7:15 pm

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by qspypl » Mon Nov 17, 2008 8:24 pm

Not any 'repair table' fix will do anything here.
Problem is simple, randomized $search_id is same as already writed in database.
Too low random number is generated for number of searches on our forum and result is duplication.
It is phpbb design flaw, same thing going for sessions. Same low random number generation.


Regards

Captain Oats
Registered User
Posts: 48
Joined: Thu Jan 17, 2008 5:07 pm

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by Captain Oats » Mon Nov 17, 2008 8:31 pm

Try to optimize your database and why not to Rebuild Search
http://www.support-phpbb2.com : Support for your phpBB2 Board

User avatar
Techie-Micheal
Security Consultant
Posts: 19511
Joined: Sun Oct 14, 2001 12:11 am
Location: In your servers

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by Techie-Micheal » Mon Nov 17, 2008 10:39 pm

qspypl wrote:Not any 'repair table' fix will do anything here.
Problem is simple, randomized $search_id is same as already writed in database.
Too low random number is generated for number of searches on our forum and result is duplication.
It is phpbb design flaw, same thing going for sessions. Same low random number generation.


Regards
Then why aren't more people having this problem? Before discounting the advice, why not try it? I've seen corrupted tables do some weird things before.
Proven Offensive Security Expertise. OSCP - GXPN

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by espicom » Tue Nov 18, 2008 2:29 am

If your autoincrement value for the table is screwed up, you can get the duplicate key error. If the index is screwed up, you can get this error. Both are things a repair will fix.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

User avatar
qspypl
Registered User
Posts: 149
Joined: Wed Dec 20, 2006 7:15 pm

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by qspypl » Wed Nov 26, 2008 5:09 am

First of all, there is not any incrementation at search_id, its generated random. Great thing is that you giving advices about table schema and you not even know how table schema looks like.
I put before repair table because it not cost anything and of course it not help.

Problems starting when we reaching peak of views (so searches too).
We handled it by our way, but now we got session duplicate error:

Code: Select all

        $sql = "UPDATE " . SESSIONS_TABLE . "
                SET session_user_id = $user_id, session_start = $current_time, session_time = $current_time, session_page = $page_id, session_logged_in = $login, session_admin = $admin
                WHERE session_id = '" . $session_id . "'
                        AND session_ip = '$user_ip'";
        if ( !$db->sql_query($sql) || !$db->sql_affectedrows() )
        {
                $session_id = md5(dss_rand());

                $sql = "INSERT INTO " . SESSIONS_TABLE . "
                        (session_id, session_user_id, session_start, session_time, session_ip, session_page, session_logged_in, session_admin)
                        VALUES ('$session_id', $user_id, $current_time, $current_time, '$user_ip', $page_id, $login, $admin)";
                if ( !$db->sql_query($sql) ) {
[b]                    die("duplicate entry this is our error");[/b]
                }
Again after insertion die() is called because we duplicating session_id.
Error showing around 30-40 times a day in 1,3 mln visits.

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by espicom » Wed Nov 26, 2008 5:34 am

Code: Select all

		mt_srand ((double) microtime() * 1000000);
		$search_id = mt_rand();
It is possible for a random number generator (if it is truly random) to generate two values that are identical. It is highly unlikely, BUT more probable if two separate instances of the generator are run with the same seed value, and you retrieve the values from both at the same instant. That falls apart, though, when you have them on the same computer, because the way concurrency works is that the tasks are NOT actually simultaneous, which reduces the possibility of getting identical results considerably. According to the documentation for the PHP command mt_srand(), however, it is hard to get it to generate the same random number sequence even with the same seed (it is, in fact, one of the complaints listed in the comments).

Now, if you are regularly getting duplicate results from the random number generator, have you checked the bug reports on your version of PHP? Are you running it on a Windows host, which might be returning invalid data for the call to microtime()?
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

User avatar
qspypl
Registered User
Posts: 149
Joined: Wed Dec 20, 2006 7:15 pm

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by qspypl » Wed Nov 26, 2008 5:54 am

We running linux, we also running php 5.2.3 version. From what you writing this should'nt be problem of randomize search_id, but when this is not database fault (when trying to insert two results with same id its normal behaviour of databases to put error its impossible [duplicate]) what else could give such error.

We are getting this error's everyday in peak hours and at night (low load) it happen too but its very rare.
This error shows only 40 times at 1400000 views (lets say new visits involving creating sessions are for ex. 150.000) view per day which giving chance for this error appear 0,02%.
Not much but i want eliminate it, it also point that this % is possible to generate same id twice.

In search code example search_id its not so obvious this is random number generation problem but in session_id its very clear:

Code: Select all

                $session_id = md5(dss_rand());

                $sql = "INSERT INTO " . SESSIONS_TABLE . "
                        (session_id,

User avatar
qspypl
Registered User
Posts: 149
Joined: Wed Dec 20, 2006 7:15 pm

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by qspypl » Fri Dec 05, 2008 2:30 pm

Well i confirmed now all this three errors depend on server (mysql) load. Low load no errors, huge load couple errors.

User avatar
Dog Cow
Registered User
Posts: 2494
Joined: Fri Jan 28, 2005 12:14 am
Contact:

Re: Duplicate entry 'xxx' for key 1 (phpbb design flaw?)

Post by Dog Cow » Fri Dec 05, 2008 5:33 pm

There's more than one way to get duplicate results from a "random" number generator, or even a "random" string generator, for that matter. Generally, though, the seed is the problem. A few months ago, I had such an issue where I was getting duplicate hashes, by refreshing a page too quickly. The solution was to change time() to instead be microtime()

In any case, the whole thing that this problem is occurring during high load indicates that the random number generator is faulty. PhpBB problem? PHP problem? Who cares? :roll: There's a fix for everything.

qspypl: The code you posted in your first post isn't the best way to handle duplicates. In fact, I had code just like that in the scenario I mentioned above, and it was causing part of the problem. Here are two alternatives:

Code: Select all

 INSERT INTO table_foo (foo,bar, bar2) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE (foor,bar, bar2)
That's one way to do it. Here's another:

Code: Select all

INSERT OR REPLACE INTO table_foo (foo,bar) Values (1,2)
Frankly, if you write over someone's search results, who cares? He can at least see page 1, and in any case is free to repeat the search again.

As for solving the randomizer issue, unfortunately dss_rand() can't take a seed as an argument. It's quite blind, in the fact that it's not tied down to any user or session. In the case of search, you want it to be tied to the current user (ie, session ID).So I'd recommend getting rid of dss_rand() and instead generating values which are tied to the current session id, like so:

Code: Select all

$search_id = abs(crc32(md5($userdata['session_id'] . microtime() ));
There's also a unique ID in the $_SERVER array, if you want to use that as well.
Moof!
Mac GUI Vault: Retro Apple II & Macintosh computing archive.
Inside Allerton bookMac GUIMac 512K Blog

Locked

Return to “2.0.x Support Forum”