`Dirty database version 1. Fix and force version` and `error: can't read limit argument N`

[ctfrancia]

Describe the Bug A clear and concise description of what the bug is.

Steps to Reproduce Steps to reproduce the behavior:

1. My migrations look like:

   CREATE TABLE IF NOT EXISTS users (
   user_id bigserial PRIMARY KEY,
   created_at timestamp(0) with time zone NOT NULL DEFAULT NOW(),
   first_name text NOT NULL,
   last_name text NOT NULL, <---- this error is intentional for demonstrational purposes
   )

2. I ran migrate with the following options: migrate -path=./migrations -database=$CHESS_DB_DSN up
3. See error: error: migration failed: syntax error at or near ")"

Expected Behavior to migrate

Migrate Version e.g. v3.4.0 Obtained by running: migrate -version: v4.17.0

Loaded Source Drivers e.g. s3, github, go-bindata, gcs, file Obtained by running: migrate -help

Source drivers: file, github, bitbucket, s3, go-bindata, github-ee, gitlab, godoc-vfs, gcs

Loaded Database Drivers e.g. spanner, stub, clickhouse, cockroachdb, crdb-postgres, postgres, postgresql, pgx, redshift, cassandra, cockroach, mysql Obtained by running: migrate -help

Database drivers: spanner, cassandra, pgx, pgx4, pgx5, yugabyte, rqlite, mongodb, stub, mysql, cockroachdb, postgresql, redshift, crdb-postgres, yugabytedb, mongodb+srv, sqlserver, firebirdsql, neo4j, clickhouse, cockroach, postgres, ysql, firebird

Go Version e.g. go version go1.11 linux/amd64 Obtained by running: go version 1.21

Stacktrace Please provide if available

Additional context Add any other context about the problem here.

I followed this issue and then when it came to the command $ migrate -path migrations/ -database $CHESS_DB_DSN force <VERSION> I get the error: error: can't read limit argument N

so now I am stuck... also a side note: If I am running this command as version 1 for the first time then what previous version am I supposed to revert to? version 0?

[Delta456]

I am also getting an issue similar to this.

Schema file

CREATE TABLE IF NOT EXISTS movies (
    id bigserial PRIMARY KEY,
    created_at timestamp(0) with time zone NOT NULL DEFAULT NOW(),
    title text NOT NULL,
    year integer NOT NULL,
    runtime integer NOT NULL,
    genres text[] NOT NULL,
    version integer NOT NULL DEFAULT 1
);

[DariaPlotnikova]

@Delta456 hi, you should use force and up commands separately:

migrate -path=... -database=... force 1    # This will mark db as 1st migration was applied (you have to check if state is correct still, command just marks migrations as not-dirty in schema_migrations table
migrate -path=... -database=... up 2       # This will try to apply 2nd migration to db