CODEWITHSUNDEEP
mysql
Talen Kylon
Talen Kylon

Reputation: 1968

How to detect if an error occured during inserts?

I am going to implement transactions for the many insert statements I have that initialize the database. I understand that a transaction will allow us to either commit the changes to the database, thus saving it. Or if one of the inserts fails, we can roll back all of the inserts that potentially did succeed. My question is, how do I detect if an insert statement has failed?

start transaction;
insert into ..
insert into ..
insert into .. 
if (failure) roll back
else commit.

Further explanation,

I have a sql script file that I run to insert a bunch of data into my database. I want to wrap a transaction around these inserts so that when run, if one of them fails then we roll back to the initial state before any inserts executed.

Upvotes: 1

Views: 2501

Answers (3)

MER
MER

Reputation: 1561

I would prefer to provide you an answer after knowing more about what you are working with (see my comment below your question). However, in general, you will receive an error from MySQL if the insert fails. Also, in general, you should check for this failure and then conditionally (if you receive a fail message) rollback. Here is a similar question with an answer: Automatic Rollback if COMMIT TRANSACTION is not reached Also, if you are working with stored procedures here's a potentially valuable post (well the answer is): mysql transaction - roll back on any exception

To be more specific: In PHP whatever method you are using to query (I recommend PDO) should return the insert failure error. Check for this error & rollback if you receive it. e.g. You can use the try catch exception handling construct & catch for PDO specific errors, which, if you receive, you can trigger your rollback in the catch.

    catch(PDOException $e)
    {
        echo 'Caught exception: ',  $e->getMessage(), "\n";
    //verify that it was an insert failure then rollback or just plain rollback becuase you received an error
    }

Hope that helps.

Upvotes: 1

madz
madz

Reputation: 1873

mysql Insert or any dml queries return something to indicate number of affected rows or any generated key.

http://dev.mysql.com/doc/refman/5.6/en/insert.html

even in some cases like Insert Select it returns a description string. All drivers have ability to detect any errors. For example in java you can try

try{ insert here commit here }catch(Sqlexception) { roll back here }

it depends on the language and driver you are going to use

Upvotes: 0

ktm5124
ktm5124

Reputation: 12123

I think the best way to do this is by logging the output to a file. When you log into MySQL, type 

tee log.txt

After your script finishes you can check that file.

Upvotes: 0

Related Questions