Database update fails, database schema problem on latest release

You beat me to posting this. I will add the update hook descriptions:

------------ ----------- --------------- ------------------------------------
Module Update ID Type Description
------------ ----------- --------------- ------------------------------------
search_api 8107 hook_update_n 8107 - Add a unique index to the
task entity type's storage.
search_api 8108 hook_update_n 8108 - Add configuration for boost
factors.
search_api 8109 hook_update_n 8109 - Enable index entity
references option by default on
existing indexes.
------------ ----------- --------------- ------------------------------------

Can confirm. Fixing the version on composer.json to 1.20.0 fixed for now.

I didn't have the error when updating my staging server, but it occured on my production site.
PRODUCTION:

20:13:24 ---------------- ----------- --------------- ---------------------------------
20:13:31 Module Update ID Type Description
20:13:31 ---------------- ----------- --------------- ---------------------------------
20:13:31 entity_browser 8201 hook_update_n 8201 - Updates entity browsers
20:13:31 to use the new media image
20:13:31 upload widget plugin.
20:13:31 search_api 8107 hook_update_n 8107 - Add a unique index to
20:13:31 the task entity type's storage.
20:13:31 search_api 8108 hook_update_n 8108 - Add configuration for
20:13:31 boost factors.
20:13:31 search_api 8109 hook_update_n 8109 - Enable index entity
20:13:31 references option by default on
20:13:31 existing indexes.
20:13:31 ---------------- ----------- --------------- ---------------------------------
20:13:31
20:13:31
20:13:31 // Do you wish to run the specified pending updates?: yes.
20:13:31
20:13:31 > [notice] Update started: search_api_update_8107
20:13:34 > [error] Exception thrown while performing a schema update. SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes: ALTER TABLE "search_api_task" ADD UNIQUE KEY `task__unique` (`type`, `server_id`, `index_id`, `data`(255)); Array
20:13:34 > (
20:13:34 > )
20:13:34 >
20:13:34 > [error] Update failed: search_api_update_8107
20:13:34 > [notice] Update started: entity_browser_update_8201
20:13:34 > [notice] Update completed: entity_browser_update_8201
20:13:34 [error] Update aborted by: search_api_update_8107
20:13:39 [error] Finished performing updates.

STAGING:
17:35:03 ---------------- ----------- --------------- ---------------------------------
17:35:11 Module Update ID Type Description
17:35:11 ---------------- ----------- --------------- ---------------------------------
17:35:11 entity_browser 8201 hook_update_n 8201 - Updates entity browsers
17:35:11 to use the new media image
17:35:11 upload widget plugin.
17:35:11 search_api 8107 hook_update_n 8107 - Add a unique index to
17:35:11 the task entity type's storage.
17:35:11 search_api 8108 hook_update_n 8108 - Add configuration for
17:35:11 boost factors.
17:35:11 search_api 8109 hook_update_n 8109 - Enable index entity
17:35:11 references option by default on
17:35:11 existing indexes.
17:35:11 ---------------- ----------- --------------- ---------------------------------
17:35:11
17:35:11
17:35:11 // Do you wish to run the specified pending updates?: yes.
17:35:11
17:35:11 > [notice] Update started: search_api_update_8107
17:35:15 > [notice] Update completed: search_api_update_8107
17:35:15 > [notice] Update started: search_api_update_8108
17:35:15 > [notice] Update completed: search_api_update_8108
17:35:15 > [notice] Update started: entity_browser_update_8201
17:35:15 > [notice] Update completed: entity_browser_update_8201
17:35:15 > [notice] Update started: search_api_update_8109
17:35:15 > [notice] Enabled "Track changes in referenced entities" on all existing search indexes.
17:35:15 > [notice] Update completed: search_api_update_8109
17:35:15 [success] Finished performing updates.

+1

If you look at the timestamps on my earlier message, the staging server updates succeeded, but when I moved the same code to production a few hours later, the database update failed. Subseqently, I copied the production database to a local site, and all three database updates worked. I then tried again on production, and the database update 8107 failed. So, I will drop back to search_api 1.20.

I ran into this issue as well. After a little digging the issue lies (for me) in my MySQL configuration.

