Duplicated task records added into the search_api_task table

I have a site with 150,000 nodes which I think is running into this bug. The index config has changed and so a reindex has been triggered during the config import step of a deployment. This is taking several hours to complete, and inspecting the search_api_task table during the import shows that 100 duplicate tasks seem to be inserted for each page?

mysql> select type, server_id, index_id, data, count(*) from search_api_task group by type, server_id, index_id, data;
+------------+-----------+---------------+---------------------------------------------------------------+----------+
| type       | server_id | index_id      | data                                                          | count(*) |
+------------+-----------+---------------+---------------------------------------------------------------+----------+
| trackItems | NULL      | article_index | a:2:{s:10:"datasource";s:11:"entity:node";s:4:"page";i:1443;} |       56 |
| trackItems | NULL      | article_index | a:2:{s:10:"datasource";s:11:"entity:node";s:4:"page";i:1444;} |       44 |
+------------+-----------+---------------+---------------------------------------------------------------+----------+
2 rows in set (0.00 sec)

Running the same query a few seconds later:

mysql> select type, server_id, index_id, data, count(*) from search_api_task group by type, server_id, index_id, data;
+------------+-----------+---------------+---------------------------------------------------------------+----------+
| type       | server_id | index_id      | data                                                          | count(*) |
+------------+-----------+---------------+---------------------------------------------------------------+----------+
| trackItems | NULL      | article_index | a:2:{s:10:"datasource";s:11:"entity:node";s:4:"page";i:1445;} |       81 |
| trackItems | NULL      | article_index | a:2:{s:10:"datasource";s:11:"entity:node";s:4:"page";i:1446;} |       19 |
+------------+-----------+---------------+---------------------------------------------------------------+----------+
2 rows in set (0.00 sec)

Is this expected behaviour, or is it actually indexing everything 100 times?

Thanks for reporting this problem!

No, this is definitely not expected behavior, and seems like a large performance problem when it happens. So really great that you spotted this, and would be important to fix.

Regarding your patch, I spotted one problem: there is no IS NULL operator for entity queries, you need to use notExists() for that. However, I also re-arranged the code a bit. I also added a test for this to prove it’s working correctly and hopefully prevent us from breaking it in the future.
Patch attached, tests/reviews would be very welcome!

Also, it would be great to find out what code is even responsible for trying to create multiple tasks. Any chance of finding that out, either of you? Apparently, that code is buggy, too, so maybe it will also cause other problems if not fixed.

Finally, if we now ensure no duplicates are added, it would actually be good to add a UNIQUE index to the tasks table for that. However, as tasks are content entities, I’m not sure how to do that – does anyone here know?

Any help with my questions, or any other feedback on the patch?

Thanks to chx I now found out how to add that unique index, and with a bit of fiddling even managed to write a database update function for it.

Please test/review and I can commit this.

(that existing task is) instead returned.

returned instead

+      if ($values === NULL) {
+        $query->notExists($property);
+        continue;
+      }
       $query->condition($property, $values, is_array($values) ? 'IN' : '=');

that foreach is not big enough for this sort just do

     if (isset($values)) {
       $query->condition($property, $values, is_array($values) ? 'IN' : '=');
     }
     else {
       $query->notExists($property);
     }

@todo Use named parameters here once we depend on PHP 8.0+.

you could pass an array right now if you so desired.

if (!empty($result)) {

if(!empty()) is only ever necessary if the variable can be undefined , but here it can't be . This is literally the exact same as if($result).

Thanks for the review!
I don’t think the thing with named parameters is urgent enough right now, though. But great catches otherwise.

Anyone available for testing this so we can finally commit it?

Well, let’s just push this and hope for the best.
Committed. Thanks again, everyone!

Hi,

we are getting a MYSQL exception with this update (on updatedb) with search_api_update_8107

[error] Exception thrown while performing a schema update. SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'data' used in key specification without a key length: ALTER TABLE "search_api_task" ADD UNIQUE KEY `task__unique` (`type`, `server_id`, `index_id`, `data`);

The Table alter to add the new keys (in specific) `data` is not passing a length and since data is of variable length that is invalid MYSQL

https://www.drupal.org/files/issues/2021-05-08/3193690-15--duplicate_tas...

Wonder if anyone has updated an existing one with this patch?

The offending code is this schema

protected function getEntitySchema(ContentEntityTypeInterface $entity_type, $reset = FALSE): array {
+ $schema = parent::getEntitySchema($entity_type, $reset);
+
+ if ($data_table = $this->storage->getBaseTable()) {
+ $schema[$data_table]['unique keys'] += [
+ 'task__unique' => ['type', 'server_id', 'index_id', 'data'],
+ ];
+ }
+
+ return $schema;
+ }

Since CORE mysql driver defines

protected function createKeySql($fields) {
    $return = [];
    foreach ($fields as $field) {
      if (is_array($field)) {
        $return[] = '`' . $field[0] . '`(' . $field[1] . ')';
      }
      else {
        $return[] = '`' . $field . '`';
      }
    }
    return implode(', ', $return);
  }

I wonder if you can pass an array there instead of just the key?

Same as #19

I get the same error when doing updatedb. We use MariaDB 10.3.29.

Very strange, I don’t have that problem with MariaDB 10.6.4. However, luckily the Drupal test bot caught this, too: see #3228210: Fix current test failures.

PostgreSQL 9.6 has similar problem:

Exception thrown while performing a schema update. SQLSTATE[54000]: Program limit exceeded: 7 ERROR: index row requires 16216 bytes, maximum size is 8191: ALTER TABLE {search_api_task} ADD CONSTRAINT search_api_task__task__unique__key UNIQUE (type,server_id,index_id,data);

#3247781: Database update fails, database schema problem on latest release