Randomly missing user_properties entries in mysql database

[deajan]

While developping grommunio_exporter for prometheus, I switched from admin-cli to mysql queries in order to fetch data.

I noticed that mysql results are sometimes incoherent.
Launching mysql manually, I got the following results from the same mysql session, with a couple of seconds between each request.
When the data is missing, just "looking" at the user via the grommunio-admin-gui populates the data again.

MariaDB [grommunio]> SELECT * FROM user_properties WHERE user_id = 46;
+---------+------------+----------+-------------+--------------------+
| user_id | proptag    | order_id | propval_bin | propval_str        |
+---------+------------+----------+-------------+--------------------+
|      46 |  235405332 |        1 | NULL        | 0                  |
|      46 |  237174787 |        1 | NULL        | 29                 |
|      46 |  805371935 |        1 | NULL        | REDACTED NAME      |
|      46 |  805765184 |        1 | NULL        | 133996427090000000 |
|      46 |  956628995 |        1 | NULL        | 0                  |
|      46 |  975568927 |        1 | NULL        | France             |
|      46 | 1713176587 |        1 | NULL        | 0                  |
|      46 | 1723006996 |        1 | NULL        | 0                  |
|      46 | 1723072532 |        1 | NULL        | 0                  |
+---------+------------+----------+-------------+--------------------+
9 rows in set (0,000 sec)

MariaDB [grommunio]> SELECT * FROM user_properties WHERE user_id = 46;
+---------+------------+----------+-------------+--------------------+
| user_id | proptag    | order_id | propval_bin | propval_str        |
+---------+------------+----------+-------------+--------------------+
|      46 |  235405332 |        1 | NULL        | 305680429          |
|      46 |  237174787 |        1 | NULL        | 36                 |
|      46 |  805371935 |        1 | NULL        | REDACTED NAME      |
|      46 |  805765184 |        1 | NULL        | 133996427090000000 |
|      46 |  956628995 |        1 | NULL        | 0                  |
|      46 |  975568927 |        1 | NULL        | France             |
|      46 | 1073020931 |        1 | NULL        | 7340032            |
|      46 | 1713176587 |        1 | NULL        | 0                  |
|      46 | 1718222851 |        1 | NULL        | 6291456            |
|      46 | 1718484995 |        1 | NULL        | 5242880            |
|      46 | 1723006996 |        1 | NULL        | 305629274          |
|      46 | 1723072532 |        1 | NULL        | 51155              |
+---------+------------+----------+-------------+--------------------+
12 rows in set (0,001 sec)

I can reproduce this issue quite easily, on two separate servers.
Any idea why proptags seem to appear / disappear ? Is the mysql database perhaps a cache only for gromox ?

Of course, I still get these error messages (have them since I use gromox 2.1x or so), but they don't necessary correlate (times don't match):

2025-11-13 12:27:11 93336 [Warning] Aborted connection 93336 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 12:44:41 94819 [Warning] Aborted connection 94819 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 12:44:41 94818 [Warning] Aborted connection 94818 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 12:46:00 95797 [Warning] Aborted connection 95797 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 12:46:00 95796 [Warning] Aborted connection 95796 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 13:02:00 97789 [Warning] Aborted connection 97789 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 13:02:00 97790 [Warning] Aborted connection 97790 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 13:06:12 97792 [Warning] Aborted connection 97792 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 13:06:12 97791 [Warning] Aborted connection 97791 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 13:25:00 100617 [Warning] Aborted connection 100617 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)
2025-11-13 13:25:00 100616 [Warning] Aborted connection 100616 to db: 'grommunio' user: 'grommunio' host: 'localhost' (Got an error reading communication packets)

Vanilla mysql config.
Running on OpenSUSE 15.6 uptodate with:

• gromox-3.1.113.g937e7f8-lp156.36.1.x86_64
• mariadb-10.11.14-150600.4.14.1.x86_64

[crpb]

just "looking" at the user via the grommunio-admin-gui populates the data again.

might be this o_0 https://github.com/search?q=repo%3Agrommunio%2Fadmin-api%20embedStoreProperties&type=code

EDIT: after a bit of following the function calls this seems a bit silly how something like _dumpuser calls the sql update call and whatnot, but maybe i missed a bit and this isn't always called...

[jengelh]

The only writes Gromox ever does to MySQL is: user.password, user.lang, user.timezone. Other tables or fields are not touched.

[deajan]

@jengelh Thanks for moving the issue, I still don't know gromox enough it seems

@juliaschroeder I've made some tests
The above result is on my grommunio test server on which I use to create / delete / recreate users for like every test I've ever done.
I've also done the same test on my home mail server which runs a little bit elder grommunio version.

Problem happens on both servers, installed with:

• grommunio-admin-api-1.17.33.0a2d18a-lp156.6.1.noarch.rpm (test server)
• grommunio-admin-api-1.17.32.021a23c-lp156.38.1.noarch.rpm (home mail server)

Please ping me if you need any additional infos.

[juliaschroeder]

Works as intended. The MySQL user_properties table is indeed only a cache for the exmdb store properties, that is synchronized when a user is read by the admin API or CLI. The table is only used to enable efficient search/filter operations for the admin and does not affect the behavior of gromox.

[deajan]

Thanks for the answer... but I must admit that this is quite disappoiting.

I develop a prometheus exporter for grommunio, which used grommunio-admin-api earlier in order to get data like storage quotas etc, but using grommunio-admin-api has been proven to be quite challenging.

When I launch admin-api requests on a per user basis, it works for me, but won't for bigger servers since it's time and resource consuming.
When I launch admin-api and make a shell request for all users, I need to parse the output which is just concatenated single user output (I do this via awk, but this again is slow).

I've got a test server where I generated 500 fake users in order to see whether performance is okay. When using admin-api there (with shell asking for multiple users at once), my exporter timeouts and segfaults, so there's definitly some performance bottleneck.

I tried to switch over to mysql in order to avoid the resource overhead of the admin api.

Is there any sane way to fetch that data somewhere ?

[crpb]

Is there any sane way to fetch that data somewhere ?

maybe the API? 🙈

EDIT: fyi, #33 (comment)

[juliaschroeder]

Retrieving every user via API is probably neither more comfortable nor faster than the current solution.
The main problem here is that the data in question (user/store properties) reside in the individual SQLite databases, while the MySQL table only has (notoriously outdated) cached values¹.
The only way to get up-to-date data is by opening each individual store, which is a comparatively expensive operation, regardless of approach (using AAPI/ACLI, building something based on libexmdbpp or accessing the database directly) and is hardly scalable.

Depending on the desired metrics, it might be easier to integrate the exporter, or at least part of it, directly into gromox to utilize internal structures and mechanisms for efficient data gathering. I will discuss this idea internally and will get back to you.

¹ Outdated in the sense that properties are not automatically kept in sync, but only when accessed by AAPI/ACLI, which is the only purpose of that table

[deajan]

@juliaschroeder Has there ever been some feedback over the discussion ?

I can't find any good solution to this.
Perhaps, an easy solution would be to have an admin-api command to reload all users into mysql cache ?
Such a command could be triggered by the exporter then.