Posting this here to see if it helps others, or if there is a better way to manage this.
Our employees text in images to our dispatch, so our SMS/MMS database files tend to grow fast and slow down the Sangoma Phone client to a crawl. So I needed a way to purge the data.
So I came up with these SQL commandsto fix it, I will likely put it on a cron job at some point.
mysql asterisk --execute="CREATE TABLE new_sms_messages LIKE sms_messages"
mysql asterisk --execute="INSERT INTO new_sms_messages SELECT * from sms_messages WHERE tx_rx_datetime >= CURDATE() - INTERVAL 1 MONTH"
mysql asterisk --execute="RENAME TABLE sms_messages TO sms_messages_old"
mysql asterisk --execute="RENAME TABLE new_sms_messages TO sms_messages"
mysql asterisk --execute="CREATE TABLE new_sms_media LIKE sms_media"
mysql asterisk --execute="INSERT INTO new_sms_media SELECT a.* from sms_media a inner join sms_messages b on a.mid = b.id WHERE tx_rx_datetime >= CURDATE() - INTERVAL 1 MONTH"
mysql asterisk --execute="RENAME TABLE sms_media TO sms_media_old"
mysql asterisk --execute="RENAME TABLE new_sms_media TO sms_media"
Any thoughts issues anyone sees with doing it this way?
I would think this could be a good feature request in to the official SMS module as a setting. That said you may wish to take into account a few gotchas.
This may cause problems and inconsistency on large datasets. These moves and renames can also lock and cause issues with active function so should be done with caution.
As a feature or someone using this in any capacity people need to make sure they are in compliance with any data retention and data security regulations etc to not get bitten in the backside.
Addressing some of this noting this is untested pseudo-code it should work but may also cause the universe to implode.
# Backup original tables
mysqldump asterisk sms_messages > sms_messages_backup.sql
mysqldump asterisk sms_media > sms_media_backup.sql
# Use transactions for atomicity
mysql asterisk --execute="
START TRANSACTION;
CREATE TABLE new_sms_messages LIKE sms_messages;
INSERT INTO new_sms_messages SELECT * FROM sms_messages WHERE tx_rx_datetime >= CURDATE() - INTERVAL 1 MONTH;
CREATE TABLE new_sms_media LIKE sms_media;
INSERT INTO new_sms_media
SELECT a.*
FROM sms_media a
INNER JOIN new_sms_messages b ON a.mid = b.id;
RENAME TABLE sms_messages TO sms_messages_old, new_sms_messages TO sms_messages;
RENAME TABLE sms_media TO sms_media_old, new_sms_media TO sms_media;
COMMIT;
"
I would be concerned that some of these operations might be treated as unlogged by the database engine, so wouldn’t meet the ACID requirements. At the very least they are going to cause write locks on the schema,
It might be that MySQL can do this in an ACID compliant way, but
This will not have any protection, as there could be an update transaction between the two dumps.
I agree this should be a feature. IMHO any system that uses a database, should have an official mechanism to manage data retention. Our system originally began in 2016 as v13 if I recall and has been restored up all the way to v17. I had over 17GB of data in the CDR, CEL, and SMS tables that we simply aren’t required to retain.
I shouldn’t need to dig through forums and patch together some SQL to do this, it should be part of a complete system. I’m perfectly fine with pay-walling it behind Sys Admin Pro or something, but it needs to be a feature.
I’m not nearly the SQL expert you are, and I agree this is just cobbled together to meet a need I had. Ideally it would be properly designed/vetted and added as a feature.