[MySQL] CREATE TABLE with non-default CHARSET/COLLATE appear to be ignored

[ssherwood]

Running Voyager to migrate a set of tables from MySQL (MariaDB 10.5) and have identified a possible issue.

Example:

CREATE TABLE `foo_abba` (
  `id` bigint(20) NOT NULL DEFAULT 0,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

Results in:

CREATE TABLE foo_abba (
    id bigint NOT NULL DEFAULT 0,
    status varchar(255),
    PRIMARY KEY (id)
) ;

The non-default CHARSET/COLLATE appears to have been ignored.

[ssherwood]

It isn't entirely clear to me what the correct behavior should be. From my initial research, Postgres does not appear to support COLLATE at a table level, only database-wide or per column.

It may be required to consider any text/varchar columns in the resulting CREATE:

CREATE TABLE foo_abba (
    id bigint NOT NULL DEFAULT 0,
    status varchar(255) COLLATE "sv-SE-x-icu",
    PRIMARY KEY (id)
) ;