Applying migrations not working as expected

[mklueh]

I´m trying a simple backup / restore on the same database with hasura CLI

After creating the migrations, I´ve deleted all my tables in Hasura and wanted to recreate them by applying the migrations, but I´m running into this issue:

File: '1578000210752_init\up.sql'
{
    "sql": "CREATE FUNCTION public.set_current_timestamp_updated_at() RETURNS trigger\n    LANGUAGE plpgsql\n    AS $$\nDECLARE\n  _new record;\nBEGIN\n  _new := NEW;\n  _new
.\"updated_at\" = NOW();\n  RETURN _new;\nEND;\n$$;\nCREATE TABLE public.team (\n    id integer NOT NULL,\n    created_at timestamp with time zone DEFAULT now() NOT NULL,\n
  updated_at timestamp with time zone DEFAULT now() NOT NULL,\n    name text NOT NULL\n);\nCREATE SEQUENCE public.team_id_seq\n    AS integer\n    START WITH 1\n    INCREMENT
 BY 1\n    NO MINVALUE\n    NO MAXVALUE\n    CACHE 1;\nALTER SEQUENCE public.team_id_seq OWNED BY public.team.id;\nCREATE TABLE public.\"user\" (\n    id integer NOT NULL,\n
   created_at timestamp with time zone DEFAULT now() NOT NULL,\n    updated_at timestamp with time zone DEFAULT now() NOT NULL,\n    name text,\n    team_id integer\n);\nCREA
TE SEQUENCE public.user_id_seq\n    AS integer\n    START WITH 1\n    INCREMENT BY 1\n    NO MINVALUE\n    NO MAXVALUE\n    CACHE 1;\nALTER SEQUENCE public.user_id_seq OWNED
BY public.\"user\".id;\nALTER TABLE ONLY public.team ALTER COLUMN id SET DEFAULT nextval('public.team_id_seq'::regclass);\nALTER TABLE ONLY public.\"user\" ALTER COLUMN id SE
T DEFAULT nextval('public.user_id_seq'::regclass);\nALTER TABLE ONLY public.team\n    ADD CONSTRAINT team_pkey PRIMARY KEY (id);\nALTER TABLE ONLY public.\"user\"\n    ADD CO
NSTRAINT user_pkey PRIMARY KEY (id);\nCREATE TRIGGER set_public_team_updated_at BEFORE UPDATE ON public.team FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updat
ed_at();\nCOMMENT ON TRIGGER set_public_team_updated_at ON public.team IS 'trigger to set value of column \"updated_at\" to current timestamp on row update';\nCREATE TRIGGER
set_public_user_updated_at BEFORE UPDATE ON public.\"user\" FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();\nCOMMENT ON TRIGGER set_public_user_upda
ted_at ON public.\"user\" IS 'trigger to set value of column \"updated_at\" to current timestamp on row update';\nALTER TABLE ONLY public.\"user\"\n    ADD CONSTRAINT user_te
am_id_fkey FOREIGN KEY (team_id) REFERENCES public.team(id) ON UPDATE RESTRICT ON DELETE RESTRICT;\n"
}
[42723] FatalError: function "set_current_timestamp_updated_at" already exists with same argument types

Are there any restrictions applying the migrations that I´m not aware of?

[joshuarobs]

I think I had that same issue as my issue as I described in #3632. I just saw it once and forgot exactly what the error was.

I'm not sure if its the same bug I encountered, but if it is, basically the answer to solving this is to only have one folder in migrations with init in it. That means, you may have multiple folders e.g. 15780405824_init and 1578040748052_init. You should delete one of them (probably the one that isn't the first one you made). Basically there shouldn't be more than one up.sql file in the migrations.

[mklueh]

@joshuarobs Hi, in my case it was a fresh hasura instance with just two tables. I did a single backup, meaning my migrations folder had just one migration directory

[shahidhk]

@mklueh

I've deleted all my tables in Hasura

Does this mean that you applied the down migration or you deleted the tables manually?

function "set_current_timestamp_updated_at" already exists

This error says the function already exists in the database. So, the clean-up was not proper?

[mklueh]

@shahidhk I´ve just removed the tables from within the Hasura dashboard, maybe I should test it with a clean postgres installation instead. thank you

[marionschleifer]

@mklueh I'm closing this issue. Feel free to re-open if you'd like to add something 🙂

[kuryaki]

