Liquibase failed in creating DATABASECHANGELOG table on Redshift

Ricto · May 17, 2023, 2:01pm

Hi there,
When running “liquibase update” command agaist Redshift from my Mac with 4.22.0 Liquibase installation, I got the following error.

[2023-05-17 09:42:41] SEVERE [liquibase.integration] ERROR: syntax error at or near "TAG"
  Position: 286 [Failed SQL: (0) CREATE TABLE test.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]
liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: ERROR: syntax error at or near "TAG"

It looks like TAG is a reserved keyword on Redshift, which caused the SQL statement failed.
Is this a bug on Liquibase? Is it possible to get around it?
Thanks!

Richard

PJatLiquibase · May 17, 2023, 6:38pm

Hi @Ricto,

Are you using the Redshift extension for Liquibase? You can find the documentation here: Using Liquibase with AWS Redshift

Once you’ve installed the liquibase-redhsift extension, it should handle the unique reserved words for Redshift, etc.

-PJ

Ricto · May 17, 2023, 9:18pm

Thanks PJ! The issue is resolved after I copied liquibase-redshift-4.22.0.jar to the liquibase/lib directory.

nivas142 · July 30, 2024, 12:06am

I am running into the same issue while trying out with githubactions. I was able to get both the liquibase and redshift drivers liquibase-redshift-4.29.0.jar : redshift-jdbc42-2.1.0.14.jar. I am running on a self hosted runner(fedora) using liquibase-github-actions/status@v4.29.0 and I don’t understand where the directory liquibase/lib exists. I tried making one from the existing directory after checkout@v4, but that doesn’t seem like working.

pgel · August 5, 2024, 6:42pm

I am also running into this similar issue as well. It just started a few days ago where everything was previously working.

We are using the liquibase/liquibase:latest docker image, along with the latest versions of the drivers - redshift-jdbc42-2.1.0.30.jar and liquibase-redshift-4.29.1.jar which are put into the /liquibase/lib directory.

Running with logLevel=FINE shows that the drivers are being loaded, and gives the following error and stack trace:

ERROR: Exception Details
[2024-08-05 17:38:30] INFO [liquibase.ui] ERROR: Exception Details
ERROR: Exception Primary Class:  RedshiftException
[2024-08-05 17:38:30] INFO [liquibase.ui] ERROR: Exception Primary Class:  RedshiftException
ERROR: Exception Primary Reason:  ERROR: current transaction is aborted, commands ignored until end of transaction block
[2024-08-05 17:38:30] INFO [liquibase.ui] ERROR: Exception Primary Reason:  ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: Exception Primary Source:  Redshift 8.0.2
[2024-08-05 17:38:30] INFO [liquibase.ui] ERROR: Exception Primary Source:  Redshift 8.0.2
[2024-08-05 17:38:30] INFO [liquibase.command] Command execution complete
[2024-08-05 17:38:30] SEVERE [liquibase.integration] Error executing SQL SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC: ERROR: current transaction is aborted, commands ignored until end of transaction block
liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at liquibase.command.CommandScope.execute(CommandScope.java:258)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:51)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:21)
        at picocli.CommandLine.executeUserObject(CommandLine.java:2045)
        at picocli.CommandLine.access$1500(CommandLine.java:148)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2465)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2457)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2419)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2277)
        at picocli.CommandLine$RunLast.execute(CommandLine.java:2421)
        at picocli.CommandLine.execute(CommandLine.java:2174)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:404)
        at liquibase.Scope.child(Scope.java:200)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:379)
        at liquibase.Scope.child(Scope.java:200)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:376)
        at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:97)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.base/java.lang.reflect.Method.invoke(Unknown Source)
        at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:116)
