Database migration failing

olynch commented 1 year ago

Issue Summary

Whenever the lemmy server starts, it immediately crashes with the error message:

thread 'main' panicked at 'Couldn't run DB Migrations', crates/db_schema/src/utils.rs:161:25

The logs for postgresql have this:

[10289] ERROR:  permission denied: "RI_ConstraintTrigger_a_16639" is a system trigger
[10289]: [10289] STATEMENT:  -- Remove the comment.read column, and create a new comment_reply table,
-- similar to the person_mention table.
--
-- This is necessary because self-joins using ltrees would be too tough with SQL views
--
-- Every comment should have a row here, because all comments have a recipient,
-- either the post creator, or the parent commenter.
create table comment_reply(
  id serial primary key,
  recipient_id int references person on update cascade on delete cascade not null,
  comment_id int references comment on update cascade on delete cascade not null,
  read boolean default false not null,
  published timestamp not null default now(),
  unique(recipient_id, comment_id)
);

-- Ones where parent_id is null, use the post creator recipient
insert into comment_reply (recipient_id, comment_id, read)
...

Steps to Reproduce

Setup the lemmy server on nixpkgs-unstable
systemctl start lemmy

Technical details

lemmy_server 1.17.1, with the nixos options:

  services.lemmy = {
    enable = true;

    settings = {
      hostname = "localcharts.org";
      federation.enabled = true;
    };

    caddy.enable = true;

    database.createLocally = true;
  };

olynch commented 1 year ago

OK, I've updated postgresql to version 15, and now I get:

