error handling

[jimfog]

So far when it comes error handling with queries in a function I follow the following logic:

 $result1 = $connection->query("insert into users values                (NULL,'" .$name. "','" .$lastname . "','".$email."','". $passwd."','". $hash."','". $usertype."')");                    if (!$result1) {              throw new Exception('error');              }         else{$result2=$connection->query("insert into business_users values               ('".$connection->insert_id."','" .$address."','".$url ."','".$phone. "','".$city. "','".$municipality. "','".$buztype. "')");               }          if(!$result2)          {  throw new Exception('error');              return false;}

The problem with the above flow is that the first query might finish successfully while the second might not.

The goal is that if one of them fails the other should not run at all...more specifically I do not want the the first query running if the second fails, something which the above code fails to do.

Even if the 2nd insertion fails the 1st will be performed.

 

I do not want partial entries in the DB...what control structure I should follow so that both insertions take place or none of them.

[birbal]

You need to look into "Transaction"

[jimfog]

You need to look into "Transaction"

Aren't these only for financial stuff?

Are there any serious performance penalties in using them?

[thescientist]

Aren't these only for financial stuff?

Are there any serious performance penalties in using them?

look it up first

http://en.wikipedia.org/wiki/Database_transaction

http://www.tutorialspoint.com/mysql/mysql-transactions.htm

[jimfog]

I got a glimpse on transactions.

One question I have is if with transactions error checking code such as the one below have any sense any more:

if (!$result1){              throw new Exception('Αδυναμία εκτέλεσης εντολής');              return false;}

Is the above pointless now?

 

I tried to use the mysqli->begin_transaction method but I got "Fatal error" Call to undefined method".

Why that? I have PHP 5.4.4 on my system

Edited September 4, 2013 by jimfog

[justsomeguy]

The mysqli class does not have a method called begin_transaction:

 

http://www.php.net/manual/en/class.mysqli.php

[jimfog]

The mysqli class does not have a method called begin_transaction:

 

http://www.php.net/manual/en/class.mysqli.php

Τhen what is this?

http://www.php.net/manual/en/mysqli.begin-transaction.php

[justsomeguy]

One thing it is, is not listed in the list of properties and methods under the class synopsis on the page I linked to. The other thing it is is a feature of PHP 5.5. You can use autocommit and commit to set up transactions.

[jimfog]

You can use autocommit and commit to set up transactions.

I finally did the above...set autocommit(FALSE).

[jimfog]

I have one last q. After the commit, should I set autocommit(TRUE)?

It is not stated clearly in the manual what to do after the transaction is finished.

[justsomeguy]

Depends what you want to do. You only need to disable autocommit if you are using transactions, so if you're not using transactions and don't want to call commit after every query then turn autocommit back on.

[jimfog]

Depends what you want to do. You only need to disable autocommit if you are using transactions, so if you're not using transactions and don't want to call commit after every query then turn autocommit back on.

So, by calling commit does that mean also that autocommit is set to true?

Here is a code example where I want to apply a transaction to these 2 queries only

   $connection->autocommit(FALSE);;      $result1 = $connection->query("insert into users values (NULL,'" .$name. "','" .$lastname . "','".$email."','". $passwd."','". $hash."','4')");      $result2=$connection->query("insert into business_users values('".$connection->insert_id."','" .$address."','".$url ."','".$phone. "','".$city. "','".$municipality. "','".$buztype. "')");      $connection->commit();

[justsomeguy]

The commit method does not change the autocommit setting, it simply commits what you have sent to the server if it hasn't already been committed. If you want things to get autocommitted then you need to change the autocommit setting.

[jimfog]

Anyway...I tried to experiment with a transaction but it does not work and I cannot understand why...here is the code:

 $connection->autocommit(FALSE);      $result1 = $connection->query("insert into users values (NULL,'" .$name. "','" .$lastname . "','".$email."','". $passwd."','". $hash."','4')");      $result2=$connection->query("insert into business_users values('".$connection->insert_id."','" .$address."','".$url ."','".$phone. "','".$city. "','".$municipality. "','".$buztype. "')");      $connection->commit();      $connection->autocommit(TRUE);

