Hello all, I noticed when I logged into my MySQL kanboard database recently that my sessions (can’t remember the actual name) was full of entries, around 95,000 or so over the years.
Can I delete them so I got a month’s worth? I can’t seem to find a function or procedure in kanboard for it so I think I would just delete through phpMyAdmin.
Anybody know of any side effects it may cause?
I don’t see any danger in deleting, at least where the ‘expire_at’ is over.
How can I read/convert that? The date is
int(11) so it’s just numbers but I can’t find a way to convert it
Copy the result of
SELECT MAX(expire_at) FROM sessions
to the clipboard. Then use that value for a SELECT to check
(!), finally for the DELETE.
DELETE FROM sessions WHERE expire_at < $your_max_value
This removes all record but the newest one.
If this is too hard for you, you have to calculate an appropriate value. The expire_at is a UNIX timestamp, aka seconds since Jan 01 1970. (UTC).
Example: To keep just some days back from the newest:
DELETE FROM sessions WHERE expire_at < ($your_newest_value - $number_of_days * 24 * 3600)
yes this was too hard but I am slowly reading up on the mysql side. After seeing my sessions table having 40mb and 95k entries, I think it needs a clean. Other tables will also need some sort of looking into to keep the database a minimum size.
I will create a neww plugin for this, so that users can click a button to clean entries from the table. starting with sessions. Both your entries will be good options.
You’re more than welcome to collab on the repo, let me know, I will add you.
For me, this brings up the question, why such a table cleanup isn’t done by the nightly cronjob. Removing expired stuff from both, file system and database, is a typical maintenance job.
I dont know, my cron works daily, my actions work, notifications etc… but the table isnt touched… I dont know what cronjob checks for so I thought its by design that the table is so big.
Obviously, there isn’t one, but should.
ok great, another flaw of Kanboard… so I will need to create a plugin to bring my database size down.
It was just a remark from my side, not a call for a plugin.
I know, Im just trying to understand whether the database sessions table size is my local issue or a kanboard issue. If its a kanboard issue (no database cleaning via cronjobs by design/oversight) then I need to create a plugin.
Out of curiosity: how large is this instance? Users? How old?
My session table has just above 100 items, but is living for years. And I never purged.
haha my plugin dashboard can answer your question easily (excellent)
But the database has been used since 2015 with all this data. The date showing in the screenshot was a manual migration when I couldnt upgrade kanboard.
This can be completed through a cleaning job in the ContentCleaner plugin
After a long time in the making and with great efforts, contributions and support from @creecros and @alfred, this plugin has been initially released.
ContentCleaner gives you greater control of your database from within Kanboard. Cleaning jobs are split into three sections, Automatic, Application and Plugins.
Users will find the Application Cleaning Jobs most useful. For example, Job #2 under Application Cleaning resolves issues reported here:
iCal data with duedate only - #54 by alfredb