How to detect if an error occured during inserts?

Question

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.

you check the individual result of each insert query. e.g. if you're doing this from some client app, e.g. php, you'd check the insert query's return value. if it's ok, then you do another insert, etc... once done inserting, you do the commit. — Marc B, May 14 '14 at 18:24
What are you working in? I mean are these stored procedures in MySQL itself? Is this some external application which connects to the DB (like Marc B asked... is this PHP?). — MER, May 14 '14 at 18:26

score 1 · Accepted Answer · edited May 23 '17 at 12:05

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.

The link to use a stored procedure is exactly what I needed. Thank you very much! — Talen Kylon, May 14 '14 at 19:01

score 0 · Answer 2 · answered May 14 '14 at 18:26

0

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.

answered May 14 '14 at 18:26

ktm5124

11,861
21
74
119

score 0 · Answer 3 · answered May 14 '14 at 18:30

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

How to detect if an error occured during inserts?

3 Answers3