MISSION CONTROL: Unable to create table in MySQL DB because of the sql_require_primary_key setting

Mission Control: Table not able to be created in MySQL due to sql_require_primary_key

Problem Statement

The MySQL Database comes with the feature sql_require_primary_key. The purpose of sql_require_primary_key is to control whether MySQL requires tables to have a primary key. 

When utilizing MySQL Database with the Mission Control, we could encounter scenarios where the tables are not able to be created because the system variable 'sql_require_primary_key' is set. You might see errors like this - 

DB: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting. (SQL State: HY000 - Error Code: 3750)

Resolution

1. Disable the “sql_require_primary_key” variable
One of the options to remediate this error is to disable the “sql_require_primary_key” in the MySQL Daabase which would allow the Table to be created. 

2. In case, it is mandatory to have “sql_require_primary_key” enabled in the Database, you would need to add the following properties in the system.yaml file - 

mc:
    database:
        mysql:
            requirePrimaryKey:
                enabled: true

[or] 

Add the following property in the artifactory.system.properties file - 

JF_MC_DATABASE_MYSQL_REQUIREPRIMARYKEY_ENABLED=true