[MARMOTTA-675] Kiwi MySQL - allow zero dates advice

• Type: Wish
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 3.4.0
• Component/s: KiWi Triple Store

Environment

Ubuntu 18.04

MySQL Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper

• Assignee: Unassigned
• Reporter: Finlay Birnie
• Created: 01/Feb/19 16:55
• Updated: 01/Feb/19 16:55

Description

Several CREATE TABLE statements failed in the initial MySQL database setup because my MySQL instance had 'NO_ZERO_DATE' and 'NO_ZERO_IN_DATE' set in the sql_mode variable as per the default. I had to find these statements in marmotta_main.log and manually issue them again after unsetting 'NO_ZERO_DATE' and 'NO_ZERO_IN_DATE'. It would have saved time if this requirement was specified in the documentation. The create statements that failed were logged as follows (there was no indication of the failure in the admin UI after I saved my DB config):

15:17:07.694 ERROR o.a.m.k.p.util.ScriptRunner - Error executing: CREATE TABLE triples ( id bigint NOT NULL, subject bigint NOT NULL REFERENCES nodes(id), predicate bigint NOT NULL REFERENCES nodes(id), object bigint NOT NULL REFERENCES nodes(id), context bigint REFERENCES nodes(id), creator bigint REFERENCES nodes(id), inferred boolean DEFAULT false, deleted boolean DEFAULT false, createdAt timestamp NOT NULL DEFAULT now(), deletedAt timestamp, PRIMARY KEY(id) ) CHARACTER SET utf8 COLLATE utf8_bin ENGINE=InnoDB
15:17:07.694 ERROR o.a.m.k.p.util.ScriptRunner - java.sql.SQLSyntaxErrorException: Invalid default value for 'deletedAt'

15:17:08.386 ERROR o.a.m.k.p.util.ScriptRunner - Error executing: CREATE TABLE ldcache_entries (   id           bigint     NOT NULL,   retrieved_at timestamp  NOT NULL,   expires_at   timestamp  NOT NULL,   resource_id  bigint     NOT NULL REFERENCES nodes(id),   update_count int        NOT NULL DEFAULT 0,   triple_count int        NOT NULL DEFAULT 0,   PRIMARY KEY(id) )
15:17:08.386 ERROR o.a.m.k.p.util.ScriptRunner - java.sql.SQLSyntaxErrorException: Invalid default value for 'expires_at'