Spring Book 2.5.0, JPA with H2 Database data.sql insert fails: Error creating bean with name 'dataSourceScriptDatabaseInitializer'

[get2mandar]

I want to raise an issue that I have observed when I tried to create a sample application with Spring Boot 2.5.0
With JPA and H2 In-Memory Database.
I initially added details for this in StackOverflow as I thought It was some issue which I was facing in my Application. But when I tried with sample Application, with only Spring Boot 2.5.0 version, and data.sql to insert data at run time. It failed.
I just updated the Spring Boot version to the older one as 2.3.10.RELEASE, the same code worked.

Stack Overflow Reference

More Details:

1. When data.sql file is used with Spring Boot Version 2.3.10.RELEASE and the Insert Statements in the data.sql file are multi-row and are on multiple lines. It works perfectly fine.

2. When data.sql renamed as an import.sql, with Spring Boot version 2.5.0 and the Insert Statements in the import.sql file are multi-row but it is expected to keep all values on the same line, this also works well.

3. When data.sql renamed as an import.sql, with Spring Boot version 2.5.0 and the Insert Statements in the import.sql file are multi-row and are on multiple lines, this scenario fails. As it considers the statement on each new line as DDL. In this case Application Runs but the data insert does not take place.

GenerationTarget encountered exception accepting command : Error executing DDL "('Data 3')" via JDBC Statement

4. And the 4th Case is when data.sql with Spring Boot version 2.5.0 and the Insert Statements in the data.sql file are multi-row and are on multiple lines, this scenario fails too At the time of Data Source Initialization and Application do not run at all.

The Stack Trace for Failure:

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2021-05-24 11:40:26.105 ERROR 23588 --- [           main] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/WORKDATA/STS_Workspaces/SpringCloudDemo/demo-data-service/target/classes/data.sql]: INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "DATA_TABLE" not found; SQL statement:
INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3') [42102-200]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1786) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.7.jar:5.3.7]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.7.jar:5.3.7]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.7.jar:5.3.7]
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:145) ~[spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758) [spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:438) [spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:337) [spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1336) [spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1325) [spring-boot-2.5.0.jar:2.5.0]
	at com.example.demo.DemoDataServiceApplication.main(DemoDataServiceApplication.java:10) [classes/:na]
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/WORKDATA/STS_Workspaces/SpringCloudDemo/demo-data-service/target/classes/data.sql]: INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "DATA_TABLE" not found; SQL statement:
INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3') [42102-200]
	at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:622) ~[spring-jdbc-5.3.7.jar:5.3.7]
	at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254) ~[spring-jdbc-5.3.7.jar:5.3.7]
	at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49) ~[spring-jdbc-5.3.7.jar:5.3.7]
	at org.springframework.boot.jdbc.init.DataSourceScriptDatabaseInitializer.runScripts(DataSourceScriptDatabaseInitializer.java:72) ~[spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.runScripts(AbstractScriptDatabaseInitializer.java:131) ~[spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applyScripts(AbstractScriptDatabaseInitializer.java:91) ~[spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.applyDataScripts(AbstractScriptDatabaseInitializer.java:85) ~[spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.initializeDatabase(AbstractScriptDatabaseInitializer.java:76) ~[spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.boot.sql.init.AbstractScriptDatabaseInitializer.afterPropertiesSet(AbstractScriptDatabaseInitializer.java:65) ~[spring-boot-2.5.0.jar:2.5.0]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845) ~[spring-beans-5.3.7.jar:5.3.7]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782) ~[spring-beans-5.3.7.jar:5.3.7]
	... 18 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "DATA_TABLE" not found; SQL statement:
INSERT INTO data_table (name) VALUES ('Data 1'), ('Data 2'), ('Data 3') [42102-200]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:453) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.command.Parser.readTableOrView(Parser.java:7628) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.command.Parser.readTableOrView(Parser.java:7599) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.command.Parser.parseInsert(Parser.java:1747) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.command.Parser.parsePrepared(Parser.java:954) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.command.Parser.parse(Parser.java:843) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.command.Parser.parse(Parser.java:815) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.command.Parser.prepareCommand(Parser.java:738) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.engine.Session.prepareLocal(Session.java:657) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.engine.Session.prepareCommand(Session.java:595) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:212) ~[h2-1.4.200.jar:1.4.200]
	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:201) ~[h2-1.4.200.jar:1.4.200]
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
	at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:601) ~[spring-jdbc-5.3.7.jar:5.3.7]
	... 28 common frames omitted

[snicoll]

There is no need to report this in two places, folks are already trying to help you on the StackOverflow thread you've started.