[5.x]: Unable to update search indexes when multiple field instances exist of a field set to be search indexed

[rynpsc]

What happened?

Description

When an Element has multiple instances of a field with "Use this field’s values as search keywords" enabled, an error is thrown when running the Updating search indexes job. The error relates to an "Integrity constraint violation".

Steps to reproduce

1. Create field e.g. text and enable "Use this field’s values as search keywords"
2. Assign two instances of the field to an Entry type, assign Entry type to an Entry
3. Create a new Entry, populate both fields, and save

Expected behavior

Search indexes are correctly updated for both fields without error.

Actual behavior

Search index job errors with an exception such as:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '57-field-6-1' for key 'searchindex.PRIMARY' The SQL being executed was: INSERT INTO `searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (57, 'field', 6, 1, ' 30 ') 

Craft CMS version

Craft Solo 5.0.0-alpha

PHP version

8.2.9

Operating system and version

Linux 6.5.10-orbstack-00110-gbcfe04c86d2f

Database type and version

MySQL 8.0.33

Image driver and version

Imagick 3.7.0 (ImageMagick 6.9.11-60)

Installed plugins and versions

No response

[i-just]

Hi, thanks for reporting! I raised a PR for this.

[brandonkelly]

Craft 5.0.0-alpha.4 is out with a fix for this.

For now, all field instances will be sharing the same combined search keyword index. We’ll discuss internally whether it’s worth it to start indexing keywords on a per-instance basis.