Issue
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.
Solution
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.
Answered By - Evgenij Ryazanov