[
https://issues.apache.org/jira/browse/NIFIREG-241?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16801717#comment-16801717
]
Bryan Bende commented on NIFIREG-241:
-------------------------------------
Hi [~michal.klempa] , Currently we have a single schema that works across H2
and Postgres. To support MySQL and/or MariaDB we would likely to have organize
the DB migrations by DB vendor and have separate copies per vendor that can be
tailored to the specific limitations and syntax of the given vendor. Not saying
we can't do this, but it does become a bit of a maintenance burden to maintain
all these different schemas.
> 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
> Priority: Minor
>
> 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)
