PostgreSQL Missing Column

[levniko]

Before creating a ticket, please consider if this should fit the discussion forum better: https://github.com/processone/ejabberd/discussions

Environment

• ejabberd version: 23.01
• Erlang version: erl + "24"
• OS: WSL2 Linux (Ubuntu 22.04)
• Installed from: official binary installer

Configuration (only if needed): grep -Ev '^$|^\s*#' ejabberd.yml

auth_method: [jwt, sql]
jwt_key: /etc/ejabberd/secret.jwk
default_db: sql
new_sql_schema: true
sql_type: pgsql
sql_server: "localhost"
sql_database: "ejabberddb"
sql_username: "admin"
sql_password: "password"
sql_port: 5432
access_rules:
  jwt_only:
    deny: admin
    allow: all
  local:
    allow: local
  c2s:
    deny: blocked
    allow: all
  announce:
    allow: admin
  configure:
    allow: admin
  muc_create:
    allow: local
  pubsub_createnode:
    allow: local
  trusted_network:
    allow: loopback

jwt_auth_only_rule: jwt_only
...
...
...
  mod_push_keepalive: {}
  ## mod_register:
  ##   ## Only accept registration requests from the "trusted"
  ##   ## network (see access_rules section above).
  ##   ## Think twice before enabling registration from any
  ##   ## address. See the Jabber SPAM Manifesto for details:
  ##   ## https://github.com/ge0rg/jabber-spam-fighting-manifesto
  ##   ip_access: trusted_network
  mod_roster:
    db_type: sql
    versioning: true
  mod_s2s_dialback: {}
  mod_shared_roster: {}
  mod_sic: {}
  mod_stream_mgmt:

Errors from error.log

2023-02-14 16:17:07.443024+03:00 [error] <0.454.0>@ejabberd_sql:check_error/2:1236 SQL query 'Q22045648' at {mod_mqtt_sql,{112,10}} failed: [{severity,'ERROR'},
                                                          {86,<<"ERROR">>},
                                                          {code,<<"42703">>},
                                                          {message,
                                                           <<"column \"server_host\" does not exist">>},
                                                          {position,34},
                                                          {file,
                                                           <<"parse_relation.c">>},
                                                          {line,3633},
                                                          {routine,
                                                           <<"errorMissingColumn">>}]

Bug description

I made the necessary setups in the ejabberd.yml file to connect psql. Host port user database etc. https://raw.githubusercontent.com/processone/ejabberd/master/sql/pg.sql I downloaded the required schemas from here and checked. But I am getting errorMissingColumn error above. How can I fix this ?

[licaon-kter]

Setup has new_sql_schema but you imported pg.sql instead of pg_new.sql

Do bookmark https://docs.ejabberd.im to look up each .yml option.

[levniko]

Under this heading I used the codes but there is lots of error ?

Fixes in PostgreSQL New Schema https://docs.ejabberd.im/admin/upgrade/from_21.12_to_22.05/#new-indexes-in-sql-for-muc

ejabberddb=> ALTER TABLE archive DROP CONSTRAINT i_archive_sh_peer;
ALTER TABLE archive DROP CONSTRAINT i_archive_sh_bare_peer;
CREATE INDEX i_archive_sh_username_peer ON archive USING btree (server_host, username, peer);
CREATE INDEX i_archive_sh_username_bare_peer ON archive USING btree (server_host, username, bare_peer);

DROP TABLE carboncopy;

ALTER TABLE push_session DROP CONSTRAINT i_push_session_susn;
CREATE UNIQUE INDEX i_push_session_susn ON push_session USING btree (server_host, username, service, node);

ALTER TABLE mix_pam DROP CONSTRAINT i_mix_pam;
ALTER TABLE mix_pam DROP CONSTRAINT i_mix_pam_us;
CREATE UNIQUE INDEX i_mix_pam ON mix_pam (username, server_host, channel, service);
CREATE INDEX i_mix_pam_us ON mix_pam (username, server_host);

ALTER TABLE route DROP CONSTRAINT i_route;
CREATE UNIQUE INDEX i_route ON route USING btree (domain, server_host, node, pid);

ALTER TABLE mqtt_pub DROP CONSTRAINT i_mqtt_topic;
CREATE UNIQUE INDEX i_mqtt_topic_server ON mqtt_pub (topic, server_host);
ERROR:  constraint "i_archive_sh_peer" of relation "archive" does not exist
ERROR:  constraint "i_archive_sh_bare_peer" of relation "archive" does not exist
ERROR:  column "server_host" does not exist
ERROR:  column "server_host" does not exist
ERROR:  table "carboncopy" does not exist
ERROR:  constraint "i_push_session_susn" of relation "push_session" does not exist
ERROR:  column "server_host" does not exist
ERROR:  constraint "i_mix_pam" of relation "mix_pam" does not exist
ERROR:  constraint "i_mix_pam_us" of relation "mix_pam" does not exist
ERROR:  column "server_host" does not exist
ERROR:  column "server_host" does not exist
ERROR:  constraint "i_route" of relation "route" does not exist
ERROR:  relation "i_route" already exists
ERROR:  constraint "i_mqtt_topic" of relation "mqtt_pub" does not exist
ERROR:  column "server_host" does not exist```

These are the table that I imported.

            List of relations

Schema	Name	Type	Owner --------+-------------------------+-------+------- public	archive	table	admin public	archive_prefs	table	admin public	bosh	table	admin public	caps_features	table	admin public	last	table	admin public	mix_channel	table	admin public	mix_pam	table	admin public	mix_participant	table	admin public	mix_subscription	table	admin public	motd	table	admin public	mqtt_pub	table	admin public	muc_online_room	table	admin public	muc_online_users	table	admin public	muc_registered	table	admin public	muc_room	table	admin public	muc_room_subscribers	table	admin public	oauth_client	table	admin public	oauth_token	table	admin public	privacy_default_list	table	admin public	privacy_list	table	admin public	privacy_list_data	table	admin public	private_storage	table	admin public	proxy65	table	admin public	pubsub_item	table	admin public	pubsub_node	table	admin public	pubsub_node_option	table	admin public	pubsub_node_owner	table	admin public	pubsub_state	table	admin public	pubsub_subscription_opt	table	admin public	push_session	table	admin public	roster_version	table	admin public	rostergroups	table	admin public	rosterusers	table	admin

@licaon-kter 

[prefiks]

Are you creating new database or you have some data that you try to migrate to new version?

If that's new installation i would suggest dropping this db and create it fresh using pg_new.sql.

[levniko]

yes I want to new installation. How can I import this schema ? @prefiks

[prefiks]

And how did you import your previous one? Generally in similar way just please use content from that link from my last answer.

[levniko]

root@LAPTOP-75E6G2GU:/home/levniko# psql -h localhost -d ejabberddb -U admin - pg.new.sql
psql: warning: extra command-line argument "-" ignored
psql: warning: extra command-line argument "pg.new.sql" ignored

There is still errors :( @prefiks

[prefiks]

i think it should be psql -h localhost -d ejabberddb -U admin -f pg.new.sql?

[levniko]

Sorry, my fault. I works now. Thanks a lot @prefiks