RESOLVED: Liquibase against Aurora 3 (8.0) with Instant DDL

erinlkolp · April 15, 2022, 5:23pm

Hello!

Does anyone know if Liquibase 4.2 and beyond support the Aurora 8 instant migration? It looks like from reading the documentation, we just use straight Liquibase it’ll work fine but I have found no good way to apply the “, ALGORITHM = INSTANT” to my generated sql calls. It would be super handy if I could use an attribute to turn on and off this feature.

Ref Links:

Altering tables in Amazon Aurora using fast DDL - Amazon Aurora

Tech details of our setup:

Percona MySQL 5.7 clusters using ClusterControl to monitor and perform HA.
Liquibase 3.8.9 (that’s what we run today but I’ve already got the upgrade going).
Jenkins is used to perform all migrations, fleet wide.
We leverage Docker for CI/CD.

What are ya’ll doing with your RDS Aurora migrations today?

-Erin

erinlkolp · April 15, 2022, 5:50pm

Ohhh I just found this:

<modifySql  dbms="mysql">  
     <append  value="engine innodb"/>
</modifySql>

via

Liquibase has thought of everything!

erinlkolp · April 15, 2022, 9:57pm

Am I hitting a bug with this? It’s inserting NULL into my generated sql.

liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set schema/269.xml::269-1::kbruce:
     Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULLALGORITHM=INSTANT' at line 1 [Failed SQL: (1064) ALTER TABLE elk.member_custom_fields ADD created_at datetime NULLALGORITHM=INSTANT]
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:124)
	at liquibase.Liquibase.lambda$null$0(Liquibase.java:272)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Scope.child(Scope.java:222)
	at liquibase.Liquibase.lambda$update$1(Liquibase.java:271)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Liquibase.runInScope(Liquibase.java:2369)
	at liquibase.Liquibase.update(Liquibase.java:217)
	at liquibase.Liquibase.update(Liquibase.java:203)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1802)
	at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:367)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.integration.commandline.Main$1.run(Main.java:366)
	at liquibase.integration.commandline.Main$1.run(Main.java:196)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:145)
	at liquibase.integration.commandline.Main.run(Main.java:196)
	at liquibase.integration.commandline.Main.main(Main.java:158)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set schema/269.xml::269-1::kbruce:
     Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULLALGORITHM=INSTANT' at line 1 [Failed SQL: (1064) ALTER TABLE elk.member_custom_fields ADD created_at datetime NULLALGORITHM=INSTANT]
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:672)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:49)
	at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:111)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:110)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Scope.child(Scope.java:222)
	at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:94)
	at liquibase.Scope.lambda$child$0(Scope.java:160)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.Scope.child(Scope.java:159)
	at liquibase.Scope.child(Scope.java:138)
	at liquibase.Scope.child(Scope.java:222)
	at liquibase.Scope.child(Scope.java:226)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:66)
	... 26 more
Caused by: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULLALGORITHM=INSTANT' at line 1 [Failed SQL: (1064) ALTER TABLE elk.member_custom_fields ADD created_at datetime NULLALGORITHM=INSTANT]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:82)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:150)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1278)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1260)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
	... 46 more
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULLALGORITHM=INSTANT' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:389)
	... 51 more

It’s the same behavior in 4.3.4 as it is in 4.9 so I assume it’s not a bug. I’ll keep working at it.

erinlkolp · April 15, 2022, 10:10pm

Here is my changeset, sanitized:

<?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"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xmlns:pro="http://www.liquibase.org/xml/ns/pro"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
		https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.9.xsd
		http://www.liquibase.org/xml/ns/dbchangelog-ext 
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
		http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.9.0.xsd">
    <changeSet author="elk" id="269-1">
        <preConditions onFail="MARK_RAN">
        <not>
            <columnExists tableName="custom_fields" columnName="created_at"/>
        </not>
        </preConditions>
        <addColumn tableName="custom_fields" xmlns:liquibasePercona="http://github.com/adangel/liquibase-percona" liquibasePercona:usePercona="false">
            <column name="created_at" type="DATETIME"/>
        </addColumn>
        <modifySql dbms="mysql">
            <append value="ALGORITHM=INSTANT"/>
        </modifySql>
    </changeSet>
    <changeSet author="elk" id="269-2">
        <preConditions onFail="MARK_RAN">
        <not>
            <columnExists tableName="custom_fields" columnName="updated_at"/>
        </not>
        </preConditions>
        <addColumn tableName="custom_fields" xmlns:liquibasePercona="http://github.com/adangel/liquibase-percona" liquibasePercona:usePercona="false">
            <column defaultValueComputed="CURRENT_TIMESTAMP" name="updated_at" type="TIMESTAMP">
                <constraints nullable="false"/>
            </column>
        </addColumn>
        <modifySql dbms="mysql">
            <append value="ALGORITHM=INSTANT"/>
        </modifySql>
    </changeSet>
</databaseChangeLog>

erinlkolp · April 18, 2022, 5:04pm

As silly as it is, this is my fix:

        <modifySql dbms="mysql">
            <append value=", ALGORITHM=INSTANT"/>
        </modifySql>

Topic		Replies	Views
Error running yaml changelog after using generateChangeLog on database General Discussion	1	1310	August 21, 2015
Unable to create function in Aurora postgres Liquibase Development	4	554	April 21, 2023
Migrating to 2.0-RC2-SNAPSHOT: Error creating DATABASECHANGELOG with latest src General Discussion	5	443	June 23, 2010
Error Reading Migration File: null General Discussion	9	1532	December 7, 2010
Migrate 1.9.5 to 4.1.1 General Discussion	2	1055	October 27, 2020

RESOLVED: Liquibase against Aurora 3 (8.0) with Instant DDL

Related topics