Cannot run LAS due to JdbcSQLSyntaxErrorException

[jessborgesp]

Hello, I have tried multiple times to install LAS9, I have tried it in different machines and different versions of Java/Python/PyFerret, however, I am not being able to make it work. I followed the tutorial from the video strictly, just adapting it to my own paths/environments, but both las and las#thredds are not activated by Tomcat. They show on my admin page, but the server pages are 404. This is the error currently showing on my prodDb.trace.db file:

2022-03-08 22:50:33 jdbc[12]: exception
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE DATA_QUALIFIER (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, VERSION BIGINT NOT NULL, TIME_COUNT VARCHAR(255), DISTINCT[] BOOLEAN NOT NULL, TYPE VARCHAR(255), TIME_UNITS VARCHAR(255), PRIMARY KEY (ID))"; expected "identifier"; SQL statement:
create table data_qualifier (id bigint generated by default as identity, version bigint not null, time_count varchar(255), distinct boolean not null, type varchar(255), time_units varchar(255), primary key (id)) [42001-200]
2022-03-08 22:50:33 jdbc[12]: exception
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE SEARCH_REQUEST (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, VERSION BIGINT NOT NULL, QUERY VARCHAR(255) NOT NULL, OFFSET[] INTEGER NOT NULL, COUNT INTEGER NOT NULL, PRIMARY KEY (ID))"; expected "identifier"; SQL statement:
create table search_request (id bigint generated by default as identity, version bigint not null, query varchar(255) not null, offset integer not null, count integer not null, primary key (id)) [42001-200]
2022-03-08 22:50:34 jdbc[12]: exception
org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "DATA_QUALIFIER" not found; SQL statement:
alter table lasrequest_data_qualifier add constraint FK76vyh5i7os9pbdrlkquilncx0 foreign key (data_qualifier_id) references data_qualifier [42102-200]

It seems to be a database query issue but since I am not familiar with SQL queries I would like to know what changes should I make to solve the SQL syntax errors? Any help would be appreciated, thank you.

[noaaroland]

What is the setting for your datasource?

dataSource:
            pooled: true
            jmxExport: true
            driverClassName: org.h2.Driver
            username: sa
            password: ''
            dbCreate: update
            url: jdbc:h2:/home/users/rhs/content4/las/db/prodDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE

Particularly the dbCreate.

Can remove your prodDB* files and start it once with:

dbCreate: create-drop

If it runs successfully after that you can go back to:

dbCreate: update

[jessborgesp]

Thank you for your reply, I just changed from 'update' to 'create-drop', and this is how it is now:

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: org.h2.Driver
    username: j
    password: j
    dbCreate: create-drop
    url: jdbc:h2:/home/jessbp/tds/las/content/las/db/prodDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE

I stopped and started tomcat again and the same errors are showing at the newly created prodDb.trace.db.

    2022-03-11 10:44:42 jdbc[12]: exception
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE DATA_QUALIFIER (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, VERSION BIGINT NOT NULL, TIME_COUNT VARCHAR(255), DISTINCT[*] BOOLEAN NOT NULL, TYPE VARCHAR(255), TIME_UNITS VARCHAR(255), PRIMARY KEY (ID))"; expected "identifier"; SQL statement:
create table data_qualifier (id bigint generated by default as identity, version bigint not null, time_count varchar(255), distinct boolean not null, type varchar(255), time_units varchar(255), primary key (id)) [42001-200]
2022-03-11 10:44:42 jdbc[12]: exception
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE SEARCH_REQUEST (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, VERSION BIGINT NOT NULL, QUERY VARCHAR(255) NOT NULL, OFFSET[*] INTEGER NOT NULL, COUNT INTEGER NOT NULL, PRIMARY KEY (ID))"; expected "identifier"; SQL statement:
create table search_request (id bigint generated by default as identity, version bigint not null, query varchar(255) not null, offset integer not null, count integer not null, primary key (id)) [42001-200]
2022-03-11 10:44:42 jdbc[12]: exception
org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "DATA_QUALIFIER" not found; SQL statement:
alter table lasrequest_data_qualifier add constraint FK76vyh5i7os9pbdrlkquilncx0 foreign key (data_qualifier_id) references data_qualifier [42102-200]

