Atlas Kubernetes Operator migration baseline error

[oleg-darrow]

Hi there, and firstly, thanks for this awesome project! Im currently testing atlas as k8s operator and trying to apply AtlasMigration

As I already have the data inside my database, I firstly created the baseline file

atlas migrate diff my_baseline \                        
  --dir "file://migrations" \
  --dev-url "docker://postgres/13" \
  --to "postgres://MY_URL/MY_DB"

then i created the migration file itself and atlas.sum. Then I applied them as configmap

apiVersion: v1
kind: ConfigMap
metadata:
  name: migrations
  namespace: default
data:
  20240430_cars.sql: |-
    CREATE TABLE cars (
      brand VARCHAR(255),
      model VARCHAR(255),
      year INT
    );
  20240430130416_my_baseline.sql: |
  ...
  atlas.sum: |
    h1:XvxavNQEVsV5YOhis38sBUhE7EcQbJtD4WjZ2a/r48o=
    20240430130416_my_baseline.sql h1:opoj6rJBR24O4XrnEZrck6nFGhJ7iQyvRYzDELx0bJU=
    20240430_cars.sql h1:2D4fsS8tMnRswHQU8CS42arptXNv/lbgAggfkJ3f948=

After that I declared AtlasMigration

apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasMigration
metadata:
  name: atlasmigration-test
  namespace: default
spec:
  baseline: "20240430130416"
  dir:
    configMapRef:
      name: migrations
  execOrder: linear
  urlFrom:
    secretKeyRef:
      key: url
      name: psql-creds

But after applying, I receive an error

Error: sql/migrate: connected database is not clean: found schema "NAME". baseline version or allow-dirty is required

Could you explain what am I doing wrong ?

[giautm]

Hello, I think it somehow related to the version of the first migration file 20240430_cars.sql can you please create new migration with atlas migrate new --edit then put its content here?

[oleg-darrow]

Hi @giautm

So I did atlas migrate new --edit, then applied new configmap and now its like this:

apiVersion: v1
kind: ConfigMap
metadata:
  name: migrations
  namespace: default
data:
  20240502072340.sql: |-
    CREATE TABLE cars (
      brand VARCHAR(255),
      model VARCHAR(255),
      year INT
    );
  20240430130416_my_baseline.sql: |
  ...
  atlas.sum: |
    h1:d0/QK4uX9nll+fLt+qjJMEAz7huRFGHzLSzTown2TKc=
    20240430130416_my_baseline.sql h1:opoj6rJBR24O4XrnEZrck6nFGhJ7iQyvRYzDELx0bJU=
    20240502072340.sql h1:VSLoTZmXMjQNKHYM7FxTLbtcOKBBBGAjPTD83BDtn70=

but unfortunately its the same (i recreated AtlasMigration object for clean testing)

Error: sql/migrate: connected database is not clean: found schema "NAME". baseline version or allow-dirty is required

[giautm]

@oleg-darrow, the baseline also need to re-generate, it should happen after the first version.

[oleg-darrow]

hi again @giautm i didnt understand to the end this part it should happen after the first version, could you explain ?

but i tried to recreate all from scratch, so i deleted everything from migrations folder and i did next in order:

1. (I have migrations folder in the root of git repo)
2. new baseline file with atlas migrate diff my_baseline ...
3. new migration file with atlas migrate new --edit
4. (atlas.sum was created and calculated automatically)
5. I applied CM with kubectl create configmap migrations --from-file=migrations
6. I applied AtlasMigration object with new baseline string

and now result the same. Error: sql/migrate: connected database is not clean: found schema "NAME". baseline version or allow-dirty is required

[giautm]

Hello @oleg-darrow, sorry for unclear. Please delete the old baseline file. Then create new one.

Because Atlas base on the order of files to apply and calculate the migrations. So the first part of file name is the version number.

In the latest comment

  atlas.sum: |
    h1:d0/QK4uX9nll+fLt+qjJMEAz7huRFGHzLSzTown2TKc=
    20240430130416_my_baseline.sql h1:opoj6rJBR24O4XrnEZrck6nFGhJ7iQyvRYzDELx0bJU=
    20240502072340.sql h1:VSLoTZmXMjQNKHYM7FxTLbtcOKBBBGAjPTD83BDtn70=

The my_baseline.sql was sorted before the first migration file. So, re-create it will resolve the issue.

[oleg-darrow]

@giautm strange, but its the same 🤔 so i deleted old baseline and created new one, so atlas.sum now is

h1:8SKfpgDIjzF1Ifl1RzgN1N7SNHqxLw4XJl2bRGrGka0=
20240502083707.sql h1:ELDU10vDlKvS2nbmHf/rs2dIB91RCIWxHiUonVm7SFE=
20240502090742_my_baseline.sql h1:G3QmCnPUi83VgdZxAfRS31nq1I+yjZbNvoA30tZ6eYk=

i changed baseline version to 20240502090742 in AtlasMigration but the error is still there

[giautm]

@oleg-darrow sorry, can you please share with version of atlas-operator you have here?

[giautm]

btw, please ensure you config the correct schema name on the url with search_path=NAME if you're working on schema scope

[oleg-darrow]

@giautm arigaio/atlas-operator:0.4.4

i have 30 schemas in this database, so i use db bound url postgres://USER:PASS@RDS_ADDRESS:5432/DB

[giautm]

~In this case you have to config exclude option to exclude the schema NAME~

i have 30 schemas in this database, so i use db bound URL

How many schemas do you want to control via the AtlasMigration resource? It should be one schema per AtlasMigration (schema connection) or you have to put all migrations of all schemas into single AtlasMigration and use the realm connection.

[oleg-darrow]

~In this case you have to config exclude option to exclude the schema NAME~

i have 30 schemas in this database, so i use db bound URL

How many schemas do you want to control via the AtlasMigration resource? It should be one schema per AtlasMigration (schema connection) or you have to put all migrations of all schemas into single AtlasMigration and use the realm connection.

I want to have ability to manage all schemas that i have, could you explain whats realm connection ?

for example this i create in public one

    CREATE TABLE cars (
      brand VARCHAR(255),
      model VARCHAR(255),
      year INT
    );

but this in schema1

    CREATE TABLE schema1.cars (
      brand VARCHAR(255),
      model VARCHAR(255),
      year INT
    );

[rotemtam]

https://atlasgo.io/integrations/kubernetes/operator#schema-and-database-bound-urls

[oleg-darrow]

well, as i said previously, im using DB bound connection (which is realm connection as i understood) postgres://USER:PASS@RDS_ADDRESS:5432/DB sorry, if im missing something

[oleg-darrow]

Hi guys @giautm @rotemtam and sorry again for disturbing. It still doesnt work on my side, maybe you have more advices that I could try ?

[jackspirou]

Hi - any updates on this? I have the same issue. Thanks @oleg-darrow for raising this issue

[jackspirou]

After reading through the atlas operator code and making some iterative guesses over a few hours I figured out I could change the revisionSchema to a different one than the default with a baseline value and it worked for me. Hopefully, this helps someone else :D

  baseline: '20241007080718'
  dir:
    configMapRef:
      name: postgres-migrations
  revisionsSchema: "not_default_schema_name"