Welcome Guest, Not a member yet? Register   Sign In
Transactions with Database unique fields errors
#1

I have encountered a funny occurrence when using manual transactions. (mysqli driver)

I like to use manual transactions as it allows my to check the status of my transactions as they run for better debugging and error handling.

the sequence essentially goes like this:

Code:
$this->db->trans_begin();
try{
   $id = $this->db->insert({table}, [arraydata]);
   if($this->db->trans_status() === FALSE){
       throw new Exception("SQL Error: {$this->error}");
   }
   //code continues
}catch(Excpetion e){
   $details = array('status'=>'error', 'message'=>e->getMessage());
   $this->db->trans_rollback();
   return $details;
}
$this->db->trans_commit();
(the actual code is a lot more in-depth with class files and models and what not, but this is the basic sequence of calls.)

I have this one table where data gets inserted, but it has a Unique attribute on one of the fields.

When db_debug is set to true, it will stop everything and display the error message saying that its a duplicate field when they have to be unique (good, its catching the error here).

how ever, when disabling the debugging, the transaction status remain true, indicating that there hasn't been an issue with query.

Essentially my error handling runs on exceptions as a lot of the project is ajax heavy, so i use exceptions to catch when something has happened and relay the information back to the front end, rolling back any changes made.

Is this supposed to be like this? or am i supposed to manually check for unique keys?
Reply
#2

Are you getting an "Invalid query:" message in your logs? Does the insert return false?

When db_debug is on, the biggest difference should be that it tries to complete the transaction (which should trigger a rollback) before displaying the error message. I'm not sure why trans_status() would not be false if you're getting an error message when db_debug is enabled.

I'm hoping the spelling error in the condition of your catch block is just a typo here, but if you copied that over from your code, it could be the source of the problem, though I might expect some other error (like "class Excpetion not found"). You may also want to check trans_status() even outside the catch block, and/or use a finally block if you're using PHP 5.5+ before calling trans_commit().
Reply
#3

Thanks for your reply.

yes that was a typo, the code provided was just a simple representation of the process the code goes through.

Funnily though i came into work today, and everything worked fine.

I can only assume there was some form of transaction error in which the database itself was unable to end a transaction?
Funny things computers :/
Reply




Theme © iAndrew 2016 - Forum software by © MyBB