MySQL Bugs: #108729: Adding fulltext index results in duplicate entry error for unrelated key

Bug #108729	Adding fulltext index results in duplicate entry error for unrelated key
Submitted:	10 Oct 2022 16:06	Modified:	13 Oct 2022 15:52
Reporter:	Mark Bench	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	8.0.30	OS:	Linux
Assigned to:		CPU Architecture:	x86
Tags:	Duplicate entry, fulltext, innodb

Description:
When I attempt to create a fulltext index on a pair of columns, I receive a "duplicate entry " error for an unrelated key. In addition, the error doesn't even make sense for that unrelated key, as the key isn't unique. This prevents creation of the fulltext index.

msqyl> ALTER TABLE `orders` ADD FULLTEXT quick_search_1 (quick_note, client_order_number);

ERROR 1062 (23000): Duplicate entry 'NULL' for key 'orders.display_id_addendum'

Simplified view of the table, with irrelevant columns removed:

CREATE TABLE `orders` (
  `id` int NOT NULL,
  `client_order_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `display_id_addendum` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `quick_note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `client_order_number` (`client_order_number`),
  ADD KEY `display_id_addendum` (`display_id_addendum`);

ALTER TABLE `orders`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

How to repeat:
Creating a minimal test case using the provided table and index structure won't create the problem. The issue must be triggered either by the particular data in the table, or the history/logs of the table.

Hi Mr. Bench,

Thank you for your bug report.

We have run your test case and we were not able to repeat the problem. Hence, please read further to find out what you should do.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php 

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.

Can't repeat.

Hi Mr. Bench,

One more item that you should notice.

You did not create full-text indices on those two columns, but, instead, ordinary VARCHAR indices. Hence, your presumptions and bug description are wrong.

Thanks for your comments! My post was probably unclear: the ordinary VARCHAR indices you reference are already existing before the attempt to create a fulltext index on the same fields. 

If that changes anything, I'd love to know, otherwise I recognize that without a reproducible test case it's difficult to assess what's going on.  I was hoping that the error message referencing a field unrelated to the fields involved in the fulltext index creation might be sufficient to indicate a bug.

Thanks for your eyes!

Hi Mr. Bench,

We tested your DDL on the 8.0.31 and it works like a charm .....

Can't repeat.