?
Solved

PDO MySQL transaction not rolling back properly or not rolling back at all

Posted on 2012-09-18
4
Medium Priority
?
452 Views
Last Modified: 2012-09-21
Hi,

I have the following code that runs a bulk insert statement, I have it all wrapped in  a transaction but when the insert causes an error, I get left with records in the database, which there shouldn't be because I rollback the transaction. So I am not sure if it is because I am running the insert as a prepared statement or if something in my code is just off and I can't see it.

 //next run additions
    unset($values);
    if($additions_count > 0)
    {
    	   $conn = new connection();  	    
	   $conn->openPDODB();
	   $dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
	   
	    for($a = 0;$a<$additions_count;$a++)
	    {
	    	    if($additions[$a]->get_password() == ""){$additions[$a]->set_password(gen_password());}
	    	    $values[] = '("' . $additions[$a]->get_username() . '","' . $additions[$a]->get_password() . '","' . $additions[$a]->get_salt() . '",' . $additions[$a]->get_licensing_entity_license_id() . ',"' . date("Y-m-d h:m:s") . '",' . $additions[$a]->get_pwd_change_required() . ')';
	    }
	    
	    $strSQL = "Insert into `" . STUDENT_TABLE . "` (`username`,`password`,`salt`,`licensing_entity_license_id`,`date_created`,`require_password_change`) Values " . implode(',',$values);
	    $dbh->beginTransaction();
	    $stmt = $dbh->prepare($strSQL);
	    try
	    {
	    	    $stmt->execute();
	    }
	    catch (PDOException $e)
	    {
	    	    $dbh->rollBack();
	    	    $ec = $stmt->errorCode();   	    	    
	    	    cleanup_and_exit("Location:/eas/upload_student_file.php?err=$ec",$handle);
	    	    //$insert_exceptions[] = $additions[$a];
	    }
	    $dbh->commit();
	    $values = array();   
    }

Open in new window


I have a constraint on the table that username/password combination is unique. When I upload a file that violates that constraint in one of the rows, it handles the error and reports back the correct error, but I end up with a partial insert into the database that remains instead of being rolled back.
0
Comment
Question by:jrm213jrm213
  • 2
4 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38416878
try moving the beginTransaction into the 'try' e.g
    try
    {
        $dbh->beginTransaction();
      $stmt->execute();
    }
0
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 38416901
You need to make sure your table is using InnoDB (or BDB).  MyISAM does not support transactions.

http://us2.php.net/manual/en/ref.pdo-mysql.php
http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html
0
 
LVL 17

Author Comment

by:jrm213jrm213
ID: 38417800
@DaveBaldwin you are probably correct, for some reason i thought the default was innodb for mysql 5+ but apparently it isn't or the database that I am working with was built in something earlier and the default was set to myIsam. I will change it and see what happens, but I am guessing it will solve the issue.
0
 
LVL 17

Author Closing Comment

by:jrm213jrm213
ID: 38422183
Thanks!
InnoDB is the new default storage engine starting with MySQL 5.6, I knew I had read that and got it in my head starting with 5, not 5.6
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question