Wrong database scheme at mysql goose migrations-scripts

[cheGGo]

When I'm trying to create a new database scheme on my mysql database using goose with the mysql backend I'm geeting these error message:

/opt/cfssl/bin/goose -path /opt/cfssl/src/github.com/cloudflare/cfssl/certdb/mysql up goose: migrating db environment 'development', current version: 0, target: 1 2016/07/28 18:08:03 FAIL 001_CreateCertificates.sql (Error 1067: Invalid default value for 'expiry'), quitting migration.

Fixing this in /certdb/mysql/migrations/001_CreateCertificates.sql by myself to another default value, I was able to create both tables.

But when using this db-scheme to generate new certificates I'm running into other problem with this scheme:

{"success":false,"result":null,"errors":[{"code":11000,"message":"Error 1406: Data too long for column 'serial_number' at row 1"}],"messages":[]}

Fixing this to a higher varbinary value in this scheme lead to another error: "Error 1292: Incorrect datetime value: '0000-00-00' for column 'revoked_at' at row 1"

So it seems, that this scheme is completely wrong. Can anyone provide me please the correct mysql database scheme for certdb? And for all others it would be nice fixing this in this github repository.

thanks in advance!

[lziest]

MySQL support is added by the community, and we haven't used MySQL in our env extensively. We test it with Travis-CI mysql service. We believe it is mysql 5.6.x, https://docs.travis-ci.com/user/build-environment-updates/2015-04-09/

What version of mysql are you using? Also, patches are welcomed!

[cheGGo]

Ah, okay. Thanks for your reply. I'm using MySQL 5.7.x, But I think the reported errors are not version related, rather than scheme-related due a wrong set of datatypes. It is running now by modifying the scheme with other datatypes, but this was a quick and dirty hack and I'm sure it can be optimized. When I've collected a bit more example-data in the database I'll optimize my scheme and provide it as patch.

[joemiller]

@cheGGo Would you share your changes to the mysql schema?

[bramford]

FAIL 001_CreateCertificates.sql (Error 1067: Invalid default value for 'expiry'), quitting migration. is an issue in MySQL from 5.7.8 onwards due to the new SQL mode defaults:

The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8

To get the migrations working in MySQL 5.7.8+, you need to disable the NO_ZERO_IN_DATE and NO_ZERO_DATE modes. Add this argument when launching mysqld: --sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Using mysql 5.6 as @lziest mentioned earlier will also work as the default modes don't include NO_ZERO_IN_DATE or NO_ZERO_DATE.

[ppff]

The thing is that mysql doesn't support '0000-00-00 00:00:00' as a date for timestamp, so I set '2000-01-01 00:00:01' and it worked for me. I didn't test it with postgres though. See here : https://dev.mysql.com/doc/refman/5.5/en/datetime.html