Matt  Watson Matt Watson - 1 year ago 62
SQL Question

What is the best way to get around these Hibernate exepctions when swithching from MySql to an in memory database for testing?

I am working on a Spring Boot application that uses MySql as it's data source. There is an entity in the application that has a field with the defintion:

@Column(name = "Vendor", columnDefinition="TEXT")
private String vendor;

The definition of the column in the database is
which is why I suspect the entity has been given the
, since without it I get the exception:

Caused by: org.hibernate.HibernateException: Wrong column type in database.example for column Vendor. Found: text, expected: varchar(255)

This is fine when I'm running the application and it talks to the MySql database. The problems come when I want to run some tests that talk to an in memory database (hsqldb in this case) configured as below:

public DataSource meshlium()
DriverManagerDataSource dataSource = new DriverManagerDataSource();
return dataSource;

* Declare the JPA entity manager factory.
public LocalContainerEntityManagerFactoryBean entityManagerFactory()
LocalContainerEntityManagerFactoryBean entityManagerFactoryOffice = new LocalContainerEntityManagerFactoryBean();


// Classpath scanning of @Component, @Service, etc annotated class

// Vendor adapter
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();

// Hibernate properties
Properties additionalProperties = new Properties();
additionalProperties.put("", "create-drop");
additionalProperties.put("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");

return entityManagerFactoryOffice;

When I run my tests and hibernate tries to create the database I get exceptions in the log file:

2016-05-05 15:44:31.020(GMT)[main] ERROR o.h.tool.hbm2ddl.SchemaExport - HHH000389: Unsuccessful: create table bluetoothData (ID_frame integer not null, RSSI varchar(255), MAC varchar(255), TimeStamp timestamp, Vendor TEXT, ID TEXT, cod varchar(255), primary key (ID_frame))
2016-05-05 15:44:31.020(GMT)[main] ERROR o.h.tool.hbm2ddl.SchemaExport - Wrong data type: TEXT in statement [create table bluetoothData (ID_frame integer not null, RSSI varchar(255), MAC varchar(255), TimeStamp timestamp, Vendor TEXT]

If I remove the
property from
the problem goes away and my tests can run, however as I described above the application won't start. So I'm stuck in a situation where I can change the code to make the application work but the tests fail, or the tests fail and the application works.

One solution that I've seen mentioned elsewhere was to change the
annotation to use the
property and remove
so that I have:

@Column(name = "Vendor", length=255)
private String vendor;

This change allows my tests pass but still throws the original exception on start up.

Is there a solution that will allow both my tests and my application to function properly while using MySql in production and hsqldb in my unit tests?

Sam Sam

So a little research reveals that you can set H2 to act in MYSQL mode.

I believe if you change your database URL to have MODE=MySQLat the end it should work!

Edit: If you wish to stick with HSQL, they also have an equivalent property - sql.syntax_mys=true