I do run my own Friendica instance and have recently been dealing intensively with the deletion settings. Because nothing is more annoying than a database that keeps growing even though you have actually configured that old posts should be deleted. In this article I will show you how to set and check the deletion settings correctly, and what pitfalls there are.

The Right Order

Before I go into the details, here is the order in which you should proceed.

First you check the current settings in the database. Then you set the global values in local.config.php. After that you take care of the user-specific settings in the pconfig table. Finally you manually trigger the worker and check the result.

Checking the Current Settings

Before you change anything, you should know what is currently configured. There are three queries for this.

Get the uid of the user ilsebilse. The result is the uid (number)

SELECT `uid`, `username`, `nickname`
FROM `user`
WHERE `nickname` = 'ilsebilse' OR `username` = 'ilsebilse';

The global expiry value of the user is in the user table.

SELECT uid, username, expire AS user_expire_days
FROM `user`
WHERE uid = 42;

A value of 0 means that expiry is completely disabled for this user. Only with a value greater than 0 does the ExpirePosts worker process this user at all.

The user-specific deletion settings are in the pconfig table.

SELECT uid, cat, k, v
FROM `pconfig`
WHERE uid = 42
  AND cat = 'expire'
ORDER BY k;

The most important keys are items, starred, notes, photos, and network_only. The photos key is set to false by default. This means that photos normally do not expire.

The global limit for external posts is in the config table.

SELECT `k`, `v`
FROM `config`
WHERE `cat` = 'system'
  AND `k` = 'expire_limit';

A value of 0 means that this function is disabled.

Setting Global Settings in local.config.php

The local.config.php is the central configuration file for instance-specific overrides. It is loaded after the default values and overrides them.

The most important settings for expiry control are these.

<?php

return [
    'system' => [
        ...
        ...
        'expire_limit' => 365,
        'dbclean' => true,
        'dbclean-expire-limit' => 10000,
        'dbclean-expire-days' => 90,
        'dbclean_expire_conversation' => 90,
        'optimize_tables' => true,
        ...
        ...
    ],
];

The expire_limit value controls how many days external posts are retained. A value of 0 disables the function.

The dbclean value activates the automatic database cleanup. Without this value, the ExpirePosts worker does not run automatically on every cron run.

The dbclean-expire-limit value limits how many records are deleted per cron run. The default value is 1000. On active instances this is too low. The database grows faster than the worker can clean up. For medium to large instances I recommend a value between 5000 and 10000.

The dbclean-expire-days value controls how many days remote posts are retained.

The optimize_tables value activates the automatic optimization of frequently used tables. But caution. This option only optimizes a subset of the tables. Other tables with deleted data are not automatically optimized. The storage space is only freed after manual optimization.

Setting User-Specific Settings

Now you take care of the user-specific settings. First you set the expiry duration in days.

UPDATE `user`
SET `expire` = 365
WHERE uid = 42;

Then you activate the desired content types in the pconfig table.

INSERT INTO `pconfig` (`uid`, `cat`, `k`, `v`) VALUES
(42, 'expire', 'items', '1'),
(42, 'expire', 'starred', '1'),
(42, 'expire', 'notes', '1'),
(42, 'expire', 'photos', '1'),
(42, 'expire', 'network_only', '0')
ON DUPLICATE KEY UPDATE `v` = VALUES(`v`);

A value of 1 activates the respective type, a value of 0 deactivates it. If you set photos to 1, photos will also expire.

Checking and Triggering the Worker

After you have set all settings, you check them with a combined query.

SELECT 
    u.uid,
    u.username,
    u.expire AS user_expire_days,
    MAX(CASE WHEN p.k = 'items' THEN p.v END) AS expire_items,
    MAX(CASE WHEN p.k = 'starred' THEN p.v END) AS expire_starred,
    MAX(CASE WHEN p.k = 'notes' THEN p.v END) AS expire_notes,
    MAX(CASE WHEN p.k = 'photos' THEN p.v END) AS expire_photos,
    MAX(CASE WHEN p.k = 'network_only' THEN p.v END) AS expire_network_only
FROM `user` u
LEFT JOIN `pconfig` p ON u.uid = p.uid AND p.cat = 'expire'
WHERE u.uid = 42
GROUP BY u.uid;

Then you manually trigger the worker in the root directory of Friendica. It normally runs at night between 00:00 and 03:00.

php ./bin/console.php worker

The ExpirePosts worker is part of the worker chain and is executed on every cron run.

Summary: The Pitfalls

There was a known bug in Friendica. The user-specific deletion settings had no effect in many versions. Posts are not deleted despite correct values. This was a bug in Friendica and not a configuration problem. - The expiry settings of posts have no effect 路 Issue #14413 路 friendica/friendica

The default value for dbclean-expire-limit is 1000. On active instances this value is too low. The database grows faster than the worker can clean up. The impression arises that the cleanup is not working.

Deleting posts does not automatically free the storage space in the database. For that, the table must be optimized. With large databases, the cleanup can take so long that tables are locked and the instance no longer responds. A manual optimize in maintenance mode is the better option in such cases.

mysqlcheck -u root -p --optimize your_database_name   # with password prompt

The values in local.config.php override the default values from static/defaults.config.php. However, they are themselves overridden by values in the config database table.

Download

Friendica is available as open-source software.