Database server table creations fail if sql_require_primary_key is set

Thanks so much mglaman for the workarount #3 🐛 Database server table creations fail if sql_require_primary_key is set Needs review

I've added the below on the settings.php as I don't have access to mysql configuration.

$databases['default']['default']['init_commands'] = array(
  'primary_key' => "SET SESSION sql_require_primary_key=0"
);

I am running into a similar issue using MySQL managed database service in Azure. When I try to save my index, or try to index content, I get this error:

Drupal\search_api\SearchApiException: Cannot add primary key to table 'search_api_db_INDEX_NAME_search_api_datasource_2': primary key already exists. in Drupal\search_api_db\Plugin\search_api\backend\Database->fieldsUpdated() (line 1187 of /var/www/html/modules/contrib/search_api/modules/search_api_db/src/Plugin/search_api/backend/Database.php).

I notice that it creates the table, but then fails on the primary key part. Then next time I try to index content or save the index, it creates new tables with _X appended (1, 2, 3.. n) like this search_api_db_INDEX_NAME_search_api_datasource_2.

Then I tried applying the patch in #11. With this I get the following error:

Drupal\search_api\SearchApiException: SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key. In @@sql_generate_invisible_primary_key=ON mode table should have a primary key. Please add a new primary key to be able to drop existing primary key.': ALTER TABLE "search_api_db_INDEX_NAME_search_api_datasource" DROP PRIMARY KEY; Array
(
)
 in Drupal\search_api_db\Plugin\search_api\backend\Database->fieldsUpdated() (line 1188 of /var/www/html/modules/contrib/search_api/modules/search_api_db/src/Plugin/search_api/backend/Database.php).

So I reverted the patch and tried adding the workaround in settings.php from #8. Then when I try to clear cache with drush cr and I get:

SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

I get the same error when I try to run SET SESSION sql_require_primary_key=0 directly in mysql.

@smulvih2: Thanks a lot for your feedback!
Then I guess we really have to make sure to always create tables already with the correct primary key. Created an MR for that, please test/review!

Would be great if some of you could test this to make sure this would now finally work reliably.

@smulvih2 I had the same issue with Azure service and I managed it thanks to the answer from the following post (configuring Azure instance) https://drupal.stackexchange.com/questions/316995/multiple-primary-key-d...

@FTE
Confirming that turning sql_generate_invisible_primary_key off on Azure Flexible server fixed the issue on our site.

new version

@ostry.sn: Could you please try out the changes in my MR to see if they fix the problem for you, too?

The same goes for everyone else here. Did you try out the MR before changing the DB server settings? I don’t think it’s a good solution to let users stumble over this and then google until they can resolve it if we might be able to provide code that just works for everyone.

Hi everyone,

I was facing the issue with an Azure instance with the sql_generate_invisible_primpary_key ON. As the client has 300+ websites, turning off the flag was not an option.

I can confirm the MR worked fine! I was able to run pending tasks, re-index content, and so on.

@cassioalmeida: Great to hear, thanks a lot for reporting back!

Anyone else who wants to still test the MR? Would be optimal to get a second confirmation.

(I’d also love to run the tests with the other DBMSs, but unfortunately pipelines don’t seem to run correctly in issue forks, at least for this project.)

Thanks @ostry.sn. I had an issue with `sql_generate_invisible_primary_key` being set to "On" in my Azure DB - applying patch #19 has resolved this.

Sorry took a while to test this MR as my project switched to a search_api_solr backend. Was able to test the MR and it works for me, no longer seeing unexpected database tables with _n added to the name, and the tables are being created correctly.

@smulvih2: Good to hear, thanks a lot for reporting back!
Merged. Thanks again, everyone!

Automatically closed - issue fixed for 2 weeks with no activity.