AttributeSlugRepository unable to save an attribute with a slightly different name

[LeonidasJP]

Tweakwise-M2: v8.11, v9.0.1
Magento: 2.4.8-p5
DB: MariaDB 11

Having a Product Attribute with Options, slightly renaming an option from - for example - Café to Cafe does not work - resulting in a database constraint violation on the ATTRIBUTE key on the tweakwise_attribute_slug table.

This seems to be failing when an attribute with almost the same name (either with or without accented letters like above) exists for the same storeview

Of the 2749 attribute slugs in tweakwise_attribute_slug, only six have an attribute_code.

While debugging Model/AttributeSlugRepository.php::save(), I noticed the following:

• Existing attribute in table: attribute=Café, slug=cafe, store_id=0, attribute_code=chair_type
• Update the Admin Code of the attribute from Café to Cafe and save the Attribute --> error
  • baseSlug = cafe
  • storeId = 0
  • existingSlug found, but with another Attribute value (Resp. Cafe (new) and Café (existing)), so counter +1
  • New slug cafe-1 doesn't exist, so while-loop is exited
  • New slug cafe-1 for attribute Cafe and StoreID 0 is persisted --> Unique Constraint Violation with message SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Cafe-0' for key 'ATTRIBUTE', query was: INSERT INTO 'tweakwise_attribute_slug' ('attribute', 'slug', 'store_id', 'attribute_code') VALUES ('Cafe', 'cafe-1', 0, 'chair_type')
• Attribute Update gets reverted...

Looks like MariaDB normalizes characters in keys, where accented letters get replaced to their base accentless variant...

What can be done to fix this issue?

[ahuininga-orisha]

@LeonidasJP.

The issue is the coalition of the table. It sees cafe and café the same in the index.

The should be fixed with: #391

[LeonidasJP]

Could something like this be a solution?

In Model/AttributeSlug.php, add a new function getNormalizedAttribute():

    public function getNormalizedAttribute(): ?string
    {
        return $this->getAttribute() ? transliterator_transliterate('Any-Latin; Latin-ASCII', $this->getAttribute()) : null;
    }

transliterator_transliterate() requires the presence of php-intl, could also be replaced with iconv('UTF-8', 'ASCII//TRANSLIT', $this->getAttribute()) which requires either php-iconv or symfony/polyfill-iconv

With this getNormalizedAttribute(), you then are able to change the Attribute-equality in Model/AttributeSlugRepository.php to:

// @lines 92-94
                    if ($existingSlug->getNormalizedAttribute() === $attributeSlug->getNormalizedAttribute()) {
                        return $attributeSlug;
                    }

[ahuininga-orisha]

@LeonidasJP Fixing it in the code may cause issues with the wrong value (cafe instead of café) send to tweakwise. It should be solved in the database so that both values have their own row.

[LeonidasJP]

Clear! We'll implement the patch from #391 as a Composer Patch for now, until that is merged in a new release.
Thanks for the rapid response!

[ahuininga-orisha]

@LeonidasJP It may be that the fix is not enough for you. It is fixed for the slug column, but it seems you may have the same issue with the attribute column. Changen the coallation of that column should fix the issue. I'll change the fix to also fix it for the attribute column

[ahuininga-orisha]

@LeonidasJP It may also be related to #381. It is possible you need both fixes

[LeonidasJP]

I'll test with #391 first, if that doesn't work, I'll add in #381 as well :-) Thanks for the heads-up!

[LeonidasJP]

@ahuininga-orisha Looks like #391 is enough to allow me to save the attribute; though I do have a Café with slug cafe, and a Cafe with slug cafe-1 for the same Storeview now :-) Is that intended behavior, or something that #381 will fix?

[ahuininga-orisha]

@LeonidasJP That is intended behaviour. It needs an different slug so the right value is send to tweakwise.