MySQL Bugs: #109242: CREATE FULLTEXT INDEX fails with ERROR 1062 (23000): Duplicate entry 'NULL-NULL'

Bug #109242	CREATE FULLTEXT INDEX fails with ERROR 1062 (23000): Duplicate entry 'NULL-NULL'
Submitted:	29 Nov 2022 21:04	Modified:	30 Nov 2022 4:42
Reporter:	E M	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	8.0.31	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	regression

Description:
Dropping then recreating a fulltext index on two columns with ~10,000 rows of random  data usually fails (making the columns NOT NULL changes the value cited in the error from 'NULL-NULL' to ''; creating the index for the first time after inserting the rows changes the index cited from <table>.<fulltext-index-name> to <table>.PRIMARY)

How to repeat:
See attached script; error does not occur every time and may be impacted by the timestamp used in the UUIDs since it usually fails reliably but sometimes starts to succeed many attempts in a row. Increasing the number of rows increases the chance of an error as does increasing the number of columns included in the index.

Reproduction script

Attachment: test.sql (application/sql, text), 195.75 KiB.

I also saw this reported here: https://stackoverflow.com/questions/73348896/add-multi-column-fulltext-index-fails-with-10...

Hello!

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

Reproduction (~4m rows, python script)

Attachment: mysql-test.py (text/x-python), 4.76 KiB.

Have the same issue, was able to reproduce it using the above attached python (creates about ~4m rows in 40 sql files + 2 other sql files (create database, alter table), and runs the scripts...

Seems to fail frequently...

Running 99_altertable.sql in mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 4: Duplicate entry 'NULL-NULL-NULL' for key 'customers.customerFullText'

I forgot to add, the above is reproducible on MySQL 8.0.30.

We ran into this issue as well. Not sure if anyone knows any workaround for this?

Warning and follow up bug:

We tried to avoid the bug by manually implementing the FTS_DOC_ID column.

By doing so, we had multiple systems either crashing mysql or even worse, unrepairable destroying some of the InnoDB tables. 
This crashing/destroying tables only happens, when we added the FTS_DOC_ID column and a FULLTEXT index to the table.
It did not happen to all tables, not immediately but tests showed the problem all over the place.

Hoping for a bugfix on #109242.

Here an example, of crashing mysql:
2023-02-24T15:52:29.558363Z 2417 [ERROR] [MY-013183] [InnoDB] Assertion failure: fts0fts.cc:5200:len == 8 thread 11688
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
15:52:29 UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x1b60a165890
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff6ea237498    mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff6e93e886b    mysqld.exe!?print_fatal_signal@@YAXH@Z()
7ff6e93e8633    mysqld.exe!?my_server_abort@@YAXXZ()
7ff6ea21d83a    mysqld.exe!?my_abort@@YAXXZ()
7ff6ea44fbe9    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff6ea3bba68    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff6ea4fc28c    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff6ea4f6c93    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff6ea2ffe31    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff6e91ba012    mysqld.exe!?ha_index_read_map@handler@@QEAAHPEAEPEBEKW4ha_rkey_function@@@Z()
7ff6e91c006f    mysqld.exe!?read_range_first@handler@@MEAAHPEBUkey_range@@0_N1@Z()
7ff6ea314e7a    mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z()
7ff6e91be531    mysqld.exe!?multi_range_read_next@handler@@MEAAHPEAPEAD@Z()
7ff6e91bb177    mysqld.exe!?ha_multi_range_read_next@handler@@QEAAHPEAPEAD@Z()
7ff6e96d57ca    mysqld.exe!?Read@IndexRangeScanIterator@@UEAAHXZ()
7ff6e95fe2b8    mysqld.exe!?update_single_table@Sql_cmd_update@@AEAA_NPEAVTHD@@@Z()
7ff6e944bbdc    mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
7ff6e93a4013    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff6e939f1b1    mysqld.exe!?dispatch_sql_command@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff6e939ded2    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff6e939f4f0    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff6e91b1728    mysqld.exe!?modify_thread_cache_size@Per_thread_connection_handler@@SAXK@Z()
7ff6ea64b519    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff6ea227fdc    mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7ffecdc69363    ucrtbase.dll!_recalloc()
7ffecf4f26bd    KERNEL32.DLL!BaseThreadInitThunk()
7ffed058dfb8    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1b5df900030): UPDATE `inbox` SET `attached_file` = NULL WHERE `id` = 1
Connection ID (thread ID): 2417
Status: NOT_KILLED

Reproducible on MySQL 8.0.29 up the curren 8.0.32

I am also reproducting on 8.0.33.
As far as I can tell, the problem occurs when creating a new FULLTEXT index when at least one column of the new index is or has been used in another FULLTEXT index. There is no need to delete an index before to trigger it.