engine:select fetching old data

[genilt...@gmail.com]

I have an administration panel that changes the prosody's DB (Mysql).
When I add more users (affiliations) to the muc, the prosody does not update.
And when I select inside one module, it does not bring the updated data that is in the DB.

Tried with:
engine:select("SELECT SQL_NO_CACHE `value` FROM `prosody` WHERE `key`='_affiliations' AND `user`=? AND `host`=?", jid, host) 

The result is OLD data. Not the current data in de database?!?

Prosody have any query/object cache? How to disable? Any idea to make the prosody update the affiliations that there are in the db?

Thanks!

[Kim Alvefur]

Hi,

On Sat, Feb 27, 2021 at 05:59:26AM -0800, genilt...@gmail.com wrote:
> I have an administration panel that changes the prosody's DB (Mysql).
> When I add more users (affiliations) to the muc, the prosody does not
> update. And when I select inside one module, it does not bring the
> updated data that is in the DB.
>

> Prosody have any query/object cache? How to disable? Any idea to make
> the prosody update the affiliations that there are in the db?

While rooms are active, the authoritative copy of it lives in memory
only. Attempting to change any data of anything active via the data
storage layer will not work, it will get overwritten by Prosody at some
point.

Same with most user data, e.g. deleting the account of an online user
will have no effect and the user can simply change their password to
restore their account. Contact lists and other data are also cached in
memory.

Databases generally don't have any way to tell Prosody that the data
changed, so Prosody has no way to react to changes in data that it's not
told about.

The safe way to interact with Prosody to ensure correct behavior is to
do it via online APIs or while Prosody is shut down. For MUC
affiliations an ad-hoc command has been added in trunk[^1] to allow
changing them from outside the room. Something similar could be done with
a HTTP API if that works better for you. That ad-hoc command could be
called via [mod_rest](https://modules.prosody.im/mod_rest) even.

[^1]: <https://hg.prosody.im/trunk/rev/e4034f6668a5>

--
hope this helps,
Kim "Zash" Alvefur

[Thilo Molitor]

I've written exactly that: a prosody module providing a http api to create/
delete muc rooms and/or add remove users to/from these rooms (attached to this
email).
It can pull config via http and handles config pushes.
The data is json for both cases.
Authentication is done via a simple secret token that can be configured and
must be provided in the "X-KWO-TOKEN" http header when calling the http
endpoint of this module.
Load the module like this onto the groupchat component:
Component "groupchats.examples.org" "muc"
kwo_groups_url = "https://url/to/pull/groups/from"
kwo_groups_token = "token to use for authentication";
modules_enabled = {
"kwo_groups",
}

Feel free to use and/or modify it.
I even wrote a module that adds all groups on the local groupserver component
a user is in to the XEP-0048 bookmarks of the user (not changing other
bookmarks the user creates manually).
I attached that module as well.
Load it onto all "normal" hosts you want or load it globally and configure the
setting "muc_host" to point to the local groupserver component you want to
automatically create bookmarks for.

In the hope that those modules will be useful - tmolitor

@zash: do you think that could be something worth publishing to
modules.prosody.im, too?