Caused by: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at liquibase.executor.jvm.ChangelogJdbcMdcListener.query(ChangelogJdbcMdcListener.java:62)
        at liquibase.changelog.StandardChangeLogHistoryService.queryDatabaseChangeLogTable(StandardChangeLogHistoryService.java:366)
        at liquibase.changelog.StandardChangeLogHistoryService.getRanChangeSets(StandardChangeLogHistoryService.java:311)
        at liquibase.changelog.AbstractChangeLogHistoryService.upgradeChecksums(AbstractChangeLogHistoryService.java:70)
        at liquibase.changelog.StandardChangeLogHistoryService.upgradeChecksums(StandardChangeLogHistoryService.java:295)
        at liquibase.command.core.helpers.DatabaseChangelogCommandStep.checkLiquibaseTables(DatabaseChangelogCommandStep.java:143)
        at liquibase.command.core.helpers.DatabaseChangelogCommandStep.run(DatabaseChangelogCommandStep.java:91)
        at liquibase.command.CommandScope.execute(CommandScope.java:220)
        ... 23 more
Caused by: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
        at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:230)
        at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:238)
        at liquibase.executor.jvm.JdbcExecutor.queryForList(JdbcExecutor.java:303)
        at liquibase.executor.jvm.JdbcExecutor.queryForList(JdbcExecutor.java:298)
        at liquibase.changelog.StandardChangeLogHistoryService.lambda$queryDatabaseChangeLogTable$2(StandardChangeLogHistoryService.java:366)
        at liquibase.executor.jvm.ChangelogJdbcMdcListener.lambda$query$1(ChangelogJdbcMdcListener.java:55)
        at liquibase.Scope.child(Scope.java:200)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.executor.jvm.ChangelogJdbcMdcListener.query(ChangelogJdbcMdcListener.java:55)
        ... 30 more
Caused by: com.amazon.redshift.util.RedshiftException: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at com.amazon.redshift.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2648)
        at com.amazon.redshift.core.v3.QueryExecutorImpl.processResultsOnThread(QueryExecutorImpl.java:2295)
        at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
        at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1878)
        at com.amazon.redshift.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:375)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeInternal(RedshiftStatementImpl.java:519)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:440)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeWithFlags(RedshiftStatementImpl.java:381)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeCachedSql(RedshiftStatementImpl.java:367)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeWithFlags(RedshiftStatementImpl.java:344)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeQuery(RedshiftStatementImpl.java:268)
        at liquibase.executor.jvm.JdbcExecutor$QueryStatementCallback.doInStatement(JdbcExecutor.java:551)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83)
        ... 39 more


Unexpected error running Liquibase: Error executing SQL SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC: ERROR: current transaction is aborted, commands ignored until end of transaction block

liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at liquibase.command.CommandScope.execute(CommandScope.java:258)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:51)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:21)
        at picocli.CommandLine.executeUserObject(CommandLine.java:2045)
        at picocli.CommandLine.access$1500(CommandLine.java:148)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2465)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2457)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2419)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2277)
        at picocli.CommandLine$RunLast.execute(CommandLine.java:2421)
        at picocli.CommandLine.execute(CommandLine.java:2174)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:404)
        at liquibase.Scope.child(Scope.java:200)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:379)
        at liquibase.Scope.child(Scope.java:200)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:376)
        at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:97)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.base/java.lang.reflect.Method.invoke(Unknown Source)
        at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:116)
Caused by: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at liquibase.executor.jvm.ChangelogJdbcMdcListener.query(ChangelogJdbcMdcListener.java:62)
        at liquibase.changelog.StandardChangeLogHistoryService.queryDatabaseChangeLogTable(StandardChangeLogHistoryService.java:366)
        at liquibase.changelog.StandardChangeLogHistoryService.getRanChangeSets(StandardChangeLogHistoryService.java:311)
        at liquibase.changelog.AbstractChangeLogHistoryService.upgradeChecksums(AbstractChangeLogHistoryService.java:70)
        at liquibase.changelog.StandardChangeLogHistoryService.upgradeChecksums(StandardChangeLogHistoryService.java:295)
        at liquibase.command.core.helpers.DatabaseChangelogCommandStep.checkLiquibaseTables(DatabaseChangelogCommandStep.java:143)
        at liquibase.command.core.helpers.DatabaseChangelogCommandStep.run(DatabaseChangelogCommandStep.java:91)
        at liquibase.command.CommandScope.execute(CommandScope.java:220)
        ... 23 more
