Uploaded image for project: 'NiFi Registry'
  1. NiFi Registry
  2. NIFIREG-241

Add support for MySQL database as backend

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 0.3.0
    • Fix Version/s: 0.4.0
    • Labels:
      None

      Description

      Similar to https://issues.apache.org/jira/browse/NIFIREG-240

      Although this issue says "it should work" : https://issues.apache.org/jira/browse/NIFIREG-173

      only H2 and Postgres works for me.

       

      I have tried to use MySQL 5.7 and MySQL 8 with NiFi Registry, with no luck. Although there should be a way to enable MySQL as a backend service instead of H2.

      Environment:

      NiFi Registry 0.3.0 (inside Docker, but this does not really affect the output)

      mysql-connector-java-5.1.47.jar

      Configuration:

      nifi-registry.properties:

      nifi.registry.db.url=jdbc:mysql://database:3306/db
      nifi.registry.db.driver.class=com.mysql.jdbc.Driver
      nifi.registry.db.driver.directory=/opt/nifi-registry/libs/
      nifi.registry.db.username=root
      nifi.registry.db.password=myPassword

      The mysql-connector-java-5.1.47.jar was placed to

      /opt/nifi-registry/libs/

       

       

      Steps to reproduce:

      1. unpack nifi registry to /opt/nifi-registry/nifi-registry-0.3.0
      2. set the nifi-registry.properties as above
      3. add mysql-connector-java-5.1.47.jar to /opt/nifi-registry/libs/
      4. start

      Or alternatively, use the Docker image I was developing and during which I found this issue.

      The docker-compose file is https://github.com/michalklempa/docker-nifi-registry/blob/develop/docker-compose.mysql.yml

      Expected behavior: working Registry

      Actual behavior MySQL 5.7:

      nifi-registry_1  | Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException:
      nifi-registry_1  | Migration V2__Initial.sql failed
      nifi-registry_1  | --------------------------------
      nifi-registry_1  | SQL State  : 42000
      nifi-registry_1  | Error Code : 1067
      nifi-registry_1  | Message    : Invalid default value for 'MODIFIED'
      nifi-registry_1  | Location   : db/migration/V2__Initial.sql (/opt/nifi-registry/nifi-registry-0.3.0/file:/opt/nifi-registry/nifi-registry-0.3.0/work/jetty/nifi-registry-web-api-0.3.0.war/webapp/WEB-INF/lib/nifi-registry-framework-0.3.0.jar!/db/migration/V2__Initial.sql)
      nifi-registry_1  | Line       : 26
      nifi-registry_1  | Statement  : CREATE TABLE BUCKET_ITEM (
      nifi-registry_1  |     ID VARCHAR(50) NOT NULL,
      nifi-registry_1  |     NAME VARCHAR(1000) NOT NULL,
      nifi-registry_1  |     DESCRIPTION TEXT,
      nifi-registry_1  |     CREATED TIMESTAMP NOT NULL,
      nifi-registry_1  |     MODIFIED TIMESTAMP NOT NULL,
      nifi-registry_1  |     ITEM_TYPE VARCHAR(50) NOT NULL,
      nifi-registry_1  |     BUCKET_ID VARCHAR(50) NOT NULL,
      nifi-registry_1  |     CONSTRAINT PK__BUCKET_ITEM_ID PRIMARY KEY (ID),
      nifi-registry_1  |     CONSTRAINT FK__BUCKET_ITEM_BUCKET_ID FOREIGN KEY (BUCKET_ID) REFERENCES BUCKET(ID)
      nifi-registry_1  | )
      nifi-registry_1  |
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:345) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate.access$900(DbMigrate.java:53) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate$4.call(DbMigrate.java:276) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:75) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:53) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:210) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:146) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.dbsupport.mysql.MySQLNamedLockTemplate.execute(MySQLNamedLockTemplate.java:60) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.dbsupport.mysql.MySQLDbSupport.lock(MySQLDbSupport.java:129) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.Flyway$1.execute(Flyway.java:971) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.Flyway.execute(Flyway.java:1464) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.Flyway.migrate(Flyway.java:971) ~[na:na]
      nifi-registry_1  |     at org.apache.nifi.registry.db.CustomFlywayMigrationStrategy.migrate(CustomFlywayMigrationStrategy.java:88) ~[na:na]
      nifi-registry_1  |     at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:63) ~[na:na]
      nifi-registry_1  |     at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1758) ~[na:na]
      nifi-registry_1  |     at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1695) ~[na:na]
      nifi-registry_1  |     ... 98 common frames omitted
      nifi-registry_1  | Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Invalid default value for 'MODIFIED'
      nifi-registry_1  |     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_191]
      nifi-registry_1  |     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_191]
      nifi-registry_1  |     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_191]
      nifi-registry_1  |     at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_191]
      nifi-registry_1  |     at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) ~[na:na]
      nifi-registry_1  |     at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) ~[na:na]
      nifi-registry_1  |     at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[na:na]
      nifi-registry_1  |     at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:267) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:142) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:76) ~[na:na]
      nifi-registry_1  |     at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:343) ~[na:na]
      nifi-registry_1  |     ... 117 common frames omitted

      Actual behavior MySQL 8:

      springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'accessPolicyResource' defined in file [/opt/nifi-registry/nifi-registry-0.3.0/work/jetty/nifi-registry-web-api-0.3.0.war/webapp/WEB-INF/classes/org/apache/nifi/registry/web/api/AccessPolicyResource.class]: Unsatisfied dependency expressed through constructor parameter 1; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'authorizationService' defined in URL [jar:file:/opt/nifi-registry/nifi-registry-0.3.0/work/jetty/nifi-registry-web-api-0.3.0.war/webapp/WEB-INF/lib/nifi-registry-framework-0.3.0.jar!/org/apache/nifi/registry/service/AuthorizationService.class]: Unsatisfied dependency expressed through constructor parameter 2; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'registryService' defined in URL [jar:file:/opt/nifi-registry/nifi-registry-0.3.0/work/jetty/nifi-registry-web-api-0.3.0.war/webapp/WEB-INF/lib/nifi-registry-framework-0.3.0.jar!/org/apache/nifi/registry/service/RegistryService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'databaseMetadataService' defined in URL [jar:file:/opt/nifi-registry/nifi-registry-0.3.0/work/jetty/nifi-registry-web-api-0.3.0.war/webapp/WEB-INF/lib/nifi-registry-framework-0.3.0.jar!/org/apache/nifi/registry/db/DatabaseMetadataService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException:
      nifi-registry_1  | Migration V2__Initial.sql failed
      nifi-registry_1  | --------------------------------
      nifi-registry_1  | SQL State  : 42000
      nifi-registry_1  | Error Code : 1071
      nifi-registry_1  | Message    : Specified key was too long; max key length is 3072 bytes
      nifi-registry_1  | Location   : db/migration/V2__Initial.sql (/opt/nifi-registry/nifi-registry-0.3.0/file:/opt/nifi-registry/nifi-registry-0.3.0/work/jetty/nifi-registry-web-api-0.3.0.war/webapp/WEB-INF/lib/nifi-registry-framework-0.3.0.jar!/db/migration/V2__Initial.sql)
      nifi-registry_1  | Line       : 17
      nifi-registry_1  | Statement  : CREATE TABLE BUCKET (
      nifi-registry_1  |     ID VARCHAR(50) NOT NULL,
      nifi-registry_1  |     NAME VARCHAR(1000) NOT NULL,
      nifi-registry_1  |     DESCRIPTION TEXT,
      nifi-registry_1  |     CREATED TIMESTAMP NOT NULL,
      nifi-registry_1  |     CONSTRAINT PK__BUCKET_ID PRIMARY KEY (ID),
      nifi-registry_1  |     CONSTRAINT UNIQUE__BUCKET_NAME UNIQUE (NAME)
      nifi-registry_1  | )
      nifi-registry_1  |
      nifi-registry_1  | 2019-03-24 18:36:12,867 INFO [main] o.a.nifi.registry.event.EventService Shutting down event consumer...
      nifi-registry_1  | 2019-03-24 18:36:12,867 INFO [main] o.a.nifi.registry.event.EventService Event consumer shutdown!
      nifi-registry_1  | 2019-03-24 18:36:12,867 WARN [pool-2-thread-1] o.a.nifi.registry.event.EventService Interrupted while polling event queue
      nifi-registry_1  | 2019-03-24 18:36:12,868 INFO [main] com.zaxxer.hikari.HikariDataSource HikariPool-1 - Shutdown initiated...
      nifi-registry_1  | 2019-03-24 18:36:12,877 INFO [main] com.zaxxer.hikari.HikariDataSource HikariPool-1 - Shutdown completed.
      nifi-registry_1  | 2019-03-24 18:36:12,897 INFO [main] o.s.b.a.l.ConditionEvaluationReportLoggingListener
      nifi-registry_1  |
      nifi-registry_1  | Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
      

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bbende Bryan Bende
                Reporter:
                michal.klempa Michal Klempa
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 0.5h
                  0.5h