[
https://issues.apache.org/jira/browse/NIFIREG-241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bryan Bende resolved NIFIREG-241.
---------------------------------
Resolution: Fixed
Fix Version/s: 0.4.0
> Add support for MySQL database as backend
> -----------------------------------------
>
> Key: NIFIREG-241
> URL: https://issues.apache.org/jira/browse/NIFIREG-241
> Project: NiFi Registry
> Issue Type: Improvement
> Affects Versions: 0.3.0
> Reporter: Michal Klempa
> Assignee: Bryan Bende
> Priority: Minor
> Fix For: 0.4.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> 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:
> {code:java}
> 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{code}
> The mysql-connector-java-5.1.47.jar was placed to
> {code:java}
> /opt/nifi-registry/libs/{code}
>
>
> Steps to reproduce:
> # unpack nifi registry to {{/opt/nifi-registry/nifi-registry-0.3.0}}
> # set the {{nifi-registry.properties}} as above
> # add mysql-connector-java-5.1.47.jar to {{/opt/nifi-registry/libs/}}
> # 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:
> {code:java}
> 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{code}
> Actual behavior MySQL 8:
> {code:java}
> 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.
> {code}
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
