Grafana system database Azure Database for MySQL - Incorrect table definition

Grafana Installation
1

Hi I am trying to set up grafana in a docker container using grafana/grafana:latest-ubuntu image. The container will be hosted in Azure Container Apps.

I want to use ‘Azure Database for MySQL’ as the location for Grafana’s internal database to make configuration persist across container restarts.

I have created a mysql instance and created a database called ‘grafana’ inside that. I have added GF_DATABASE_TYPE, GF_DATABASE_HOST, GF_DATABASE_USER,
GF_DATABASE_PASSWORD as environment variables and it appears to be connecting to the db.

Also turned on GF_DATABASE_LOG_QUERIES for extra log info.

However the container fails to start. In the logs the following message:
“Error 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key”

I think it is for table seed_assigment.

Any help would be appriciated.

Full logs:
2023-10-07T13:43:45.09884 Connecting to the container ‘grafana’…
2023-10-07T13:43:45.11997 Successfully Connected to container: ‘grafana’ [Revision: ‘grafana–hoklwn5’, Replica: ‘grafana–hoklwn5-6859f67b79-tp829’]
2023-10-07T13:43:17.322800042Z logger=settings t=2023-10-07T13:43:17.322762854Z level=info msg=“Path Plugins” path=/var/lib/grafana/plugins
2023-10-07T13:43:17.322810592Z logger=settings t=2023-10-07T13:43:17.322772593Z level=info msg=“Path Provisioning” path=/etc/grafana/provisioning
2023-10-07T13:43:17.322813888Z logger=settings t=2023-10-07T13:43:17.322777872Z level=info msg=“App mode production”
2023-10-07T13:43:17.323036969Z logger=sqlstore t=2023-10-07T13:43:17.322979412Z level=info msg=“Connecting to DB” dbtype=mysql
2023-10-07T13:43:17.342195197Z logger=sqlstore.xorm t=2023-10-07T13:43:17.342032893Z level=error msg=ShowSQL show=show caller=
2023-10-07T13:43:17.347760443Z logger=sqlstore.xorm t=2023-10-07T13:43:17.347635143Z level=info msg=“[SQL] SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=? and TABLE_NAME=? interface {}{"grafana", "migration_log"} - took: 4.98181ms” caller=
2023-10-07T13:43:17.349975848Z logger=sqlstore.xorm t=2023-10-07T13:43:17.349891744Z level=info msg=“[SQL] SELECT id, migration_id, sql, success, error, timestamp FROM migration_log - took: 2.107621ms” caller=
2023-10-07T13:43:17.358079727Z logger=sqlstore.xorm t=2023-10-07T13:43:17.357979073Z level=info msg=“[SQL] SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=? and TABLE_NAME=? interface {}{"grafana", "migration_log"} - took: 3.665877ms” caller=
2023-10-07T13:43:17.359819175Z logger=sqlstore.xorm t=2023-10-07T13:43:17.359724932Z level=info msg=“[SQL] SELECT id, migration_id, sql, success, error, timestamp FROM migration_log - took: 1.647384ms” caller=
2023-10-07T13:43:17.366657369Z logger=sqlstore.xorm t=2023-10-07T13:43:17.366557737Z level=info msg=“[SQL] SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=? and TABLE_NAME=? interface {}{"grafana", "migration_log"} - took: 3.318683ms” caller=
2023-10-07T13:43:17.367913398Z logger=sqlstore.xorm t=2023-10-07T13:43:17.367806981Z level=info msg=“[SQL] SELECT id, migration_id, sql, success, error, timestamp FROM migration_log - took: 1.169013ms” caller=
2023-10-07T13:43:17.372213907Z logger=migrator t=2023-10-07T13:43:17.372142254Z level=info msg=“Starting DB migrations”
2023-10-07T13:43:17.421317922Z logger=sqlstore.xorm t=2023-10-07T13:43:17.421209313Z level=info msg=“[SQL] SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=? and TABLE_NAME=? interface {}{"grafana", "migration_log"} - took: 49.02505ms” caller=
2023-10-07T13:43:17.423007902Z logger=sqlstore.xorm t=2023-10-07T13:43:17.422835199Z level=info msg=“[SQL] SELECT id, migration_id, sql, success, error, timestamp FROM migration_log - took: 1.51639ms” caller=
2023-10-07T13:43:17.430376366Z logger=migrator t=2023-10-07T13:43:17.430300555Z level=info msg=“Executing migration” id=“add primary key to seed_assigment”
2023-10-07T13:43:17.436866086Z logger=sqlstore.xorm t=2023-10-07T13:43:17.436768079Z level=info msg=“[SQL] ALTER TABLE seed_assignment ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id) - took: 6.439151ms” caller=
2023-10-07T13:43:17.436880263Z logger=migrator t=2023-10-07T13:43:17.436818795Z level=error msg=“Executing migration failed” id=“add primary key to seed_assigment” error=“Error 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key”
2023-10-07T13:43:17.436886996Z logger=migrator t=2023-10-07T13:43:17.436829105Z level=error msg=“Exec failed” error=“Error 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key” sql=“code migration”
2023-10-07T13:43:17.448557019Z logger=sqlstore.xorm t=2023-10-07T13:43:17.448456463Z level=info msg=“[SQL] INSERT INTO migration_log (migration_id,sql,success,error,timestamp) VALUES (?, ?, ?, ?, ?) interface {}{"add primary key to seed_assigment", "code migration", false, "Error 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key", "2023-10-07 13:43:17"} - took: 11.581292ms” caller=
2023-10-07T13:43:17.449503248Z Error: ✗ migration failed (id = add primary key to seed_assigment): Error 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

2

Hi tomb2be,

We had the same issues when upgrading Azure flexible server from 5.7 to 8.0.21.

We found that Azure by default sets parameter sql_generate_invisible_primary_key=ON which will add a column called my_row_id to created tables (since it is AUTO_INCREMENT).

Grafana will try to ALTER and also add another primary key (id) which is also AUTO_INCREMENT and will create a conflict since there can only be one auto column which will give an error → incorrect table definition.

Solution:
Set sql_generate_invisible_primary_key=OFF

You can either do it from the Azure user interface by clicking on the server resource, under settings press server parameters, press All and search for sql_generate_invisible_primary_key and switch from ON to OFF.

Or if you are using terraform to deploy everything and then just add a resource connected to the mysql flexible server and apply:

resource "azurerm_mysql_flexible_server_configuration" "sql_generate_invisible_primary_key" {
  name                 = "sql_generate_invisible_primary_key"
  resource_group_name  = azurerm_resource_group.resource_group.name
  server_name          = azurerm_mysql_flexible_server.mysql.name
  value                = "Off"
}

I hope this help you and that it is not much difference if you are using Mysql server and not Mysql flexible server.

BR
Dimitrios

3

Thanks Dimitrios. That worked perfectly