Execute this in the hasura SQL console if you run into this DROP FUNCTION public.set_current_timestamp_updated_at;

[tirumaraiselvan]

Could this be related to: #3243

@mklueh It would be very helpful if you could give a small repro of your setup that throws this error.

[amitava82]

Getting same error. Trying to delete function as suggested by @kuryaki throws

postgres-error : cannot drop function set_current_timestamp_updated_at() because other objects depend on it

[joshuarobs]

As of 24 May 2020, I still encountered this same error when trying to apply a migration to a new Hasura server that I started locally on Docker, on Hasura server version 1.2.1.

I tried using the answer that @kuryaki suggested, but it didn't work for me. That may work for a Hasura server that already has data in it. But mine was a clean slate. Calling hasura migrate apply --endpoint http://localhost:8080 gave me this error.

Basically, the answer that I posted earlier on 4 Jan is the solution that worked for me. Again. That's not to say that kuryaki's answer won't work, it may for some situations. But for those in my situation, my answer worked again.

What's my answer? tl;dr: Ensure you have only 1 "init" migration in your migrations folder. You may want to create one, before deleting all the other migrations.

[peerhenry]

I have the same issue as @amitava82 and I only have 1 migration called "init".

@marionschleifer Why did you close this issue? Can you please reopen because I don't see any solution here yet.

[Kluskey]

I'm running into this issue as well. Tried @kuryaki's solution and received the same error that @amitava82 is getting:
postgres-error : cannot drop function set_current_timestamp_updated_at() because other objects depend on it.

Also tried @joshuarobs solution with only having 1 "init" migration.

Any other thoughts, ideas, or workarounds?

---

Update: I was able to work around the issue by creating a fresh Hasura Heroku install of the remote I was trying to push the migration to, and both the hasura migrate apply --endpoint ... and hasura metadata apply --endpoint ... worked fine.
My guess is that somewhere on either the source or destination console, I had made a change that wasn't reflected in the source and/or the destination, and that's where the migration began to run into issues.
Note: v1.2.2

[Legys]

Encountered the same issue on a brand new project from the beginning.

[spencerpauly]

Also encountered this. Possibly caused by me manually uploading the metadata file from the console instead of the CLI? Going to try it fresh. Running a fresh database on digitalocean from the digitalocean link

[venom90]

Any update on this issue?

[ghost]

I've also ran into this issue. Is it possible this is caused by residual data left on the server?

The docs don't say how to reset migration history on server.

https://hasura.io/docs/latest/graphql/core/migrations/advanced/resetting-migrations.html

When I run hasura migrate status I get:

VERSION        NAME  SOURCE STATUS  DATABASE STATUS
1618611753391  -     Not Present    Present
1618617440008  -     Not Present    Present
1618617542566  init  Present        Present

[aindong]

I encountered this issue when created a new server instance, I have an existing database that is used by hasura on previous server and when I created a new instance to use the same database the error happens

{"exec_status":"FatalError","hint":null,"message":"function \"gen_hasura_uuid\" already exists with same argument types","status_code":"42723","description":null}

[corepay]

@aindong I had same issue a v1 hasura db moved to hasura v2.

1. Import your old DB dump into new database
   2, Drop the hdb_catalog in new database and launch a new Hasura on it to rebuild the schema
2. Go to your database public folder in DATA browser and TRACK ALL tables and relationships

[cristinapicatoste]

I've used CASCADE in the drop query, maybe it helps you (eg):

await queryRunner.query (`DROP TRIGGER IF EXISTS account_bi ON app_account CASCADE;` );`

[samuela]

I'm seeing the same bug. It's reproducible for me whenever I delete my postgres database and start fresh. I haven't done anything fishy outside of hasura that might mess up my migrations.

EDIT: Figured out the issue. I ran hasura migrate export (or something like that) after getting hit with #7728. The export created a new migration which captured the entire state of the schema, without any regard for diffing against things that were already created by previous migrations. In other words, it created a migration that would be appropriate as an initial migration but not as a new migration relative to the existing ones. So deleting all migrations prior to the hasura migrate export one fixed the issue for me.

[EgedotErcan]

Execute this in the hasura SQL console if you run into this DROP FUNCTION public.set_current_timestamp_updated_at;

This works well sir, thanks for the solution. I have just one note to solve this problem. You have to do this where you apply the migration and metadata It will work if you do like this.

[manasag]

Closing this due to low activity, and resolution paths being present. Please create a new issue if something like this issue still occurs.