Messages not removed from MYSQL when user deleted+orphan store removed.
-
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.
-
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-cacheAnd 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.
-
@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 runningoptimize-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, …).
-
@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
-
@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.
-
@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.
-
@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.
-
@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.