0

I have to create a script that inserts some values into a transaction. Is it possible to roll back automatically if any error occurs on a list of mysql commands?

I have the following code so far:

SET autocommit=false;
START TRANSACTION;

INSERT INTO table(c1,c2)
VALUES('a','a');

INSERT INTO table(c1,c2)
        VALUES('a','a'), -- should fail, duplicate entry
        VALUES('b','b');
SELECT 'Success';
COMMIT;
ROLLBACK; -- Print message

SET autocommit=true;

I can't make it work for some reason first insert is always executed. any ideas how to make it to roll back on any error?

fwf
  • 23
  • 7
  • Not unless you write a stored proceedure so you can create a error handler [See](https://stackoverflow.com/questions/9974325/mysql-transaction-within-a-stored-procedure) – RiggsFolly Feb 08 '22 at 18:02
  • And [this may help you](https://www.mysqltutorial.org/mysql-error-handling-in-stored-procedures/) – RiggsFolly Feb 08 '22 at 18:05
  • @RiggsFolly I want a solution without stored procedures if possible – fwf Feb 08 '22 at 18:10
  • 1
    dont think you can handle errors programatically unless you are in a SP. So unless you intend to do this line by line in a terminal so you can manually decide whether to commit or rollback I dont think you have a choice – RiggsFolly Feb 08 '22 at 18:13

0 Answers0