I messed the 2nd query on purpose. It does not executed....but according to transactions definition neither the first query should.

The 1st query though does execute. So we have the problem now of partial data in the db.

 

Any ideas what might be wrong?

[birbal]

you need to detect when does your queries are failing and according to that you need to rollback. check method for 'rollback' in whatever API you are using

[justsomeguy]

Note that you need to rollback before committing if there was a problem.

[jimfog]

Yes. rollback was the missing "link".

[jimfog]

This is the code I finally concluded:

 $connection->autocommit(FALSE);      $result1 = $connection->query("insert into users values (NULL,'" .$name. "','" .$lastname . "','".$email."','". $passwd."','". $hash."','4')");      $connection->rollback();      $result2=$connection->query("insert into business_users values('".$connection->insedress."','".$url ."','".$phone. "','".$city. "','".$municipality. "','".$buztype. "')");      $connection->commit();      $connection->autocommit(TRUE);

Τhe reason I am showing it is that I encountered an error where the data of the first query entered the db while of the second's(there was a foreign key violation) did not.

Since I am using rollback this should not be the case.

 

By seeing the code above is the use of the rollback a correct one?

 

Furthermore...if a there was an error in the transaction(meaning the queries were not performed) what error checking mechanism should I use to be notified about it?

Edited September 9, 2013 by jimfog

[justsomeguy]

You only use rollback if you actually want to roll the transaction back. You are sending the first query, and then immediately rolling it back (basically canceling it), then running the second query, and then committing that one. So the first query never takes effect because you always roll it back. You need to check for errors with the queries and roll back the entire transaction if any query had an error, that is what rollback is for. Rollback is like undo for queries that have not been committed yet.

[jimfog]

You only use rollback if you actually want to roll the transaction back. You are sending the first query, and then immediately rolling it back (basically canceling it), then running the second query, and then committing that one. So the first query never takes effect because you always roll it back. You need to check for errors with the queries and roll back the entire transaction if any query had an error, that is what rollback is for. Rollback is like undo for queries that have not been committed yet.

Αm I going to check the for errors with the usual way?

meaning...if(!$result){......rollback}.

 

result is the variable where the query is stored.

[justsomeguy]

Yes, it will return false if there was an error with the query.

[jimfog]

Τhis is the code I have concluded(based on an example from the PHP manual).Tell me what you think. So far it works OK but I have not tested it for all scenarios yet:

 $all_query_ok=true;            $connection->autocommit(FALSE);        $result1 = $connection->query('update users set password ="NULL",hash="NULL",email="NULL",usertype="5" where users.email="'.$sessionmail.'"')?null:$all_query_ok=false;        $result2=  $connection->query('delete from favorites where (select users.user_ID from users where favorites.userID=users.user_ID)')?null:$all_query_ok=false;        if($all_query_ok)         {$connection->commit();        return $accoudel='1';        $connection->autocommit(TRUE);        }        else        {$connection->rollback();         $connection->autocommit(TRUE);        return false;        }

[thescientist]

the query will already return false if there's an error, no need for an extra variable and the tertiaries.

http://php.net/manual/en/pdo.query.php

$connection->autocommit(FALSE);$result1 = $connection->query('update users set password ="NULL",hash="NULL",email="NULL",usertype="5" where users.email="'.$sessionmail.'"');$result2 = $connection->query('delete from favorites where (select users.user_ID from users where favorites.userID=users.user_ID)');if($result1 && $result2) {  //it was successful}else{  //it wasn't successful}

Also, any lines of code put after a return statement won't get executed, so you're if should have the return statement last.

Edited September 10, 2013 by thescientist

[jimfog]

Ι am not using PDO, but MySqli.

[justsomeguy]

That doesn't matter, mysqli::query also returns false if a query fails, right?