Vitess v8 PlanetScale MySQL db constraint error

Question

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)

it says incorrect usage of UPDATE and LIMIT but I'm not even using LIMIT. — Average Capitalist, Mar 18 '22 at 20:44

Ergest Basha · Accepted Answer · 2022-03-18T21:31:29.223

1

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` ;

edited Mar 18 '22 at 21:31

answered Mar 18 '22 at 20:59

Ergest Basha

7,870
4
8
28

When I remove the ORDER BY it gives same error... And I'm not even using ORDER BY in UPDATE, it's a SELECT query ( now I removed ORDER BY to prevent same answers like this ) – Average Capitalist Mar 18 '22 at 21:23
@AverageCapitalist try `INNER JOIN` and let me know – Ergest Basha Mar 18 '22 at 21:32
Wow! This is almost working! I have no idea why this gave me an error... But it says "Duplicate entry '1-2'" Because I have a constraint set for unique position – Average Capitalist Mar 18 '22 at 21:51
When I manually do the SELECT query, it displays everything just fine and "p" key is not duplicate. Not sure why MySQL thinks it's breaking the Unique index constraint – Average Capitalist Mar 18 '22 at 21:51
@AverageCapitalist without data and table description I can't tell – Ergest Basha Mar 18 '22 at 21:56
I think I know why this is happening... Since MySQL is updating one row at a time, maybe a row with for example 0.5 got updated to 1, but the row with position 1 isn't updated yet so that's why it broke the constraint? – Average Capitalist Mar 18 '22 at 22:05
https://www.db-fiddle.com/f/fZY8dLTbtRFF3Nwe4rPT2L/0 Here is a db fiddle snippet! It's raising the constraint error – Average Capitalist Mar 18 '22 at 23:53
@AverageCapitalist if you check the [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d893cd935a8480e325e6a6f16edc3c34) you will see that you are trying to update `1 2` values which exists and that's the way how `UNIQUE INDEX Channel_guildId_position_key(guildId, position)` suppose to work – Ergest Basha Mar 19 '22 at 08:45
I read the error and now I understand why, but it's supposed to turn each position into `1`, `2`, `3` respectively. This works perfectly in Postgres implementation, but I'm using MySQL... How would I fix this problem? It shouldn't break the constraint if the entire UPDATE query completed – Average Capitalist Mar 19 '22 at 20:16

Vitess v8 PlanetScale MySQL db constraint error

1 Answers1