Transactions do not commit/rollback when db_debug is TRUE on certain OS’s |
|||
|---|---|---|---|
| Date: | 04/22/2008 | Severity: | Minor |
| Status: | Resolved | Reporter: | gtech |
| Version: | 1.6.1 | ||
| Keywords: | Libraries, Database Class | ||
| Forum Thread: | http://codeigniter.com/forums/viewthread/77294/ | ||
Description
When db_debug=FALSE in the config/database.php transactions work fine,
when db_debug=TRUE transactions are not completed on some os’s and cause strange results or table locks.
why?
line 303 of DB_driver.php will call display_error() if db_debug is turned on as soon as a query fails.
line 1143 in the display_error() function is exit; which will terminate the script if db_debug is on and that is maybe why we are seeing different behaviors on different OS’s.
Although rollback/commit appears to work on my machine at home with debug on, it does not on other machines. I’m guessing this could be a race condition between PHP exiting and the commit/rollback happening.
anyway the thread below shows some tests that have been taken.
http://codeigniter.com/forums/viewthread/77294/
solution
in my opinion it should be made clear in the documentation that db_debug needs to be set to FALSE for transactions to work OR we don’t display the error inside a transaction until its commited or rolled back.
Code Sample
//mysql defaulted as innodb tables
create table test(t varchar(20) unique);
//controller
$this->db->trans_start();
$this->db->insert('test',array('t' => 2));
$this->db->insert('test',array('t' => 2));
echo"?";
$this->db->trans_complete();
echo"?";
return
Expected Result
?
followed by an SQL error showing duplicate entry on the screen (after the commit or rollback has happened)
OR documentation says that db_debug has to be set to FALSE when using transactions.
Actual Result
CI 1.6.1, PHP 5.2.3, mysql 5.0.45 on Ubuntu Gusty:
SQL error is displayed
The transaction is not completed, so when the page is refreshed a nested transaction occurs.
CI 1.6.1, PHP 5.2.1, MySQL 5.0.37 on Windows XP:
The transaction is completed, so rollback always happens.
this could be because the php command exit; behaves differently on different machines?
I logged this bug after discussing the problem with other community members. hope its helpful.
Comment on Bug Report
| Posted by: Derek Jones on 12 May 2008 1:48pm | |
|
A fix has been committed, please check out DB_Driver from the SVN to confirm. Also see the note in user_guide/database/transactions.html also in the SVN regarding the $trans_strict property that sets “Strict Mode” operation on transactional databases. |
| Posted by: gtech on 17 May 2008 9:40pm | |
|
Hello I just checked out the fix in 1.6.2 and I am unsure if all conditions have been thought of, unless I am misunderstanding something at the moment the fix is to complete the transaction when an error occurs during the transaction, which will of course cause the transaction to rollback. BUT what if there are queries in the original transaction still left to be completed after the error.. These queries will no longer be in a transaction and will not get rolled back, as rollback has already happened. maybe there needs to be some form of flag that is true when the dbcalls are in a transaction so that when there is an error (and db_debug is true) the display_error function is not called. Then in trans_complete call display_error after rollback? |