If you installed, like me, your site on MySQL <5.7:
By default MySQL uses the character set utf8 which means that we use 3 bytes for every 1 character. This means, column type of varchar(10) uses 30 bytes resulting in the max prefix size for compact row format to be equivalent to varchar(255). That is 255 * 3bytes = 765 bytes which is, two bytes less than the max of 767 bytes.

With innodb_large_prefix set to on and using row format COMPRESSED or DYNAMIC, you can increase the max prefix character size to 65536 bytes instead of 767 bytes. The below chart shows the max character length with InnoDB large prefix and [COMPRESSED| DYNAMIC] row formats. These values, expect for utf8mb4, are higher than the maximum row size of a table, so there is no way to hit these limits.

I've updated MySQL to 5.7, but my tables still had the COMPACT row format. You can check this by running "SHOW TABLE STATUS;". This will show the row format of your tables. If this is set to COMPACT, you have to update your tables to DYNAMIC.

You can check the default by running: "show global variables like 'innodb_default_row_format';". On <5.7 the default is COMPACT, so all tables will be created as COMPACT. In 5.7 the default is DYNAMIC.

https://community.pivotal.io/s/article/Apps-are-down-due-to-the-Maximum-...

I hope this helps!

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);

+1
alike #6 - can't update.
I also can not update my db version. neither settings.
falling back to version 1.20

I also ran into this issue. Solved it by converting all tables from compact to dynamic, using the script on https://stackoverflow.com/a/22091156 which runs 'optimize table' on all tables in the database.

Always test on non-production first ;)

I also ran into this issue and was able to get past it by running a query to alter the ROW_FORMAT of the search_api_task table. For me, the query was ALTER TABLE search_api_task ROW_FORMAT=dynamic;, which I ran using SequelAce connected to my hosting provider.
Once I ran that, I was able to run the database updates.

With that being said, I wonder if this should be patched to update search_api_update_8107 to include the query that changes the ROW_FORMAT if it is not set as dynamic.

I would like the thoughts of others running into this issue before putting in the effort of writing a patch.

#11 fixed this issue for me. Thanks!

Also same issue, had to add "set global innodb_large_prefix=on;" also. Tested on dev and staging environment

Commands used:
set global innodb_large_prefix=on;
ALTER TABLE search_api_task ROW_FORMAT=dynamic;
OPTIMIZE TABLE search_api_task;

Thanks for fixinfo!

These solutions are not universal and thus can't be the part of the module.

I've found meeting the database requirements for Drupal 9 fixes this issue. The requirements are here: https://www.drupal.org/docs/understanding-drupal/how-drupal-9-was-made-a...

Unfortunately, Search API is still ^8.8 || ^9.

FYI this is causing tests on other modules to fail, e.g. https://www.drupal.org/pift-ci-job/2234340

Adding static patch so it can be used securely with composer patches

@DamienMcKenna please review

@maximpodorov That is disappointing that the fixes aren't universal, however I fully understand that only putting a fix for one DB type in when this could cause issues on multiple is not the best solution. For the time being, I'm going to recommend the query I added in #11 to my coworkers as we run into this issue or to not update the module just yet.

Tests fails are unrelated to the issue. Back to needs review

P.S. It's weird that tests cannot be run when using MR

I also confirm that #11 works. Thank you dorficus.

#11 fixed the problem for me also! Big thanks

#11 worked for me, thanks a lot!

#11 worked for me.

#11 fixed for me too!

In our case, we had to use these commands to make it work on our MySQL 5.5 instance:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;

logout & login (to get the global values)

ALTER TABLE search_api_task ROW_FORMAT=dynamic;
OPTIMIZE TABLE search_api_task;

#11 did fix it. What I did was create a dump of the database and then import it back in with the current defaults. That converted all tables to the new dynamic row format as and maybe other defaults as well.

#21 fixed the problem for us

It does fix the update yes, but a partial unique index really does seem weird. Are we sure that there is no risk that data is longer and the first N characters (be that 255 or 191) are the same? If that risk does not exist, then that would mean that there's no need for data to be longer anyway?

So, limiting to 255 was not an issue - but limiting to 191 is? :)

I should mention that an easier method if you're using the solution I posted in #11 is to just run the drush command drush sqlq "ALTER TABLE search_api_task ROW_FORMAT=dynamic;"

It's a little easier than trying to tunnel in using something like Sequel Ace, in my opinion.

Same problems here... #35 worked for me.

