AWS Cloudera Director 2.2 failed to start with external MySQL 5.7.17 DB

[#1]

I modified application.properties as follows to support MySQL:

lp.database.type: mysql
lp.database.username: clouderadirector
lp.database.password: TopSecret
lp.database.host: 10.3.1.197
lp.database.port: 3306
lp.database.name: clouderadirectordb (brand new database)

After the modification, Cloudera Director failed to start due to flyway exception on migration: first it said no migration necessary, but later indicated a failed migration.

2017-01-24 17:18:06] INFO [main] - o.f.c.i.dbsupport.DbSupportFactory: Database: jdbc:mysql://10.3.1.197:3306/clouderadirectordb (MyS
QL 5.7)
[2017-01-24 17:18:06] INFO [main] - o.f.core.internal.command.DbValidate: Validated 5 migrations (execution time 00:00.030s)
[2017-01-24 17:18:06] INFO [main] - o.f.core.internal.command.DbMigrate: Current version of schema `clouderadirectordb`: 3.2.0.0.1
[2017-01-24 17:18:06] INFO [main] - o.f.core.internal.command.DbMigrate: Schema `clouderadirectordb` is up to date. No migration neces
sary.
[2017-01-24 17:18:06] INFO [main] - o.f.c.i.dbsupport.DbSupportFactory: Database: jdbc:mysql://10.3.1.197:3306/clouderadirectordb (MyS
QL 5.7)
[2017-01-24 17:18:06] INFO [main] - c.c.l.p.u.ReferentialIntegrityService: Disabling referential integrity for MYSQL
[2017-01-24 17:18:06] INFO [main] - c.c.l.p.util.PipelinePurgeService: Purging PIPELINES
[2017-01-24 17:18:06] INFO [main] - c.c.l.p.util.PipelinePurgeService: Purging PIPELINE_JOBS
[2017-01-24 17:18:06] INFO [main] - c.c.l.p.util.PipelinePurgeService: Purging PIPELINE_VALUES
[2017-01-24 17:18:06] INFO [main] - c.c.l.p.u.ReferentialIntegrityService: Enabling referential integrity for MYSQL
[2017-01-24 17:18:06] INFO [main] - o.f.core.internal.command.DbValidate: Validated 15 migrations (execution time 00:00.057s)
[2017-01-24 17:18:06] INFO [main] - o.f.core.internal.command.DbMigrate: Current version of schema `clouderadirectordb`: 3.2.0.0.1
[2017-01-24 17:18:06] WARN [main] - o.s.b.c.e.AnnotationConfigEmbeddedWebApplicationContext: Exception encountered during context init
ialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [com/cloudera/launchpad/config/FlywayMigrationsConfig.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Schema `clouderadirectordb` contains a failed migration to version 3.2.0.0.1 !
[2017-01-24 17:18:06] WARN [main] - c.c.l.c.security.CipherSchemeFactory: Allowing cipher scheme to be set again, prior scheme is com.
cloudera.launchpad.common.security.TripleDESCipher@5215cd9a
[2017-01-24 17:18:06] INFO [main] - o.e.j.server.handler.ContextHandler: Stopped o.s.b.c.e.j.JettyEmbeddedWebAppContext@bc57b40{/,file
:/tmp/jetty-docbase.8174602001888934659.7189/,UNAVAILABLE}
[2017-01-24 17:18:06] ERROR [main] - o.s.boot.SpringApplication: Application startup failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [com/cloudera/launchpad/config/FlywayMigrationsConfig.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Schema `clouderadirectordb` contains a failed migration to version 3.2.0.0.1 !

[2017-01-24 17:18:06] WARN [main] - c.c.l.c.security.CipherSchemeFactory: Allowing cipher scheme to be set again, prior scheme is com.
cloudera.launchpad.common.security.TripleDESCipher@5215cd9a
[2017-01-24 17:18:06] INFO [main] - o.e.j.server.handler.ContextHandler: Stopped o.s.b.c.e.j.JettyEmbeddedWebAppContext@bc57b40{/,file
:/tmp/jetty-docbase.8174602001888934659.7189/,UNAVAILABLE}
[2017-01-24 17:18:06] ERROR [main] - o.s.boot.SpringApplication: Application startup failed

[#2]

Tried latest 2.3, failed at the same spot.

[#3]

Here is application.log for v2.3.

[2017-02-08 12:22:06.301 -0500] INFO  [main] - application: Initializing Spring embedded WebApplicationContext
...skipping...
        at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)
        at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207)
        at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
        at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
        at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:1059)
        at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1418)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:1006)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1713)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1650)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1579)
        ... 25 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Invalid default value for 'CREATED_AT'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.Util.getInstance(Util.java:408)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)

[#4]

Hi zhuw.bigdata,

We'll take a look. Is this a new installation of Cloudera Director, or an upgrade from a previous version? Also, is the database running in RDS or one you've installed and configured yourself?

[#5]

This is a brand new installation. MySQL 5.7 is installed in EC2 instance by myself, not RDS.

[#6]

I tried both v2.2 and v2.3. Both don't work with MySQL 5.7.

[#7]

Loosen up your sql mode. Run this prior to installation:

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Mysql server version 5.7.17

You obviously need to fix the flyway error first if you don't recreate the db.