Error "Schema "information_schema" not found;" running "migrate up" on H2 database

Question

I'm testing h2 database in a ColdBox application (on Lucee) ... I can connect to database with Quick but I'm not able to run migrations...

this is the error displayed in CommandBox after migration init and migration up

ERROR (5.2.1+00295)
Schema "information_schema" not found; SQL statement:SELECT 1 FROM "information_schema"."tables" WHERE "table_name" = ? AND "table_schema" = ? [90079-172]   
MigrationService.cfc
SELECT 1 FROM "information_schema"."tables" WHERE "table_name" = 'cfmigrations' AND "table_schema" = 'testAppDB'

Both Application.cfc and box.json use ENVIRONMENT settings:

# ColdBox Name and Environment
APPNAME=tastApp
ENVIRONMENT=development

DB_BUNDLEVERSION=1.3.172
DB_PORT=
DB_USER=sa
DB_BUNDLENAME=org.h2
DB_CLASS=org.h2.Driver
DB_HOST=127.0.0.1
DB_DRIVER=org.h2.Driver
DB_SCHEMA=appDB
DB_DATABASE=appDB
DB_CONNECTIONSTRING=jdbc:h2:/Users/elena/coldbox-examples/testApp/database/appDB;MODE=MySQL;
DB_PASSWORD=

h2 was downloaded from Lucee download page and saved in /lib folder (I've added dependencies in box.json)

this is the grammar settings:

"defaultGrammar":"AutoDiscover@qb"

What am I missing?

Thank you in advance

Elena

UPDATE - May 18

Thanks to Evgenij,

now I am able to query the latest version of h2, 1.4.200, which accepts the setting (CASE_INSENSITIVE_IDENTIFIERS = TRUE) which makes all identifier names (table names, column names) case insensitive.

Unfortunately migrations with commandbox-migrations still give me error:

these are the current configurations I am using:

"cfmigrations":{
    "migrationsDirectory":"resources/database/migrations",
    "schema":"INFORMATION_SCHEMA",
    "connectionInfo":{
        "bundleName":"com.h2database",
        "bundleVersion":"1.4.200",
        "password":"",
        "connectionString":"jdbc:h2:/Users/elena/coldbox-examples/testApp/database/appDB;MODE=MySQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;",
        "class":"org.h2.Driver",
        "username":""
    },
    "defaultGrammar":"AutoDiscover@qb"
}

this is the last error:

> * > CLI v5.2.1 > 10:59 AM > ~/coldbox-examples/testApp/ > testApp (1.0.0) >
 lucee 5.3.7 (running) >
> migrate up
ERROR (5.2.1+00295)                                                                                                                                             Unable to resolve com.h2database [108](R 108.0): missing requirement [com.h2database [108](R 108.0)] osgi.wiring.package; (&(osgi.wiring.package=org.osgi.framework)(version>=1.5.0)) 
Unresolved requirements: [[com.h2database [108](R 108.0)] osgi.wiring.package; (&(osgi.wiring.package=org.osgi.framework)(version>=1.5.0))]
  caused by: org.osgi.framework.BundleException
Unable to resolve com.h2database [108](R 108.0): missing requirement [com.h2database [108](R 108.0)] osgi.wiring.package; (&(osgi.wiring.package=org.osgi.framework)(version>=1.5.0)) Unresolved requirements: [[com.h2database [108](R 108.0)] osgi.wiring.package; (&(osgi.wiring.package=org.osgi.framework)(version>=1.5.0))]

score 0 · Answer 1 · answered May 06 '21 at 12:45

0

H2 1.3.172 is very old and it can't properly emulate non-standard lower case INFORMATION_SCHEMA from MySQL.

You need to use a recent version of H2 (such as 1.4.200, for example) with ;MODE=MySQL;DATABASE_TO_LOWER=TRUE appended to JDBC URL, ;MODE=MySQL alone is not enough.

answered May 06 '21 at 12:45

Evgenij Ryazanov

6,960
2
10
18

Thank you Evgenij; I've downloaded suggested version and saved in in lib fonder of my coldbox application, but now I've this error: The OSGi Bundle with name [org.h2] in version [1.4.200] is not available locally (/Users/elena/.CommandBox/server/A6BB937D9E26EE318D085EF2B2DB46FA-testApp/lucee-5.3.7.47/WEB-INF/lucee-server/bundles; What am I missing? – elena May 07 '21 at 13:59
It was renamed to `com.h2database` in the version 1.4.198. – Evgenij Ryazanov May 07 '21 at 15:32
ok... what about this error:Unable to resolve com.h2database [106](R 106.0): missing requirement [com.h2database [106](R 106.0)] osgi.wiring.package; (&(osgi.wiring.package=org.osgi.framework)(version>=1.5.0)) Unresolved requirements: [[com.h2database [106](R 106.0)] osgi.wiring.package; (&(osgi.wiring.package=org.osgi.framework)(version>=1.5.0))] caused by: org.osgi.framework.BundleException Unable to resolve com.h2database [106](R 106.0): missing requirement [com.h2database [106](R 106.0)] osgi.wiring.package; (&(osgi.wiring.package=org.osgi.framework)(version>=1.5.0)) Unresolved re... – elena May 13 '21 at 16:31
another question, I hope the last: In H2 1.3.172 the bundle name was 'h2.org' and driver class name was 'org.h2.Driver... in H2 1.4.199 the bundle name is 'com.h2database'; what the driver class name is? – elena May 14 '21 at 08:18
It is still `org.h2.Driver`, but it's rarely appropriate to use it directly, since Java 6 / JDBC 4.0 drivers are registered automatically. – Evgenij Ryazanov May 14 '21 at 09:01
Hello Evgenij, unfortunately the class name is mandatory for commandbox-migrations :( ... with this configuration ["migrationsDirectory":"resources/database/migrations", "schema":"INFORMATION_SCHEMA", "connectionInfo":{ "bundleName":"com.h2database", "bundleVersion":"1.4.200", "connectionString":"jdbc:h2:/Users/elena/coldbox-examples/testApp/database/appDB;MODE=MySQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;", "class":"org.h2.Driver"}, "defaultGrammar":"AutoDiscover@qb"] I had the error posted on May 13 at 16:31 Thanks for your help anyway – elena May 17 '21 at 10:33

Error "Schema "information_schema" not found;" running "migrate up" on H2 database

1 Answers1