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

Posted on 2012-09-18
Last Modified: 2012-09-21

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
    if($additions_count > 0)
    	   $conn = new connection();  	    
	    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);
	    $stmt = $dbh->prepare($strSQL);
	    catch (PDOException $e)
	    	    $ec = $stmt->errorCode();   	    	    
	    	    //$insert_exceptions[] = $additions[$a];
	    $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.
Question by:jrm213jrm213
    LVL 25

    Expert Comment

    try moving the beginTransaction into the 'try' e.g
    LVL 82

    Accepted Solution

    You need to make sure your table is using InnoDB (or BDB).  MyISAM does not support transactions.
    LVL 17

    Author Comment

    @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.
    LVL 17

    Author Closing Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now