When I index the new content of my site I found in the logs the following error:

"SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x98\xB3\xF0\x9F...' for column 'word' at row 7"

The reason is that in one my fields there is a text with emojis (emoticons) and the tables of the index have the collation "uf8_general_ci" and "utf8_bin".

I can save the text in Drupal without problems and it displays right, but when I try to re-index the content the error comes again and the process stops.

Would it be possible to change the collation of this tables to utf8mb4 so that they can support this kind of content. I changed it manually and it worked as expected.

Advice:
The problem is solved by enabling "transliteration" to the processor but I need to be able to find words with characters like äüßö

IS

Scenario 2:
1.- You have "search_api" and have a defined a index with fulltext fields into.
2.- You edit a node and insert some unicode (emojis) and text with characters like äüößáé.
3.- You DISABLE (or don't enable) "Transliteration" for the defined index processor.
4.- You try to re-index your content and it produces the error commented at the start of this text.

SHOULD
Scenario 2:
1.- You have "serach_api" and have a defined index with fulltext fields into.
2.- You edit a node and insert some unicode (emojis) and text with characters like äüößáé.
3.- You DISABLE "Transliteration" for the defined index processor.
4.- You try to re-index your content and it works right.

I hope I have described correctly the problem.

CommentFileSizeAuthor
#3 2902907-3--4_byte_words.patch3.59 KBdrunken monkey
#3 2902907-3--4_byte_words--tests_only.patch2.51 KBdrunken monkey

Comments

juagarc4 created an issue. See original summary.

juagarc4’s picture

juagarc4 commented
Issue summary: View changes
drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Category: Feature request » Bug report
Status: Active » Needs review
StatusFileSize
new 2902907-3--4_byte_words--tests_only.patch2.51 KB
new 2902907-3--4_byte_words.patch3.59 KB

Thanks a lot for reporting this problem, and writing such a great issue summary!
The problem is easy enough to understand and I fear we've caused this ourselves: While Drupal itself defaults to using utf8mb4, we actually have code in \Drupal\search_api_db\DatabaseCompatibility\MySql::alterNewTable() to change this to the other types. As explained in the comment:

The Drupal MySQL integration defaults to using a 4-byte-per-character encoding, which would make it impossible to use our normal 255 characters long varchar fields in a primary key (since that would exceed the key's maximum size). Therefore, we have to convert all tables to the "utf8" character set – but we only want to make fulltext tables case-sensitive.

However, I guess this actually doesn't even apply to fulltext fields, since their field length is only 50 characters. So, while I'm not 100% sure, it seems like we can easily fix this. Patch attached, please test whether this resolves the problem for you! (You'll need to disable and re-enable the index to make it work, though.)
Also, you can probably just manually change the collation of your index's text table (search_api_db_INDEX_text).

borisson_’s picture

borisson_
Primary language Dutch
Location Mechelen, 🇧🇪
commented

Is the test-only patch supposed to pass?

juagarc4’s picture

juagarc4 commented

Hi,

First of all, thank you for your quick response.

I've tested the patch "2902907-3--4_byte_words.patch" and it worked fine in my last tests.
I've recreated the site from scratch using config manager and some own build scripts.
The tables are now created using the new collation "utf8_general_ci" and "utf8mb4_bin" and the indexing works as expected.

Thank you very much!!

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Status: Needs review » Needs work

Is the test-only patch supposed to pass?

Hm, damn, no. Can't find any way to write that test so it passes with the patch but fails without it. I tried:

$text = json_decode('"\u1F601"');
$text = '😁';
$text = chr(0xf0) . chr(0x9f) . chr(0x98) . chr(0x81);

Can anyone think of another option? Otherwise, I guess we just have to go with that test (or just have no test at all, instead of one that's not correctly testing the regression).

@ juagarc4: Thanks a lot for reporting back! Good to hear it works as intended. (Even though the tests disagree …)

juagarc4’s picture

juagarc4 commented

HI @drunken monkey

I tried it too and I couldn't find any way to write that test so it passes with the patch but fails without it.

Could it be marked as incomplete?.
At least we have a message to consider when the test is executed.

For example:
// Stop here and mark this test as incomplete.
$this->markTestIncomplete(
'Test incomplete because there is no way to do that it passes with the patch but fails without it.'
);

Thank you

  • drunken monkey committed eeb9f58 on 8.x-1.x 
    Issue #2902907 by drunken monkey, juagarc4: Fixed indexing of multibyte...
drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Status: Needs work » Fixed

Hm, OK. Still, thanks for trying!
Then I guess let's just commit it without a test.
Thanks again, everyone!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

driskell’s picture

driskell commented

Just wanted to raise this change made queries that join the _text table to the other table unable to use the item_id PRIMARY key index, because one is utf8mb4 and the other is utf8. Thus when you have 10s of thousands of entries in the tables it is extremely slow and taxing on the database.

Raised it here - would be great to hear what the solution might be: https://www.drupal.org/project/search_api/issues/3227268