Thanks!

Even with patch #21 I get:

 ------------ ----------- --------------- -------------------------------- 
  Module       Update ID   Type            Description                     
 ------------ ----------- --------------- -------------------------------- 
  search_api   8107        hook_update_n   Add a unique index to the task  
                                           entity type's storage.          
  search_api   8108        hook_update_n   Add configuration for boost     
                                           factors.                        
  search_api   8109        hook_update_n   Enable index entity references  
                                           option by default on existing   
                                           indexes.                        
 ------------ ----------- --------------- -------------------------------- 


 // Do you wish to run the specified pending updates?: yes.                                                             

>  [notice] Update started: search_api_update_8107
>  [error]  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'updateIndex-solr-content_index-NULL' for key 'task__unique': ALTER TABLE {search_api_task} ADD UNIQUE KEY `task__unique` (`type`, `server_id`, `index_id`, `data`(191)); Array
> (
> )
>  
>  [error]  Update failed: search_api_update_8107 
 [error]  Update aborted by: search_api_update_8107 
 [error]  Finished performing updates. 

Hi everyone,

Thanks a lot for the feedback and help on this issue.

Reporting back after encountering the same issue with the following configuration:

• Database System: MySQL, Percona Server, or equivalent
• Database version: 5.7.35-38-log
• PHP: 7.3
• Drupal: 9.2.10
• Search API: 1.21

Rolled-back for now to 1.20, until a clear solution appears, whether upgrading the DB system, patching or altering table.

Thanks in advance!

#11 has worked for me as well.

Upgrading to MariaDB 10.4 worked for me.

For those on Pantheon, this can be done in your pantheon.yml like so;

database:
  version: 10.4

+1 on #40 for the Pantheon peeps here.

FYI Pantheon announced today that later this month they are going to be providing a way of upgrading to MariaDB 10.4 from within the dashboard, but for now per #40 updating the pantheon.yml file is a relatively quick way of accomplishing this. A coworker mentioned that it can take a while for the database upgrade to run, so it's recommended to do that upgrade separate to any other deployments.

IMHO this should be a "won't fix" as it only affects Drupal 8 sites, D8 is EOL, and there's a workaround available (update the database), but it would be worth mentioning this in the release notes & project page.

No, this does not only affect Drupal 8, we're seeing this when updating D9 sites. That said, as commented in #33, a partial unique index on a field seems very strange, no matter how many characters that happens to be exactly.

@Berdir, yeah, but this is the MySQL limitation. Core is doing it automatically for regular indexes (but not unique), and the entire MySQL driver need an update, as newer MySQL versions can hold more than 191/255 chars. Anyway this is the whole different story.

See https://git.drupalcode.org/project/drupal/-/blob/9.3.x/core/lib/Drupal/C...

  protected function createKeysSql($spec) {
    $keys = [];

    if (!empty($spec['primary key'])) {
      $keys[] = 'PRIMARY KEY (' . $this->createKeySql($spec['primary key']) . ')';
    }
    if (!empty($spec['unique keys'])) {
      foreach ($spec['unique keys'] as $key => $fields) {
        $keys[] = 'UNIQUE KEY `' . $key . '` (' . $this->createKeySql($fields) . ')';
      }
    }
    if (!empty($spec['indexes'])) {
      $indexes = $this->getNormalizedIndexes($spec);
      foreach ($indexes as $index => $fields) {
        $keys[] = 'INDEX `' . $index . '` (' . $this->createKeySql($fields) . ')';
      }
    }

    return $keys;
  }

  /**
   * Gets normalized indexes from a table specification.
   *
   * Shortens indexes to 191 characters if they apply to utf8mb4-encoded
   * fields, in order to comply with the InnoDB index limitation of 756 bytes.
   *
   * @param array $spec
   *   The table specification.
   *
   * @return array
   *   List of shortened indexes.
   *
   * @throws \Drupal\Core\Database\SchemaException
   *   Thrown if field specification is missing.
   */
  protected function getNormalizedIndexes(array $spec) {
    $indexes = $spec['indexes'] ?? [];
    foreach ($indexes as $index_name => $index_fields) {
      foreach ($index_fields as $index_key => $index_field) {
        // Get the name of the field from the index specification.
        $field_name = is_array($index_field) ? $index_field[0] : $index_field;
        // Check whether the field is defined in the table specification.
        if (isset($spec['fields'][$field_name])) {
          // Get the MySQL type from the processed field.
          $mysql_field = $this->processField($spec['fields'][$field_name]);
          if (in_array($mysql_field['mysql_type'], $this->mysqlStringTypes)) {
            // Check whether we need to shorten the index.
            if ((!isset($mysql_field['type']) || $mysql_field['type'] != 'varchar_ascii') && (!isset($mysql_field['length']) || $mysql_field['length'] > 191)) {
              // Limit the index length to 191 characters.
              $this->shortenIndex($indexes[$index_name][$index_key]);
            }
          }
        }
        else {
          throw new SchemaException("MySQL needs the '$field_name' field specification in order to normalize the '$index_name' index");
        }
      }
    }
    return $indexes;
  }

