Spring Boot Database initialization MySQLException for Trigger

Question

I am using Spring Boot Database initialization using Spring JDBC with schema.sql file.I am using MYSQL

If I have simple table creation in schema.sql as follows it works fine

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

But when I add one trigger as follows which runs correctly in MySQL Workbench

DROP TRIGGER IF EXISTS Persons_log_update; 

CREATE TRIGGER Persons_log_update 
    BEFORE UPDATE ON Persons
    FOR EACH ROW 
BEGIN

    INSERT INTO Personshistory(PersonID,LastName,FirstName,Address,City)
    values(OLD.PersonID,OLD.LastName,OLD.FirstName,OLD.Address,OLD.City);

END ^;

I have used spring.datasource.separator=^; in properties file as mentioned here

But it fails with exception as

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TRIGGER Persons_log_update BEFORE UPDATE ON Persons FOR EACH ROW BE' at line 1

I guess my problem is same as this question but that is in postgresql.

Edit: If I remove spring.datasource.separator=^; from properties file and have below cursor

DROP TRIGGER IF EXISTS Persons_log_update; 

DELIMITER $$
CREATE TRIGGER Persons_log_update 
    BEFORE UPDATE ON Persons
    FOR EACH ROW 
BEGIN

    INSERT INTO Personshistory(PersonID,LastName,FirstName,Address,City)
    values(OLD.PersonID,OLD.LastName,OLD.FirstName,OLD.Address,OLD.City);

END$$
DELIMITER ;

It gives same error

 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE TRIGGER Persons_log_update BEFORE UPDATE ON Persons FO' at line 1

Are you sure that spring.datasource.separator=^ does not replace your semicolon as separator? What kind of error do you get if you get rid of this property configuration? — Jagger
– Jagger, Commented Mar 8, 2017 at 16:22
@Jagger As per your suggestion removed spring.datasource.separator=^ but getting similar error. Check updated question — MasterCode
– MasterCode, Commented Mar 8, 2017 at 16:59

MasterCode · Accepted Answer · 2017-03-15 12:37:23Z

5

My issue was resolved when I have added spring.datasource.separator=^; in application.properties and every line out side the procedure/trigger should be terminated with ^; Example as follows:

DROP TRIGGER IF EXISTS Persons_log_update ^; 

CREATE TRIGGER Persons_log_update 
    BEFORE UPDATE ON Persons
    FOR EACH ROW 
BEGIN

    INSERT INTO Personshistory(PersonID,LastName,FirstName,Address,City)
    values(OLD.PersonID,OLD.LastName,OLD.FirstName,OLD.Address,OLD.City);

END ^;

answered Mar 15, 2017 at 12:37

MasterCode

9956 gold badges21 silver badges46 bronze badges

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

1 Comment

Mohicane Over a year ago

This answer save my days!

pjsagar · Accepted Answer · 2020-12-10 08:48:57Z

setting spring.datasource.separator=^; does not resolved my issue.

I am using ResourceDatabasePopulator class to load sql scripts and found that this class provides method to set Separator so i did that.

Note: dataSource object is autowired and is configured by spring. so add it (javax.sql.DataSource) before using it.

@Autowired DataSoruce dataSource

ResourceDatabasePopulator triggersPopulator = new ResourceDatabasePopulator(false, false, StandardCharsets.UTF_8.toString(), new ClassPathResource("triggers.sql"));
        triggersPopulator.setSeparator("//");
        triggersPopulator.execute(dataSource);

and done changes in sql script as per separator (//).

drop trigger if exists news_data_total_news_incremental_trigger;
//
create trigger news_data_total_news_incremental_trigger
after insert on `news_data` for each row
begin
declare cnt,newcnt bigint;
select `count` into cnt from `news_data_total_news` limit 1;
set newcnt = cnt + 1;
update `news_data_total_news` set `count` = newcnt where `count` = cnt;
end//

And it worked.

One more thing, we can have multiple ResourceDatabasePopulator objects with different separator defined.

ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator(false, false, StandardCharsets.UTF_8.toString(), new ClassPathResource("data.sql"));
        resourceDatabasePopulator.execute(dataSource);
        
ResourceDatabasePopulator triggersPopulator = new ResourceDatabasePopulator(false, false, StandardCharsets.UTF_8.toString(), new ClassPathResource("triggers.sql"));
        triggersPopulator.setSeparator("//");
        triggersPopulator.execute(dataSource);

As you can see, i have two ResourceDatabasePopulator objects to load two different sql scripts, data.sql with default separator and triggers.sql with // as separator.

Collectives™ on Stack Overflow

Spring Boot Database initialization MySQLException for Trigger

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related