All database tables doesn't have a primary key column

[swtch1]

When using Grafana with MariaDB with Galera cluster the database migration/creation fails because of a the setting innodb_force_primary_key=1, which is required for Galera cluster to work properly. You don't need to set up a Galera cluster to reproduce this issue, you can do it by setting that property on a normal MariaDB instance.

I have corrected the issue in PR 12970.

What Grafana version are you using?

5.2.2

What OS are you running grafana on?

RHEL 7.3

What did you do?

bin/grafana-server web

What was the expected result?

Successful service start

What happened instead?

EROR[08-17|10:29:06] Executing migration failed               logger=migrator id="Create annotation_tag table v2" error="Error 1173: This table type requires a primary key"
EROR[08-17|10:29:06] Exec failed                              logger=migrator error="Error 1173: This table type requires a primary key" sql="CREATE TABLE IF NOT EXISTS `annotation_tag` (\n`annotation_id` BIGINT(20) NOT NULL\n, `tag_id` BIGINT(20) NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;"
EROR[08-17|10:29:06] Server shutdown                          logger=server reason="Service init failed: Migration failed err: Error 1173: This table type requires a primary key"

[bergquist]

#12970 (comment)

[azhiltsov]

I faced the same issue running MySQL > 8.0.13 with sql_require_primary_key=ON
I tried to resolve it by adding new column 'id' with primary index to the tables without primaries.
However this break the tests and I couldn't figure why.
Would really appreciate if someone can help me.

[hongkongkiwi]

I'm facing the same issue, DigitalOcean Managed MySQL databases require a primary key.

So I can't use grafana with this because the tables have no primary keys defined.

Can this be fixed? Any reason for specifically not adding primary keys?

[hongkongkiwi]

I had to dump the sql schema and then add some primary keys on the missing tables then import. It would be great if this was just part of the original schema.

[Silvenga]

@marefr I'm not sure this is fixed, with an empty database:

t=2021-02-05T04:44:38+0000 lvl=info msg="Executing migration" logger=migrator id="copy alert_rule_tag v1 to v2"
t=2021-02-05T04:44:38+0000 lvl=eror msg="Executing migration failed" logger=migrator id="copy alert_rule_tag v1 to v2" error="Error 3098: The table does not comply with the requirements by an external plugin."
t=2021-02-05T04:44:38+0000 lvl=eror msg="Exec failed" logger=migrator error="Error 3098: The table does not comply with the requirements by an external plugin." sql="INSERT INTO `alert_rule_tag` (`tag_id`\n, `alert_id`) SELECT `tag_id`\n, `alert_id` FROM `alert_rule_tag_v1`"
service init failed: migration failed: Error 3098: The table does not comply with the requirements by an external plugin.

If I'm reading the code right, the temporary table used for migration doesn't have a primary key, so the migration fails.

[Silvenga]

Anyone who comes across this from Google:

• You can start Grafana targeting a temp database server.
• Dump the schema (generated by the migrations).
• Import it into your MySql cluster that requires primary keys.
• Profit!

[dansimone]

This issue was closed as of #22255, but one thing not addressed by that PR is creating all tables initially with primary keys. What Grafana does now is:

1. Create alert_rule_tag and annotation_tag tables without primary keys
2. Migrate them to new schemas that contain primary keys

However, if you are running using MySQL with sql_require_primary_key=ON, then step 1) fails out of the gate.

Is there any reason to not change things so that the initial schema created (for a fresh Grafana instance) for alert_rule_tag and annotation_tag contain primary keys.

The workaround provided by @Silvenga and others technically work, but seem like unnecessary hoops to have to jump through.