microsoft / azure-redcap-paas

Automated deployment of REDCap with Azure Blob storage as the storage back-end
MIT License
29 stars 59 forks source link

Support disabling sql_generate_invisible_primary_key via MySQL configuration entry (resolves MySQL 8.x issue as reported by REDCap database checks) #52

Open epopisces opened 11 months ago

epopisces commented 11 months ago

As reported in the REDCap Community forums, building REDCap from scratch when using the latest 8.x version of MySQL is (sporadically) resulting in the following error: YOUR REDCAP DATABASE STRUCTURE IS INCORRECT

The solution is to set the sql_generate_invisible_primary_key value on the MySQL flexible server to 'OFF' prior to running the REDCap install script for the database.

Via Terraform these could be implemented via an azurerm_mysql_flexible_server_configuration resource:

resource "azurerm_mysql_flexible_server_configuration" "disable_invisible_primary_key" {
  resource_group_name = azurerm_resource_group.redcap.name
  server_name         = azurerm_mysql_flexible_server.redcap.name
  name                = "sql_generate_invisible_primary_key"
  value               = "OFF"
}

I have not yet tested whether this has an impact when using prior version of MySQL: if so it could include a count = startswith(var.mysqlVersion, "8") ? 1 : 0. Or even better such a conditional used in a locals block should add a map an argument to a merge() function such as suggested in #50 (preferred).

SvenAelterman commented 11 months ago

This has been resolved in the Bicep code. It does not work with SQL versions prior to 8.0.30.