Messages not removed from MYSQL when user deleted+orphan store removed.

IShouldHaveBeenAQATester

Hello,

I’ve been testing various methods to import from imap, and i’ve noticed that when I delete a user the store orphans. After I use kopano-storeadm -R <guid>, there is still data in the mysql database.

Is there a way to clean this up? I’d like to know that when I delete a store permanently, the messages/store aren’t still taking up space on disk.

IShouldHaveBeenAQATester

@ishouldhavebeenaqatester

Well, ok I think I solved this as well. I got my mysql store down from 16G (with no emails in any mailbox) to 500M or so by doing the following:

kopano-admin --purge-softdelete 0
kopano-admin --clear-cache

And then I logged into the mysql server as root & did
“optimize table tproperties”
“optimize table properties”

Hopefully this can help someone else with storage cleanup.

fbartels

@ishouldhavebeenaqatester it feels like you got the size of your database from the mysql/mariadb data directory and not from a dump/sql query.

If that is the case then your observation is correct. The ibdata file does not shrink when lots of data is deleted in the database. One should also note that kopano-server should be stopped before running optimize-tables

PS: when reporting issues its always helpful to also include some information about the environment. Like operating system, versions of all components involved (os, core, WebApp, client os, browser, …).

IShouldHaveBeenAQATester

@fbartels Thanks for the response & tips.

I did do a full mysql dump (after deleteing the users & deleting the orphan stores) to make sure I wasn’t just seeing the mysql (directory) size, and that there was actual data there. Here is my dump size:

root@po:~# du -hs /root/kopanoserver.mysql 
12G     /root/kopanoserver.mysql

However, after I had dropped the stores, I had TONS of mail actually in the database. I could do a select * from (i think it was either tproperties or properties) and see e-mail bodies!!! I am not sure why that wasn’t cleaned up when i dropped the stores…? Any ideas on that?

I was under the impression the --purge-softdelete 0 and --clear-cache were for cleaning up user email that was in an existing store, not for the problem i was trying to solve. From what I could see in the documentation as well as kopano-admin and kopano-storeadm, I didn’t see anything on clenaing up email left in mysql from a dropped store.

In any case, thanks for your response. I’m a Zimbra OSS refugee (I run mail at home for my family for the 3 people living in my household) and still learning.

Also: You are right, I should include os/software version. For those seeing this thread at a later date:

rob2@po:~$ dpkg -l | grep kopano
ii  kopano-backup                        8.7.0-7ubuntu1                    all          Complete and feature rich groupware solution - backup
ii  kopano-common                        8.7.0-7ubuntu1                    all          Complete and feature rich groupware solution - common files
ii  kopano-contacts:amd64                8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - contact mapi provider
ii  kopano-core                          8.7.0-7ubuntu1                    all          Metapackage to install the Kopano Core stack
ii  kopano-dagent                        8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - delivery agent
ii  kopano-gateway                       8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - POP3 and IMAP gateway
ii  kopano-ical                          8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - CalDAV and CalDAV gateway
ii  kopano-libs:amd64                    8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - Free/Busy library
ii  kopano-monitor                       8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - quota monitor
ii  kopano-search                        8.7.0-7ubuntu1                    all          Complete and feature rich groupware solution - indexer
ii  kopano-server                        8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - server component
ii  kopano-spamd                         8.7.0-7ubuntu1                    all          Complete and feature rich groupware solution - Spam daemon
ii  kopano-spooler                       8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - email spooler
ii  kopano-utils                         8.7.0-7ubuntu1                    amd64        Complete and feature rich groupware solution - admin tools
ii  kopano-webapp-apache2                3.5.14+dfsg1.orig-1               all          WebApp for the Kopano Collaboration Platform - Apache2
ii  kopano-webapp-common                 3.5.14+dfsg1.orig-1               all          WebApp for the Kopano Collaboration Platform - common files
ii  python3-kopano                       8.7.0-7ubuntu1                    all          Complete and feature rich groupware solution - Python bindings
rob2@po:~$ uname -a
Linux po 5.4.0-64-generic #72-Ubuntu SMP Fri Jan 15 10:27:54 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
rob2@po:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.1 LTS
Release:        20.04
Codename:       focal

fbartels

@ishouldhavebeenaqatester said in Messages not removed from MYSQL when user deleted+orphan store removed.:

8.7.0-7ubuntu1

That version is ancient. I would not really recommend to use the Kopano versions that come bundled with Debian/Ubuntu, they are lacking several patches and the way we organise patchlevel releases in the past also did not really make it easier for the Debian maintainers to keep their packages closer to ours.

Our newest approach with LTS branches used in Kopano One could make their life easier, but they have already made their intention public to orphan these packages for the next release.

Therefore I would rather recommend to get a subscription for Kopano One and use our packages.

@ishouldhavebeenaqatester said in Messages not removed from MYSQL when user deleted+orphan store removed.:

–clear-cache

this option only drops caches stored in ram. it has basically the same effect as restarting kopano-server

@ishouldhavebeenaqatester said in Messages not removed from MYSQL when user deleted+orphan store removed.:

–purge-softdelete 0

is rather what made your stored data go away. When objects are deleted in Kopano they are only soft deleted to allow the user to restore them. Only a purge will actually remove the data.

IShouldHaveBeenAQATester

@fbartels
I looked into Kopano One, but it looks like the licensing is prohibitive for a 3 person household. I don’t see licensing options for less than 10 users if I’m reading it correctly…? If I’m wrong, please let me know.

Unless there are security implications, I’m ok staying on an older/distro version.

fbartels

@ishouldhavebeenaqatester said in Messages not removed from MYSQL when user deleted+orphan store removed.:

I don’t see licensing options for less than 10 users if I’m reading it correctly…?

No, that is indeed the case. It was five users as a minimum before and switched to ten for Kopano One.

IShouldHaveBeenAQATester

@fbartels
Thanks for confirming. I guess I’m at where I’m at.

I may look into the nightlies if they are reasonably stable.

I can’t end up in the situation where I have an unstable mail system, as our personal email is used for all sorts of communication from my son’s school, bills, etc.

If 8.7 seems to work well, I may just run it with no internet access except postfix/mta which gets updated by Ubuntu & require my family to vpn in to use webapp & imaps. Everything else is listening on sockets/localhost at the moment.