I created a new node index with a lot of fields. When I try to index, I get the message "Couldn't index items. Check the logs for details.".
Error in logs is:
Drupal\search_api\SearchApiException: SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed: ALTER TABLE {search_api_db_search_node_index} ADD INDEX `_p_text_body_1` (`p_text_body_1`(10)); Array ( ) in Drupal\search_api_db\Plugin\search_api\backend\Database->fieldsUpdated() (regel 1033 van .../modules/contrib/search_api/modules/search_api_db/src/Plugin/search_api/backend/Database.php).
I'm using Search API 8.x-1.0-beta4.
| Comment | File | Size | Author |
|---|---|---|---|
| #8 | 2844945-7--catch_database_exceptions_properly.patch | 2.79 KB | drunken monkey |
Comments
Comment #2
jeroentComment #3
jeroentComment #4
borisson_According to the error, there's a maximum of 64 indexes that can be created per table.
It looks like we create an index for every field, including fulltext fields.
The field you're trying to create at that time is a fulltext field.
I'm pretty sure that creating an index for those is not very useful. I think this is a bug report for the db-backend.
@drunken monkey, do you think it would make sense to remove the indexes for fulltext fields?
Comment #5
drunken monkeyThis is very strange, since we actually already fixed that problem back in #2403275: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed in SearchApiDbService->fieldsUpdated() (included since Alpha 11).
So either you're using a much older version after all, or for some reason in your case an exception not extending
\PDOExceptionis thrown for this error. Otherwise, I just don't see how this could be possible, from looking at the code.If you're sure about the module version, could you try to find out what the exception caught on line 1033 in your case? The attached patch might already be enough to do that, in case you're not a developer.
@ Joris: Might be an option, yes, but doesn't really solve the problem here. Skipping indexes for fulltext fields (if this even is a fulltext field) would just slightly raise the number of fields it takes to trigger this bug.
I guess it's true that those indexes have a much smaller chance of being useful for fulltext fields – but then, we can't really know this either way, for any kind of fields. We just add indexes and hope for the best, really (and that storage space isn't a problem).
Comment #6
jeroent@drunken monkey, I was using Search API 8.x-1.0-beta4. Today, I upgraded to the latest version (beta 5). The error I get is:
Comment #7
drunken monkeyOh, I see now! Seems Drupal actually has its own set of database exceptions, not inheriting from
PDOException, so we have to catch those separately! I wasn't aware of that, and it seems like a pretty stupid idea, but that's apparently how it is.Thanks a lot for posting this additional information, really helpful!
Patch attached which should properly catch this – please test!
(Although I now wonder whether we should even log a warning in this case – not much the user can do about this, it will just confuse most of them, and it won't actually have any negative impact, except for, perhaps, slower searches in some specific cases.)
Likewise, I'd be interested in a review by some SQL expert, whether just blindly adding all these indexes really has any benefit, and doesn't just deteriorate performance (especially for indexing). But that's a different issue.
Comment #8
drunken monkeyComment #9
drunken monkeyComment #10
jeroent@drunken monkey,
I tried the patch in #8 and it seems to work fine. Errors are now logged as warning instead of error.
I also created a follow-up issue to check if adding the indexes really has any benefit: #2867306: Is adding an index for each column in the denormalized index table really a good idea?.
Comment #12
drunken monkeyGood to hear, thanks for testing!
Committed.