Caused by: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
        at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:230)
        at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:238)
        at liquibase.executor.jvm.JdbcExecutor.queryForList(JdbcExecutor.java:303)
        at liquibase.executor.jvm.JdbcExecutor.queryForList(JdbcExecutor.java:298)
        at liquibase.changelog.StandardChangeLogHistoryService.lambda$queryDatabaseChangeLogTable$2(StandardChangeLogHistoryService.java:366)
        at liquibase.executor.jvm.ChangelogJdbcMdcListener.lambda$query$1(ChangelogJdbcMdcListener.java:55)
        at liquibase.Scope.child(Scope.java:200)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.executor.jvm.ChangelogJdbcMdcListener.query(ChangelogJdbcMdcListener.java:55)
        ... 30 more
Caused by: com.amazon.redshift.util.RedshiftException: ERROR: current transaction is aborted, commands ignored until end of transaction block
        at com.amazon.redshift.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2648)
        at com.amazon.redshift.core.v3.QueryExecutorImpl.processResultsOnThread(QueryExecutorImpl.java:2295)
        at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
        at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1878)
        at com.amazon.redshift.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:375)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeInternal(RedshiftStatementImpl.java:519)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:440)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeWithFlags(RedshiftStatementImpl.java:381)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeCachedSql(RedshiftStatementImpl.java:367)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeWithFlags(RedshiftStatementImpl.java:344)
        at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeQuery(RedshiftStatementImpl.java:268)
        at liquibase.executor.jvm.JdbcExecutor$QueryStatementCallback.doInStatement(JdbcExecutor.java:551)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83)
        ... 39 more

Anyone else experience this and figure out any solutions?

pgel · August 5, 2024, 6:46pm

It’s also worth noting that we are using Redshift Serverless, and liquibase is able to successfully connect to the database and create the databasechangelog table, and then the error occurs. it does not create the databasechangeloglock table or insert any rows into databasechangelog

pgel · August 7, 2024, 6:02pm

This appears to be a bug within liquibase v4.29
Downgrading to using liquibase/liquibase:4.28 docker image solves the problem.

It does not appear to matter if I use
liquibase-redshift-4.28.jar or liquibase-redshift-4.29.1.jar but I will use v28 for consistency.

martynmay · December 18, 2024, 7:28pm

We’re still getting the same issue on Liquibase v4.30 with Redshift. Same changelogs work when using v4.27 before. extensions are also placed correctly.

/usr/bin/internal/lib/liquibase-redshift-4.30.0.jar
/usr/bin/internal/lib/redshift-jdbc42-2.1.0.30.jar

[2024-12-18 18:34:38] INFO [liquibase.ui] ERROR: Exception Details
ERROR: Exception Primary Class:  RedshiftException
[2024-12-18 18:34:38] INFO [liquibase.ui] ERROR: Exception Primary Class:  RedshiftException
ERROR: Exception Primary Reason:  ERROR: current transaction is aborted, commands ignored until end of transaction block
[2024-12-18 18:34:38] INFO [liquibase.ui] ERROR: Exception Primary Reason:  ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: Exception Primary Source:  Redshift 8.0.2
[2024-12-18 18:34:38] INFO [liquibase.ui] ERROR: Exception Primary Source:  Redshift 8.0.2

Topic		Replies	Views
Redshift support General Discussion	18	1429	May 2, 2013
Liquibase update command fail on Redshift General Discussion	2	657	May 17, 2023
Strange things with Java 8 Liquibase Development	3	447	September 11, 2014
Liquibase failing to create a table on redshift from ADO pipelines General Discussion	1	230	August 22, 2023
Unable to tag the database using Liquibase for future rollback General Discussion	1	363	July 26, 2017

Liquibase failed in creating DATABASECHANGELOG table on Redshift

Related topics