These are the messages I am seeing at my catalina.out regarding the deployment of las.war and las#thredds.war:

11-Mar-2022 10:44:31.154 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployWAR Deploying web application archive [/home/jessbp/tds/tomcat/webapps/las.war]
11-Mar-2022 10:44:31.693 WARNING [localhost-startStop-1] org.apache.tomcat.util.descriptor.web.WebXml.setVersion Unknown version string [4.0]. Default version will be used.
11-Mar-2022 10:44:31.702 WARNING [localhost-startStop-1] org.apache.tomcat.util.descriptor.web.WebXml.setVersion Unknown version string [4.0]. Default version will be used.
11-Mar-2022 10:44:34.662 INFO [localhost-startStop-1] org.apache.jasper.servlet.TldScanner.scanJars At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
10:44:34,698 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Could NOT find resource [logback-test.xml]
10:44:34,698 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Could NOT find resource [logback.groovy]
10:44:34,698 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Found resource [logback.xml] at [file:/home/jessbp/tds/tomcat/webapps/las/WEB-INF/classes/logback.xml]
10:44:34,724 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - debug attribute not set
10:44:34,725 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - About to instantiate appender of type [ch.qos.logback.core.ConsoleAppender]
10:44:34,730 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - Naming appender as [CONSOLE]
10:44:34,755 |-WARN in ch.qos.logback.core.ConsoleAppender[CONSOLE] - This appender no longer admits a layout as a sub-component, set an encoder instead.
10:44:34,755 |-WARN in ch.qos.logback.core.ConsoleAppender[CONSOLE] - To ensure compatibility, wrapping your layout in LayoutWrappingEncoder.
10:44:34,755 |-WARN in ch.qos.logback.core.ConsoleAppender[CONSOLE] - See also http://logback.qos.ch/codes.html#layoutInsteadOfEncoder for details
10:44:34,756 |-INFO in ch.qos.logback.classic.joran.action.RootLoggerAction - Setting level of ROOT logger to ERROR
10:44:34,756 |-INFO in ch.qos.logback.core.joran.action.AppenderRefAction - Attaching appender named [CONSOLE] to Logger[ROOT]
10:44:34,756 |-INFO in ch.qos.logback.classic.joran.action.LoggerAction - Setting level of logger [pmel.sdig.las] to DEBUG
10:44:34,756 |-INFO in ch.qos.logback.classic.joran.action.LoggerAction - Setting additivity of logger [pmel.sdig.las] to false
10:44:34,756 |-INFO in ch.qos.logback.core.joran.action.AppenderRefAction - Attaching appender named [CONSOLE] to Logger[pmel.sdig.las]
10:44:34,757 |-INFO in ch.qos.logback.classic.joran.action.LoggerAction - Setting level of logger [grails.app.Bootstrap] to DEBUG
10:44:34,757 |-INFO in ch.qos.logback.classic.joran.action.LoggerAction - Setting additivity of logger [grails.app.Bootstrap] to false
10:44:34,757 |-INFO in ch.qos.logback.core.joran.action.AppenderRefAction - Attaching appender named [CONSOLE] to Logger[grails.app.Bootstrap]
10:44:34,757 |-INFO in ch.qos.logback.classic.joran.action.LoggerAction - Setting level of logger [grails.plugins.elasticsearch] to DEBUG
10:44:34,757 |-INFO in ch.qos.logback.classic.joran.action.LoggerAction - Setting additivity of logger [grails.plugins.elasticsearch] to false
10:44:34,757 |-INFO in ch.qos.logback.core.joran.action.AppenderRefAction - Attaching appender named [CONSOLE] to Logger[grails.plugins.elasticsearch]
10:44:34,757 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - End of configuration.
10:44:34,757 |-INFO in ch.qos.logback.classic.joran.JoranConfigurator@6a844369 - Registering current configuration as safe fallback point
10:44:35.994 [localhost-startStop-1] INFO  pmel.sdig.las.ApplicationLoader - The following profiles are active: production
10:44:44.788 [localhost-startStop-1] INFO  pmel.sdig.las.ReadMetadataJobService - Starting updates on unprocessed THREDDS catalogs
10:44:44.907 [localhost-startStop-1] DEBUG pmel.sdig.las.BootStrap - Starting the init bootstrap closure...
10:44:44.908 [localhost-startStop-1] INFO  pmel.sdig.las.InitializationService - This is las verison 9.7.2
10:44:44.909 [localhost-startStop-1] DEBUG pmel.sdig.las.InitializationService - Setting up Ferret environment from the runtime environment.
10:44:46.332 [localhost-startStop-1] DEBUG pmel.sdig.las.InitializationService - Data set count = 0
10:44:46.332 [localhost-startStop-1] DEBUG pmel.sdig.las.InitializationService - No data sets found. Setting up default data sets.  Entered method...
10:44:46.553 [localhost-startStop-1] INFO  pmel.sdig.las.ApplicationLoader - Started ApplicationLoader in 11.402 seconds (JVM running for 16.056)
11-Mar-2022 10:44:46.599 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployWAR Deployment of web application archive [/home/jessbp/tds/tomcat/webapps/las.war] has finished in [15,445] ms