This bug has a history in core

As per #37, patch #21 doesn't seem to fix the issue.

Back to Active.

Thanks!

I am having a similar issue here - on D9 using pantheon. It is a new site build and I am unable to move the database from a dev to test environment due to the database error on search_api tables in this ticket. #40 did not work for me (I was already using 10.4).

@DYdave, #37 displays a totally unrelated fail, back to RTBC

I am experiencing this issue as well on Mariadb 10.4. Patch #21 does not resolve the issue for me.

We ran into this issue. Solved it by converting all tables row format from compact to dynamic.

#11 solves the issue from my side. Thanks dorficus

#21 worked for us

This happens on Pantheon. We cannot just convert the database at this time.

> IMHO this should be a "won't fix" as it only affects Drupal 8 sites, D8 is EOL, and there's a workaround available (update the database), but it would be worth mentioning this in the release notes & project page.

Not true - just updated to 9.3.0 and applied this update and saw it. Plus, we are currently on MariaDB 10.0 on Pantheon and cannot just upgrade it at this point in time.

The patch in #21 does allow us to deploy and update Pantheon successfully. Whether this is the right fix, I am not sure.

Patch 21 didn't fix the issue in my case.

Maybe patch 21 is meant to work for new installations only? I have the working site and patching it didn't help.

@mathiasgmeiner did you do the patch testing with clean install or did you patch the site with already existing database? And what was your config?

-------------

#11 did fix my issue though. I am able to do the backup and local restore after applying the fix #11.

My config: D9.3.0, MySQL 5.7, PHP7.4

Patch 21 didn't fix the issue in my case.

What exactly is not working? Could you provide more details? The idea is that you apply this patch and then re-run previously failed db updates

I just realized after your latest post that the reason why the patch didn't work for me was because I didn't run the update.php after applying the patch. I didn't have problem with database update before (or I was not aware of it)... only with database restore... so my focus was on restore attempt.

Sorry for noise. Reverting the issue status back to where it was before my #55 change.

Thanks for your last comment @Taran2L.

Maybe it would be good to add one sentence into your comment where the patch #21 is - that it is needed to run database update after the patch is applied. So that others who have restore issues like me (or any other database issues with the same error message) are aware of it.

Neither The workaround in #11 or #13, nor the patch in #21 nor the combination of the these worked for me. Row style was already dynamic (see screenshot).

Anybody else have some solution?

Might be multiple problems here? I could create a separate issue if preferred.

Some background
I am trying to update an inherited drupal installation from D8 to D9. I don't feel it is wise to proceed without this update.

Output from ``drush updb``
The following updates are pending:
search_api module :
8107 - Add a unique index to the task entity type's storage.
8108 - Add configuration for boost factors.
8109 - Enable index entity references option by default on existing indexes.

Do you wish to run all pending updates? (y/n): y
Exception thrown while performing a schema update. SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {search_api_task} ADD UNIQUE KEY `task__unique` (`type`, [error]
`server_id`, `index_id`, `data`(191)); Array
(
)

Performing search_api_update_8107 [ok]
Failed: Exception thrown while performing a schema update. SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes: ALTER TABLE {search_api_task} ADD UNIQUE KEY `task__unique` (`type`, [error]
`server_id`, `index_id`, `data`(191)); Array
(
)

You are using MyISAM engine, and it has a different limits, basically length of all fields is being used, i.e.:

type => 50
server_id => 50
index_id => 50
data => 191

