[SOLVED] Vitess v8 PlanetScale MySQL db constraint error

Issue

Using Vitess v8 (PlanetScale DB)
Table:

CREATE TABLE `Channel` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `guildId` INTEGER UNSIGNED NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `description` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `position` INTEGER NOT NULL,
    `parentId` INTEGER NULL,
    `ratelimit` INTEGER NOT NULL DEFAULT 0,
    `type` ENUM('textChannel', 'categoryChannel') NOT NULL,

    INDEX `Channel_guildId_idx`(`guildId`),
    UNIQUE INDEX `Channel_guildId_name_key`(`guildId`, `name`),
    UNIQUE INDEX `Channel_guildId_position_key`(`guildId`, `position`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Test Data:

INSERT INTO Channel (id, guildId, name, position, type) VALUES (1, 1, 'ch1', 1, 'textChannel');
INSERT INTO Channel (id, guildId, name, position, type) VALUES (2, 1, 'ch2', 2, 'textChannel');
INSERT INTO Channel (id, guildId, name, position, type) VALUES (3, 1, 'ch3', 3, 'textChannel');

First step: (It’s ok if you don’t understand why this step is important)

UPDATE `Channel` SET `position` = 0.5 WHERE `id` = 3;

Edited query provided by Ergest:

UPDATE `Channel` AS `ch`
INNER JOIN ( 
  SELECT `id` as `id2`,
  ( SELECT COUNT(*)
    FROM (SELECT * FROM `Channel`) AS `b`
    WHERE `b`.`position` <= `a`.`position`
  ) AS `p`
  FROM `Channel` AS `a` WHERE `guildId` = 1
) AS `td` ON `ch`.`id` = `td`.`id2`
SET `ch`.`position` = `td`.`p`;

Error:

error: code = AlreadyExists desc = Duplicate entry '1-2' for key 'Channel.Channel_guildId_position_key' (errno 1062)

Solution

You are using ORDER BY.

MySQL docs :

Multiple-table syntax: For the multiple-table syntax, UPDATE updates
rows in each table named in table_references that satisfy the
conditions. Each matching row is updated once, even if it matches the
conditions multiple times. For multiple-table syntax, ORDER BY and
LIMIT cannot be used.

I see no point on using ORDER BY position ASC inside subquery when you are not using LIMIT

Try using INNER JOIN:

UPDATE `Channel` AS `ch`,
INNER JOIN ( 
            SELECT`id`,
                      ( SELECT COUNT(*)
                        FROM `Channel` AS `b`
                        WHERE `b`.`position` <= `a`.`position`
                      ) AS `p`
           FROM `Channel` AS `a` WHERE `guildId` = 1
           ) AS `td` on   `ch`.`id` = `td`.`id`; 
SET `ch`.`position` = `td`.`p` ;

Answered By – Ergest Basha

Answer Checked By – Clifford M. (BugsFixing Volunteer)