MySQL database setup fails (invalid default timestamp value)

[bunsenmcdubbs]

The V1_INITIAL_VERSION.sql script fails when it tries to create the alfio.event table. Specifically it fails because end_ts's does not have a valid default value.

SQL State  : 42000
Error Code : 1067
Message    : Invalid default value for 'end_ts'
Location   : alfio/db/MYSQL/V1__INITIAL_VERSION.sql (/Users/andrew/dev/hackgt/test/alf.io/build/resources/main/alfio/db/MYSQL/V1__INITIAL_VERSION.sql)
Line       : 53
Statement  : create table event(
    id integer auto_increment primary key not null,
    short_name varchar(128) not null,
    description varchar(2048) not null,
    location varchar(2048) not null,
    latitude varchar(255) not null,
    longitude varchar(255) not null,
    start_ts timestamp  not null,
    end_ts timestamp  not null,
    time_zone varchar(255) not null,
    regular_price_cts integer not null,
    currency varchar(3),
    available_seats integer not null,
    vat_included boolean not null,
    vat decimal(5,2) not null,
    allowed_payment_proxies varchar(2048) not null,
    private_key varchar(2048) not null,
    org_id integer not null
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE utf8_bin

I am running MySQL v5.7.9 on Mac and do not have any custom settings for MySQL (all defaults).

mysql  Ver 14.14 Distrib 5.7.9, for osx10.9 (x86_64) using  EditLine wrapper

I think the MySQL timestamp documentation and their notes on NO_ZERO_DATE might be relevant but I'm not sure... because 5.7.9 seems to have deprecated the NO_ZERO_DATE setting. What is the intended/preferred behavior here?

alfio.log

[syjer]

Hi, @BunsenMcDubbs , that's a nice bug...

Mysql must be doing some quite strange interpretation of it's own rules as I don't understand how we can violate the NO_ZERO_DATE setting if we don't specify a default value...

As you may see, we are testing against mysql 5.5 on travis, so this is the preferred version, I'll try to add newer mysql version in the test matrix before trying to track down the bug. Obviously, any pull requests are accepted :)

[bunsenmcdubbs]

I just did a bit more testing/playing around with the create table script and the error only came up when there was a table with 2 or more timestamp columns. Other people have also encountered this "feature" of MySQL.

TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

^from documentation

This manifests itself in the Invalid default value for 'end_ts' error message because it automatically tries to set the default value of end_ts to 0000-00-00 00:00:00 but NO_ZERO_DATE and NO_ZERO_IN_DATE -- although deprecated as of 5.7.4 -- are in effect and on by default in 5.7.8+. (select @@sql_mode will reveal the current settings).

Because NO_ZERO_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

^ also applies to NO_ZERO_IN_DATE

This means that for <= 5.7.x versions of MySQL, removing or keeping the two flags disabled will keep this issue from appearing. For future (5.7+) versions, stay off of strict mode. They are enabled in the default settings on my version of MySQL (setting them with set @@sql_mode only lasts the duration of that connection). The solution I can think of is to add

set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

to the beginning of the sql script. Is it ok to paint with such a broad brush? (I retrieved all the active default settings and then removed only the two relevant modes). NOTE: I did this and it works (finishes the V1 script but now I'm running into (and fixing) syntax issues in other setup scripts).

Out of curiosity @syjer: can you run select @@sql_mode on the Travis CI instance and see what its settings are? Presumably these two flags are not set but I'm curious as to what the other settings are.

[syjer]

@BunsenMcDubbs ouch, mysql is really quirky :/

I'm wondering if simply adding a single timestamp column and then making an alter table statement for adding the second one would do the trick?

About select @@sql_mode travis say the following:

0.01s$ mysql -u root -e 'select VERSION()'
+-----------------------------+
| VERSION()                   |
+-----------------------------+
| 5.5.41-0ubuntu0.12.04.1-log |
+-----------------------------+
before_script.4
0.01s$ mysql -u root -e 'select @@sql_mode'
+------------+
| @@sql_mode |
+------------+
|            |
+------------+

[syjer]

finally I was able to add mysql 5.6 to the test matrix!

the output is:

$ mysql -u root -e 'select VERSION()'
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.6.30-0ubuntu0.14.04.1 |
+-------------------------+
before_script.4
0.01s$ mysql -u root -e 'select @@sql_mode'
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

[bunsenmcdubbs]

Possible fix

@syjer Creating the table with one timestamp column and then altering it does not work (same error). If there aren't any settings that we need especially, we can just have

set @@sql_mode = ''

at the beginning of the migration script to mitigate this

Other SQL issues

I'm currently running into other issues with the migration script

SQL State  : HY000
Error Code : 1553
Message    : Cannot drop index 'unique_ticket_field_configuration': needed in a foreign key constraint
Location   : alfio/db/MYSQL/V15_1.8.2__ALTER_TICKET_FIELD_CONF.sql (/Users/andrew/dev/hackgt/alf.io/build/resources/main/alfio/db/MYSQL/V15_1.8.2__ALTER_TICKET_FIELD_CONF.sql)
Line       : 22
Statement  : alter table ticket_field_configuration drop index unique_ticket_field_configuration

I assume this is an issue with MySQL v5.7 again? (I'm looking into it more right now)

[cbellone]

@BunsenMcDubbs your working copy (or your fork) is outdated, see https://github.com/exteso/alf.io/commit/e9e2878af7e725eddab26d606cdc9c776526d9d1

V15_1.8.2__ALTER_TICKET_FIELD_CONF.sql should run now...

[bunsenmcdubbs]

Oh ok thanks! I'll check it out tonight (~10 hours from now). (As long as I don't run into anymore issues) I'll open a pull request to deal with the initial NO_ZERO_DATE issue and make that small fix.

On Tue, Jun 28, 2016 at 1:57 AM, Celestino Bellone <notifications@github.com

wrote:

@BunsenMcDubbs https://github.com/BunsenMcDubbs your working copy (or your fork) is outdated, see e9e2878 https://github.com/exteso/alf.io/commit/e9e2878af7e725eddab26d606cdc9c776526d9d1

this issue should be solved now

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/exteso/alf.io/issues/131#issuecomment-228955724, or mute the thread https://github.com/notifications/unsubscribe/AAqric7BK21NWJqoPjjYUsoLqbqk8hzWks5qQLfggaJpZM4I-q5s .

Best Wishes, Andrew

[syjer]

merged, thanks!