Error when executing mvn sql:execute

Question

I want Maven to execute the sql file, and the database schema it generates will be later used in my program. But it doesn't work, maybe caused by 'DELIMITER'. When I execute 'mvn sql:execute', it prints that

[ERROR] Failed to execute:  DELIMITER $$

Here is part of my pom.xml

        <plugin>
            <groupId>org.codehaus.mojo</groupId>  
            <artifactId>sql-maven-plugin</artifactId>
            <version>1.5</version>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>5.1.21</version>
                </dependency>
            </dependencies>
            <executions>
                <execution>
                    <id>init-schema</id>
                    <goals>
                        <goal>site</goal>
                    </goals>
                    <phase>deploy</phase>
                </execution>
            </executions>
            <configuration>
                <driver>com.mysql.jdbc.Driver</driver>
                <username>root</username>
                <password>root</password>
                <url>jdbc:mysql://127.0.0.1:3306/?useUnicode=true&amp;characterEncoding=UTF-8</url>
                <autocommit>false</autocommit>
                <srcFiles>
                    <srcFile>src/main/sql/jellyjolly-schema.sql</srcFile>
                </srcFiles>
            </configuration>
        </plugin>

Here is part of my sql file

DELIMITER $$

USE `jellyjolly_schema`$$
DROP TRIGGER IF EXISTS `jellyjolly_schema`.`delete_user` $$
USE `jellyjolly_schema`$$


CREATE TRIGGER delete_user
AFTER DELETE
ON jj_users
FOR EACH ROW
BEGIN
    ## delete the posts that belong to the user
    DELETE FROM jj_blog_posts WHERE author_user_id=OLD.user_id;
END
$$

Is it because the keyword 'DELIMITER' is not supported by MySQL Java connector which is invoked by Maven. So how can I solve it?

Why are you using such a weird delimiter. The usual delimiter is ";". — khmarbaise, Jan 16 '13 at 12:56
@khmarbaise If i use delimiter ';', ';' cannot appear in declaim statement. For example, 'DELETE FROM jj_blog_posts WHERE author_user_id=OLD.user_id;' cannot end with ';' — Wenhao Ji, Jan 16 '13 at 13:01
I agree there is probably a more elegant solution. You could have a look at [this question](http://stackoverflow.com/questions/13072354/sql-maven-plugin-with-multiple-delimiters) for inspiration. — Sean Connolly, Jan 16 '13 at 13:16

score 2 · Accepted Answer · edited May 23 '17 at 12:15

After reading your comments, I believe the best solution is the one accepted here. That is, use only one delimiter (preferably the default, ;) and use the sql-maven-plugin configuration to require the delimiter to occur on it's own line to actually be a delimiter.


    &ltconfiguration&gt
      &ltdelimiterType&gtrow&lt/delimiterType&gt
    &lt/configuration&gt

(credit to @Zheka)

Your trigger would then look like:

CREATE TRIGGER delete_user
AFTER DELETE
ON jj_users
FOR EACH ROW
BEGIN
    ## delete the posts that belong to the user
    DELETE FROM jj_blog_posts WHERE author_user_id=OLD.user_id; # this delimiter is ignored
END
; # this delimiter is recognized

score 0 · Answer 2 · edited Aug 24 '17 at 10:59

0

You can specify a new delimiter in the sql-maven-plugin configuration like so:


   &ltconfiguration&gt
     &ltdelimiter&gt$$&lt/delimiter&gt
   &lt/configuration&gt

More on this configuration parameter at this page.

edited Aug 24 '17 at 10:59

kenorb

155,785
88
678
743

answered Jan 16 '13 at 13:00

Sean Connolly

5,692
7
37
74

If part of the sql use delimiter ; and another part use delimiter $$, how to handle it? – Wenhao Ji Jan 16 '13 at 13:26
I don't believe it is possible, sorry. – Sean Connolly Jan 16 '13 at 13:43

score 0 · Answer 3 · answered Dec 28 '14 at 23:52

if yo u dont want to use <delimiterType>row</delimiterType> another way to do it besides sean's answer is to get rid of the DELIMITER $$ and $$ and use a delimiter other than semicolon in the plugin.

First change the delimiter in the plugin, for example

<delimiter>;;</delimiter>

Then the example trigger above will look like

USE `jellyjolly_schema`;;
DROP TRIGGER IF EXISTS `jellyjolly_schema`.`delete_user`;;
USE `jellyjolly_schema`;;


CREATE TRIGGER delete_user
AFTER DELETE
ON jj_users
FOR EACH ROW
BEGIN
    ## delete the posts that belong to the user
    DELETE FROM jj_blog_posts WHERE author_user_id=OLD.user_id;
END;;

or you can use something else as the delimiter in the plugin and change the trigger ;; accordingly

Error when executing mvn sql:execute

3 Answers3

Linked