Logging tables shown as 'different' incorrectly after upgrading to MariaDB 10.4.x
We recently upgraded to MariaDB 10.4.x and have been running into some problems with logging.
- \CRM_Logging_Schema::columnsWithDiffSpecs - has an exception for dealing with 'timestamp' fields that default to the current timestamp (you don't want that to happen in the logging tables) however the format for how that is described by MariaDB has changed in recent versions of MariaDB:
https://mariadb.com/kb/en/now/
When displayed in the INFORMATION_SCHEMA.COLUMNS table, a default CURRENT TIMESTAMP is displayed as CURRENT_TIMESTAMP up until MariaDB 10.2.2, and as current_timestamp() from MariaDB 10.2.3, due to to MariaDB 10.2 accepting expressions in the DEFAULT clause.
- \CRM_Logging_Schema::columnsWithDiffSpecs - is showing this as a 'difference' for all id fields where the original table is not NULLABLE, but the logging table is NULLABLE and has a default of NULL. In this screenshot the 'original' table is displayed first, followed by the logging table:
This is hitting the condition (same lines as above):
elseif ($civiTableSpecs[$col]['COLUMN_DEFAULT'] != CRM_Utils_Array::value('COLUMN_DEFAULT', $logTableSpecs[$col]) &&
!strstr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'TIMESTAMP')
) {
My guess is that the COLUMN_DEFAULT was not 'NULL' by default prior to our upgrade to MariaDB 10.4.x, or it's reporting it differently through the SHOW TABLE / COLUMNS commands? I suggest we alter the elseif to account for this case.
The symptom of these is that it tries to ALTER all the applicable ID columns every time an extension is enabled, which times out if your logging tables are large. Because it doesn't actually change the schema, it does this every time an extension is enabled/disabled. Or when fixSchemaDifferences is triggered.
PR: incoming