• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

How do I address this php 4.1.0, mysql issue with duplicate entry error

Hello, we have this is legacy web form app, written in php4.1.0. client started getting sporadic Duplicate entry for key 1 errors within the past three to four weeks. After looking into their db, found that they had a massive amount of data.  I 'assumed' that due to:

the amount of data
age of application & db
power, or lack thereof, of their network and computers, some people still running xp on pentium machines

Two [or more] users grab the same # from the CV_SEQUENCE_NUMBER table, one finishes first, sending new request number to the request table; then next user finishes and gets bumpped with the Duplicate entry error

the request # will look like this '11-3197' [current year, '-', sequential number]

The big issue, in addition to the dup error of course, is that the request submission dies, per the code instructions, and the form is reset to nothing.  They have a massive form that has tons of input fields, so that is very, very painful to the end user when this occurs

Since this is a legacy app and will hopefully be phased out within the next 6 to 12 months - how can I address the dup issue or what I was thinking, how can I make the process run again, once it encounters the dup error, but before it refreshes the browser windows and removes all the form data?

Thanks in advance.
 request table view request table schema
User fills in web form, clicks submit and the following code runs:

//Get the request number
$requestNumber=getRequestNumber();

//Upload Files
$_REQUEST['Q_34']=parseUploadedFiles($requestNumber);

//Create the Request

$sql = "INSERT INTO REQUEST (R_NUMBER, R_STATUS) VALUES ('" . $requestNumber . "','o')";
mysql_query($sql,$db) or die("ERROR >srphp-iir43: " . mysql_error()." - #".mysql_errno());
=====================================================================================================
/*
* Changed 050218 - Request number is just a seven digit number
*/

function getRequestNumber()
{
	global $db;

	$sql = "SELECT CV_SEQUENCE_NUMBER,CV_YEAR FROM CURRENT_VALUES";
	$result = mysql_query($sql,$db) or die("ERROR: " . mysql_error());
	$row = mysql_fetch_assoc($result);

	incrementRequestNumber();

	if($row['CV_YEAR']!=date('y'))
    {
    	incrementRNYear();
    	$sql = "SELECT CV_SEQUENCE_NUMBER,CV_YEAR FROM CURRENT_VALUES";
     	$result = mysql_query($sql,$db) or die("ERROR: " . mysql_error());
      	$row = mysql_fetch_assoc($result);
      	incrementRequestNumber();
      	
      	return $row['CV_YEAR']."-".sprintf("%04d",$row['CV_SEQUENCE_NUMBER']);
    }
    else
    {
        return $row['CV_YEAR']."-".sprintf("%04d",$row['CV_SEQUENCE_NUMBER']);
    }
}


/*
* Changed 050218 - Request number is just a number that is incremented
*/

function incrementRequestNumber($requestNumber="none")
{
	global $db;

	//If a requestNumber isn't specified, get the current value
	$sql = "SELECT CV_SEQUENCE_NUMBER FROM CURRENT_VALUES";
	$result = mysql_query($sql,$db) or die("ERROR: " . mysql_error());
	$row = mysql_fetch_assoc($result);

	$newNum=$row['CV_SEQUENCE_NUMBER'] + 1;

	$sql  = "UPDATE CURRENT_VALUES SET CV_SEQUENCE_NUMBER='";
	$sql .= $newNum ."'";

	mysql_query($sql,$db) or die("ERROR: " . mysql_error());

}

Open in new window

0
justmelat
Asked:
justmelat
  • 4
  • 2
  • 2
  • +1
1 Solution
 
dirknibleckCommented:
Why not entirely replace getRequestNumber with incrementRequestNumber. This way you increment the number and return it at the same time.
function incrementRequestNumber($requestNumber="none")
{
        global $db;

        //If a requestNumber isn't specified, get the current value
        $sql = "SELECT CV_SEQUENCE_NUMBER FROM CURRENT_VALUES";
        $result = mysql_query($sql,$db) or die("ERROR: " . mysql_error());
        $row = mysql_fetch_assoc($result);

        $newNum=$row['CV_SEQUENCE_NUMBER'] + 1;

        $sql  = "UPDATE CURRENT_VALUES SET CV_SEQUENCE_NUMBER='";
        $sql .= $newNum ."'";

        mysql_query($sql,$db) or die("ERROR: " . mysql_error());

        return $newNum;
}

Open in new window

0
 
Hugh McCurdyCommented:
Just an idea in case dirknibleck's idea doesn't work.

Why die?  Why not do a retry?  

Put the queries into loops.

for ( $retry = 0; $retry < 10; $retry++ )
{
 
    $result = mysql_query($sql,$db);

    if ( $result != null )
    {
       process as you do now and then
       break;
    }
    else
        sleep ( 2 );   // sleep 2 seconds.
}

You could check $retry to see if it got to 10.  If so, then die.  Or see if it got to 9.  Ugly programming but you are just trying to slap a "bandage" on this.
0
 
justmelatAuthor Commented:
Hummm, actually I like both of your suggestions - let me test both.

If both work, do you guys mind splitting the points?
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Hugh McCurdyCommented:
You decide about the points.  Good luck.
0
 
dirknibleckCommented:
It's totally your call, let's just get it working first.
0
 
justmelatAuthor Commented:
LOL - agreed dirknibleck:

I am working on setting it up now.
0
 
Ray PaseurCommented:
This describes something called a "race condition" -- Two [or more] users grab the same # from the CV_SEQUENCE_NUMBER table, one finishes first, sending new request number to the request table; then next user finishes and gets bumpped with the Duplicate entry error

One solution is LOCK TABLES.  You should be able to make INSERT queries into a table without using LOCK TABLES so long as you use an auto_increment key.  But if you have to modify the tables more than once for the same client, you risk the race condition and the resulting collisions.

Sure hope you get the contract to refactor this application for more modern versions of PHP!

Best regards, ~Ray
0
 
justmelatAuthor Commented:
HI Ray

I can't use the auto increment because they have a special numbering process.
0
 
Ray PaseurCommented:
Yeah, I understand.  Then LOCK TABLES may be your friend.
0
 
justmelatAuthor Commented:
Hi hmccurdy:

You solutions is working for my client thus - entire day without the duplicate entry error.  I did change your for into a while loop.  Thanks so much for your help.  Monday of course will be the true test, that is when they will have a normal/heavy work load.  I want to see if they complain about the delay in a request submitting itself because of the sleep command.  See my changes attached.

dirknibleck:  I really liked your solution, but they still got the error message when I made the change, there were fewer errors, but still errors.  I think it is just due to the age of the application, the database, their network and computers, but thank you for your suggestion.
//Get the request number
    $done = 0;
    $loopThru = 0; //add the if statement to see how many times it went through loop
    while ($done ==0)
    {
	$requestNumber=getRequestNumber();

	//Upload Files
	$_REQUEST['Q_34']=parseUploadedFiles($requestNumber);

	//Create the Request

	$sql = "INSERT INTO REQUEST (R_NUMBER, R_STATUS) VALUES ('" . $requestNumber . "','o')";
    //mysql_query($sql,$db) or die("ERROR >srphp-iir43: " . mysql_error()." - #".mysql_errno());
	mysql_query($sql,$db);
        if (mysql_errno($db) != 1062)
        {
            $done = 1;
        }
        else
        {
            sleep(1);
            $loopThru++;
        }

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now