Sql file at startup produce an SQL Error Exception with H2 database [DATAJPA-933]

[spring-projects-issues]

Hervé DARRITCHON opened DATAJPA-933 and commented

Please find in Reference URL a link to my github repository with a simple project that produces the error. With a project using Spring Boot, Spring Data JPA, I put an sql file in classpath to initialize the embeded database (h2). The file is named data-h2.sql following the naming proposed in the documentation.

In fact, the first sql line produces all the time an SQL Error even if it's a proper SQL command with a good syntax. I have found that because if I put "fake" sql command lile a truncate table and after I put my insert commands to initialize my database and I set in the propertie file:

 
spring.datasource.continue-on-error=true

The server start and the database is initialize but if I set the configuration spring.datasource.continue-on-error to false then there is an error at startup on the first sql command. Exemple of exception throws by the server at startup :

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory': Post-processing of FactoryBean's singleton object failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/Users/throdo/Programmation/Workspaces/Java/Embeded%20DB%20Initialisation%20Issue/target/classes/data-h2.sql]: INSERT INTO users (id, firstname, lastname, age) VALUES (1, 'john', 'doe', 34), (2, 'jane', 'smith', 44), (3, 'robert', 'strike', 23); nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT[*] INTO USERS (ID, FIRSTNAME, LASTNAME, AGE) VALUES (1, 'john', 'doe', 34), (2, 'jane', 'smith', 44), (3, 'robert', 'strike', 23) "; SQL statement:
INSERT INTO users (id, firstname, lastname, age) VALUES (1, 'john', 'doe', 34), (2, 'jane', 'smith', 44), (3, 'robert', 'strike', 23) [42000-192]
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:116)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1595)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:254)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:220)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:351)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:332)
	at org.springframework.orm.jpa.EntityManagerFactoryUtils.findEntityManagerFactory(EntityManagerFactoryUtils.java:143)
	at org.springframework.orm.jpa.EntityManagerFactoryAccessor.setBeanFactory(EntityManagerFactoryAccessor.java:137)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeAwareMethods(AbstractAutowireCapableBeanFactory.java:1597)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1565)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545)
	... 47 more
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/Users/throdo/Programmation/Workspaces/Java/Embeded%20DB%20Initialisation%20Issue/target/classes/data-h2.sql]: INSERT INTO users (id, firstname, lastname, age) VALUES (1, 'john', 'doe', 34), (2, 'jane', 'smith', 44), (3, 'robert', 'strike', 23); nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT[*] INTO USERS (ID, FIRSTNAME, LASTNAME, AGE) VALUES (1, 'john', 'doe', 34), (2, 'jane', 'smith', 44), (3, 'robert', 'strike', 23) "; SQL statement:
INSERT INTO users (id, firstname, lastname, age) VALUES (1, 'john', 'doe', 34), (2, 'jane', 'smith', 44), (3, 'robert', 'strike', 23) [42000-192]
	at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:494)
	at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:231)
	at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:48)
	at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:157)
	at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runDataScripts(DataSourceInitializer.java:114)
	at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.onApplicationEvent(DataSourceInitializer.java:107)
	at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.onApplicationEvent(DataSourceInitializer.java:47)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:166)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:138)
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:381)
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:335)
	at org.springframework.boot.autoconfigure.orm.jpa.DataSourceInitializedPublisher.postProcessAfterInitialization(DataSourceInitializedPublisher.java:70)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsAfterInitialization(AbstractAutowireCapableBeanFactory.java:422)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.postProcessObjectFromFactoryBean(AbstractAutowireCapableBeanFactory.java:1723)
	at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:113)
	... 57 more
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT[*] INTO USERS (ID, FIRSTNAME, LASTNAME, AGE) VALUES (1, 'john', 'doe', 34), (2, 'jane', 'smith', 44), (3, 'robert', 'strike', 23) "; SQL statement:
INSERT INTO users (id, firstname, lastname, age) VALUES (1, 'john', 'doe', 34), (2, 'jane', 'smith', 44), (3, 'robert', 'strike', 23) [42000-192]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.message.DbException.getSyntaxError(DbException.java:191)
	at org.h2.command.Parser.getSyntaxError(Parser.java:530)
	at org.h2.command.Parser.parsePrepared(Parser.java:488)
	at org.h2.command.Parser.parse(Parser.java:317)
	at org.h2.command.Parser.parse(Parser.java:293)
	at org.h2.command.Parser.prepareCommand(Parser.java:254)
	at org.h2.engine.Session.prepareLocal(Session.java:560)
	at org.h2.engine.Session.prepareCommand(Session.java:501)
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1202)
	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170)
	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
	at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:473)

---

Affects: 1.9.4 (Gosling SR4)

Reference URL: https://github.com/herveDarritchon/spring-boot-issues

Attachments:

• Bildschirmfoto 2016-07-20 um 17.46.15.PNG (36.90 kB)
• Bildschirmfoto 2016-07-20 um 17.46.18.PNG (38.95 kB)

[spring-projects-issues]

Oliver Drotbohm commented

Your SQL file contains a couple of invisible characters before the INSERT. If you remove those the sample works fine

[spring-projects-issues]

Oliver Drotbohm commented

Attached screenshot of the "broken" file

[spring-projects-issues]

Oliver Drotbohm commented

Attached screenshot of the file the way it's supposed to look like

[spring-projects-issues]

Oliver Drotbohm commented

Oh and a final note. This is not really related to Spring Data but the general SQL script execution support in core Spring Framework

[spring-projects-issues]

Hervé DARRITCHON commented

yes it works fine.

Sorry for this fake bug report.

I feel ashamed of myself :(

[spring-projects-issues]

Oliver Drotbohm commented

No worries, tricky thing to spot. Happy we got it working for you!

[spring-projects-issues]

Jens Schauder commented

Batch closing resolved issue without a fix version and a resolution indicating that there is nothing to release (Won't fix, Invalid ...)