(50 + 50 + 50 + 191) * 3 (or 4) bytes = 1023 (1364) bytes

However, running on MyISAM is not supported by Drupal, see https://www.drupal.org/node/2278745

This sounds like a major priority issue to me.

Encountered the issue again on another server running MariaDB 10.1.45-MariaDB-0+deb9u1 Debian 9.12

Solved it by dropping the search_api_task table (which was empty), and recreating it as following, in which i removed the index and changed the charset from utf8mb4 to utf8.

CREATE TABLE `search_api_task` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) DEFAULT NULL,
  `server_id` varchar(50) DEFAULT NULL,
  `index_id` varchar(50) DEFAULT NULL,
  `data` longtext DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The base table for search_api_task entities.';

After that running drush updatedb completed successfully.

I don't know the exact inner workings from the module, but:
1. We don't output data from the index directly in the website
2. We are migrating to a new server soon anyway.

I guess changing the charset might mess up the data in the index?

#62 worked for me.

Solution #62 worked in my case.

Original values in create table query :
`data` longtext,
CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

New values as per #62 comment:
`data` longtext DEFAULT NULL,
CHARSET=utf8

Thanks a lot to everyone here who is exchanging information and trying to help resolve this. My apologies for not seeing this sooner – unfortunately, I’m way behind on the issue queues and with “Normal” priority there was no chance to see it sooner.

In cases where an update breaks the site, please feel free to a) set Priority to “Major” or even “Critical” (the latter especially if as many are affected as here) and b) contact me via Slack, mail or contact form if I don’t react within a week or so. (If it’s critical, I guess you can even ping me right away, to make sure I see it ASAP.)

@ NickDJM: Thanks a lot for reporting this issue!

@ Taran2L: Thanks a lot for the patch, looks great!
As it should solve the problem for a lot of people, and there is no clear way (as far as I can see) to fix the problem for Postgres as well, I already went ahead and committed it (with minimal style changes). Thanks again!
Regarding the problem testing MRs, yes, that’s pretty annoying. See #3190024: Problem with test dependencies when testing issue forks – unfortunately, no-one on the d.o dev team has seemed interested in providing support for it so far.

@ joegraduate: Thanks a lot for switching this ticket’s priority to “Major”. That way, I saw it at least in time to fix this before the next (1.22) release.

@ Berdir: You’re right, a UNIQUE key with a prefix length is pretty strange. I was worried about that, too.
While it seems pretty unlikely that this would ever actually lead to a problem, and even then it shouldn’t be a big one (as the task system isn’t critical to normal operations), it’s still an undesirable state of affairs.

