Precondition primaryKeyExists seems to not work correctly on MariaDB
Description
Environment
Mint Linux
MariaDB-Version: 10.0.34-MariaDB-0ubuntu0.16.04.1
Activity
Show:

Michael Hausl May 15, 2018 at 10:21 AM
I found some more information on that. The issue is that MariaDB does not store the name of a primary key. All PK-names are 'null', so searching for a specific primary key does not work therefore.
It looks like the primaryKeyExists precondition does not work correctly on MariaDB.
I have the following example changelog:
{{
<?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.3.xsd" logicalFilePath="temp.master.xml">
<changeSet author="mh" id="1">
<createTable tableName="TABLE_A">
<column name="COLUMN_1" type="INTEGER">
<constraints nullable="false" primaryKey="true" primaryKeyName="PK_TABLE_A"/>
</column>
<column name="COLUMN_2" type="VARCHAR2(60)">
<constraints nullable="false" primaryKey="true" primaryKeyName="PK_TABLE_A"/>
</column>
<column name="COLUMN_3" type="VARCHAR2(256)"/>
</createTable>
</changeSet>
<changeSet author="mh" id="2">
<preConditions onFail="MARK_RAN">
<primaryKeyExists tableName="TABLE_A" primaryKeyName="PK_TABLE_A"/>
</preConditions>
<dropPrimaryKey tableName="TABLE_A" constraintName="PK_TABLE_A"/>
</changeSet>
<changeSet author="mh" id="3">
<addPrimaryKey tableName="TABLE_A" constraintName="PK_TABLE_A" columnNames="COLUMN_1"/>
</changeSet>
</databaseChangeLog>
}}
Running it on an empty Firebird database, works just fine.
Running it on an empty MariaDB, gets me the following error message:
_
liquibase.exception.MigrationFailedException: Migration failed for change set temp.master.xml::3::mh:
Reason: liquibase.exception.DatabaseException: (conn:46) Multiple primary key defined
Query is : ALTER TABLE temp.TABLE_A ADD PRIMARY KEY (COLUMN_1) [Failed SQL: ALTER TABLE temp.TABLE_A ADD PRIMARY KEY (COLUMN_1)]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:78)
at liquibase.Liquibase.update(Liquibase.java:202)
at liquibase.Liquibase.update(Liquibase.java:179)
at liquibase.integration.commandline.Main.doMigration(Main.java:1205)
at liquibase.integration.commandline.Main.run(Main.java:191)
at liquibase.integration.commandline.Main.main(Main.java:129)
Caused by: liquibase.exception.DatabaseException: (conn:46) Multiple primary key defined
Query is : ALTER TABLE temp.TABLE_A ADD PRIMARY KEY (COLUMN_1) [Failed SQL: ALTER TABLE temp.TABLE_A ADD PRIMARY KEY (COLUMN_1)]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:356)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1211)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:600)
... 7 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: (conn:46) Multiple primary key defined
Query is : ALTER TABLE temp.TABLE_A ADD PRIMARY KEY (COLUMN_1)
at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:139)
at org.mariadb.jdbc.internal.util.ExceptionMapper.getException(ExceptionMapper.java:101)
at org.mariadb.jdbc.internal.util.ExceptionMapper.throwAndLogException(ExceptionMapper.java:77)
at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:224)
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:253)
at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:266)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)
... 12 common frames omitted
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Multiple primary key defined
Query is : ALTER TABLE temp.TABLE_A ADD PRIMARY KEY (COLUMN_1)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1115)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1047)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1002)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:142)
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:243)
... 14 common frames omitted
_
So the primary key does obviously not get dropped on MariaDB as it should be, which from my point of view can only originate from a non-working precondition.