CODEWITHSUNDEEP
mariadb
Sebastian Lore
Sebastian Lore

Reputation: 469

MariaDB constraint is incorrectly formed although columns are of the same type

I've got a problem while creating (altering) foreign key.

I have two tables in my DB (created via flyway migrations):

connector (migration)

create table if not exists connector
(
    id char(36) not null,
    # other fields omitted
    primary key (id)
) DEFAULT CHARACTER SET 'utf8mb4'
  COLLATE 'utf8mb4_unicode_520_ci';

connector_preset (migration)

CREATE TABLE IF NOT EXISTS connector_preset
(
    id CHAR(36) NOT NULL,
    # other fields omitted

    PRIMARY KEY (id)
);

I want to create a link between connector and connector_preset, so I created another migration like this:

ALTER IGNORE TABLE `connector`
    # `connector_preset`.`id`
    ADD COLUMN IF NOT EXISTS `preset_id` CHAR(36),
    ADD CONSTRAINT `fk_connector_preset_id` FOREIGN KEY (`preset_id`) REFERENCES `connector_preset` (`id`);

but it fails with the following error:

SQL State  : HY000
Error Code : 1005
Message    : (conn=4) Can't create table `test`.`connector` (errno: 150 "Foreign key constraint is incorrectly formed")
Location   : db/migration/...
Line       : 34
Statement  : ALTER IGNORE TABLE `connector`
    # `connector_preset`.`id`
    ADD COLUMN IF NOT EXISTS `preset_id` CHAR(36),
    ADD CONSTRAINT `fk_connector_preset_id` FOREIGN KEY (`preset_id`) REFERENCES `connector_preset` (`id`),

The columns seem to be of the same type. Also, for some reason it works in local k8s cluster (10.3.29-MariaDB), but fails in integration tests (testcontainers, MariaDB 10.6.11). Also fails in GH Actions which use 10.3.29, which is strange since it's working locally.

UPD: If I set mariadb version in testcontainers to 10.3.29 - it still fails.

UPD: Tried altering connector_preset table to use the same charset and collation:

ALTER IGNORE TABLE `connector_preset` DEFAULT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci';

Still doesn't work

Upvotes: 1

Views: 71

Answers (1)

Georg Richter
Georg Richter

Reputation: 7476

Table connector was created with character set utf8mb4 and collation utf8mb4_unicode_520_ci, while connector_preset was created with servers default character set and collation, which obviously differs.

So you have to convert your table to the right character set.

Your attempt to change it with ALTER TABLE ... DEFAULT CHARACTER SET 'utf8mb4' sets the default character set for the table, but not for the columns. Instead you have to convert it with:

ALTER TABLE connector_preset CONVERT TO CHARSET utf8mb4 COLLATE utf8mb4_unicode_520_ci

Upvotes: 1

Related Questions