Trouble with Integration Testing MySQL Syntax on an H2 Database in Spring
<p>I am working on a project which uses different MySQL schemas for customer management, e.G. every customer has his own database schema, which is created with a skeleton template.</p>

<p>For compatibility issues, I have to create new MySQL schemas from Java, which was before done in PHP. I created a small service class, which works perfectly on a MySQL database by repeatedly using <code>CREATE TABLE 'a' LIKE 'b';</code>, however this does not work with H2db since H2 does not support the <code>LIKE</code>-part when creating tables. I then created a MySQL and modified the file to be easily processed with Java (<code>Files.readAllLines</code>, which is fed into <code>Statement.executeBatch</code>). However, this dump also fails on a H2db because of Statements like <code>COLLATION=UTF8</code>. Sadly, these are important to have, since we often have special chars which need to be encoded correctly, so simply removing said statements from the sql file is not recommended.</p>

<p>I initialize the DataSource like this:</p>

<pre><code>public DataSource dataSource(ResourceLoader resourceLoader) {
    return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.H2)
            .setScriptEncoding("UTF-8")
            .addScript("data.sql").build();
}
</code></pre>

<p>So, my configuration possibilities are scarce. We use <code>gradle</code> to build our application, so the suggested <code>maven</code>-plugin won't help either - at least I have not found out ow to use it with <code>gradle</code>, if that is possible at all.</p>

<p>So my problem here is, that I'd need to check if the Service actually persists the data correctly, but I cannot do it in-memory since H2 does not support the syntax, and I am not allowed to use the "real" MySQL database, since all our tests must be detached from "real" connections and shall only use in-memory stores for databases, if necessary.</p>