@ Postgres users: Sorry that this still won’t work for you currently (for some setups, at least), but for Postgres I have even less an idea of how to fix this. (Unfortunately, Postgres doesn’t even support prefix lengths for keys – see #1148856: Postgres schema doesn't support keylength on a unique index. (Btw, I’d appreciate it if someone could help me finally get this committed!))

Maybe we should just get rid of that UNIQUE key completely? It’s really just a safety net, the other changes in #3193690-10: Duplicated task records added into the search_api_task table should already be enough to ensure no duplicate tasks are added. And if it causes this many problems, then I don’t think it’s worth the trouble.

@ everyone: Who’s in favor of just removing that UNIQUE key? Does anyone see problems with that?

@drunken monkey,

Regarding the problem testing MRs, yes, that’s pretty annoying. See #3190024: Problem with test dependencies when testing issue forks – unfortunately, no-one on the d.o dev team has seemed interested in providing support for it so far.

afaik, just add them a require-dev in your composer.json, let's open a follow-up to figure it out .. I'm almost sure that I was able to run tests with forks ...

I've checked it one more time and I see a circular dependency issue: search_api requires search_api_autocomplete which requires search_api, but in 1.x version, however the forked version will be on a random branch, see

- drupal/search_api_autocomplete[dev-1.x, 1.0.0-beta2, ..., 1.5.0] require drupal/search_api 1.x -> satisfiable by drupal/search_api[dev-1.x, 1.0.0-alpha1, ..., 1.x-dev (alias of dev-1.x)] from composer repo (https://packages.drupal.org/8) but drupal/search_api[dev-merge-request-20] from vcs repo (git /var/lib/drupalci/vcsRepoDir) has higher repository priority. The packages with higher priority do not match your constraint and are therefore not installable. See https://getcomposer.org/repoprio for details and assistance.

I guess this has changed since with Composer 2 intro ... let's maybe create a follow up

And THANKS for finally committing this

OK, of course I was a bit too hasty and even created a release with this commit before finally remembering that there are no automatic entity schema updates anymore.
So, this would actually have needed an update hook to work correctly. The impact is minimal, though, mostly just the annoying error on the status report – and only for people who had already updated to 1.21 successfully before.
So, I think we should probably first figure out how to proceed here before supplying that update hook (and maybe then just creating a quick and small new release).

@ Taran2L: Wow, thanks a lot for figuring that out, that’s the first helpful insight I received for the MR problem!
Instead of creating a new issue, though, you can just reply in the existing one – if it turns out we can resolve this within the Search API (or Search API Autocomplete) module, we can still move the issue.

@drunken monkey yeah :) we just noticed our builds failing as we have coverage to ensure we never have entity definitions that need changing.

What would be the problem with providing the update hook in a patch release (or a regular release cause it's not yet using semver)?

Thanks

I would like to add that the https://www.drupal.org/project/devel_entity_updates module helped in this case.

I have installed it and run "drush entup" afterwards - and the status warning about the "search_entity_task" entity witch needs an update is fixed.

Whatever code snippet this module is using during the "drush entup" command can be used probably for our update hook needed here.

What would be the problem with providing the update hook in a patch release (or a regular release cause it's not yet using semver)?

None at all. However, I think we should first decide whether the UNIQUE key should just be dropped again, in order to not have the same problem right after the release again, and be in need of a third release within weeks.

Do you have any opinion on that?

Are there going to be any issues with 1.22 for people who altered the table to dynamic? Or does it not matter?

I have the same error on all the websites where I have updated to 1.22 :

OK, if no-one else has an opinion on this, and since I’d like to resolve this (and create a new release without that problem) ASAP, I suggest just dropping that problematic UNIQUE key again. The tiny benefit doesn’t seem to compare well to the numerous problems it has caused. Also, it seems the cleanest solution, which will work for all DBMSs.

Please review/test and comment on the attached patch! If it works for everyone and no-one objects within the next few days, I’ll commit it.
(Please run the database updates after applying the patch. Afterwards, the error on the “Status reports” page should vanish.)

Are there going to be any issues with 1.22 for people who altered the table to dynamic? Or does it not matter?

I don’t think so, no. But I haven’t checked.

Hi @drunken, I confirm that your patch works very well, thank you :-)

I ran into this, and the command in #11 worked.

It's a bit of a frustrating type bug, because I think depending on various factors, it can potentially not show up on lower environments and then raise it's head on prod, if the prod DB was created when your hosting had an older version of mysql, and then if mysql got updated everywhere the DBs might have been loaded fresh from a dump on all the lower environments while that might not be the case on prod.

+1 for #75, thank you very much for your work on this @drunken monkey!

#75 got rid of the error on the status report. I reindexed my site and ran a few test searches. Everything looked good.

#75 works cleanly for me too. I re-indexed for the sake of it and search still seems to be working fine. Thank you @drunken monkey.

Thanks for all the feedback, good to hear this works. Then let’s just do it like that.

Committed and created both a change record and a new patch release.

Thanks again, everyone!

Sorry for bumping this again, but it seems this solution still doesn’t work for everyone: #3260230-6: Error on search_api_update_8110.
Also, I thought it would probably make sense to not even add that index in the first place in case someone updates from an earlier module version?

In any case, it would be very helpful to get some people to test or look at that patch before committing, so we don’t introduce yet another bug in connection with this cursed index. (However, as the patch just changes the existing search_api_update_8110() function, testing only makes sense for people who have not yet installed that update – or, even better, not even update #8107.)

Hey There all,

Having encountered this problem on Acquia Hosting with a client, we discovered that simply un-installing the module, then reinstalling it, will address the issue. The problem is the database index tables that were constructed under an older version of mysql (or percona) which allowed for some construction of problematic indexes that are no longer permitted. Trying to update the module will clash with the database schema prescription, however, uninstalling, then reinstalling it, will re-create the table (actually, and obviously, all of the relevant tables) under the prescription of the updated DB schema.

I hope that helps.

Thanks,