auth.identities type cast issue

[nCrafts]

Describe the bug

I am trying to run supabase.auth.admin.generateLink on the server. I get the error AuthRetryableFetchError. When looking into the logs I can co-relate this to a postgres error instead. The error seems to be coming from an admin query, which I have no control over.

I've tried re-starting my project, and have also done a database reset. Everything short of starting a new project.

Here is the error stack:

operator does not exist: uuid = text

{
  "file": null,
  "host": "db-econnpaukunccukooobo",
  "metadata": [],
  "parsed": [
    {
      "application_name": "gotrue_migrations",
      "backend_type": "client backend",
      "command_tag": "UPDATE",
      "connection_from": "127.0.0.1:39722",
      "context": null,
      "database_name": "postgres",
      "detail": null,
      "error_severity": "ERROR",
      "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",
      "internal_query": null,
      "internal_query_pos": null,
      "leader_pid": null,
      "location": null,
      "process_id": 2850,
      "query": "-- previous backfill migration left last_sign_in_at to be null, which broke some projects\n\nupdate auth.identities\n  set last_sign_in_at = '2022-11-25'\n  where\n    last_sign_in_at is null and\n    created_at = '2022-11-25' and\n    updated_at = '2022-11-25' and\n    provider = 'email' and\n    id = user_id::text;\n",
      "query_id": 0,
      "query_pos": 294,
      "session_id": "6577735e.b22",
      "session_line_num": 3,
      "session_start_time": "2023-12-11 20:38:54 UTC",
      "sql_state_code": "42883",
      "timestamp": "2023-12-11 20:38:54.211 UTC",
      "transaction_id": 0,
      "user_name": "supabase_auth_admin",
      "virtual_transaction_id": "6/3853"
    }
  ],
  "parsed_from": null,
  "project": null,
  "source_type": null
}

System information

• OS: Debian
• Version of supabase-js: 2.38.5
• Version of Node.js: 21.6

Additional context

The error doesn't happen for me locally, and I am guessing won't happen in a new project. I could start a new project now but would not be feasible in a live environment. Would love to know what's happening here.

[kangmingtay]

@nCrafts is this an old project that was recently being restored? are you managing your database migrations using prisma + supabase CLI? it seems like your auth service is down because one of the migrations keeps failing to run on it - i've fixed the issue on your project (econnpaukunccukooobo) already.

[nCrafts]

@kangmingtay Yes, this was an old project that was restored. I am using Supabase CLI but not Prisma. I reset the remote DB and then re-ran all migrations. How can I prevent this issue in the future? Thanks.

[kangmingtay]

@nCrafts can you elaborate on the steps you took to reproduce this? what commands did you run to do this: "I reset the remote DB and then re-ran all migrations." ?

typically, you should be using the remote db as the main source of truth and ensure that your local dev is up-to-date. before re-running migrations, you should also inspect the migrations created to ensure that there are no migrations that touch the supabase managed schemas (auth, storage, realtime, etc..)

[nCrafts]

Basically I wanted to reset the remote database and re-create it based on the local migration files. I ran this on the remote database:

do $$ declare
r record;
begin
for r in (select tablename from pg_tables where schemaname = 'public') loop
execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade';
end loop;
end $$;

DELETE FROM auth.schema_migrations;

Then this:

npx supabase db reset --linked
npx supabase db push

The migrations ran without errors.

My migrations do contain changes to the storage schema, to create buckets. I am guessing that isn't the right way to do this?

[kangmingtay]

hey @nCrafts, why did you run DELETE FROM auth.schema_migrations; on the remote database? the auth service relies on that table to decide what migrations to run - was this generated by prisma or supabase CLI?

[nCrafts]

@kangmingtay I see. My intention was to reset the remote database and re-run all migrations from my local instance. I was hoping DELETE FROM auth.schema_migrations; would allow me to do that, but I guess not? Is there another you recommend that I can use to reset the remote database? Like how I can use npx supabase db reset locally.

[kangmingtay]

@nCrafts i'm not sure i understand, isn't the point of supabase db reset to reset the remote database and re-run all local migrations?

[nCrafts]

@kangmingtay supabase db reset does that, but locally. Now I see that a --linked argument was added at some point which would do what I was trying to do, unsuccessfully, with some hacks. I will use that in the future. Thanks for your help!

[swyxio]

@kangmingtay it looks like i have the same exact problem as nCrafts here.

can you please take a look at mrwkaizvbyavqcftnagf?

i also use supabase without prisma. to my knowledge i didn't run any DELETE FROM auth.schema_migrations; type query. woudl love to know how you debug this and see whats wrong.

[kiwicopple]

hey everyone. If you're receiving invalid response was received from the upstream server from the Auth system, it could be related to a faulty migration:

https://github.com/orgs/supabase/discussions/20722