Row size too large, Too many keys specified

Drupal\search_api\SearchApiException: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs: ALTER TABLE "search_api_db_prod" ADD `field__surface_engine_power_1` VARCHAR(255) DEFAULT NULL COMMENT 'The field\'s value for this item'; Array ( ) in Drupal\search_api_db\Plugin\search_api\backend\Database->fieldsUpdated() (line 1143 of modules/contrib/search_api/modules/search_api_db/src/Plugin/search_api/backend/Database.php). Drupal\Core\Database\StatementWrapper->execute(Array, Array) (Line: 944) Drupal\Core\Database\Connection->query('ALTER TABLE {search_api_db_prod} ADD `field__surface_engine_power_1` VARCHAR(255) DEFAULT NULL COMMENT 'The field\'s value for this item'') (Line: 437) Drupal\mysql\Driver\Database\mysql\Schema->addField('search_api_db_prod', 'field__surface_engine_power_1', Array) (Line: 833) Drupal\search_api_db\Plugin\search_api\backend\Database->createFieldTable(Object, Array, 'index') (Line: 1087) Drupal\search_api_db\Plugin\search_api\backend\Database->fieldsUpdated(Object) (Line: 700) Drupal\search_api_db\Plugin\search_api\backend\Database->updateIndex(Object) (Line: 154) Drupal\search_api\Task\ServerTaskManager->executeTask(Object) (Line: 114) Drupal\search_api\Task\ServerTaskManager->processEvent(Object, 'search_api.task.updateIndex', Object) call_user_func(Array, Object, 'search_api.task.updateIndex', Object) (Line: 142) Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher->dispatch(Object, 'search_api.task.updateIndex') (Line: 215) Drupal\search_api\Task\TaskManager->executeSpecificTask(Object) (Line: 359) Drupal\search_api\Task\TaskManager->processBatch(Array, Array, Array) (Line: 295) _batch_process() (Line: 137) _batch_do() (Line: 93) _batch_page(Object) (Line: 55) Drupal\system\Controller\BatchController->batchPage(Object) call_user_func_array(Array, Array) (Line: 123) Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}() (Line: 580) Drupal\Core\Render\Renderer->executeInRenderContext(Object, Object) (Line: 124) Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->wrapControllerExecutionInRenderContext(Array, Array) (Line: 97) Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}() (Line: 169) Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object, 1) (Line: 81) Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1) (Line: 58) Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1) (Line: 48) Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1) (Line: 48) Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1) (Line: 51) Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1) (Line: 23) Stack\StackedHttpKernel->handle(Object, 1, 1) (Line: 713) Drupal\Core\DrupalKernel->handle(Object) (Line: 19)

Comments & Activities

Issue created by @proweb.ua
Comment about 2 years ago →
🇺🇦Ukraine proweb.ua
Status changed to Postponed: needs info about 2 years ago10:18pm 25 February 2023
Comment about 2 years ago →
🇦🇹Austria drunken monkey Vienna, Austria
The problem with the maximum number of keys is well-known (see, e.g., #2403275: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed in SearchApiDbService->fieldsUpdated() → and #2844945: SearchApiException Too many keys specified; max 64 keys allowed → ), but won’t do much harm. If you want, you can just manually remove or add indexes to the database table that you think will be most useful. (E.g., fields you often sort by, or single-valued fields often used for filtering.) Not really much we can do, except try to be smarter about which columns to place indexes on – see 📌 Is adding an index for each column in the denormalized index table really a good idea? Active .

The second error is a new one, though, and of course both more serious and harder to work around.
A quick research, though, suggests that setting innodb_strict_mode = 0 in the MySQL settings might prevent the error for you, at least during table creation. An error would still occur if an actual row got above that size, but that is probably far less likely than the maximum row size exceeding it, given our generous VARCHAR field lengths. So, could be worth a try.

Otherwise, not really sure how to deal with this in the module generically. If we could spot this problem, one thing we could probably always do is just split the table into two (or more) parts and just JOIN them at query time. Less than ideal for performance, but better than an exception preventing any indexing, I’d say. However, not that trivial to implement, and as this is (I think) the first complaint about this in 15 years, this doesn’t seem like a very common problem. We might also be fine just ignoring it unless there are more reports about it.

Generally, when you have such a large index, investing in a dedicated search server like Solr seems like a good idea anyways.
Also, please make sure you really need all those 88 fields indexed, and be aware that only fields on which you want to filter or sort, or which you want to search, need to be indexed.

Row size too large, Too many keys specified

Problem/Motivation

Comments & Activities