"Install/Update database schematic" table creation error.

[writexavier]

When choosing option 2 from init.pl, the following attempt to create a non table is performed...here is a section of the output:

Table release_annotation
Table release_gid_redirect
Table over_art_presence AS ENUM ('absent', 'present', 'darkened');
Table release_meta
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cover_art_presence NOT NULL DEFAULT 'absent'' at line 1 at backend/mysql.pl line 446,  line 1253.
Table release_coverart
Table release_label
Table release_packaging

At first glance it looks like the script has a problem parsing the ENUM data type as a result of the ");"

[writexavier]

At second glance the issue is in replication/CreateTables.sql.

Unlike Postgres, MySQL does not support CREATE TYPE.

CREATE TYPE cover_art_presence AS ENUM ('absent', 'present', 'darkened');

CREATE TABLE release_meta
(
    id                  INTEGER NOT NULL, -- PK, references release.id CASCADE
    date_added          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    info_url            VARCHAR(255),
    amazon_asin         VARCHAR(10),
    amazon_store        VARCHAR(20),
    cover_art_presence  cover_art_presence NOT NULL DEFAULT 'absent'
);

I changed to (though it will be overriden on next schema download)

# CREATE TYPE cover_art_presence AS ENUM ('absent', 'present', 'darkened');

CREATE TABLE release_meta
(
    id                  INTEGER NOT NULL, -- PK, references release.id CASCADE
    date_added          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    info_url            VARCHAR(255),
    amazon_asin         VARCHAR(10),
    amazon_store        VARCHAR(20),
    cover_art_presence  ENUM ('absent', 'present', 'darkened') NOT NULL
);

With mysql if NOT NULL is set for an ENUM column then the first ENUM value is automatically the default.