Search API / Database Search installation failed: Cannot add primary key to index field table

Problem/Motivation

I have installed search_api and search_api_db on my local env to work on a simple index form a node.
In my index, I've added the title and a specific boolean field_freemium of my node. On my local env, there were no problem at all for the module installation and the config import.

On my staging env, I've encountered a problem with the config import (modules installation + custom index/server configuration).
Here is the error:
[error] Drupal\search_api\SearchApiException while adding index Leçon to server Elephorm BDD: Cannot add primary key to table 'search_api_db_lecon_field_freemium': primary key already exists. in Drupal\search_api_db\Plugin\search_api\backend\Database->fieldsUpdated().

After some investigations i've found that mysql have some variables that help manage its primary keys in table creation:

• sql_require_primary_key
• sql_generate_invisible_primary_key

In my local env, these two variables were OFF. In staging, the sql_generate_invisible_primary_key was ON in MySql and that's why it cause the error.
During the config import, Drupal tries to create a table with no primary key, so MySql creates a default one. Then, Drupal try to add its own primary key and it throw the above error.

My environement:

• Mysql 8
• Drupal 9.5.11
• Search API module 1.30.0

Proposed resolution

In my case, I've just set the sql_generate_invisible_primary_key OFF on my staging env and it worked.

This issue does not really talk only about Search Api but I thought it could be nice to have a trace of this issue. You can see more about this type of error in the related issues section. Maybe we could take so time to look what has been done in the core to handle this problem in order to apply it on Search API.