2

I'm getting a very weird error from H2 where it tells me that it doesn't know a data type but doesn't tell me which one it is.

That's the error message:

Unknown data type: ; SQL statement:
CREATE TABLE bans (id INT NOT NULL AUTO_INCREMENT, player_id INT NOT NULL, operator_id INT NOT NULL, end DATETIME NULL, reason VARCHAR(1024) NOT NULL, PRIMARY KEY (id), INDEX (player_id), INDEX (end), FOREIGN KEY (player_id) REFERENCES players(id), FOREIGN KEY (operator_id) REFERENCES players(id)) [50004-200]

and this is the SQL query in plain:

CREATE TABLE bans (id INT NOT NULL AUTO_INCREMENT, player_id INT NOT NULL, operator_id INT NOT NULL, end DATETIME NULL, reason VARCHAR(1024) NOT NULL, PRIMARY KEY (id), INDEX (player_id), INDEX (end), FOREIGN KEY (player_id) REFERENCES players(id), FOREIGN KEY (operator_id) REFERENCES players(id))

I neither understand what H2 is trying to tell me, nor do I see what's wrong with that query. I tried playing around with the spacing but to no avail.

Edit 1:

I'm opening the database connection with this JDBC string:

jdbc:h2:%s;AUTO_SERVER=%s;DATABASE_TO_UPPER=FALSE

(And using String.format to set the two appropriate values. First one naturally being the base file name and the second either TRUE or FALSE (either work).)

I am not changing any other settings or enabling any other modes. The only thing I do is create 2 tables before and inserting a bit of data into them.

Improve this question
3
  • H2 1.4.200 accepts SET MODE MySQL; CREATE TABLE PLAYERS(ID INT); CREATE TABLE bans (id INT NOT NULL AUTO_INCREMENT, player_id INT NOT NULL, operator_id INT NOT NULL, end DATETIME NULL, reason VARCHAR(1024) NOT NULL, PRIMARY KEY (id), INDEX (player_id), INDEX (end), FOREIGN KEY (player_id) REFERENCES players(id), FOREIGN KEY (operator_id) REFERENCES players(id)); without any exceptions. You need to provide a complete test case that illustrates your problem. Commented Dec 5, 2019 at 1:48
  • Perhaps you didn't set a compatibility mode for non-standard definition of an index. Commented Dec 5, 2019 at 1:49
  • @EvgenijRyazanov I'm not using MySQL mode though. Just DATABASE_TO_UPPER=FALSE. Commented Dec 5, 2019 at 1:50

3 Answers 3

Reset to default
3

You cannot use non-standard INDEX(player_id) and INDEX(end) in H2 without a MySQL compatibility mode. (Actually database indexes aren't covered by the Standard.)

Either use a MySQL compatibility mode, or use a separate CREATE INDEX command such as

CREATE INDEX ON bans(end);

Index on player_id column is not needed, because non-unique index will be created automatically by H2 for constraint FOREIGN KEY (player_id) REFERENCES players(id).

Please also note that behavior of DATABASE_TO_UPPER=FALSE was changed since 1.4.198. With this setting all column names are case-sensitive. You may want to use DATABASE_TO_LOWER=TRUE instead.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

It’s the small things sometimes... Though what weird error message nonetheless. And thank you for the tip with the alternative flag. I used it originally to have the same capitalizations as the other database I use in the that software. Good to know I can force them to be lowercase.
Current H2 throws Syntax error in SQL statement "… PRIMARY KEY (ID), INDEX ([*]PLAYER_ID), INDEX (END), …"; expected "IDENTITY, BIGSERIAL, SERIAL, data type", such message is more or less reasonable, a data type of the INDEX column is expected instead of (.
1

Another thing to watch out for are forbidden characters in column names. e.g. if you use a column name such as @Column(name="my-column") then you'll actually see the exact same error message.

Unknown data type: ; SQL statement:

When you're really stuck, connect to the console webpage of the database, and try to execute the query there. (There's usually a query in the exception message).

If you run an embedded H2 database, then you can enable the console webpage by setting the following properties:

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

# view database on http://localhost:8080/h2-console
spring.h2.console.enabled=true
Improve this answer

Comments

1

Sometimes you get this exception when you have the same function in H2 and in your dev DB, but the functionality is not the same:

For example CONVERT function in H2 and Orcacle.

In this case you can override the H2 function when you put this in your H2 URL: BUILTIN_ALIAS_OVERRIDE=1;

Then you have to create an alias in your test resources sql:

CREATE ALIAS CONVERT FOR "com.example.config.H2Functions.convert";

Then create this class with the emethod in java:

public class H2Functions {

public static String convert(String param1, String param2) {
    return StringUtils.stripAccents(param1);
}}

If you are using Junit5 you have to implement org.junit.jupiter.api.extension.BeforeEachCallback;

    @Override
    public void beforeEach(ExtensionContext context) throws Exception {
         DataSource dataSource = getDataSource(context);
         backupDatabase(dataSource);
    }

    private void backupDatabase(DataSource dataSource) {
        try {
            Connection connection = dataSource.getConnection();
            Statement statement = connection.createStatement();
            RunScript.execute(connection, IOUtils.getReader(H2Extension.class.getResourceAsStream("/h2_functions.sql")));
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

And put these annotations on your test class:

@SpringBootTest
@ExtendWith({SpringExtension.class, H2Extension.class})
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.