Redshift: Unable to create uppercase schema history table

[Punnapulusu]

Which version and edition of Flyway are you using?

5.0.7

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Command Line

Which database are you using (type & version)?

Redshift and MS SQL

Which operating system are you using?

RHEL

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)
Using the -table as a command line argument for the custom schema history table ..
It creates the schema history table that needs to be created but keeps trying again and again .. and then says that the table already exists and throws out error

Database: jdbc:redshift://uxs-eswa-talc-redshift-dip-int-auto.xxxxx.xxxx.com:5439/talc_dip_int_auto (PostgreSQL 8.0)
Creating Schema History table: "talc_dip_int_auto"."NBA"
ERROR:
Script failed

SQL State : 42P07
Error Code : 500310
Message : Amazon Invalid operation: Relation "NBA" already exists;
Line : 17
Statement : CREATE TABLE "talc_dip_int_auto"."NBA" (
"installed_rank" INT NOT NULL SORTKEY,
"version" VARCHAR(50),
"description" VARCHAR(200) NOT NULL,
"type" VARCHAR(20) NOT NULL,
"script" VARCHAR(1000) NOT NULL,
"checksum" INTEGER,
"installed_by" VARCHAR(100) NOT NULL,
"installed_on" TIMESTAMP NOT NULL DEFAULT getdate(),
"execution_time" INTEGER NOT NULL,
"success" BOOLEAN NOT NULL
)

What did you expect to see?

it should create the schema history table and pass on to the next step or migrating the SQL's

What did you see instead?

SQL State : 42P07
Error Code : 500310
Message : Amazon Invalid operation: Relation "NBA" already exists;
Line : 17
Statement : CREATE TABLE "talc_dip_int_auto"."NBA" (
"installed_rank" INT NOT NULL SORTKEY,
"version" VARCHAR(50),
"description" VARCHAR(200) NOT NULL,
"type" VARCHAR(20) NOT NULL,
"script" VARCHAR(1000) NOT NULL,
"checksum" INTEGER,
"installed_by" VARCHAR(100) NOT NULL,
"installed_on" TIMESTAMP NOT NULL DEFAULT getdate(),
"execution_time" INTEGER NOT NULL,
"success" BOOLEAN NOT NULL
)

[axelfontaine]

Hmm I wonder if this has to do with the table name being uppercase and Flyway not finding it when checking whether it already exists. Do you have the same result with a lowercase name?

[axelfontaine]

It indeed turned out to be an uppercase issue as Redshift internally is case-insensitive and pg_class contains the lowercase name of the table. Workaround until the fix is out: use a lowercase table name.