How to remove old CDRs

That’s where my syntax came from. Apparently date 1 < calldate < date 2 does not work as it deleted everything in my CDR.

Sorry, I didnt look that closely at your syntax. I just remebered I had one saved for future use. Now I am interested to see if anyone has other suggestions on how to skin the cat.

I’d love to export chunks at a time so I can reimport them if need be, like every month or every year, before deleting them from the PBX.

Use mysqldump with similar condition before
the delete, you will get a text file of the soon to be deleted calks that you could import back if necessary

1 Like

Working from memory:

DELETE FROM cdr WHERE calldate BETWEEN ‘2018-06-21 09:58:00’ AND ‘2018-06-21 10:00:00’;

1 Like

Did you end up using this cron job on a live system successfully?

I thought we settled for just deleting everything older than a couple months but I just looked and it was never implemented. Will do some testing this weekend.

DELETE FROM cdr WHERE calldate BETWEEN ‘2018-06-21 09:58:00’ AND ‘2018-06-21 10:00:00’;

does work but when I copied and pasted it didn’t like the ’ (needs to be a single quote). Its still not clear what optimizing the database does, and I still have no idea how to export everything to a csv from the command prompt. I went looking for a log that would show me what happens when I click Search in the CDR module in the GUI but cant find anything.

When you work with all this stuff, you need to understand how the shell you are using (probably bash) works with quotes and double quotes. without that knowledge you will remain confused , but luckily we have google to alleviate your angst.

1 Like

I just deleted about 4 million lines from the asteriskcdrdb database but it didn’t do much of anything to reduce disk usage. It looks like I need to rebuild the tables as the empty cells are still taking up space. Will the optimize command do that? And how safe is that to do on a database this large?

image

No, removing the records when you are using innodb never reduces the size of the the underlying table, there are google remedies out there for that , but logistically 4 million cdr records are not going to be impactful, my guess is it your cel table that is the pig

So running the optimize command didn’t actually change the size of the cdr much. I can’t find documentation on the cel table in the freepbx wiki. Any links would be appreciated.

*edit: it looks like you just replace the column from calldate to eventtime, but it does seem to take forever. I’ll have to mess with this some more.

image

Adding an index would probably help if there isn’t one, from, mysql :-

USE asteriskcdrdb;
SHOW INDEXES FROM cel;

if not there then

ALTER TABLE `cel` ADD INDEX `eventtime` (`eventtime`);

Think of the CDR and the CEL tables as the “Header” and the “Details” tables.

The CDR has basic information about the call, and the CEL has all of the details; how long it rang, when it was picked up, when the bridge was created, etc.

Needless to say, there will be a LOT more data in the CEL table… to get rid of the records, you will need to delete from both tables.

One way would be using two delete commands;

DELETE FROM CDR Where calldate < DATE_SUB(now(), INTERVAL 6 MONTH);
DELETE FROM CEL WHERE eventtime < DATE_SUB(now(), INTERVAL 6 MONTH);

However, a more “proper” way to do it would be with a delete join…

DELETE cdr, cel FROM cdr RIGHT JOIN cel ON cdr.uniqueid = cel.uniqueid WHERE calldate < DATE_SUB(now(), INTERVAL 6 MONTH);

*** DISCLAIMER *** OK… someone double-check that… use at your own risk, always make backups, and cover your children’s eyes before messing with your database. That being said, that should delete the records older than 6 months from the cdr, along with the associated records for each call in cel.

3 Likes

This might need to be in a different thread but how do I move the CEL and CDR database and table to a different directory on the same virtual machine? I tried creating a new directory (/media/CELtable as a test) and gave it the same ownership and properties that /var/lib/MySQL/asteriskcdrdb has (0700 and owner MySQL). Then I inserted that path into the remote CEL DB Table field in Advanced Settings. I left the DB name blank and left it enabled. Did a fwconsole restart and don’t see anything happening.

Should probably be a different thread… but here you go.

Shut down mysql
Move all of the files in your current data directory to the new location
Locate my.ini file, and edit it to change the datadir parameter to point to the new location
start mysql

1 Like

How are you shutting down MySQL? I tried with fwconsole stop and when I followed those steps it broke the GUI. It was easily fixed by replacing the correct directory in the my.cnf file (assuming that’s the right file and its not really called my.ini)

mysqladmin shutdown

… should work.

there is a my.cnf in /etc that will be the first one read by mysql.

If you shut down mysql, move the data files (all of them… you can’t pick and choose), then edit /etc/my.conf and change the datadir= then you should be able to start mysqld and have it work.

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.