[jira] [Resolved] (NIFI-11055) [H2] access to nifi-flow-audit

["David Handermann (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/NIFI-11055?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David Handermann resolved NIFI-11055.
-------------------------------------
      Assignee: David Handermann
    Resolution: Information Provided

The H2 database files are opened and locked for NiFi internal usage and are not intended for reading through external methods, such as DBCPConnectionPool. The QueryNiFiReportingTask supports using SQL to read and export Flow Configuration history, so that may provide a better solution.

> [H2] access to nifi-flow-audit
> ------------------------------
>
>                 Key: NIFI-11055
>                 URL: https://issues.apache.org/jira/browse/NIFI-11055
>             Project: Apache NiFi
>          Issue Type: Bug
>          Components: NiFi Stateless
>    Affects Versions: 1.18.0, 1.19.0, 1.19.1
>         Environment: software platform
>            Reporter: Piotr
>            Assignee: David Handermann
>            Priority: Major
>
> After Nifi upgrade from version 1.16.3 to 1.18 and 1.19 we are not able to read embeded h2 database nifi-flow-audit. 
> In our solution we used embeded h2 in Automatic mix mode (AUTO_SERVER=TRUE in nifi.properties)
>  * 
>  ## H2 Settings*
> *nifi.database.directory=./database_repository*
> *nifi.h2.url.append=;LOCK_TIMEOUT=25000;WRITE_DELAY=0;AUTO_SERVER=TRUE*
> But now we are receiving below error and nifi don't want start:
> *FactoryBean threw exception on object creation; nested exception is org.h2.jdbc.JdbcSQLFeatureNotSupportedException: Feature not supported: "AUTO_SERVER=TRUE && DB_CLOSE_ON_EXIT=FALSE" [50100-214]*
> When we set AUTO_SERVER=FALSE, ** nifi works correctly, but we are not able to read data from database, and we are receiving error thath file is locked:
> QueryDatabaseTableRecord[id=*] Processing failed: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Database may be already in use: "/opt/nifi/nifi-1.19.1/database_repository/nifi-flow-audit.mv.db". Possible solutions: close all other connection(s); use the server mode [90020-214])
>  - Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Database may be already in use: "/opt/nifi/nifi-1.19.1/database_repository/nifi-flow-audit.mv.db". Possible solutions: close all other connection(s); use the server mode [90020-214])
>  - Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Database may be already in use: "/opt/nifi/nifi-1.19.1/database_repository/nifi-flow-audit.mv.db". Possible solutions: close all other connection(s); use the server mode [90020-214]
>  - Caused by: org.h2.mvstore.MVStoreException: The file is locked: /opt/nifi/nifi-1.19.1/database_repository/nifi-flow-audit.mv.db [2.1.214/7]
>  - Caused by: java.nio.channels.OverlappingFileLockException
> WORKAROUND:
> - copy h2 database to separate file -  by python sciprt which is run in processor execute process 
> - query executed in QueryDatabaseTableRecord
> - DBCPConnectionPool 1.20.0 - IMPORTANT - set Max Connection Lifetime e.g. 5 sec
> SOURCE of the issue:
> DB_CLOSE_ON_EXIT=FALSE properties is set in H2EmbeddedDatabaseConfigurer class inside library spring-jdbc
> path to class on git:
> [https://github.com/spring-projects/spring-framework/blob/main/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/embedded/H2EmbeddedDatabaseConfigurer.java]
> issue reported:
> [https://github.com/spring-projects/spring-framework/issues/30024]
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)