Graphql-engine: 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 https://github.com/hasura/graphql-engine/issues/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: https://github.com/hasura/graphql-engine/issues/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