0

I have this small script in my populate.sql which is supposed to roll back all inserts if any fails. However im not being able to execute populate.sql through "source populate.sql" cause it says i have a syntax error on my handler, but i've already check mysql 5.7 docs and everything seems all right. What am i doing wrong?

DELIMITER $$
CREATE PROCEDURE foo()
BEGIN

    DECLARE rollbacka BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET rollbacka = 1;

    START TRANSACTION;
    insert into obra values('101-903aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','101-903.jpeg');
    insert into obra values('101-904','101-904.jpeg');
    insert into obra values('101-905','101-905.jpeg');
    insert into obra values('101-906','101-906.jpeg');
    insert into obra values('101-907','101-907.jpeg');

    IF rollbacka THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

END $$
DELIMITER ;


select * from museu.obra;

[update]Here's what im testing. It Shouldn't update table obras cause first attribute of the first insert is too big, so it will throw an exception, however when i comment it, the table remains empty, in that situation the other 4 inserts should work.

John Doe
  • 59
  • 7
  • Not sure if you do or not, but you can only use this inside stored programs (stored procedures/functions/triggers). In general, rollback is the default behaviour for transactions when an error occurs, but it might depend on the logic of your script if that is possible - you "just" have to make sure it doesnt reach the commit, including anything that implicitely commits (e.g. alter table) – Solarflare Jan 07 '19 at 14:42
  • Thanks for the answer, could you provide an example ? I just want to do many inserts and if any fails rollback all of them – John Doe Jan 07 '19 at 16:41
  • Well, if you e.g. just run your script in mysql client, an error will end execution and it will be rolled back automatically if you started a transaction (try e.g. your test script without the handler code and add something that causes an error). Check e.g. [here](https://stackoverflow.com/q/6121917) for some methods. – Solarflare Jan 07 '19 at 20:24
  • ive already seen some examples (including that link) but they always return some syntax error – John Doe Jan 07 '19 at 20:33
  • "some syntax error" is rather unspecific. Add your (sample, but complete) code to your question, maybe how you run it (e.g. `mysql < script`) and the error you get. – Solarflare Jan 07 '19 at 20:57
  • at the moment im doing this : (check post) – John Doe Jan 07 '19 at 21:06
  • You would have to actually execute the procedure (e.g. `call foo();`), you are currently just creating it. – Solarflare Jan 07 '19 at 21:44

0 Answers0