[Solved] CommandAcceptanceException: Error executing DDL

1. Problem

When writing tests that depend on H2 database in a Spring boot application, we may encounter the CommandAcceptanceException: Error executing DDL. The root cause exception will be org.h2.jdbc.JdbcSQLSyntaxErrorException in the logs. The exception stack trace will look something like this:

2024-09-23T13:04:14.577+05:30  WARN 32624 --- [spring-boot-hibernate-search-lucene] [           main] 
o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error 
executing DDL "create table book (id bigint not null auto_increment, author varchar(255) not null, title varchar(
255) not null, primary key (id)) engine=InnoDB" via JDBC [Syntax error in SQL statement "create table book (id 
bigint not null auto_increment, author varchar(255) not null, title varchar(255) not null, primary key (id)) 
engine[*]=InnoDB"; expected "identifier";]
...
...
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table book (id bigint not 
null auto_increment, author varchar(255) not null, title varchar(255) not null, primary key (id)) engine=InnoDB" 
via JDBC [Syntax error in SQL statement "create table book (id bigint not null auto_increment, author varchar(255) 
not null, title varchar(255) not null, primary key (id)) engine[*]=InnoDB"; expected "identifier";]
...
...
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table book (id bigint not 
null auto_increment, author varchar(255) not null, title varchar(255) not null, primary key (id)) 
engine[*]=InnoDB"; expected "identifier"; SQL statement:
...
...

If you are trying to run unit tests with the H2 database and facing the above error, continue reading for the reason and fixes.

2. Reason

The keyword to notice in the above stack trace is ‘engine[*]=InnoDB‘. The InnoDB is the default storage engine for MySQL databases. The presence of InnoDB in the DDL statement means that the development/production datasource configuration (that uses MySQL database) has leaked into the test environment.

Specifically, the test environment is using the ‘hibernate.dialect’ as ‘org.hibernate.dialect.MySQLDialect’.

The correct dialect to use with the H2 database is ‘org.hibernate.dialect.H2Dialect‘.

3. Solution

As this is a configuration issue, we must fix it at the configuration level.

Option 1. Fix the Configuration Leak

The most appropriate way to fix the “CommandAcceptanceException: Error executing DDL” error is to find and fix the configuration leak. There could be many places where it can be fixed, such as:

• Fix the dialect in application-test.properties

Make sure you are using the org.hibernate.dialect.H2Dialect.

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_ON_EXIT=TRUE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

• Read the properties values dynamically, and do not assign hardcoded values in JPA configuration class.

Another common mistake could be that in JpaConfig.java (or any other class where you are configuring datasource), you could be setting the hardcoded value to dialect property. The correct way is to read the configuration dynamically.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories
public class JpaConfig {

	private final Environment env;

	// Other beans ...

	private Properties hibernateProperties() {

		Properties properties = new Properties();

		//This is INCORRECT
		//properties.put("hibernate.hbm2ddl.auto", "update");	
		//properties.put("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");

		properties.put("hibernate.hbm2ddl.auto", 
			env.getProperty("spring.jpa.hibernate.ddl-auto", "update"));
		properties.put("hibernate.dialect", 
			env.getProperty("spring.jpa.database-platform", "org.hibernate.dialect.MySQLDialect"));
		return properties;
	}
}

You could be setting the dialect in more places, so you should look for and fix all those places.

Option 2. Append ‘MODE=MySQL‘ to Connection String

If you cannot figure out where you are leaking the configuration or making the code changes will take longer than expected, and you want to run the tests immediately, you can pass the engine the information in the connection string.

Note that this fix by using “;MODE=MySQL” should be temporary because fixing the configuration leak is the correct way to fix this error.

#spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_ON_EXIT=TRUE
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_ON_EXIT=TRUE;MODE=MySQL

4. Summary

As discussed in this post, the ‘CommandAcceptanceException: Error executing DDL‘ exception is mainly caused by the configuration leak from the dev/prod environment to the test environment. We have to find and fix the leak to solve this issue.

Happy Learning !!