why no index and/or PK on table DATABASECHANGELOG?

Quote from MariaDB Galera Cluster - Known Limitations

All tables should have a primary key (multi-column primary keys are supported). DELETE operations are unsupported on tables without a primary key. Also, rows in tables without a primary key may appear in a different order on different nodes.

Galera requires that every table should have a PK, or at least an index. This limation mainly because of replication (wsrep plugin).

We operate a Galera/MariaDB cluster and I see customers with DATABASECHANGELOG which has no index and PK. I guess this table is append-only (no update or delete operations)

I don't know Liquibase, that's why I ask for the reason of missing index and/or PK here. Should I open a bug report or do I don't understand this use case?

+----------------------------------------------------------------------------------------+------------+------------+-------------+---------------------+-----------------------+
| schema                                              | table_rows | non_unique | cardinality | medium distribution | replication row reads |
+----------------------------------------------------------------------------------------+------------+------------+-------------+---------------------+-----------------------+
(...)
| xxx.DATABASECHANGELOG                               |        571 |       NULL |        NULL |            571.0000 |           326041.0000 |
| xxxx.DATABASECHANGELOG                              |        491 |       NULL |        NULL |            491.0000 |           241081.0000 |
| xxxxx.DATABASECHANGELOG                             |        433 |       NULL |        NULL |            433.0000 |           187489.0000 |
+----------------------------------------------------------------------------------------+------------+------------+-------------+---------------------+-----------------------+

Solution

Check out this Jira ticket

Liquibase was changed to not create a primary key in databasechangelog table because it introduced problems with key sizes and wasn't really necessary. I didn't put in a check and drop for an existing primary key yet. It should be dropped but doesn't cause a problem unless you are hitting an edge case where you have very long id, author and/or file paths.

There's also described a possible workaround:

A simple workaround could be to add a change set with a not primaryKeyExists pre-condition and addPrimaryKey change. A more involved workaround could be to create a plugin which overrides the CreateDatabaseChangeLogTableGenerator and/or StandardChangeLogHistoryService which implement PrioritizedService.

Below is an example of the simple workaround. I've optimized the indexes to reduce table scans, sorting and bookmark lookups on SQL Server, but it's probably equally applicable to Oracle. I was not really concerned about the maximum key length of 900 bytes on SQL Server being exceeded.

<?xml version="1.0" encoding="UTF-8" ?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="
      http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">

  <property name="liquibaseCatalogName" value=""/>
  <property name="liquibaseSchemaName" value="${database.liquibaseSchemaName}"/>
  <property name="databaseChangeLogTableName" value="${database.databaseChangeLogTableName}"/>
  <property name="liquibaseTablespaceName" value=""/>

  <changeSet id="1" author="your-name">
      <preConditions onFail="MARK_RAN">
          <primaryKeyExists
              catalogName="${liquibaseCatalogName}"
              schemaName="${liquibaseSchemaName}"
              tableName="${databaseChangeLogTableName}"
              primaryKeyName="PK_${databaseChangeLogTableName}"/>
      </preConditions>
      <dropPrimaryKey
          catalogName="${liquibaseCatalogName}"
          schemaName="${liquibaseSchemaName}"
          tableName="${databaseChangeLogTableName}"
          constraintName="PK_${databaseChangeLogTableName}"/>
  </changeSet>

  <changeSet id="2" author="your-name">
      <createIndex
          catalogName="${liquibaseCatalogName}"
          schemaName="${liquibaseSchemaName}"
          tableName="${databaseChangeLogTableName}"
          indexName="IX_${databaseChangeLogTableName}_DATEEXECUTED_ORDEREXECUTED"
          tablespace="${liquibaseTablespaceName}"
          clustered="true">

          <column name="DATEEXECUTED"/>
          <column name="ORDEREXECUTED"/>
      </createIndex>
  </changeSet>

  <changeSet id="3" author="your-name">
      <addPrimaryKey
          catalogName="${liquibaseCatalogName}"
          schemaName="${liquibaseSchemaName}"
          tableName="${databaseChangeLogTableName}"
          constraintName="PK_${databaseChangeLogTableName}"
          tablespace="${liquibaseTablespaceName}"
          clustered="false"
          columnNames="ID,AUTHOR,FILENAME"/>
  </changeSet>

</databaseChangeLog>