Syntax exception on trigger with multiple statements with MySQL and JDBC

Question

I am trying to create a trigger that performs multiple operations in MySQL 5.5.28 with InnoDB.

I have two tables, "test" and "test_watcher": changes to the first are recorded in the watcher table with the help of triggers. The last trigger needs to perform 2 operations on DELETE, it works in MySQL Workbench (with DELIMITER) but doesn't if I create it with JDBC.

CREATE TRIGGER `AD_test_FER` AFTER DELETE
  ON `test`
  FOR EACH ROW
      BEGIN
        -- if it's been inserted, modified and deleted but never synced, 
        -- the revision is NULL: no one needs to know about it
        DELETE FROM test_watcher WHERE pk = OLD.id AND revision IS NULL;

        -- if it has been synced already, we just update the flag
        UPDATE test_watcher SET flag = -1 WHERE pk = OLD.id;
      END;

I keep getting com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax.

I know it works with DELIMITER $$ in Workbench, but JDBC doesn't support it.

I've achieved it in PostgreSQL and would post the code if necessary.

Do you by any chance use the connection property allowMultiQueries=true. This might screw up things because it might simply split on ;. — Mark Rotteveel
– Mark Rotteveel, Commented Jan 31, 2013 at 10:57
Very good point, I wasn't aware of it. I checked it and it seems to work. Thank you for pointing that out. — David M.
– David M., Commented Jan 31, 2013 at 12:27

Mark Rotteveel · Accepted Answer · 2013-01-31 14:00:57Z

2

This behavior might be caused by the connection property allowMultiQueries=true. My guess is this property will make MySQL break up queries on the ; as a query separator and then execute those as separate queries, essentially breaking your trigger creation code.

As you said in a - now deleted - answer that adding allowMultiQueries=true actually solved the problem (contrary to my expectiation), the problem might actually be the last ; in your query. So another thing to check is if the problem goes away by removing the last ; (in END;) in your script (and not using allowMultiQueries=true). Some database don't consider ; to be valid at the end of a statement (as it is actually a delimiter to separate statements).

(this answer is based on my comment above)

edited Jan 31, 2013 at 14:00

answered Jan 31, 2013 at 12:31

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David M. Over a year ago

Actually, it wasn't about this property, but there was a problem with the way the ; were handled. Thank you for the hint!

Devart · Accepted Answer · 2013-01-31 10:21:32Z

2

If JDBC does not support delimiters (DELIMITER is a client command), then execute these two statements separately - one by one.

answered Jan 31, 2013 at 10:21

Devart

120k23 gold badges172 silver badges189 bronze badges

3 Comments

David M. Over a year ago

I also thought about this, but how could I do it? They are inside the trigger and should be executed at the same time.

Devart Over a year ago

No, I meant: DROP TRIGGER, then CREATE TRIGGER.

David M. Over a year ago

Okay, I updated my code to avoid nmisunderstandings. Please consider the DELETE and UPDATE statements inside the trigger creation.

Collectives™ on Stack Overflow

Syntax exception on trigger with multiple statements with MySQL and JDBC

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related