H2 database initialisation on first startup?

[enridaga]

I am following the documentation and trying to use release trellis-0.12 but on startup, I get a WARN message about a DB table not found. I am using h2 as I am just looking to play a bit with trellis, so I expected the DB schema to be setup on startup.

I did basic editing of the config.yml file:

# Database
database:
  # driverClass: org.mariadb.jdbc.Driver
  # driverClass: org.postgresql.Driver
  driverClass: ${TRELLIS_DB_DRIVER:-org.h2.Driver}

  # The database username
  user: ${TRELLIS_DB_USERNAME:-trellis}

  # The database password
  password: ${TRELLIS_DB_PASSWORD:-pwdhere}

  # The JDBC URL
  # url: jdbc:mysql://db.example.com/db-name
  # url: jdbc:postgresql://db.example.com/db-name
  url: ${TRELLIS_DB_URL:-jdbc:h2://.../trellis/trellis-0.12.1/database}

When I first start I get the following WARN message in the console log:

WARN  [2020-06-05 14:16:23,955] org.trellisldp.webac.WebAcService: Unable to auto-initialize Trellis: org.jdbi.v3.core.statement.UnableToCreateStatementException: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "EXTENSION" not found; SQL statement:
SELECT ext FROM extension WHERE resource_id = ? [42102-200] [statement:"SELECT ext FROM extension WHERE resource_id = ?", arguments:{positional:{0:0}, named:{}, finder:[]}]. See DEBUG log for more info

Looked into DEBUG logs on the server log file but I am not finding useful hints.

What's the best way to move forward?

[acoburn]

Hi @enridaga, it is probably easiest not to edit the config file directly but rather pass those values in through ENV variables. That is, set the following two variables:

TRELLIS_DB_DRIVER=org.h2.Driver
TRELLIS_DB_URL=jdbc:h2:/path/to/trellis/trellis-0.12.1/database

If you don't mind using Docker, there is a full docker-compose example here: https://github.com/trellis-ldp/trellis-docker-tests/blob/master/trellis-h2-compose.yml using H2. In order to use the latest release, change the image from develop to either:

image: trellisldp/trellis:0.12.1

or

image: trellisldp/trellis:latest

I just ran this locally (with docker-compose, using 0.12.1) and it started with no problem. If I had to guess what the issue is, I'd bet that the JDBC URL you are providing is not correct. I've always had better luck with absolute paths, and if you're not using Docker, verify that the file permissions on that location are correct.

[enridaga]

Hi @acoburn, thank you for your reply! I would avoid docker for now. I've used the env variables as you recommended and only edited the config.yml for the username and password. I left all the other variables as they are and placed the software in /opt/trellis - permissions are OK. The h2 database files are generated but I still get the same error:

WARN  [2020-06-09 08:38:04,543] org.trellisldp.http.TrellisHttpResource: Unable to auto-initialize Trellis: org.jdbi.v3.core.statement.UnableToCreateStatementException: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "RESOURCE" not found; SQL statement:
SELECT id, interaction_model, modified, is_part_of, deleted, acl, ldp_membership_resource, ldp_has_member_relation, ldp_is_member_of_relation, ldp_inserted_content_relation, binary_location, binary_modified, binary_format FROM resource WHERE subject = ? [42102-200] [statement:"SELECT id, interaction_model, modified, is_part_of, deleted, acl, ldp_membership_resource, ldp_has_member_relation, ldp_is_member_of_relation, ldp_inserted_content_relation, binary_location, binary_modified, binary_format FROM resource WHERE subject = ?", arguments:{positional:{0:trellis:data/}, named:{}, finder:[]}]. See DEBUG log for more info

I enabled DEBUG logs and checked the trellis.log file but I don't get any more information.

[acoburn]

The issue here is that you need to run the db migrate command before starting the server. You can see an example here: https://github.com/trellis-ldp/trellis/blob/master/platform/dropwizard/src/main/docker/command.sh#L3

[enridaga]

Yep, that worked. Thank you @acoburn !