Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
I am not able to index some contents because the score value is out range.
SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'score' at row 12: INSERT INTO @search_api_db_content_text (item_id, field_name, word, score) VALUES (:db_insert_placeholder_0,
....
[:db_insert_placeholder_2643] => 5566238033197400064 )
Comment | File | Size | Author |
---|---|---|---|
#17 | search_api_db-2827961-17.patch | 772 bytes | Tess Bakker |
#16 | search_api_db-2827961-16.patch | 637 bytes | joey91133 |
#5 | 2827961-5--gigantic_token_scores.patch | 2.46 KB | drunken monkey |
|
Comments
Comment #2
Steven Jones CreditAttribution: Steven Jones at ComputerMinds commentedWe are seeing this error too, but upon inspection the score column has numbers much bigger than the apparent error value. Weird!
Comment #3
Steven Jones CreditAttribution: Steven Jones at ComputerMinds commentedOkay, so no idea where the number in the error message is coming from, but we're trying to insert some massive score values, that are larger than a unsigned 32bit integer.
I suppose that SearchAPI DB should
min
the desired score with the upper limit on the DB column.Comment #4
Steven Jones CreditAttribution: Steven Jones at ComputerMinds commentedCRAZY!
Got to the bottom of this for our site.
We are using the 'HTML filter' and the 'Ignore characters' processor on an HTML field.
'Ignore characters' runs first, and removes all the '\' from HTML closing tags.
'HTML filter' runs next and finds more and more tags with boosts, so the boosts get higher and higher as the recursion into the nested HTML tags gets more and more!
The solution for us was to change the weight of the 'Ignore characters' processor to be higher than the 'HTML Filter' so that the HTML characters we not changed.
Comment #5
drunken monkeyThanks a lot for reporting this issue and already providing a detailed analysis of the problem.
Seems we should do two things here:
- Change the default processor weights so that the HTML filter will run before the "Ignore characters" processors does.
- In the database backend, make sure the score can never exceed the maximum database column size (apparently 4 bytes in all three supported DBMSs).
Would be great if you could take a look at the attached patch and tell me if you think it makes sense like this!
Comment #6
Steven Jones CreditAttribution: Steven Jones at ComputerMinds commentedI should point out that in the UI the weights of the processors only go from -10 to 10, so I think a separate issue needs to resolve that too, so that the ordering doesn't get wiped out if I load and save.
That being said, just from a visual review, the patch looks good to me!
Comment #7
borisson_I haven't tested the patch but it looks like it makes sense. I'm going to be bold and set this to RTBC.
Comment #9
drunken monkeyOK, thanks you two!
Committed.
Comment #11
quadrifolia CreditAttribution: quadrifolia commentedalso check for score values below zero, happens for me:
$score = min((int) $score, 4294967295);
$score = max((int) $score, 0);
Comment #12
gwagroves CreditAttribution: gwagroves commentedI'm also seeing negative values that need to be constrained to 0.
Comment #13
jimkeller CreditAttribution: jimkeller commentedThis issue still occurs due to code in fieldsUpdated() in search_api_db/src/Plugin/search_api/backend/Database.php that tries to avoid a reindex by running an UPDATE query on the score.
I temporarily worked around it by forcing $reindex=true in the code below (line 943) and re-indexing, but not sure what the ramifications of doing this actually are. We should either always reindex in this case, or we should apply the same logic as the above patch (although it might be tricky due to differences in DBMS).
Comment #14
jhedstromAs noted in #11 when something causes the score to go negative, this
min()
solution doesn't work since an out-of-range score in the negative will be smaller than the max int size.Comment #15
cilefen CreditAttribution: cilefen as a volunteer commentedFollowing up on #4, the boost values can exceed PHP_FLOAT_MAX so I opened #3209167: Because of multiplying inside recursion, HTML Filter's tag boost can produce astronomical boost values, which besides being nonsensical, breaks the Solr boost_term in Search API Solr by exceeding PHP_FLOAT_MAX but that issue may belong in this project.
Comment #16
joey91133 CreditAttribution: joey91133 as a volunteer commentedadd patch with #11 to solve score field have negative number.
Comment #17
Tess BakkerRe-roll