11-Mar-2022 10:44:46.603 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployWAR Deploying web application archive [/home/jessbp/tds/tomcat/webapps/las#thredds.war]
11-Mar-2022 10:44:48.584 INFO [localhost-startStop-1] org.apache.jasper.servlet.TldScanner.scanJars At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by dap4.servlet.DapController (file:/home/jessbp/tds/tomcat/webapps/las%23thredds/WEB-INF/lib/d4servletshared-4.6.19-SNAPSHOT.jar) to field java.nio.charset.Charset.defaultCharset
WARNING: Please consider reporting this to the maintainers of dap4.servlet.DapController
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
initCatalog /home/jessbp/tds/tomcat/content/ftds/catalog.xml
11-Mar-2022 10:44:51.857 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployWAR Deployment of web application archive [/home/jessbp/tds/tomcat/webapps/las#thredds.war] has finished in [5,254] ms

Are there any constraints when it comes to versions of Java/Tomcat/Python/PyFerret or any library for installing LAS9?

[noaaroland]

Did you remove the old prodDB files after you changed to drop-create and before you restarted tomcat?

[jessborgesp]

Yes, and just to make sure I did the process again as below:

jessbp@jessbp:~/tds/las/content/las/db$ ls
prodDb.mv.db  prodDb.trace.db
jessbp@jessbp:~/tds/las/content/las/db$ rm prodDb.mv.db
jessbp@jessbp:~/tds/las/content/las/db$ rm prodDb.trace.db
jessbp@jessbp:~/tds$ cd tomcat/bin/
jessbp@jessbp:~/tds/tomcat/bin$ ./startup.sh

Using the following dataSource:

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: org.h2.Driver
    username: j
    password: j
    dbCreate: create-drop
    url: jdbc:h2:/home/jessbp/tds/las/content/las/db/prodDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE

The same output is showing at my prodDB file, unfortunately there were no changes here.

[noaaroland]

Sorry for the delay and sorry that this issue came up because in the end it was my fault. I made a mistake with the release files. Some of the configuration moved to the application.yml file in recent Grails upgrade. There are a few lines of configuration missing in the application.yml file that was originally released with 9.7.2. If you add these lines to the top of the application.yml file:

server:
    servlet:
        context-path: /las
grails:
    resources:
        pattern: '/**'

then your installation should work. N.B. The errors in the db trace are spurious and will not affect the running of the application. They will likely still be there after this change.

I also updated the application.yml file in release 9.7.2 to include the necessary changes.