Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
MySQL Records Occassionally Overwritten
mysql version 5.0.51a
In order to describe what is going on here, I need to be a bit descriptive. I will try my best to explain what is important here. The tables are involved in an in-house credit application. The salesperson fills out a form for the company applying for the credit. They can then save that application to complete later or send off to the accounting dept for approval. The accountant can save the record for later, return it to the salesperson, or approve it. In any of these operations the entire form is either inserted (when first created) or updated in the table record.
When any operations that transfer the access to the form to the salesperson or accountant, an e-mail is sent to the appropriate party, which includes a link to the record. The salesperson only has access to the records they have created. This is done by simply checking their login username, held in a session variable, with a field in the table that also holds their username.
At the top of the form is a select box that holds records waiting to be processed. What is available for the salesperson in that box is records he/she has stored or records sent back to him/her for corrections and re-submission. They can pull up a form by simply selecting one. They can also retrieve a form by clicking on the e-mail link, sent to them when they submit a form or the admin (accountant) returns a form to them. Likewise, the accountant can do the same, by both methods, with records sent to them to process.
Each transaction in this process is logged in a Transaction Detail Table.
There are many error checks that prevent a record from being accessed inappropriately. (Bear with me, this is all important) Salesperson and Accountant do not have access to the record at the same time and, once an application is approved, neither have access except to view.
The Problem
Everything is dependent on the ID field, which is an auto-increment mysql field in the CreditApp table. This number is stored in the log file in the "AppID" field. In about 1% of these transactions, either when the salesperson submits the form to the admin (accountant) or the accountant approves it (I'm not sure which), rather than updating the correct record, a completely unrelated record is updated. Each overwritten record is a record that has been previously processed (meaning "approved" by the accountant), which means there is no more allowed access to the record, except to view. More often than not, the record overwritten is 2 or 3 years old.
Although I'm not sure whether the record is overwritten on submit by the salesperson or on approval by the admin, the other peculiar thing is that, when this overwrite happens, the entries in the log table, on submit by the salesperson, do not have the AppID that relates them to the form record. It is blank. This probably indicates that the problem is on submit by the salesperson, or, at least, begins there.
So here is a simplified mock up of the database processing (I am sure there is a much more eloquent way to do this but alas...):
In order to describe what is going on here, I need to be a bit descriptive. I will try my best to explain what is important here. The tables are involved in an in-house credit application. The salesperson fills out a form for the company applying for the credit. They can then save that application to complete later or send off to the accounting dept for approval. The accountant can save the record for later, return it to the salesperson, or approve it. In any of these operations the entire form is either inserted (when first created) or updated in the table record.
When any operations that transfer the access to the form to the salesperson or accountant, an e-mail is sent to the appropriate party, which includes a link to the record. The salesperson only has access to the records they have created. This is done by simply checking their login username, held in a session variable, with a field in the table that also holds their username.
At the top of the form is a select box that holds records waiting to be processed. What is available for the salesperson in that box is records he/she has stored or records sent back to him/her for corrections and re-submission. They can pull up a form by simply selecting one. They can also retrieve a form by clicking on the e-mail link, sent to them when they submit a form or the admin (accountant) returns a form to them. Likewise, the accountant can do the same, by both methods, with records sent to them to process.
Each transaction in this process is logged in a Transaction Detail Table.
There are many error checks that prevent a record from being accessed inappropriately. (Bear with me, this is all important) Salesperson and Accountant do not have access to the record at the same time and, once an application is approved, neither have access except to view.
The Problem
Everything is dependent on the ID field, which is an auto-increment mysql field in the CreditApp table. This number is stored in the log file in the "AppID" field. In about 1% of these transactions, either when the salesperson submits the form to the admin (accountant) or the accountant approves it (I'm not sure which), rather than updating the correct record, a completely unrelated record is updated. Each overwritten record is a record that has been previously processed (meaning "approved" by the accountant), which means there is no more allowed access to the record, except to view. More often than not, the record overwritten is 2 or 3 years old.
Although I'm not sure whether the record is overwritten on submit by the salesperson or on approval by the admin, the other peculiar thing is that, when this overwrite happens, the entries in the log table, on submit by the salesperson, do not have the AppID that relates them to the form record. It is blank. This probably indicates that the problem is on submit by the salesperson, or, at least, begins there.
So here is a simplified mock up of the database processing (I am sure there is a much more eloquent way to do this but alas...):
if($Process == "RegularSave") // Salesperson storing record for later
{
$Status = "Store";
$StoreTitle = $NewTitle;
if(empty($StoreTitle)){$Error[] = "Title cannot be blank. Record was not saved!";}
$Q = "SELECT ID, StoreTitle FROM CreditApp WHERE ID = '$ID' OR UniqueID = '$UniqueID'"; // UniqueID prevents double entry on refresh of new record
$Result = mysql_query($Q);
if(!$Result){$Error[] = "Database error in storage result!";}
}
elseif($Process == "RegularSubmit") //Sslesperson submitting record
{
$Status = "Received";
$StoreTitle = $NewTitle;
if(empty($StoreTitle)){$Error[] = "Title cannot be blank. Record was not saved!";}
$Q = "SELECT ID FROM CreditApp WHERE ID = '$ID' OR UniqueID = '$UniqueID'"; // UniqueID prevents double entry on refresh of new record
$Result = mysql_query($Q);
if(!$Result){$Error[] = "Database error in ID Check!";}
}
elseif($Process == "AdminProcess" || $Process == "AdminSave" || $Process == "AdminReturn")
{
// Status variable set here as to "Revised", "Rejected", "Approved", etc.
// THEN:
$Q = "SELECT ID FROM CreditApp WHERE ID = '$ID'";
$Result = mysql_query($Q);
if(!$Result){$Error[] = "Database error in ID Check!";}
}
elseif($Process == "AdminSend")
{
// Setup for e-mail from admin when returning record for corrections
$ReturnDate = dFormat($Time,41);
$FromName = $AdminName;
$FromEmail = $AdminAddress;
$ReturnUser = $_SESSION['FullName'];
$DetailMsg = nl2br($Message);
$NoteString = '======================='."\n".$ReturnUser.': '.$Today."\n".$Message."\n".'======================='."\n".$Notes;
$R = mysql_query("UPDATE CreditApp SET Notes = '$NoteString', Status = 'Return', ReturnDate = '$ReturnDate', ReturnUser = '$ReturnUser', AdminID = '$_SESSION[User]' WHERE ID = '$ID'");
$M = mysql_query("INSERT INTO CustAcctStatsDetail (AppID,Action,Detail,Form,TranUser) VALUES ('$ID','Return for Corrections','$DetailMsg','$FormName','$_SESSION[User]')");
$HTMLData = ('Your credit request for '.$AcctName.' has been returned for the following reasons:<br /><br />'.nl2br($Message).'<br /><br />
FormLink: You can access the record from <a href="'.$MainDir.'credit.php?iKey='.$ID.'&ret=1">this link</a>.<br />
You will also find it available in your stored records list at the top of the Credit Application Request form.
<br /><br />');
}
if(count($Error) == 0 && $Process != "AdminSend")
{
if(mysql_num_rows($Result) != 0) // Indicates record already exists
{
#=====================================================#
# Update Existing Record #
#=====================================================#
$X = mysql_fetch_array($Result);
$Q = "UPDATE CreditApp ... WHERE ID = '$X[ID]'"; // Standard Update set of fields
$Result = mysql_query($Q);
if(!$Result){$Error[] = "Database update error! (1) ApproveDate: ".$ApproveDate.' '.mysql_error();}
else
{
// Here related tables are updated (simple one-to-many relationships for form data)
// THEN:
#=====================================================#
# Log any changes #
#=====================================================#
$LQ = "UPDATE CustAcctStats SET StoreTitle = '$StoreTitle', Company = '$AcctName',";
if($AppType == "New"){$ApprovalString = $Approval;}
elseif($Approval == "Approved"){$ApprovalString = "Completed";} // Revised entry
else{$ApprovalString = $Approval;}
if($_SESSION[GVars][Approval] != $Approval || $AppType == "Revised")
{
$StatusString = $Status.'/Credit';
$LQ .= " Status = '$StatusString', CreditApproval = '$ApprovalString', CreditDate = '$ThisDate',";
}
$TAction=array(); $TDetail=array();
if($_SESSION[GVars][SubmitDate] != $SubmitDate)
{
$TAction[] = 'Form Submitted';
$TDetail[] = $AppType != "Revision" ? "Credit Application submitted for approval" : "Credit Revision Request submitted";
$LQ .= " SubmitDate = '$SubmitDate'";
}
if($_SESSION[GVars][Approval] != $Approval || $_SESSION[GVars][SubmitDate] != $SubmitDate || $AppType == "Revised")
{
if(substr($LQ,-1) == ','){$LQ = substr($LQ,0,-1);}
$LQ .= " WHERE AppID = '$ID'";
$Result = mysql_query($LQ);
if(!$Result){$Error[] = "Log File Error! [1] ".mysql_error();}
}
if($_SESSION[GVars][Approval] != $Approval || $AppType == "Revised")
{
if($AppType != "Revised")
{
if($Approval == "Approved")
{
$TAction[] = '<span class="LogBlue">Credit Application Approved</span>'; $TDetail[] = 'This Credit Application has been approved for '.$CreditAmt;
}
elseif($Approval == "Declined")
{
$TAction[] = '<span class="LogRed">Credit Application Declined</span>'; $TDetail[] = 'This Credit Application has been declined';
}
}
else
{
if($Approval == "Approved")
{
$TAction[] = '<span class="LogBlue">Credit Revision Approved</span>'; $TDetail[] = 'This submitted credit revision has been approved and completed.';
}
elseif($Approval == "Rejected")
{
$TAction[] = '<span class="LogRed">Credit Revision Rejected</span>'; $TDetail[] = 'This Credit Revision has been rejected';
}
}
}
if($_SESSION[GVars][Status] != $Status)
{
$TAction[] = 'Status Change';
if(!empty($_SESSION[GVars][Status]))
{
$TDetail[] = 'Status change from '.$_SESSION[GVars][Status].' to '.$Status;
}
else
{
$TDetail[] = 'Status change set to '.$Status;
}
}
if($_SESSION[GVars][StoreTitle] != $StoreTitle)
{
$TAction[] = 'Store Title Change';
if(empty($_SESSION[GVars][StoreTitle]))
{
$TDetail[] = 'Store Title created: '.$StoreTitle;
}
else
{
$TDetail[] = 'Store Title change from '.$_SESSION[GVars][StoreTitle].' to '.$StoreTitle;
}
}
$TranCount = count($TAction);
for($a=0;$a<$TranCount;$a++)
{
$Q = "INSERT INTO CustAcctStatsDetail (AppID,Action,Detail,Form,TranUser) VALUES ('$ID','$TAction[$a]','$TDetail[$a]','$FormName','$_SESSION[FullName]')";
$Result = mysql_query($Q);
if(!$Result){$Error[] = "Log File Error! [2]";}
if($Status == "Processed")
{
$Q = "UPDATE CustAcctStats SET StoreTitle = '$StoreTitle', Company = '$AcctName', CreditDate = CURDATE(), Date = NOW(), AdminUser = '$_SESSION[User]' WHERE AppID = '$ID'";
}
else
{
$Q = "UPDATE CustAcctStats SET StoreTitle = '$StoreTitle', Company = '$AcctName', Date = NOW() WHERE AppID = '$ID'";
}
$Result = mysql_query($Q);
}
switch($Process)
{
// Text is set here to display result and status to the user
}
}
}
elseif(!$_SESSION['Admin']) // Record is new entry. Admin only deals with records in process
{
#=====================================================#
# Create New Record #
#=====================================================#
$Q = "INSERT INTO CreditApp ..."; // Standard Insert set of fields
$Result = mysql_query($Q);
if(!$Result){$Error[] = "Error in database insert! (1) ".mysql_error($Conn);}
else
{
// Here related tables are updated (simple one-to-many relationships for form data)
// THEN:
#=====================================================#
# Create new Log Entry #
#=====================================================#
$CreditApproval = !empty($Approval) ? $Approval : "";
if(!empty($ApproveDate)){$CreditDate = $ApproveDate;}
if(!empty($DeclineDate)){$CreditDate = $DeclineDate;}
if($Process == "RegularSave")
{
if($AppType != "Revision")
{
$Action = "Record Created";
$Detail = "A new record was created but stored to submit at a later date.";
$StatusString = "Store/Credit";
}
else
{
$CreditApproval = "Current";
$Action = "Credit Revision";
$Detail = "A Credit Revision was created but stored to submit at a later date.";
$StatusString = "Store/Credit";
}
}
elseif($Process == "RegularSubmit")
{
if($AppType != "Revision")
{
$Action = "Record Created";
$Detail = "A new record was created and sent to Administration for approval.";
$StatusString = "Received/Credit";
}
else
{
$CreditApproval = "Current";
$Action = "Credit Revision";
$Detail = "A Credit Revision was sent to Administration.";
$StatusString = "Received/Credit";
}
}
else
{
$Action = "Error!";
if(empty($Process)) // "Detail text added 2/19/15 (Previously was blank)
{
$Detail = "Process variable is empty";
}
else
{
$Detail = $Process.' should equal RegularSave or RegularSubmit';
}
}
if(empty($CreditDate)){$CreditDate = "0000-00-00";}
if(empty($SubmitDate)){$SubmitDate = "0000-00-00";}
$Result = mysql_query("INSERT INTO CustAcctStats (AppID,AppType,User,StoreTitle,Company,Status,CreditApproval,CreditDate,SubmitDate,Date)
Values ('$ID','$AppType','$_SESSION[User]','$StoreTitle','$AcctName','$StatusString','$CreditApproval','$CreditDate','$SubmitDate',NOW())");
if(!$Result){$Error[] = "Log File Error! [3] ".mysql_error();}
else
{
$Result = mysql_query("INSERT INTO CustAcctStatsDetail (AppID,Action,Detail,Form,TranUser,TranDate) VALUES ('$ID','$Action','$Detail','$FormName','$_SESSION[FullName]',NOW())");
if(!$Result){$Error[] = "Log File Error! [4]";}
}
}
}
elseif($_SESSION['Admin'])
{
$Error[] = "Record not found!<br />Please exit Admin mode if you want to save a new record!";
}
}
if(($_POST['Submit'] == "Submit" || $Process == "AdminSend" || $Process == "AdminProcess") && count($Error) == 0)
{
// Here the e-mail is generated
}
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
That was the first place I looked too.
The algorithm for UniqueID is a 9-digit of random numbers and alpha characters, including symbols.
This is a small table. There are about 4,000 records. of those, only about 1400 are affected by this process and could possibly be affected by this problem.
As a test, I created 50,000 Unique ID's. with each creation and insert, I first checked the field for duplicates. I then added another 50,000, doing the same. There was never a duplicate. So the chance of 11 duplicates occurring in 1400 entries is probably pretty nil.
Really not sure why I used OR in that query - perhaps just because it felt safer. The Unique ID is a hidden field in the form that is generated if the value is empty. It's usage is simply to prevent a double-click from creating two entries. I would need to re-check the process. It's possible there are times when the ID might match but the Unique ID may be new.
The algorithm for UniqueID is a 9-digit of random numbers and alpha characters, including symbols.
This is a small table. There are about 4,000 records. of those, only about 1400 are affected by this process and could possibly be affected by this problem.
As a test, I created 50,000 Unique ID's. with each creation and insert, I first checked the field for duplicates. I then added another 50,000, doing the same. There was never a duplicate. So the chance of 11 duplicates occurring in 1400 entries is probably pretty nil.
Really not sure why I used OR in that query - perhaps just because it felt safer. The Unique ID is a hidden field in the form that is generated if the value is empty. It's usage is simply to prevent a double-click from creating two entries. I would need to re-check the process. It's possible there are times when the ID might match but the Unique ID may be new.
There's nothing in your code that shows how you generate the random UniqueID, but unless you do this right, you will never get a truly random number. The fact that the overwritten records are from a few years ago, my guess is that you're creating duplicate 'unique' numbers. If you want it to be unique, then at least concatenate it with the primary key, which you know absolutely has to be unique! Maybe use an id_timestamp combination.
And as gr8gonzo says, running your query with the OR statement looks like it will definitely cause you problems. As your ID is a unique primary key, then select the record using that and only that - asking for records in any other way will not guarantee you get the correct one.
And as a side-project that you should really make time for - switch your old mysql_* code to the new MySQLi or PDO. If you don't and your php is upgraded, then overwriting records will be the least of your worries - nothing will work !
And as gr8gonzo says, running your query with the OR statement looks like it will definitely cause you problems. As your ID is a unique primary key, then select the record using that and only that - asking for records in any other way will not guarantee you get the correct one.
And as a side-project that you should really make time for - switch your old mysql_* code to the new MySQLi or PDO. If you don't and your php is upgraded, then overwriting records will be the least of your worries - nothing will work !
So the second thing I would do is add in some file-based logging so you can capture information that might lead to the answer.
Original Code:
New Code:
Once the problem happens again, check the logs.
For what its worth, I would recommend you change your form to use an onsubmit Javascript event that calls a custom function. Have that custom function:
1. Set a global Javascript boolean variable to indicate the submission is in process, and abort if that variable is already set to true.
2. Disable the submit button.
3. Programmatically submit the form (you may need to programmatically remove the "onsubmit" event from the <form> tag beforehand).
4. For an extra-nice effect, you can display a lightbox overlay with a nice "Processing..." message to make the process extra-friendly.
5. You could even change the submission process into an AJAX call so you can properly handle the response, if there are any errors. That way, the form contents are not lost - errors can be corrected and re-submitted quickly. No need to deal with messy back buttons or any caching problems. You can then redirect the user directly to a confirmation page when the submission is successful, which will also eliminate any issues with re-posted data due to a user hitting the refresh button.
So beyond eliminating duplicate submissions due to double-clicks, you should get a nice user experience AND also eliminate duplicate submissions from POSTed refreshes, AND you don't need the UniqueID at all.
Finally, if you're going to generate a unique ID that stays in the system, consider using the UNIX timestamp as a prefix (microtime(true) is even better). That way, you can still have the random digits and characters, but there will never be a chance of an older combination coming up again.
Original Code:
$X = mysql_fetch_array($Result);
$Q = "UPDATE CreditApp ... WHERE ID = '$X[ID]'"; // Standard Update set of fields
New Code:
$X = mysql_fetch_array($Result);
file_put_contents("{$Process}-debug.log", "\$Q = {$Q}, Resulting ID = {$X[ID]}\n", FILE_APPEND | LOCK_EX);
$Q = "UPDATE CreditApp ... WHERE ID = '$X[ID]'"; // Standard Update set of fields
Once the problem happens again, check the logs.
For what its worth, I would recommend you change your form to use an onsubmit Javascript event that calls a custom function. Have that custom function:
1. Set a global Javascript boolean variable to indicate the submission is in process, and abort if that variable is already set to true.
2. Disable the submit button.
3. Programmatically submit the form (you may need to programmatically remove the "onsubmit" event from the <form> tag beforehand).
4. For an extra-nice effect, you can display a lightbox overlay with a nice "Processing..." message to make the process extra-friendly.
5. You could even change the submission process into an AJAX call so you can properly handle the response, if there are any errors. That way, the form contents are not lost - errors can be corrected and re-submitted quickly. No need to deal with messy back buttons or any caching problems. You can then redirect the user directly to a confirmation page when the submission is successful, which will also eliminate any issues with re-posted data due to a user hitting the refresh button.
So beyond eliminating duplicate submissions due to double-clicks, you should get a nice user experience AND also eliminate duplicate submissions from POSTed refreshes, AND you don't need the UniqueID at all.
Finally, if you're going to generate a unique ID that stays in the system, consider using the UNIX timestamp as a prefix (microtime(true) is even better). That way, you can still have the random digits and characters, but there will never be a chance of an older combination coming up again.
Not very confident that it will solve the problem, but I will take gr8gonzo's advice and find a way to eliminate the UniqueID. Hardly seems, if no matches occur in 100,000 iterations, that there would be any possibility of 11 occurring in 1,400 entries. Of course, the first iterations were comparing against a small number of entries, but still ...
If you're curious, this is the generator I am using (level 3 with a length of 9):
Writing a JavaScript submit, at this point, would cause some major restructuring, which is really not in the books.
I would end up having to send JavaScript variables to PHP, so the only logical thing to do would be to use Ajax, and I've spent so much time trying to troubleshoot this error that I'm quite reticent to tell my client I need to completely restructure the code unless I am confident it will solve the problem..
As far as the error routine, as you can see, I store the error messages in an array, Any errors present aborts the processing at various points.
The file you see here is part of an include. The submit goes to the top of the page where proper processing variables are set, followed by the processing include. The form is re-populated. At the bottom of the page is a routine that checks for errors and displays any that might be available. So, basically, the user stays on the form which, from their viewpoint, is not cleared until it is successfully submitted.
I've implemented file logging using the $X fetch. I had actually removed that, as it is really redundant - I already have the ID and the $ID is used to create the query in the first place.
So, some good suggestions here, but I don't know that I've found a solution.
If you're curious, this is the generator I am using (level 3 with a length of 9):
function UniqueCode($length=6,$level=2)
{
list($usec, $sec) = explode(' ', microtime());
srand((float) $sec + ((float) $usec * 100000));
$validchars[1] = "0123456789abcdfghjkmnpqrstvwxyz";
$validchars[2] = "0123456789abcdfghjkmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
$validchars[3] = "0123456789!@#$%&()-=+abcdfghjkmnpqrstvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%&()-=+";
$UCode = "";
$counter = 0;
while ($counter < $length) {
$actChar = substr($validchars[$level], rand(0, strlen($validchars[$level])-1), 1);
// All character must be different
if (!strstr($UCode, $actChar))
{
$UCode .= $actChar;
$counter++;
}
}
return $UCode;
}
Writing a JavaScript submit, at this point, would cause some major restructuring, which is really not in the books.
I would end up having to send JavaScript variables to PHP, so the only logical thing to do would be to use Ajax, and I've spent so much time trying to troubleshoot this error that I'm quite reticent to tell my client I need to completely restructure the code unless I am confident it will solve the problem..
As far as the error routine, as you can see, I store the error messages in an array, Any errors present aborts the processing at various points.
The file you see here is part of an include. The submit goes to the top of the page where proper processing variables are set, followed by the processing include. The form is re-populated. At the bottom of the page is a routine that checks for errors and displays any that might be available. So, basically, the user stays on the form which, from their viewpoint, is not cleared until it is successfully submitted.
I've implemented file logging using the $X fetch. I had actually removed that, as it is really redundant - I already have the ID and the $ID is used to create the query in the first place.
So, some good suggestions here, but I don't know that I've found a solution.
I understand the hesitation, but at a basic level:
1. An incorrect record is being updated.
2. The only way for that to happen is for an UPDATE query to target that record.
3. If the only UPDATE query performed is the one shown in your code above, then the only targeted ID is contained in $X[ID]
4. If so, then the only way for an incorrect record to be updated would be to have that incorrect record's ID come back in $X[ID].
5. If the incorrect record's ID is coming back in that result set, then it's coming from an unexpected result in the SELECT query.
Because it only happens rarely, the only way to confirm is to log all the relevant queries and see what it all looks like when the SELECT returns the wrong ID, then run that same query manually to see the result set.
I'd also suggest trying to add a UNIQUE KEY index onto JUST the UniqueID column on that table to see if it is successful, and then remove it (just to avoid introducing any additional variables into the debugging process). If it comes back and complains about not being successful, then I'd do a SELECT to find out which UniqueID values have more than one record. Maybe the function didn't run as expected at some point and resulted in a blank unique ID.
1. An incorrect record is being updated.
2. The only way for that to happen is for an UPDATE query to target that record.
3. If the only UPDATE query performed is the one shown in your code above, then the only targeted ID is contained in $X[ID]
4. If so, then the only way for an incorrect record to be updated would be to have that incorrect record's ID come back in $X[ID].
5. If the incorrect record's ID is coming back in that result set, then it's coming from an unexpected result in the SELECT query.
Because it only happens rarely, the only way to confirm is to log all the relevant queries and see what it all looks like when the SELECT returns the wrong ID, then run that same query manually to see the result set.
I'd also suggest trying to add a UNIQUE KEY index onto JUST the UniqueID column on that table to see if it is successful, and then remove it (just to avoid introducing any additional variables into the debugging process). If it comes back and complains about not being successful, then I'd do a SELECT to find out which UniqueID values have more than one record. Maybe the function didn't run as expected at some point and resulted in a blank unique ID.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
As it turned out, the problem was, indeed, in the UniqueID function, which was occasionally producing a duplicate. There was a flaw in the test I had written, so it was not showing any matches.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
SELECT ... FROM CreditApp WHERE ID = '$ID' OR UniqueID = '$UniqueID'
I'm assuming that UniqueID is supposed to be unique, but considering that this problem happens very rarely and that the record that gets updated is an OLDER record, I'm wondering if your UniqueID isn't completely unique across all records. In a scenario where you had two records:
ID | UniqueID | Record_Date
123 | abcdef1234567 | 2012-01-01
5000 | abcdef1234567 | 2014-05-03
...you would end up using and updating ID 123 instead of ID 5000, because they both have the same UniqueID and record 123 would NORMALLY come from (not always, but most of the time the primary auto-incrementing ID comes back in a natural order).
I'm not sure how the UniqueID is generated, but maybe the query should be using "AND" instead of "OR" ?
SELECT ... FROM CreditApp WHERE ID = '$ID' AND UniqueID = '$UniqueID'
...or else maybe the unique ID algorithm needs to be adjusted. It would help to see the algorithm you're using and to know the estimated volume of records in that table.