CODEWITHSUNDEEP
javamysqlmaven
Wenhao Ji
Wenhao Ji

Reputation: 5161

Error when executing mvn sql:execute

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?

Upvotes: 2

Views: 2463

Answers (3)

Sean Connolly
Sean Connolly

Reputation: 5801

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


   <configuration>
     <delimiter>$$</delimiter>
   </configuration>

More on this configuration parameter at this page.

Upvotes: 0

Rich
Rich

Reputation: 153

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

Upvotes: 0

Sean Connolly
Sean Connolly

Reputation: 5801

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.


    <configuration>
      <delimiterType>row</delimiterType>
    </configuration>

(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

Upvotes: 2

Related Questions