CODEWITHSUNDEEP
mysqldatabaseh2
iyerland
iyerland

Reputation: 642

org.h2.jdbc.JdbcSQLException: Column "ID" not found

I have the following DDL in my code:

CREATE TABLE IF NOT EXISTS SOMETABLE (
  id BIGINT AUTO_INCREMENT NOT NULL,
  ...
  FOREIGN KEY (id) REFERENCES OTHERTABLE(id)
  ...
);

Here's the definition of OTHERTABLE:

create table "OTHERTABLE" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  "code" VARCHAR NOT NULL,
  "name" VARCHAR NOT NULL,
  "enabled" BOOLEAN NOT NULL,
  "app_id" VARCHAR NOT NULL);

Please note that OTHERTABLE is auto generated by SLICK (Scala ORM Stack)!

This works with MySQL (which is our dev/prod database), however, our unit tests use H2 database, and executing this gives the following stack trace:

org.h2.jdbc.JdbcSQLException: Column "ID" not found
at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:169)
at org.h2.message.DbException.get(DbException.java:146)
at org.h2.table.Table.getColumn(Table.java:613)
at org.h2.table.IndexColumn.mapColumns(IndexColumn.java:75)
at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:203)
at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:70)
at org.h2.command.ddl.CreateTable.update(CreateTable.java:169)
at org.h2.command.CommandContainer.update(CommandContainer.java:79)
at org.h2.command.Command.executeUpdate(Command.java:235)

There is a way out of this, and that is to change id to "id" in all the places, but this changes breaks in MySQL!!

Since MySQL is our prod database, I have no other choice but to ignore the unit-tests!!

Is there a solution for this on the H2 database side?

Thanks

Upvotes: 5

Views: 19662

Answers (3)

Evan
Evan

Reputation: 41

Try to use FULL PATH to your database in project. This problem was solved after relative path was excluded. There is some bug with "~/RELATIVE_PATH" using in last H2 database versions! Bad example: "jdbc:h2:~\com\project\db\h2\h2testdb" GOOD EXAMPLE: "jdbc:h2:C:\Users\UserName\IdeaProjects\projectname\com.project\src\main\java\com\test\db\h2\h2testdb"

Upvotes: 0

manuelvigarcia
manuelvigarcia

Reputation: 2094

You are looking at the wrong query

I know this is making a fool of myself in a public forum, but still I might not be the only short-sighted around.

In my case, I am using H2 --i.e. not MySQL-- so proper treating of lower and upper case is somewhat out of suspicion. The rest of the ecosystem is made of Java, spring, JPA, and Eclipse.

Across the project, the same LONG_COLUMN_NAME was used in upper and lower case, so I started there, changing all occurrences (Java code, select queries, create statements...), but it didn't eradicate the exception.

Then I traced back the exception, to the exact line in the extractData(ResultSet rs) method extracting the value for LONG_COLUMN_NAME, to the DAO method executing the query, to the query string actually used... DOH! (this.facePalm(new SoundEffect(SOUNDS.FOR_ALL_THE_OFFICE_TO_HEAR))) the table has the column but this test query does not: it was copy-pasted from a production query, then the tables and the production code evolved to use a new version of the query including the new column and the test was left behind. So: indeed the LONG_COLUMN_NAME is not to be found in any form or fashion in the result set.

Changing the query string field to point to the same string property as used in the production code solved the issue.

Upvotes: 0

Thomas Mueller
Thomas Mueller

Reputation: 50087

If you quote the column id when creating OTHERTABLE using double quotes ("id"), then you have to quote it as well when creating the referential integrity constraint, and when querying data. Basically, you have to quote it each time. I suggest to not quote it when creating the table, because that way you don't have to quote it later on. Quoting means the the identifier is case sensitive. For MySQL, it works because internally MySQL converts unquotes identifiers to lowercase, unlike other databases. But for H2 and other databases it doesn't work.

The following two statements work for both MySQL and H2:

CREATE TABLE IF NOT EXISTS OTHERTABLE (
  id BIGINT AUTO_INCREMENT NOT NULL
);
CREATE TABLE IF NOT EXISTS SOMETABLE (
  id BIGINT AUTO_INCREMENT NOT NULL,
  FOREIGN KEY (id) REFERENCES OTHERTABLE(id)
);

So if you got an exception in the second statement, you most likely have used a different way to create the first table (OTHERTABLE). And this is where the problem is.

Next time, if you ask a question, please also include the create table statement of the first table, and post the complete error message.

Upvotes: 9

Related Questions