Error executing cmn-data-svc-ddl-dml-mysql-2.2-sql during AIO

Question

Running the script acumos_k8s_prep.sh throws an error during database initialization:

+ sedi '1s/^/use acumos_cds;\n/' cmn-data-svc-ddl-dml-mysql-2.2.sql
+ sed --version
+ sed -i -- '1s/^/use acumos_cds;\n/' cmn-data-svc-ddl-dml-mysql-2.2.sql
+ mysql -h 172.16.83.xxx -P 30001 --user=xxx --password=xxx
ERROR 1364 (HY000) at line 587: Field 'MODIFIED_DATE' doesn't have a default value
++ fail
++ set +x

in line 587 an entry is inserted

INSERT INTO C_ROLE (ROLE_ID, NAME, ACTIVE_YN, CREATED_DATE) VALUES ('12345678-abcd-90ab-cdef-1234567890ab', 'MLP System User', 'Y', CURRENT_TIMESTAMP());

into table C_ROLE

CREATE TABLE C_ROLE (
  ROLE_ID CHAR(36) NOT NULL PRIMARY KEY,
  NAME VARCHAR(100) NOT NULL,
  ACTIVE_YN CHAR(1) DEFAULT 'Y' NOT NULL,
  CREATED_DATE TIMESTAMP NOT NULL DEFAULT 0,
  MODIFIED_DATE TIMESTAMP NOT NULL,
  UNIQUE INDEX C_ROLE_C_NAME (NAME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

which indeed has no default for MODIFIED_DATE.

Clearly a miss, but I have tested this hundreds of times without a failure, and the role-insert DML lines have not changed in months! I can only guess a new behavior of mariadb finally revealed this fault. What version of mariadb was installed by the AIO in your K8S? I opened https://jira.acumos.org/browse/ACUMOS-2891 — chrisinmtown, May 13 '19 at 20:06
The Server version is: 10.3.14-MariaDB Source distribution: I will also update the Jira ticket. Thanks. — mwelss-fhg, May 14 '19 at 06:52

score 0 · Accepted Answer · answered May 13 '19 at 21:09

This may be caused by a change in the MariaDB version 10.2+ docker image, possibly setting strict for SQL mode. My database didn't show this failure, it has this config:

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

I submitted a patch to the DML script so it supplies NOW() for every MODIFIED_DATE column. After the merge, you can either get the latest script directly from https://gerrit.acumos.org, or wait for an update to the A.I.O.

HTH

my sql_mode is: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION — mwelss-fhg, May 14 '19 at 06:54

Error executing cmn-data-svc-ddl-dml-mysql-2.2-sql during AIO

1 Answers1