search

supabase / cli

Supabase CLI. Manage postgres migrations, run Supabase locally, deploy edge functions. Postgres backups. Generating types from your database schema.
https://supabase.com/docs/reference/cli/about
MIT License
1.07k stars 209 forks source link

ERROR: multiple primary keys for table #1428

Closed totzk9 closed 1 year ago

totzk9 commented 1 year ago

Describe the bug ERROR: multiple primary keys for table.

To Reproduce Steps to reproduce the behavior:

  1. Create a new project
  2. Add tables via Dashboard
  3. Connect project with via CLI and Github via link and supabase db pull which populates migrations
  4. supabase db push
  5. Error

Expected behavior Should not error

Screenshots

supabase db pull
Connecting to remote database...
Schema written to supabase/migrations/20230827014200_remote_schema.sql
Update remote migration history table? [Y/n] n
Finished supabase db pull.
supabase db push
Applying migration 20230827014200_remote_schema.sql...
ERROR: multiple primary keys for table "app_users" are not allowed (SQLSTATE 42P16)
At statement 105: ALTER TABLE ONLY "public"."app_users"                            
    ADD CONSTRAINT "app_users_pkey" PRIMARY KEY ("id")                             
Try rerunning the command with --debug to troubleshoot the error.

Desktop (please complete the following information):

Additional context Add any other context about the problem here.

sweatybridge commented 1 year ago

Hi @totzk9, I need to know more about your schema to help with debugging. Could you create a support ticket by going to Help > Contact Support via the dashboard?

totzk9 commented 1 year ago

Just had to create a new project then added the same tables. It's now working. But I think it's still a bug for the previous project.

sweatybridge commented 1 year ago

Sounds like the difference is this prompt

Update remote migration history table? [Y/n] n

You want to answer Y here so that existing migrations are not applied twice. Since add constraint is not idempotent, applying twice would throw an error.

totzk9 commented 1 year ago

No it was still an issue even I answered Y and another error occurs

sweatybridge commented 1 year ago

Do you have the logs for the other error? It wouldn't be possible to debug a new error with existing logs.

obouchari commented 10 months ago

I'm having same issue, started on clean slate but still got the same error: ERROR: multiple primary keys for table "schema_migrations" are not allowed (SQLSTATE 42P16) At statement 48: ALTER TABLE ONLY "supabase_migrations"."schema_migrations" ADD CONSTRAINT "schema_migrations_pkey" PRIMARY KEY ("version")

Update remote migration history table? [Y/n] I did answer "Yes" here.

Mohammed-Yasin-Mulla commented 10 months ago

I also have the same issue as well

jamesshamim123 commented 10 months ago

I also have the same issue with a fresh project!

Mohammed-Yasin-Mulla commented 10 months ago

I was able to work around this and fix the error. Delele all the migration files and to start fresh.

Insted of using the command

 supabase db pull

to pull my schema from my remote DB. I used this command 

supabase db diff -f migration_file_name --linked

make sure you have liked to your supabase instace to your CLI before running the above command ☝🏼

Note --linked means you have you DB on supabase.com, if you have a self hosted instance you replace --linked with --db-url DB_URL

After you have succesfully have your new migration files populated, run the command 

supabsae db reset

Hope it helps you all

sweatybridge commented 10 months ago

I believe this is a regression introduced by this PR https://github.com/supabase/cli/pull/1783. I will work on a fix.

pradelkai commented 9 months ago

Having the same issue.

AmitMirgal commented 9 months ago

I believe the issues still exists ... having the same issue. supabase CLI version - 1.131.5

supabase db pull

error output

Connecting to remote database...
Setting up initial schema....
Applying migration 20240117205057_remote_schema.sql...
ERROR: multiple primary keys for table "schema_migrations" are not allowed (SQLSTATE 42P16)
At statement 90: ALTER TABLE ONLY "supabase_migrations"."schema_migrations"
    ADD CONSTRAINT "schema_migrations_pkey" PRIMARY KEY ("version")
Try rerunning the command with --debug to troubleshoot the error.
sweatybridge commented 9 months ago

@AmitMirgal you need to use the beta version for now npx supabase@beta db pull

The fix will be released to stable next Tuesday.

ziranjameshe commented 9 months ago

I am still experiencing the same issue with npx supabase@beta

npx supabase@beta db pull
npx supabase@beta migration up
Connecting to local database...
Applying migration 20240121013831_remote_schema.sql...
ERROR: multiple primary keys for table "schema_migrations" are not allowed (SQLSTATE 42P16)
At statement 23: ALTER TABLE ONLY "supabase_migrations"."schema_migrations"                
    ADD CONSTRAINT "schema_migrations_pkey" PRIMARY KEY ("version")                        
Try rerunning the command with --debug to troubleshoot the error.
anshunjain commented 9 months ago

I am facing a similar issue while trying to setup a local instance. (also tried the beta pull.

otymartin commented 9 months ago

Same...

MaximusMcCann commented 9 months ago

Same

sweatybridge commented 9 months ago

Seems like too many errors caused by having supabase_migrations schema pulled as a migration. I've reverted that behaviour in beta release v1.138.1.

For those having errors, please rerun npx supabase@beta db pull. Apologies for the inconvenience.

MaximusMcCann commented 9 months ago

@sweatybridge New errors using the @beta portion

$ npx supabase@beta db pull --workdir ./src --linked --schema public
Enter your database password:
Connecting to remote database...
Skipping migration .DS_Store... (file name must match pattern "<timestamp>_name.sql")
pg_dump: error: no matching extensions were found
error running container: exit 1
The auth and storage schemas are excluded. Run supabase db pull --schema auth,storage again to diff them.
error Command failed with exit code 1.

droping the @beta portion then worked for me.

sweatybridge commented 9 months ago

@MaximusMcCann ~please run beta again with db dump --create-ticket flag. I need more system info to reproduce~

EDIT: I found the issue after enabling --debug logs. A new beta release is ~on the way~ done.

FJohlinger commented 9 months ago

I have a similar problem to @AmitMirgal when trying to run the 'Deploy Migrations to Staging' workflow:

supabase db push

error output

Connecting to remote database...
Applying migration 20240202123856_remote_schema.sql...
ERROR: multiple primary keys for table "schema_migrations" are not allowed (SQLSTATE 42P16)
At statement 71: ALTER TABLE ONLY "supabase_migrations"."schema_migrations"                
    ADD CONSTRAINT "schema_migrations_pkey" PRIMARY KEY ("version")                        
Try rerunning the command with --debug to troubleshoot the error.

I was following the instructions from here, copying example file .github/workflows/staging.yml. I have already tried changing to using the pre-release version v1.138.2 (which should include PR #1889) instead of 'latest'. This doesn't seem to make a difference, though.

I'm currently setting up my development environment with local/staging/production and my staging project is still empty. This was supposed to be the first push to the staging project. For the CI GitHub action I'm currently using pre-release version v1.137.3, due to another problem I ran into, not sure if that could make any difference.

chriskrogh commented 7 months ago

https://github.com/supabase/supabase/issues/20405#issuecomment-1902276878

this fixed it for me