DO / Digital Ocean, mySQL and sql_require_primary_key using KeyCloak 19.0.2

Configuring the server
1

With the following setup, KeyCloak via Docker cannot initialize a first-run setup.
Scenario:
Digital Ocean (DigitalOcean.com) managed MySQL 8 server, Quay (19.0.2) deployed via the App service. This is a new database.

ERROR: Unable to create or change a table without a primary key, when the system variable ‘sql_require_primary_key’ is set

From what I can discern, there are missing primary keys in the table schema’s. Primary keys are required by the latest updates to mySQL 8 at DO. it also seems the only ‘potential’ route is using DO’s management API to disable a key feature of mySQL 8, which they highly advise against.

Does anyone else know of a better method to overcome this issue? Hopefully one that would work within the DockerFile config?

2

You can changes this setting easily and then change it back after the tables have been created if you are concerned about long term impact. The only downside to having this turned off is potential query performance subject to the overall table schema and query design. The following is the simple management api call:

curl -X PATCH \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
  -d '{"config": {"sql_require_primary_key": false}}' \
  "https://api.digitalocean.com/v2/databases/$DIGITALOCEAN_DATABASE_POOL_ID/config"