[11567] ERROR:  permission denied for schema public at character 28
[11567] STATEMENT:  CREATE TABLE IF NOT EXISTS __diesel_schema_migrations (
               version VARCHAR(50) PRIMARY KEY NOT NULL,
               run_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

olynch commented 1 year ago

I made the lemmy user a postgres SUPERUSER, and now I get the error:

Error: LemmyError { message: None, inner: no PostgreSQL user name specified in startup packet, context: "SpanTrace" }

dessalines commented 1 year ago

You should not be manually trying to set up diesel migration tables, those automatically get generated by the lemmy service.

Reinstall lemmy using the official docs, because there's no way to recover if you've manually altered those migration tables. https://join-lemmy.org/docs/en/administration/administration.html

dessalines commented 1 year ago

Also, use !lemmy_support or the matrix channel to get help with installation.

CobaltCause commented 1 year ago

Just hit these issues on 0.17.2 (also on NixOS) and fixed them.

You can solve the problem in the issue description with the following steps:

Start lemmy, let the migrations fail

Once the migrations fail, use this file (read the comment at the top):

-- Use this after starting lemmy for the first time when postgres logs show you
-- an error about being unable to delete triggers. This file is a reproduction
-- of [this][0] plus the changes from [this PR][1] with the additions of just
-- this comment and the final line at the end to update the migrations table
-- added.
--
-- This file needs to be run as your `lemmy` database user against the `lemmy`
-- database. Make sure to also pass `-v ON_ERROR_STOP=1` to `psql` (or
-- equivalent for whatever tool you're using) to prevent weirdness.
--
-- [0]: https://github.com/LemmyNet/lemmy/blob/c32585b03429f0f76d1e4ff738786321a0a9df98/migrations/2022-07-07-182650_comment_ltrees/up.sql
-- [1]: https://github.com/LemmyNet/lemmy/pull/3002

-- Remove the comment.read column, and create a new comment_reply table,
-- similar to the person_mention table. 
-- 
-- This is necessary because self-joins using ltrees would be too tough with SQL views
-- 
-- Every comment should have a row here, because all comments have a recipient, 
-- either the post creator, or the parent commenter.
create table comment_reply(
  id serial primary key,
  recipient_id int references person on update cascade on delete cascade not null,
  comment_id int references comment on update cascade on delete cascade not null,
  read boolean default false not null,
  published timestamp not null default now(),
  unique(recipient_id, comment_id)
);

-- Ones where parent_id is null, use the post creator recipient
insert into comment_reply (recipient_id, comment_id, read)
select p.creator_id, c.id, c.read from comment c
inner join post p on c.post_id = p.id
where c.parent_id is null;

--  Ones where there is a parent_id, self join to comment to get the parent comment creator
insert into comment_reply (recipient_id, comment_id, read)
select c2.creator_id, c.id, c.read from comment c
inner join comment c2 on c.parent_id = c2.id;

-- Drop comment_alias view
drop view comment_alias_1;

alter table comment drop column read;

create extension ltree;

alter table comment add column path ltree not null default '0';
alter table comment_aggregates add column child_count integer not null default 0;

-- The ltree path column should be the comment_id parent paths, separated by dots. 
-- Stackoverflow: building an ltree from a parent_id hierarchical tree:
-- https://stackoverflow.com/a/1144848/1655478

create temporary table comment_temp as 
WITH RECURSIVE q AS (
    SELECT  h, 1 AS level, ARRAY[id] AS breadcrumb
    FROM    comment h
    WHERE   parent_id is null
    UNION ALL
    SELECT  hi, q.level + 1 AS level, breadcrumb || id
    FROM    q
    JOIN    comment hi
    ON      hi.parent_id = (q.h).id
)
SELECT  (q.h).id,
    (q.h).parent_id,
    level,
    breadcrumb::VARCHAR AS path,
    text2ltree('0.' || array_to_string(breadcrumb, '.')) as ltree_path
FROM    q
ORDER BY
    breadcrumb;

-- Remove indexes and foreign key constraints, and disable triggers for faster updates
alter table comment disable trigger user;

alter table comment drop constraint if exists comment_creator_id_fkey;
alter table comment drop constraint if exists comment_parent_id_fkey;
alter table comment drop constraint if exists comment_post_id_fkey;
alter table comment drop constraint if exists idx_comment_ap_id;

drop index if exists idx_comment_creator;
drop index if exists idx_comment_parent;
drop index if exists idx_comment_post;
drop index if exists idx_comment_published;

-- Add the ltree column
update comment c 
set path = ct.ltree_path
from comment_temp ct
where c.id = ct.id;

-- Update the child counts
update comment_aggregates ca set child_count = c2.child_count
from (
  select c.id, c.path, count(c2.id) as child_count from comment c
  left join comment c2 on c2.path <@ c.path and c2.path != c.path
  group by c.id
) as c2
where ca.comment_id = c2.id;

-- Delete comments at a depth of > 150, otherwise the index creation below will fail
delete from comment where nlevel(path) > 150;

-- Delete from comment where there is a missing post
delete from comment c where not exists (
  select from post p where p.id = c.post_id
);

-- Delete from comment where there is a missing creator_id
delete from comment c where not exists (
  select from person p where p.id = c.creator_id
);

-- Re-enable old constraints and indexes
alter table comment add constraint "comment_creator_id_fkey" FOREIGN KEY (creator_id) REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE;
alter table comment add constraint "comment_post_id_fkey" FOREIGN KEY (post_id) REFERENCES post(id) ON UPDATE CASCADE ON DELETE CASCADE;
alter table comment add constraint "idx_comment_ap_id" unique (ap_id);

create index idx_comment_creator on comment (creator_id);
create index idx_comment_post on comment (post_id);
create index idx_comment_published on comment (published desc);

-- Create the index
create index idx_path_gist on comment using gist (path);

-- Drop the parent_id column
alter table comment drop column parent_id cascade;

alter table comment enable trigger user;

-- This was not part of the original file but is necessary to unblock the rest
-- of the migrations
insert into __diesel_schema_migrations (version) values ('20220707182650');

Old solution (is fine but isn't what'll ship in future versions of Lemmy)

```sql -- Use this after starting lemmy for the first time when postgres logs show you -- an error about being unable to delete triggers. This file is a reproduction -- of [this][0] from upstream before code requiring superuser (ew) was added -- with only this comment and the final line at the end to update the migrations -- table added. -- -- [0]: https://raw.githubusercontent.com/LemmyNet/lemmy/9c3efe32e7b2ef7978b7957eac10691beb551dde/migrations/2022-07-07-182650_comment_ltrees/up.sql -- Remove the comment.read column, and create a new comment_reply table, -- similar to the person_mention table. -- -- This is necessary because self-joins using ltrees would be too tough with SQL views -- -- Every comment should have a row here, because all comments have a recipient, -- either the post creator, or the parent commenter. create table comment_reply( id serial primary key, recipient_id int references person on update cascade on delete cascade not null, comment_id int references comment on update cascade on delete cascade not null, read boolean default false not null, published timestamp not null default now(), unique(recipient_id, comment_id) ); -- Ones where parent_id is null, use the post creator recipient insert into comment_reply (recipient_id, comment_id, read) select p.creator_id, c.id, c.read from comment c inner join post p on c.post_id = p.id where c.parent_id is null; -- Ones where there is a parent_id, self join to comment to get the parent comment creator insert into comment_reply (recipient_id, comment_id, read) select c2.creator_id, c.id, c.read from comment c inner join comment c2 on c.parent_id = c2.id; -- Drop comment_alias view drop view comment_alias_1; alter table comment drop column read; create extension ltree; alter table comment add column path ltree not null default '0'; alter table comment_aggregates add column child_count integer not null default 0; -- The ltree path column should be the comment_id parent paths, separated by dots. -- Stackoverflow: building an ltree from a parent_id hierarchical tree: -- https://stackoverflow.com/a/1144848/1655478 create temporary table comment_temp as WITH RECURSIVE q AS ( SELECT h, 1 AS level, ARRAY[id] AS breadcrumb FROM comment h WHERE parent_id is null UNION ALL SELECT hi, q.level + 1 AS level, breadcrumb || id FROM q JOIN comment hi ON hi.parent_id = (q.h).id ) SELECT (q.h).id, (q.h).parent_id, level, breadcrumb::VARCHAR AS path, text2ltree('0.' || array_to_string(breadcrumb, '.')) as ltree_path FROM q ORDER BY breadcrumb; -- Add the ltree column update comment c set path = ct.ltree_path from comment_temp ct where c.id = ct.id; -- Update the child counts update comment_aggregates ca set child_count = c2.child_count from ( select c.id, c.path, count(c2.id) as child_count from comment c left join comment c2 on c2.path <@ c.path and c2.path != c.path group by c.id ) as c2 where ca.comment_id = c2.id; -- Create the index create index idx_path_gist on comment using gist (path); -- Drop the parent_id column alter table comment drop column parent_id cascade; -- This was not part of the original file but is necessary to unblock the rest -- of the migrations insert into __diesel_schema_migrations (version) values ('20220707182650'); ```

Restart lemmy, it should run the rest of the migrations and then start up as usual
Congratulations, you have now avoided giving the lemmy database user superuser rights

You can solve the problem in https://github.com/LemmyNet/lemmy/issues/2784#issuecomment-1475489971 by following the guidance in https://github.com/LemmyNet/lemmy/issues/2784#issuecomment-1578915147.

Original incorrect version

You can solve the problem in https://github.com/LemmyNet/lemmy/issues/2784#issuecomment-1475489971 by applying the following patch: ```diff diff --git a/crates/utils/src/settings/mod.rs b/crates/utils/src/settings/mod.rs index fe8c8ae4..f472ce89 100644 --- a/crates/utils/src/settings/mod.rs +++ b/crates/utils/src/settings/mod.rs @@ -44,7 +44,7 @@ impl Settings { pub fn get_database_url(&self) -> String { let conf = &self.database; format!( - "postgres://{}:{}@{}:{}/{}", + "postgresql://{}:{}@{}:{}/{}", utf8_percent_encode(&conf.user, NON_ALPHANUMERIC), utf8_percent_encode(&conf.password, NON_ALPHANUMERIC), conf.host, ``` Ripgrep says there are more occurrences of `postgres://` but this is the only one I had to change to make it work. I'll open a PR to upstream this in the near future.

Nutomic commented 1 year ago

@CobaltCause Based on the documentation, postgres:// and postgresql:// should be equivalent. Its odd that only one would work for you.

CobaltCause commented 1 year ago

You're totally right, that was completely benign, that patch does nothing. I'd been awake far too long at the time of writing that comment and just wrote the completely wrong thing. The actual fix was to patch the NixOS module like this, since it's already using the configuration file:

--- original.nix
+++ working.nix
@@ -150,9 +156,6 @@

         environment = {
           LEMMY_CONFIG_LOCATION = "/run/lemmy/config.hjson";
-
-          # Verify how this is used, and don't put the password in the nix store
-          LEMMY_DATABASE_URL = with cfg.settings.database;"postgres:///${database}?host=${host}";
         };

         documentation = [

I'll upstream this (and some other fixes for issues I had with the NixOS module).

Cmdrd commented 1 year ago

@CobaltCause I tried your manual migration steps but get failures at this point:

lemmy=> insert into comment_reply (recipient_id, comment_id, read) select p.creator_id, c.id, c.read from comment c inner join post p on c.post_id = p.id where c.parent_id is null; ERROR: column c.read does not exist LINE 2: select p.creator_id, c.id, c.read from comment c

Not versed enough in Postgres (yet) to know how to fix this.

CobaltCause commented 1 year ago

Hmm, I can't reproduce that from commit 1c9f0c2bed51eb240d8514d28993c5516c019afc (current main branch) running the migrations with my change (minus the diesel table update) on a fresh postgresql container.

t0stiman commented 1 year ago

@CobaltCause How am i supposed to execute that SQL script? I tried saving it to /home/ubuntu/temp/idk.sql and then executing sudo -iu lemmy psql -f /home/ubuntu/temp/idk.sql, but i got:

psql:/home/ubuntu/temp/idk.sql:23: ERROR:  relation "person" does not exist
psql:/home/ubuntu/temp/idk.sql:29: ERROR:  relation "comment_reply" does not exist
LINE 1: insert into comment_reply (recipient_id, comment_id, read)
                    ^
psql:/home/ubuntu/temp/idk.sql:34: ERROR:  relation "comment_reply" does not exist
LINE 1: insert into comment_reply (recipient_id, comment_id, read)
                    ^
psql:/home/ubuntu/temp/idk.sql:37: ERROR:  view "comment_alias_1" does not exist
psql:/home/ubuntu/temp/idk.sql:39: ERROR:  relation "comment" does not exist
CREATE EXTENSION
psql:/home/ubuntu/temp/idk.sql:43: ERROR:  relation "comment" does not exist
psql:/home/ubuntu/temp/idk.sql:44: ERROR:  relation "comment_aggregates" does not exist
psql:/home/ubuntu/temp/idk.sql:68: ERROR:  relation "comment" does not exist
LINE 4:  FROM    comment h
                 ^
psql:/home/ubuntu/temp/idk.sql:74: ERROR:  relation "comment" does not exist
LINE 1: update comment c 
               ^
psql:/home/ubuntu/temp/idk.sql:83: ERROR:  relation "comment_aggregates" does not exist
LINE 1: update comment_aggregates ca set child_count = c2.child_coun...
               ^
psql:/home/ubuntu/temp/idk.sql:86: ERROR:  relation "comment" does not exist
psql:/home/ubuntu/temp/idk.sql:89: ERROR:  relation "comment" does not exist
psql:/home/ubuntu/temp/idk.sql:93: ERROR:  relation "__diesel_schema_migrations" does not exist
LINE 1: insert into __diesel_schema_migrations (version) values ('20...

CobaltCause commented 1 year ago

Sounds like you didn't follow step 1? You'll also need to remove the insertion into __diesel_schema_migrations before trying again.

CobaltCause commented 1 year ago

(I've just updated https://github.com/LemmyNet/lemmy/issues/2784#issuecomment-1578337686 to reflect what'll ship with future versions of Lemmy (via https://github.com/LemmyNet/lemmy/pull/3002), the old SQL is still available but hidden in a details section underneath it.)

glacials commented 1 year ago

@CobaltCause I applied the old solution (before you updated with the new) and it worked swimmingly! Thanks for your help here. But I've now noticed the instance cannot post any comments. I get this error on Lemmy 0.17.3 and 0.17.4:

Jun 14 23:49:09 fediverse lemmy_server[298617]: 2023-06-14T23:49:09.474034Z ERROR HTTP request{http.method=GET http.scheme="http" http.host=l.twos.dev http.target=/api/v3/ws otel.kind="server" request_id=02c66a9c-ca12-4b6b-b3cd-2e3ad9ebc7ae http.status_code=101 otel.status_code="OK"}: lemmy_server::api_routes_websocket: couldnt_create_comment: no binary output function available for type ltree
Jun 14 23:49:09 fediverse lemmy_server[298617]:    0: lemmy_api_crud::comment::create::perform
Jun 14 23:49:09 fediverse lemmy_server[298617]:            with self=CreateComment { content: "testing", post_id: PostId(1548), parent_id: None, language_id: None, form_id: Some("comment-form-IedzxiQlxXYN2m09eEQg"), auth: Sensitive }
Jun 14 23:49:09 fediverse lemmy_server[298617]:              at /home/glacials/.cargo/registry/src/index.crates.io-6f17d22bba15001f/lemmy_api_crud-0.17.4/src/comment/create.rs:42
Jun 14 23:49:09 fediverse lemmy_server[298617]:    1: lemmy_server::root_span_builder::HTTP request
Jun 14 23:49:09 fediverse lemmy_server[298617]:            with http.method=GET http.scheme="http" http.host=l.twos.dev http.target=/api/v3/ws otel.kind="server" request_id=02c66a9c-ca12-4b6b-b3cd-2e3ad9ebc7ae http.status_code=101 otel.status_code="OK"
Jun 14 23:49:09 fediverse lemmy_server[298617]:              at /home/glacials/.cargo/registry/src/index.crates.io-6f17d22bba15001f/lemmy_server-0.17.4/src/root_span_builder.rs:16

Do you know what I should do to fix this?

CobaltCause commented 1 year ago

Unfortunately no, I only ever used 0.17.2. I was able to post comments from my instance, though.

adisbladis commented 1 year ago

@CobaltCause I applied the old solution (before you updated with the new) and it worked swimmingly! Thanks for your help here. But I've now noticed the instance cannot post any comments. I get this error on Lemmy 0.17.3 and 0.17.4:

Jun 14 23:49:09 fediverse lemmy_server[298617]: 2023-06-14T23:49:09.474034Z ERROR HTTP request{http.method=GET http.scheme="http" http.host=l.twos.dev http.target=/api/v3/ws otel.kind="server" request_id=02c66a9c-ca12-4b6b-b3cd-2e3ad9ebc7ae http.status_code=101 otel.status_code="OK"}: lemmy_server::api_routes_websocket: couldnt_create_comment: no binary output function available for type ltree
Jun 14 23:49:09 fediverse lemmy_server[298617]:    0: lemmy_api_crud::comment::create::perform
Jun 14 23:49:09 fediverse lemmy_server[298617]:            with self=CreateComment { content: "testing", post_id: PostId(1548), parent_id: None, language_id: None, form_id: Some("comment-form-IedzxiQlxXYN2m09eEQg"), auth: Sensitive }
Jun 14 23:49:09 fediverse lemmy_server[298617]:              at /home/glacials/.cargo/registry/src/index.crates.io-6f17d22bba15001f/lemmy_api_crud-0.17.4/src/comment/create.rs:42
Jun 14 23:49:09 fediverse lemmy_server[298617]:    1: lemmy_server::root_span_builder::HTTP request
Jun 14 23:49:09 fediverse lemmy_server[298617]:            with http.method=GET http.scheme="http" http.host=l.twos.dev http.target=/api/v3/ws otel.kind="server" request_id=02c66a9c-ca12-4b6b-b3cd-2e3ad9ebc7ae http.status_code=101 otel.status_code="OK"
Jun 14 23:49:09 fediverse lemmy_server[298617]:              at /home/glacials/.cargo/registry/src/index.crates.io-6f17d22bba15001f/lemmy_server-0.17.4/src/root_span_builder.rs:16

Do you know what I should do to fix this?

I saw the same issue earlier and resolved it by upgrading my postgresql daemon. I was always building against version 14 but my host was running on version 12 which is apparently too old for some features that lemmy depends on.

CobaltCause commented 1 year ago

FWIW I was using PostgreSQL 14 at the time so that's probably why I didn't hit this

glacials commented 1 year ago

I was running PostgreSQL 12 but continue to see the same error after upgrading to 15 (both my database server and web server, to be sure). Was there any other action you had to perform as part of the upgrade?

Had you followed the old instructions, or the new?

adisbladis commented 1 year ago

I was running PostgreSQL 12 but continue to see the same error after upgrading to 15 (both my database server and web server, to be sure). Was there any other action you had to perform as part of the upgrade?

Even after upgrading with pg_upgrade I was still experiencing issues. I made a dump of the old db and imported into the new one and that worked.

glacials commented 1 year ago

@adisbladis Wow that worked, thanks! I have no idea why. All I did:

CREATE DATABASE lemmy_v1;

pg_dump -h MY_DB_HOST -U MY_LEMMY_USER lemmy > dump.sql
psql    -h MY_DB_HOST -U MY_LEMMY_USER lemmy_v1 < dump.sql

And then edited my /etc/lemmy/lemmy.hjson to point to lemmy_v1, rebooted it, and now I can comment perfectly. ⁉️

LemmyNet / lemmy