#84541Uncaught TYPO3 Exception in indexed_search: duplicate key (hash) error
Description
I get an Exception with HTTP status code 503 on loading a page with the Exception message:
Oops, an error occurred! An exception occurred while executing 'INSERT INTO `index_words` (`wid`, `baseword`, `metaphone`) VALUES (?, ?, ?)' with params [55602717, "images\/botschaften", "66411504"]: Duplicate entry '55602717' for key 'PRIMARY'
exception.png
System
- TYPO3 version 8.7.17
- current master
Severity of error
Even if Configuration preset “Live” is selected, an error is still displayed in the frontend on first load of page. The page is not rendered. Doesn’t happen for all pages, only for pages where the hash collision occurs. Still, it’s pretty ugly.
exception2.png
Reproduce
- Activate the extension indexed_search
- Add static includes for indexed_search
- Add the following words to a new or existing content element: graf gettogethers abfluss erworbener
- Load the page in the frontend (you may have to do that twice)
Expected result
Page is displayed
Actual result
Exception message:
Uncaught TYPO3 Exception An exception occurred while executing 'INSERT INTO `index_words` (`wid`, `baseword`, `metaphone`) VALUES (?, ?, ?)' with params [186135449, "gettogethers", "199699927"]: Duplicate entry '186135449' for key 'PRIMARY'
Reproduce with test code
You can also reproduce the problem with this simple test script, which includes the original hash function from IndexedSearchUtility and some test strings
<?php
$str = ['graf', 'gettogethers', 'abfluss', 'erworbener'];
function md5inthash($stringToHash)
{
return hexdec(substr(md5($stringToHash), 0, 7));
}
foreach ($str as $s) {
print("string=$s hash=" . md5inthash($s) . "\n");
}
The first 2 strings and the third and forth string produce the same hash (which causes the collisions):
string=graf hash=186135449
string=gettogethers hash=186135449
string=abfluss hash=211412923
string=erworbener hash=211412923
Additional information
If you need to reproduce this several times, you can delete the index for the page by selecting the “Indexing” module, select the page with the content you inserted earlier, then select “Detailed statistics” and press the “Delete” garbage can.
You can also check the sys_log for similar errors:
SELECT uid,FROM_UNIXTIME(tstamp),details FROM sys_log WHERE details LIKE '%index_word%' and error = 2 ORDER BY uid DESC LIMIT 10;
Cause of error
(the link goes to a specific commit which may no longer reflect the current codebase)
hash collision?
Affected fields
These are the fields in the DB, that get filled using the current hash algorithm:
(all affected fields by looking at DB schema and content and guessing):
- index_debug.phash
- index_fulltext.phash
- index_grlist.phash
- index_grlist.phash_x
- index_grlist.hash_gr_list
- index_phash.phash
- index_phash.phash_grouping
- index_phash.contentHash
- index_rel.phash
- index_rel.wid
- index_section.phash (see https://forge.typo3.org/issues/79802)
- index_section.phash_t3
- index_words.wid (see this issue)
- index_words.metaphone
Currently however, only index_words.wid seems to be affected by the collisions.
Reviews 8
- main 74688[!!!][TASK] Use full md5 hash for `indexed_search`
- 11.5 79754[BUGFIX] Prevent exception caused by hash collisions in indexed_search
- 12.4 79741[BUGFIX] Prevent exception caused by hash collisions in indexed_search
- main 74742[BUGFIX] Prevent exception caused by hash collisions in indexed_search
- main 66492[WIP][BUGFIX] Add unique ID to indexed search DBs to avoid DB errors
- main 58717[BUGFIX] Change hashing algorithm for indexed_search
- main 58704[BUGFIX] Fix hashing algorithm of indexed_search (this patch is for TYPO3 10!!!)
- main 56470[WIP][BUGFIX] Change datatype of hash fields in index_words table