server icon indicating copy to clipboard operation
server copied to clipboard
verified publisher icon nextcloud

[Bug]: After removing a user with occ, some of its related data remains in a few DB tables

Open oleole39 opened this issue 2 years ago • 3 comments

⚠️ This issue respects the following points: ⚠️

  • [X] This is a bug, not a question or a configuration/webserver/proxy issue.
  • [X] This issue is not already reported on Github (I've searched it).
  • [X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
  • [X] Nextcloud Server is running on 64bit capable CPU, PHP and OS.
  • [X] I agree to follow Nextcloud's Code of Conduct.

Bug description

Hello,

Using Nextcloud 26.0.2 (Yunohost package, but it seems not to be limited to that very package), when deleting a user using occ, I notice that if most of that user data is deleted as expected, some do remains (neither particularly heavy nor sensitve data, but still...) in the following tables

  • oc_addressbookchanges
  • oc_jobs - this table seems related to logging, probably it is fine not to remove the deleted user's data from there?
  • oc_storages
  • oc_mail_tags - reported at Nextcloud Mail app's forum
  • oc_notifications_settings - reported at Nextcloud Notification app's Github

Steps to reproduce

  1. Create a user testuser
  2. Associate an email account to it in Nextcloud Mail app (that may be an optional step)
  3. Delete the user using occ user:delete testuser
  4. Check mentioned MySQL table to see whether data related to testuser still remain in there.

Expected behavior

I guess all data related to the deleted user should be removed (or at least that there an option is provided to do so)

Installation method

None

Nextcloud Server version

26

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Nginx

Database engine version

MariaDB

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

  • [ ] Default user-backend (database)
  • [X] LDAP/ Active Directory
  • [X] SSO - SAML
  • [X] Other

Configuration report

{
    "system": {
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "localhost",
            "mydomain.tld"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "25.0.6.1",
        "overwrite.cli.url": "https:\/\/mydomain.tld",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "ldapProviderFactory": "OCA\\User_LDAP\\LDAPProviderFactory",
        "updatechecker": false,
        "memcache.local": "\\OC\\Memcache\\APCu",
        "integrity.check.disabled": true,
        "filelocking.enabled": true,
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": "5555",
            "timeout": "0.0",
            "password": "***REMOVED SENSITIVE VALUE***"
        },
        "hashing_default_password": true,
        "localstorage.allowsymlinks": true,
        "simpleSignUpLink.shown": false,
        "logout_url": "https:\/\/mydomain.tld\/yunohost\/sso\/?action=logout",
        "maintenance": false,
        "loglevel": 2
    }
}

List of activated Apps

Enabled:
  - calendar: 4.3.4
  - circles: 25.0.0
  - cloud_federation_api: 1.8.0
  - comments: 1.15.0
  - contactsinteraction: 1.6.0
  - dashboard: 7.5.0
  - dav: 1.24.0
  - federatedfilesharing: 1.15.0
  - federation: 1.15.0
  - files: 1.20.1
  - files_external: 1.17.0
  - files_mindmap: 0.0.28
  - files_pdfviewer: 2.6.0
  - files_rightclick: 1.4.0
  - files_sharing: 1.17.0
  - files_trashbin: 1.15.0
  - files_versions: 1.18.0
  - firstrunwizard: 2.14.0
  - forms: 3.3.0
  - logreader: 2.10.0
  - lookup_server_connector: 1.13.0
  - mail: 2.2.6
  - nextcloud_announcements: 1.14.0
  - notifications: 2.13.1
  - oauth2: 1.13.0
  - password_policy: 1.15.0
  - photos: 2.0.1
  - polls: 5.0.5
  - privacy: 1.9.0
  - provisioning_api: 1.15.0
  - ransomware_protection: 1.14.0
  - recommendations: 1.4.0
  - related_resources: 1.0.4
  - serverinfo: 1.15.0
  - settings: 1.7.0
  - sharebymail: 1.15.0
  - spreed: 15.0.6
  - support: 1.8.0
  - survey_client: 1.13.0
  - systemtags: 1.15.0
  - tasks: 0.15.0
  - text: 3.6.0
  - theming: 2.0.1
  - twofactor_backupcodes: 1.14.0
  - user_ldap: 1.15.0
  - user_status: 1.5.0
  - viewer: 1.9.0
  - weather_status: 1.5.0
  - workflowengine: 2.7.0
Disabled:
  - accessibility
  - activity: 2.15.0
  - admin_audit
  - bruteforcesettings
  - deck: 1.8.5
  - encryption
  - files_videoplayer
  - groupfolders: 13.1.3
  - integration_whiteboard: 0.0.14
  - richdocuments: 7.1.4
  - shifts: 1.8.6
  - suspicious_login
  - talked: 0.5.0
  - twofactor_totp
  - updatenotification: 1.12.0

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

oleole39 avatar Jun 07 '23 03:06 oleole39

I'm not sure we can really clear the oc_jobs table. Each app would need to decide if the job is still relevant or not. Maybe the deleted user triggered something that is to be executed for everyone (e.g. announcements queue the activity/notification generation as background job and they should still get created even if the poster got deleted)

Which specific jobs did you find?

nickvergessen avatar Jun 21 '23 08:06 nickvergessen

I'm not sure we can really clear the oc_jobs table. Each app would need to decide if the job is still relevant or not. Maybe the deleted user triggered something that is to be executed for everyone (e.g. announcements queue the activity/notification generation as background job and they should still get created even if the poster got deleted)

Which specific jobs did you find?

I didn't keep the database dump from the time of my initial report, but I have just made a new test and I can see the following DB entries remaining in oc_jobs:

INSERT INTO `oc_jobs` VALUES (377,'OCA\\Settings\\BackgroundJobs\\VerifyUserData','{\"verificationCode\":\"\",\"data\":\"[email protected]\",\"type\":\"email\",\"uid\":\"newtestuser\",\"try\":0,\"lastRun\":1687344003}',0,1687347603,0,0,'9af5e760c1e4480ec1518b4a0d196377',1);
INSERT INTO `oc_jobs` VALUES (391,'OCA\\Forms\\BackgroundJob\\UserDeletedJob','{\"owner_id\":\"newtestuser\"}',0,1687347659,0,0,'f8daf9579cc4744ed433f733c77306da',1);
INSERT INTO `oc_jobs` VALUES (392,'OCA\\Polls\\Cron\\UserDeletedJob','{\"owner\":\"newtestuser\"}',0,1687347659,0,0,'6ebc4694b7ca5b46fc2abe90eaf35738',1);

For me this is not a problem if that remained logged, but I don't know whether it can appear as sensitive data to some other people, or whether on large instances and user accounts with a longer life than the account I just created and deleted for test purpose it could consists of a significant amount of useless data.

For future reference, below is the full set of database's entries matching the test user's name after user removal (using occ user:delete)

Full log after user deletion using occ 
root@test:~# mysqldump -u root --no-create-info --extended-insert=FALSE nextcloud | grep -i "newtestuser"
INSERT INTO `oc_addressbookchanges` VALUES (41,'LDAP:newtestuser.vcf',41,2,1);
INSERT INTO `oc_addressbookchanges` VALUES (42,'LDAP:newtestuser.vcf',42,2,2);
INSERT INTO `oc_addressbookchanges` VALUES (43,'LDAP:newtestuser.vcf',43,2,3);
INSERT INTO `oc_circles_event` VALUES ('a3c9f7d8-a8f3-4aea-bf9d-b5f9e27f35c0','mydomain.tld','{\"class\":\"OCA\\\\Circles\\\\FederatedItems\\\\SingleMemberAdd\",\"severity\":3,\"params\":[],\"internal\":[],\"data\":[],\"result\":[],\"origin\":\"mydomain.tld\",\"sender\":\"mydomain.tld\",\"itemId\":\"\",\"outcome\":{\"id\":\"qkkkK53hhUFHa6FAAxZclqnA5hTbUnj\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":1,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":0,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":0,\"invitedBy\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"displayName\":\"Circles\",\"instance\":\"mydomain.tld\",\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null,\"owner\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"circleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":1687344005,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1687344005,\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null}}},\"circle\":{\"id\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"name\":\"group:special_permissions_group\",\"displayName\":\"special_permissions_group\",\"sanitizedName\":\"\",\"source\":2,\"population\":0,\"config\":1540,\"description\":\"\",\"url\":\"\",\"creation\":1656727202,\"initiator\":{\"id\":\"GQaOjV6JmwR3x6pqoOJloT3EjDGS8B3\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1656727202},\"owner\":{\"id\":\"GQaOjV6JmwR3x6pqoOJloT3EjDGS8B3\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1656727202,\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"settings\":{\"population\":0,\"populationInherited\":0}}},\"members\":[{\"id\":\"qkkkK53hhUFHa6FAAxZclqnA5hTbUnj\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":1,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":0,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":0,\"invitedBy\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"displayName\":\"Circles\",\"instance\":\"mydomain.tld\",\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null,\"owner\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"circleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":1687344005,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1687344005,\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null}}},\"circle\":{\"id\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"name\":\"group:special_permissions_group\",\"displayName\":\"special_permissions_group\",\"sanitizedName\":\"\",\"source\":2,\"population\":0,\"config\":1540,\"description\":\"\",\"url\":\"\",\"creation\":1656727202,\"initiator\":{\"id\":\"GQaOjV6JmwR3x6pqoOJloT3EjDGS8B3\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1656727202},\"owner\":{\"id\":\"GQaOjV6JmwR3x6pqoOJloT3EjDGS8B3\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1656727202,\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"settings\":{\"population\":0,\"populationInherited\":0}}}],\"circle\":{\"id\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"name\":\"group:special_permissions_group\",\"displayName\":\"special_permissions_group\",\"sanitizedName\":\"\",\"source\":2,\"population\":0,\"config\":1540,\"description\":\"\",\"url\":\"\",\"creation\":1656727202,\"initiator\":{\"id\":\"GQaOjV6JmwR3x6pqoOJloT3EjDGS8B3\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1656727202},\"owner\":{\"id\":\"GQaOjV6JmwR3x6pqoOJloT3EjDGS8B3\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1656727202,\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"settings\":{\"population\":0,\"populationInherited\":0}},\"member\":{\"id\":\"qkkkK53hhUFHa6FAAxZclqnA5hTbUnj\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":1,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":0,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":0,\"invitedBy\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"displayName\":\"Circles\",\"instance\":\"mydomain.tld\",\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null,\"owner\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"circleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":1687344005,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1687344005,\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null}}},\"circle\":{\"id\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"name\":\"group:special_permissions_group\",\"displayName\":\"special_permissions_group\",\"sanitizedName\":\"\",\"source\":2,\"population\":0,\"config\":1540,\"description\":\"\",\"url\":\"\",\"creation\":1656727202,\"initiator\":{\"id\":\"GQaOjV6JmwR3x6pqoOJloT3EjDGS8B3\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1656727202},\"owner\":{\"id\":\"GQaOjV6JmwR3x6pqoOJloT3EjDGS8B3\",\"circleId\":\"CW3amqI3dhDDVwqQhOS8JJS9WARwQdh\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1656727202,\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"settings\":{\"population\":0,\"populationInherited\":0}}}}','[]',0,3,0,0,NULL,1687346702);
INSERT INTO `oc_circles_event` VALUES ('f58d4e9e-be01-47a3-ba74-950a71ad33cf','mydomain.tld','{\"class\":\"OCA\\\\Circles\\\\FederatedItems\\\\SingleMemberAdd\",\"severity\":3,\"params\":[],\"internal\":[],\"data\":[],\"result\":[],\"origin\":\"mydomain.tld\",\"sender\":\"mydomain.tld\",\"itemId\":\"\",\"outcome\":{\"id\":\"ipRiHQQUmldogXOOenQSm5u2vVnXHh5\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":1,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":0,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":0,\"invitedBy\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"displayName\":\"Circles\",\"instance\":\"mydomain.tld\",\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null,\"owner\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"circleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":1687344005,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1687344005,\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null}}},\"circle\":{\"id\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"name\":\"group:all_users\",\"displayName\":\"all_users\",\"sanitizedName\":\"\",\"source\":2,\"population\":0,\"config\":1540,\"description\":\"\",\"url\":\"\",\"creation\":1653616801,\"initiator\":{\"id\":\"gYHX6LJ7498wHNoXkfWmD2J8ngHgqke\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1653616801},\"owner\":{\"id\":\"gYHX6LJ7498wHNoXkfWmD2J8ngHgqke\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1653616801,\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"settings\":{\"population\":0,\"populationInherited\":0}}},\"members\":[{\"id\":\"ipRiHQQUmldogXOOenQSm5u2vVnXHh5\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":1,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":0,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":0,\"invitedBy\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"displayName\":\"Circles\",\"instance\":\"mydomain.tld\",\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null,\"owner\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"circleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":1687344005,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1687344005,\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null}}},\"circle\":{\"id\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"name\":\"group:all_users\",\"displayName\":\"all_users\",\"sanitizedName\":\"\",\"source\":2,\"population\":0,\"config\":1540,\"description\":\"\",\"url\":\"\",\"creation\":1653616801,\"initiator\":{\"id\":\"gYHX6LJ7498wHNoXkfWmD2J8ngHgqke\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1653616801},\"owner\":{\"id\":\"gYHX6LJ7498wHNoXkfWmD2J8ngHgqke\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1653616801,\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"settings\":{\"population\":0,\"populationInherited\":0}}}],\"circle\":{\"id\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"name\":\"group:all_users\",\"displayName\":\"all_users\",\"sanitizedName\":\"\",\"source\":2,\"population\":0,\"config\":1540,\"description\":\"\",\"url\":\"\",\"creation\":1653616801,\"initiator\":{\"id\":\"gYHX6LJ7498wHNoXkfWmD2J8ngHgqke\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1653616801},\"owner\":{\"id\":\"gYHX6LJ7498wHNoXkfWmD2J8ngHgqke\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1653616801,\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"settings\":{\"population\":0,\"populationInherited\":0}},\"member\":{\"id\":\"ipRiHQQUmldogXOOenQSm5u2vVnXHh5\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":1,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":0,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":0,\"invitedBy\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"displayName\":\"Circles\",\"instance\":\"mydomain.tld\",\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null,\"owner\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"circleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"singleId\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"userId\":\"newtestuser\",\"userType\":1,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"newtestuser\",\"displayUpdate\":1687344005,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1687344005,\"basedOn\":{\"id\":\"5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"name\":\"user:newtestuser:5R13xYIyd6pXnJsmtlKe9fXFsaP1z84\",\"displayName\":\"newtestuser\",\"sanitizedName\":\"\",\"source\":1,\"population\":0,\"config\":1,\"description\":\"\",\"url\":\"\",\"creation\":1687344005,\"initiator\":null}}},\"circle\":{\"id\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"name\":\"group:all_users\",\"displayName\":\"all_users\",\"sanitizedName\":\"\",\"source\":2,\"population\":0,\"config\":1540,\"description\":\"\",\"url\":\"\",\"creation\":1653616801,\"initiator\":{\"id\":\"gYHX6LJ7498wHNoXkfWmD2J8ngHgqke\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1653616801},\"owner\":{\"id\":\"gYHX6LJ7498wHNoXkfWmD2J8ngHgqke\",\"circleId\":\"wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf\",\"singleId\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"userId\":\"circles\",\"userType\":10000,\"instance\":\"mydomain.tld\",\"local\":true,\"level\":9,\"status\":\"Member\",\"displayName\":\"Circles\",\"displayUpdate\":1663988401,\"notes\":[],\"contactId\":\"\",\"contactMeta\":\"\",\"joined\":1653616801,\"basedOn\":{\"id\":\"8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"name\":\"app:circles:8glKpyICJhSe79QlL6xsw9myfe7kmzq\",\"displayName\":\"Circles\",\"sanitizedName\":\"\",\"source\":10001,\"population\":0,\"config\":8193,\"description\":\"\",\"url\":\"\",\"creation\":1653612015,\"initiator\":null}},\"settings\":{\"population\":0,\"populationInherited\":0}}}}','[]',0,3,0,0,NULL,1687346702);
INSERT INTO `oc_circles_member` VALUES (34,'5R13xYIyd6pXnJsmtlKe9fXFsaP1z84','wnzbSPKe4pwKkrOH3wmObv5Pu8DRLAf','ipRiHQQUmldogXOOenQSm5u2vVnXHh5','newtestuser',1,'','8glKpyICJhSe79QlL6xsw9myfe7kmzq',1,'Member','[]','newtestuser','2023-06-21 11:25:02','',NULL,'2023-06-21 11:25:02');
INSERT INTO `oc_circles_member` VALUES (35,'5R13xYIyd6pXnJsmtlKe9fXFsaP1z84','CW3amqI3dhDDVwqQhOS8JJS9WARwQdh','qkkkK53hhUFHa6FAAxZclqnA5hTbUnj','newtestuser',1,'','8glKpyICJhSe79QlL6xsw9myfe7kmzq',1,'Member','[]','newtestuser','2023-06-21 11:25:02','',NULL,'2023-06-21 11:25:02');
INSERT INTO `oc_filecache` VALUES (16325,2,'appdata_ocj3l4h9vi3l/identityproof/user-newtestuser','8db59aecb39e7227335b104ba7e72b4b',62,'user-newtestuser',2,1,0,1687344303,1687344303,0,0,'6492d4af51c04',31,'');
INSERT INTO `oc_filecache` VALUES (16326,2,'appdata_ocj3l4h9vi3l/identityproof/user-newtestuser/private','f745cb450f98ab481ff7f392100bdd35',16325,'private',8,3,3588,1687344303,1687344303,0,0,'1b16a17743b0152ef7c5b90819665a18',27,'');
INSERT INTO `oc_filecache` VALUES (16327,2,'appdata_ocj3l4h9vi3l/identityproof/user-newtestuser/public','16d5ab76b2f3ca5a00065ba49a455378',16325,'public',8,3,451,1687344303,1687344303,0,0,'cd29129bf0147a27cb888f09f8a64a92',27,'');
INSERT INTO `oc_jobs` VALUES (377,'OCA\\Settings\\BackgroundJobs\\VerifyUserData','{\"verificationCode\":\"\",\"data\":\"[email protected]\",\"type\":\"email\",\"uid\":\"newtestuser\",\"try\":0,\"lastRun\":1687344003}',0,1687347603,0,0,'9af5e760c1e4480ec1518b4a0d196377',1);
INSERT INTO `oc_jobs` VALUES (391,'OCA\\Forms\\BackgroundJob\\UserDeletedJob','{\"owner_id\":\"newtestuser\"}',0,1687347659,0,0,'f8daf9579cc4744ed433f733c77306da',1);
INSERT INTO `oc_jobs` VALUES (392,'OCA\\Polls\\Cron\\UserDeletedJob','{\"owner\":\"newtestuser\"}',0,1687347659,0,0,'6ebc4694b7ca5b46fc2abe90eaf35738',1);
INSERT INTO `oc_ldap_group_members` VALUES ('all_users','a:4:{i:0;s:2:\"fb\";i:1;s:12:\"user1\";i:2;s:4:\"user2\";i:3;s:13:\"newtestuser\";}');
INSERT INTO `oc_ldap_group_members` VALUES ('special_permissions_group','a:4:{i:0;s:2:\"fb\";i:1;s:12:\"user1\";i:2;s:4:\"user2\";i:3;s:13:\"newtestuser\";}');
INSERT INTO `oc_mail_tags` VALUES (32,'newtestuser','$label1','Important','#FF7A66',1);
INSERT INTO `oc_mail_tags` VALUES (33,'newtestuser','$label2','Travail','#31CC7C',1);
INSERT INTO `oc_mail_tags` VALUES (34,'newtestuser','$label3','Personnel','#A85BF7',1);
INSERT INTO `oc_mail_tags` VALUES (35,'newtestuser','$label4','À faire','#317CCC',1);
INSERT INTO `oc_mail_tags` VALUES (36,'newtestuser','$label5','Plus tard','#B4A443',1);
INSERT INTO `oc_notifications_settings` VALUES (7,'newtestuser',0,0,0);
INSERT INTO `oc_storages` VALUES (24,'local::/home/yunohost.multimedia/newtestuser/',1,NULL);

oleole39 avatar Jun 21 '23 12:06 oleole39

At least the last 2 of those jobs are actual clean up jobs to remove data of the deleted user as I mentioned and expected above. So yeah can't clear that table.

nickvergessen avatar Jun 22 '23 07:06 nickvergessen

Understood. And for the sake of completeness, do you think something can be done for:

  • oc_addressbookchanges
  • oc_filecache
  • oc_storages

Note about the log pasted in my previous message - mentions of:

  • oc_circles_event
  • oc_circles_member
  • oc_ldap_group_members
  • and of course oc_notifications_settings

have disappeared after applying your fix in the notification thread. For the 3 first tables, I am not sure why though (maybe I had been dumping the database too quick after user deletion for the log here above (although I kind of remember following more or less the same rhythm in both cases)...

oleole39 avatar Jun 23 '23 12:06 oleole39

I guess you need to post more detailed what data you found in those tables

nickvergessen avatar Jun 23 '23 12:06 nickvergessen

I guess you need to post more detailed what data you found in those tables

Have you found the full log at the very end of this message (inside <details> tag)? Not sure what kind of additional data you would be referring to?

oleole39 avatar Jun 23 '23 17:06 oleole39