1

db-fiddle

This is the definition of my table:

CREATE TABLE `Posts` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Guid` uuid NOT NULL DEFAULT uuid(),
  `Tenant` varchar(400) DEFAULT NULL,
  `Slug` varchar(400) NOT NULL DEFAULT uuid(),
  `Locale` varchar(400) NOT NULL,
  `Title` varchar(400) DEFAULT NULL,
  `ImageGuid` uuid DEFAULT NULL,
  `Summary` varchar(400) DEFAULT NULL,
  `TimeToRead` bigint(20) DEFAULT NULL,
  `AcceptsComment` bit(1) DEFAULT b'1',
  `UtcDate` datetime NOT NULL,
  `LastUpdateUtcDate` datetime DEFAULT NULL,
  `StateKey` varchar(400) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_Posts_Unique_Guid` (`Guid`),
  UNIQUE KEY `IX_Posts_Unique_Locale_And_Slug` (`Locale`,`Slug`) USING HASH,
  KEY `IX_Posts_Guid` (`Guid`),
  KEY `IX_Posts_Tenant` (`Tenant`),
  KEY `IX_Posts_Slug` (`Slug`),
  KEY `IX_Posts_Locale` (`Locale`),
  FULLTEXT KEY `IX_Posts_FullText` (`Slug`,`Title`,`Summary`),
  CONSTRAINT `Ck_Posts_NonEmptyGuid` CHECK (`Guid` <> '00000000-0000-0000-0000-000000000000'),
  CONSTRAINT `Ck_Posts_NonEmptyImageGuid` CHECK (`ImageGuid` <> '00000000-0000-0000-0000-000000000000')
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

And I have one and only one record in it.

INSERT INTO `Posts` (`Id`, `Guid`, `Tenant`, `Slug`, `Locale`, `Title`, `ImageGuid`, `Summary`, `TimeToRead`, `AcceptsComment`, `UtcDate`, `LastUpdateUtcDate`, `StateKey`) VALUES
(13,    'a52b1e08-37d7-54f2-a10c-db6abb9a5af0', '', 'کلید-های-اتوماتیک-MCCB-آشنایی-با-ساختمان-عملکرد-و-تنظیمات-کلید-کلید-کمپکت-جریان-اتصال-کوتاه-المان-حرارتی-المان-مغناطیسی',  'fa',   'کلید های اتوماتیک |MCCB  |آشنایی با ساختمان عملکرد و تنظیمات کلید| کلید کمپکت| جریان اتصال کوتاه| المان حرارتی | المان مغناطیسی|', '258c90a3-9d86-4b85-9b69-27adcefb6cde', NULL,   5,  CONV('1', 2, 10) + 0,   '2021-02-02 00:00:00',  '2021-07-14 11:09:14',  'Draft');

Now when I want to update the record:

update Posts
set
    Guid = "a52b1e08-37d7-54f2-a10c-db6abb9a5af0",
    Slug = "کلید-های-اتوماتیک-mccb-آشنایی-با-ساختمان-عملکرد-و-تنظیمات-کلید-کلید-کمپکت-جریان-اتصال-کوتاه-المان-حرارتی-المان-مغناطیسی",
    Locale = "fa",
    Title = "کلید های اتوماتیک |MCCB  |آشنایی با ساختمان عملکرد و تنظیمات کلید| کلید کمپکت| جریان اتصال کوتاه| المان حرارتی | المان مغناطیسی|",
    ImageGuid = "258c90a3-9d86-4b85-9b69-27adcefb6cde",
    TimeToRead = "5",
    UtcDate = "2021-02-02 00:00:00",
    LastUpdateUtcDate = "2021-07-14 11:09:14",
    StateKey = "Draft"
where Id = 13

I get this error:

Duplicate entry 'fa-کلید-های-اتوماتیک-mccb-آشنایی-با-س...' for key 'IX_Posts_Unique_Locale_And_Slug'

How is that even possible? How a table that has one record can ever encounter duplicate entry error on update?

Version: MariaDB-1:11.3.2+maria~ubu2204

2
  • Did you mean 10.11.3? Or 11.3.2?
    – Rick James
    Commented May 1 at 4:17
  • @RickJames, this is what select version(); gives me: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 Commented May 1 at 4:47

1 Answer 1

2

This appears to be MDEV-29345 caused by case insensitive updates on text/blob/varchar(above 333 characters) updates on unique indexes.

The MDEV-371 Unique indexes for blobs feature has exposed a large number of edge cases (bugs) that we are fixing but this is still to be fixed.

A work around is to:

  • use 333 characters (or less) as the Slug length; and
  • remove "USING HASH" on the index (the BTREE default performant too).

Work around #2 is:

  • COLLATE=uca1400_as_cs; or
  • a case sensitive collate on the Slug column

ref: https://dbfiddle.uk/o5KPlLkB

3
  • I don't know why somebody downvoted this. I'm not a professional database developer, but If I was writing its code, I would check to see if the current row being updated conflicts with itself or not, and if the duplicate was itself, I would update the row. Commented May 1 at 4:45
  • 1
    I'm not sure either, I made sure it was positive as it was a genuinely good question. Application check and then do/dont are subject to race conditions. Its safer to just use unique indexes like you have and handle dup sql errors.
    – danblack
    Commented May 1 at 4:54
  • reducing the length is possible. But I'm not adding that USE HASH to my index creation statement. I use a tool that generates the database based on a JSON file. And it does not generate the USE HASH. This is a simple index script it generates: create unique index IX_Posts_Unique_Locale_And_Slug on Blog.Posts` (Locale, Slug)` Commented May 1 at 4:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.