Liquibase without changelogs with SpringBoot 2.7.7 and GCP Spanner

General Discussion
1

I am trying to use Liquibase with SpingBoot2.7.7.
— application.properties contains
spring.liquibase.change-log=classpath:master.xml
– master.xml contains

<?xml version="1.0" encoding="UTF-8"?>



When db/migration contains 1 sql file, it is inserting a row in databasechangelog with id=raw. But, we as soon as I am adding one more file to same location, for new file as well, it is trying to create entry with id=raw, which is failing because of PK on id.

Any insight on this will be helpful.

Thanks,
Sanjeev

2

There should not be a PK on databasechangelog.id, that is not the correct key. The correct PK would be ID, Author, Filename.

3

Thanks @daryldoak for your response. However, these tables are auto generated by Liquibase library itself. When I am trying to drop this table and recreate, getting below issues:-

  1. Create databasechangelog with Unique key : SQL Error [P0001]: ERROR: constraint is not supported, create a unique index instead.
  2. Create databasechangelog without any key so that Unique key can be added later via Unique Index: SQL Error [P0001]: ERROR: Primary key must be defined for a table.

Referring to liquibase-spanner/limitations.md at master · cloudspannerecosystem/liquibase-spanner · GitHub, it seems these are Spanner limitations.

  • Unique constraints: Use UNIQUE INDEX instead of UNIQUE CONSTRAINT
  • Add/Drop primary key: Cloud Spanner requires that all tables have a primary key. The primary key must be defined when the table is created, and cannot be dropped or added later.
    Any way to recreate these tables?
4

Are you using the cloud-spanner extension?

https://contribute.liquibase.com/extensions-integrations/directory/database-tutorials/cloud-spanner/

5

I am using it with SpringBoot2.7.7. No other extension have been used.

Using Spanner with Postgres dialect

6

I’d recommend following that database tutorial for Spanner. It says you need the extension since spannner is not supported by the core product.

7

@daryldoak can you please share URL what you are pointing to? I am referring to With Maven - contribute.liquibase.com

8

The link is posted above. There are 2 links in the post, even though it looks like one.

https://contribute.liquibase.com/extensions-integrations/directory/database-tutorials/cloud-spanner/

9

Thanks @daryldoak for the information. However, it is not clear if this is also tested with Spanner with PG Dialect. Document suggest to use google-cloud-spanner-jdbc, which won’t work with PG Dialect.

Regards,
Sanjeev

10

Issue was in migration SQL scripts. One should use liquibase formatted sql and control changeset, which is unlike Flyway, which gets version etc. from file name. Below is what I added:-
–liquibase formatted sql
–changeset loite:1

Refer to Example Changelogs: SQL Format for more details