ejabberd sql DB 'rosterusers' table is only partially filled with user in sr_user

Before creating a ticket, please consider if this should fit the discussion forum better.

Environment

• ejabberd version: 26.04
• Erlang version: Erlang/OTP 27 [erts-15.2.7]
• OS: Ubuntu 22.04
• Installed from: source

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

loglevel: 5

  mod_roster:
    db_type: sql
    versioning: true
  mod_shared_roster:
    db_type: sql
...

<img width="800" height="600" alt="Image" src="https://github.com/user-attachments/assets/8b86a4d9-c87c-4726-825b-78a45885a4d0" />

Errors from error.log/crash.log

No errors

Bug description

A bit of history:
ejabberd 25.10 was installed on an old machine 'Giada', and ejabberd was running well on this system without any problem. However 'Giada' machine crashes during an OS upgrade to ubuntu 22.04; until the system is unable to boot. Hence decided to rebuild the whole home server on a new machine 'G11' with ubuntu 22.04 and ejabberd 26.04.

Following observations are based on ejabberd 26.04 installed on the new machine.
An sr_group was created with the sql table as shown below

Sql table sr_group

('atalk_member', '[{label,<<\"atalk_member\">>},{displayed_groups,[<<\"atalk_member\">>]},{description,<<\"atalk member\">>}]', '2026-07-22 01:33:18');

