[Amazon](500310) Invalid operation: syntax error at or near "t"

[tyler-laberge]

Liquibase Version: 3.8.7
liquibase-redshift version: 1.1
redshift driver version: RedshiftJDBC42-no-awssdk-1.2.37.1061

When running liquibase update command using the CLI the error

[Amazon](500310) Invalid operation: syntax error at or near "t"

occurs - full stack trace below

22:33:29.874 ERROR [liquibase.integration.commandline.Main]: Unexpected error running Liquibase: [Amazon](500310) Invalid operation: syntax error at or near "t" 
Position: 33;
liquibase.exception.DatabaseException: [Amazon](500310) Invalid operation: syntax error at or near "t" 
Position: 33; [Failed SQL: (500310) UPDATE public.databasechangelog t SET TAG='14-NON_PROD' FROM (SELECT DATEEXECUTED, ORDEREXECUTED FROM public.databasechangelog ORDER BY DATEEXECUTED DESC, ORDEREXECUTED DESC LIMIT 1) sub WHERE t.DATEEXECUTED=sub.DATEEXECUTED AND t.ORDEREXECUTED=sub.ORDEREXECUTED]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:402)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:59)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:131)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:111)
	at liquibase.changelog.StandardChangeLogHistoryService.tag(StandardChangeLogHistoryService.java:437)
	at liquibase.database.AbstractJdbcDatabase.tag(AbstractJdbcDatabase.java:874)
	at liquibase.Liquibase.tag(Liquibase.java:1176)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1491)
	at liquibase.integration.commandline.Main.run(Main.java:303)
	at liquibase.integration.commandline.Main.main(Main.java:163)
Caused by: java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at or near "t" 
Position: 33;
	at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
	at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
	at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)
	at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)
	at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)
	at com.amazon.redshift.client.PGMessagingContext.moveThroughMetadata(Unknown Source)
	at com.amazon.redshift.client.PGMessagingContext.getNoData(Unknown Source)
	at com.amazon.redshift.client.PGClient.directExecuteExtraMetadataWithMessage(Unknown Source)
	at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source)
	at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: syntax error at or near "t" 
Position: 33;
	... 13 common frames omitted

This issue is supposed to be fixed in #7 , however liquibase doesn't seem to be picking up the "RedshiftTagDatabaseGenerator" class which fixes the issue, and is instead just using the base TagDatabaseGenerator class.

I can see that this extension is being added to the classpath, and that it is picking up the "RedshiftDatabase", so I am not sure why it is not finding the "RedshiftTagDatabaseGenerator"

22:33:24.269 INFO  [liquibase.integration.commandline.Main]: Liquibase Community 3.8.7 by Datical
22:33:24.270 DEBUG [liquibase.integration.commandline.Main]: Adding 'file:/opt/redshift-driver/redshift-driver-1.2.37.1061.jar' to the Java classpath.
22:33:24.270 DEBUG [liquibase.integration.commandline.Main]: Adding 'file:/opt/liquibase-redshift/liquibase-redshift-1.1.jar' to the Java classpath.

22:33:25.070 DEBUG [liquibase.ext.redshift.database.RedshiftDatabase]: Connected to User@jdbc:redshift://<redshifturl>
22:33:25.070 DEBUG [liquibase.ext.redshift.database.RedshiftDatabase]: Setting auto commit to false from true

Here is the command I am running

liquibase --changeLogFile="/tmp/migrations/changelog-master.xml" --driver="com.amazon.redshift.jdbc42.Driver" \
    --databaseClass="liquibase.ext.redshift.database.RedshiftDatabase" --classpath="/opt/redshift-driver/redshift-driver-1.2.37.1061.jar:/opt/liquibase-redshift/liquibase-redshift-1.1.jar" --logLevel=debug --url="${REDSHIFT_URL}" \
    --username="${REDSHIFT_USER}" --password="${REDSHIFT_PASSWORD}" update

Any help would be appreciated.

[tyler-laberge]

I was able to get around this by using the liquibase maven plugin and moving all the dependencies into a pom file.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>...</groupId>
    <artifactId>...</artifactId>
    <version>...</version>

    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <!--Needed for redshift-jdbc42-->
    <pluginRepositories>
        <pluginRepository>
            <id>mulesoft-repo</id>
            <url>https://repository.mulesoft.org/nexus/content/repositories/public/</url>
        </pluginRepository>
    </pluginRepositories>

    <build>
        <pluginManagement>
            <plugins>
                <plugin>
                    <groupId>org.liquibase</groupId>
                    <artifactId>liquibase-maven-plugin</artifactId>
                    <version>3.8.0</version>
                    <configuration>
                        <changeLogFile>migrations/changelog-master.xml</changeLogFile>
                        <driver>com.amazon.redshift.jdbc42.Driver</driver>
                        <databaseClass>liquibase.ext.redshift.database.RedshiftDatabase</databaseClass>
                        <logLevel>debug</logLevel>
                        <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
                    </configuration>
                    <dependencies>
                        <dependency>
                            <groupId>com.amazon.redshift</groupId>
                            <artifactId>redshift-jdbc42</artifactId>
                            <version>1.2.37.1061</version>
                        </dependency>
                        <dependency>
                            <groupId>org.liquibase.ext</groupId>
                            <artifactId>liquibase-redshift</artifactId>
                            <version>1.1</version>
                        </dependency>
                    </dependencies>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>

</project>

And running with

mvn liquibase:update -Dliquibase.url=${REDSHIFT_URL} -Dliquibase.username=${REDSHIFT_USER} -Dliquibase.password=${REDSHIFT_PASSWORD} -Dliquibase.contexts="${CONTEXT}"