Sql table `sr_user

('abc123@atalk.sytes.net', 'atalk_member', '2026-07-22 01:33:56'),
('eagle@atalk.sytes.net', 'atalk_member', '2026-07-22 01:34:05'),
('hawk@atalk.sytes.net', 'atalk_member', '2026-07-22 01:34:13'),
('kingfisher@atalk.sytes.net', 'atalk_member', '2026-07-22 01:34:23'),
('leopard@atalk.sytes.net', 'atalk_member', '2026-07-22 01:34:31'),
('parrot@atalk.sytes.net', 'atalk_member', '2026-07-22 01:34:38'),
('peacock@atalk.sytes.net', 'atalk_member', '2026-07-22 01:34:47'),
('swordfish@atalk.sytes.net', 'atalk_member', '2026-07-22 01:34:56'),
('swan@atalk.sytes.net', 'atalk_member', '2026-07-22 01:35:04'),
('swifts@atalk.sytes.net', 'atalk_member', '2026-07-22 01:35:13'),
('xyz123@atalk.sytes.net', 'atalk_member', '2026-07-22 01:35:29');

However it was found that the 'rosterusers' is only partially filled with only a single item, and 'rostergroups' table is empty:

Table `rosterusers`
('abc123', 'abc123@atalk.org', 'abc123', 'N', 0, 'O', '', 'N', '', 'item', '2026-07-31 08:19:16');

I am not sure if the above sql table conents is the new sql scheme design for ejabberd v26.04.

According to XEP-0060: Publish-Subscribe
The protocol enables XMPP entities to create nodes (topics) at a pubsub service and publish information at those nodes; an event notification (with or without payload) is then broadcasted to all entities that have subscribed to the node.

While testing on aTalk, it was found that ejabberd server, sending the omemo pubsub event is not consistent to all users in the sr_user'; in particular the pubsub event e.g.

<pubsub xmlns='http://jabber.org/protocol/pubsub'>
  <items node='eu.siacs.conversations.axolotl.devicelist'>
    <item id='current'>
      <list xmlns='eu.siacs.conversations.axolotl'>
        <device id='495696021'/>
      </list>
   </item>
 </items>
</pubsub>

is not broadcast to some of the contacts (in sr_user) when the user has registered with the server. This lead to problem when sending omemo messages. My understanding is that:

Subscription and Presence: When a user connects or updates presence and send_last_published_item is triggered for active subscriptions.

Should't that the ejabberd server auto sends the pubsub event when a user is registered with the server;
OR Is that an option not properly defined in yml configure file as in my case.
OR the pubub event must manually request by user upon registered with the server.
Please advice.

In my old "Giada' machine the sql data contains the following: The 'subscription' is saved as 'B' for all users
Can this be the reason for the above observed pubsub event problem.

-- Previous sql data prior to update to ejabberd 26.04 ----
-- This Sql DB was last working well with 25.10 ----

--
-- Dumping data for table `rostergroups`
--

INSERT INTO `rostergroups` (`username`, `jid`, `grp`) VALUES
('hawk', 'cmeng@atalk.sytes.net', 'atalk member'),
('leopard', 'xyz123@atalk.sytes.net', 'atalk member'),
('leopard', 'eagle@atalk.sytes.net', 'atalk member'),
('leopard', 'swan@atalk.sytes.net', 'atalk member'),
('leopard', 'parrot@atalk.sytes.net', 'atalk member'),
('leopard', 'hawk@atalk.sytes.net', 'atalk member'),
('leopard', 'asduser@atalk.sytes.net', 'atalk member'),
('leopard', 'swordfish@atalk.sytes.net', 'atalk member'),
('leopard', 'peacock@atalk.sytes.net', 'atalk member'),
('leopard', 'kingfisher@atalk.sytes.net', 'atalk member'),
('leopard', 'abc123@atalk.sytes.net', 'atalk member'),
('swordfish', 'abc123@atalk.sytes.net', 'atalk_member'),
('swordfish', 'kingfisher@atalk.sytes.net', 'atalk_member'),
('swordfish', 'swan@atalk.sytes.net', 'atalk_member'),
('abc123', 'leopard@atalk.sytes.net', 'atalk_member'),
('abc123', 'swan@atalk.sytes.net', 'atalk_member'),
('abc123', 'swordfish@atalk.sytes.net', 'atalk_member'),
('swan', 'swan@atalk.sytes.net', 'atalk_member'),
('swordfish', 'swordfish@atalk.sytes.net', 'atalk_member');

--
-- Dumping data for table `rosterusers`
--

INSERT INTO `rosterusers` (`username`, `jid`, `nick`, `subscription`, `ask`, `askmessage`, `server`, `subscribe`, `type`, `created_at`) VALUES
('abc123', 'leopard@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2022-02-17 01:09:31'),
('abc123', 'swan@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2022-02-17 01:09:38'),
('leopard', 'hawk@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2017-05-20 09:07:57'),
('hawk', 'leopard@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2017-05-20 09:07:57'),
('leopard', 'abc123@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2017-06-05 22:06:3 'item', '2018-09-20 11:21:04'),
('swordfish', 'swan@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2022-01-08 02:14:59'),
('swan', 'swordfish@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-06-21 05:47:00'),
('swordfish', 'abc123@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2022-01-08 02:14:11'),
('abc123', 'swordfish@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2022-02-17 01:09:39'),
('swordfish', 'kingfisher@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2022-01-08 02:14:41'),
('kingfisher', 'swordfish@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-06-21 06:05:55'),
('leopard', 'xyz123@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-08-25 02:07:06'),
('leopard', 'eagle@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-08-25 02:07:07'),
('leopard', 'swan@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-08-25 02:07:07'),
('leopard', 'parrot@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-08-25 02:07:07'),
('leopard', 'swordfish@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-08-25 02:07:07'),
('leopard', 'peacock@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-08-25 02:07:07'),
('leopard', 'kingfisher@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2020-08-25 02:07:07'),
('swan', 'swan@atalk.sytes.net', '', 'B', 'N', '', 'N', '', 'item', '2023-01-09 08:16:29'),
('swordfish', 'swordfish@atalk.sytes.net', 'swordfish@atalk.sytes.net', 'B', 'N', '', 'N', '', 'item', '2023-02-02 03:00:10');

Start with the mod_roster and mod_shared_roster configuration and compare the current rosterusers and rostergroups data with the previous database dump. Reproduce registration and OMEMO pubsub delivery for users in sr_user, then trace whether the missing roster records correlate with inconsistent events. Done means the cause is identified and pubsub delivery behavior is consistent or the required configuration or migration is documented.