Mautic Cleanup Commands

# normal mautic cleanup up commandos (deletes only unused ips and anonymous leads which are at least 3 weeks old)
# must be called multiple times
php bin/console mautic:unusedip:delete -n --limit=99999999999
php bin/console mautic:unusedip:delete -n --limit=99999999999
php bin/console mautic:maintenance:cleanup --days-old=21 -n
php bin/console mautic:maintenance:cleanup --days-old=21 -n

# after 30 days we clear infos in email stats about specific email details => email is stil in lead profile 
php bin/console doctrine:query:sql "UPDATE email_stats SET tokens = NULL WHERE date_sent < (NOW() - INTERVAL 30 DAY);"
php bin/console doctrine:query:sql "UPDATE email_stats SET open_details = NULL WHERE date_sent < (NOW() - INTERVAL 30 DAY);"
php bin/console doctrine:query:sql "UPDATE email_stats SET last_opened = NULL WHERE date_sent < (NOW() - INTERVAL 30 DAY);"

# we delete email stats without existing lead 
php bin/console doctrine:query:sql 'DELETE from email_stats where lead_id is null;'

# after 6 month we delete email stats emails 
php bin/console doctrine:query:sql 'DELETE from email_stats where date_sent < (NOW() - INTERVAL 180 DAY);'

# after 3 month we only store emails stats which was ready by user 
php bin/console doctrine:query:sql 'DELETE from email_stats where date_sent < (NOW() - INTERVAL 90 DAY) and is_read=0;'

# we clean audit log from no longer existent leads 
sudo -u $LOCAL_USER $PHP_BIN $MAUTIC_DOCROOT/bin/console doctrine:query:sql 'DELETE al from audit_log al where bundle="lead" and object="lead" and object_id not in (select id from leads where id=al.object_id);'

# we clear notifications
php bin/console doctrine:query:sql "TRUNCATE TABLE notifications;"

# we delete all 1 month old import entries 
php bin/console doctrine:query:sql 'DELETE FROM lead_event_log where bundle="lead" and object="import" AND date_added < (NOW() - INTERVAL 30 DAY);'

# we delete all pages hits no longer connected with any lead (anonymous or non anonymous)
php bin/console doctrine:query:sql 'DELETE from page_hits where lead_id is NULL;'

# This maybe risky todo / but works fine in most scenarios 

# Old problem query: problem if the last campaing entry is removed 
#php bin/console doctrine:query:sql "DELETE FROM campaign_lead_event_log WHERE date_triggered < (NOW() - INTERVAL 30 DAY);"

# New version: 

# cleanup campaign_lead_event_log after 30 days 
php bin/console doctrine:query:sql "DELETE clel FROM campaign_lead_event_log clel WHERE date_triggered < (NOW() - INTERVAL 30 DAY) and clel.id not in ( SELECT max_id FROM ( select max(id) as max_id FROM campaign_lead_event_log  group by lead_id, campaign_id) AS tmptable );"

# cleanup campaign history in lead profile after 30 days 
php bin/console doctrine:query:sql "DELETE FROM lead_event_log where bundle="campaign" and object="campaign" AND date_added < (NOW() - INTERVAL 30 DAY);"


# Maybe soft(er) solution (untested)
#php bin/console doctrine:query:sql "UPDATE campaign_lead_event_log SET metadata = '' WHERE date_triggered < (NOW() - INTERVAL 30 DAY);"

# Really important to free up mysql disk space 
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE page_hits;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE audit_log;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE email_stats;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE notifications;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE campaign_lead_event_log;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE lead_event_log;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE ip_addresses;"
php bin/console doctrine:query:sql "OPTIMIZE LOCAL TABLE lead_ips_xref;"

See mautic forum discussion:
https://forum.mautic.org/t/mautic-database-is-huge-can-i-manually-delete-old-records/22646/6

At the moment I'm testing this 2 queries for additional cleanups:

# cleanup campaign_lead_event_log after 30 days / don't delete if it is the last entry 
DELETE clel FROM campaign_lead_event_log clel WHERE date_triggered < (NOW() - INTERVAL 30 DAY) and clel.id not in ( SELECT max_id FROM ( select max(id) as max_id FROM campaign_lead_event_log  group by lead_id, campaign_id) AS tmptable )

# cleanup campaign history in lead profile after 30 days 
DELETE FROM lead_event_log where bundle="campaign" and object="campaign" AND date_added < (NOW() - INTERVAL 30 DAY);

@sebastian-fahrenkrog There are a few questions you have in your scripts above have these been relooked at?
I have tested all of them except this one as it does not seem complete.

php bin/console doctrine:query:sql 'DELETE al from audit_log al where bundle="lead" and object="lead" and object_id not in (select id from leads where id=al.object_id);'

And the 2 you mention above are these working now, or do they need more work?
Look forward to your reply
M

@raramuridesign
You are right. Correct version should be:

# we clean audit log from no longer existent leads 
sudo -u $LOCAL_USER $PHP_BIN $MAUTIC_DOCROOT/bin/console doctrine:query:sql 'DELETE al from audit_log al where bundle="lead" and object="lead" and object_id not in (select id from leads where id=al.object_id);'

I changed that in the script.

Regarding this:

And the 2 you mention above are these working now, or do they need more work?

Yes, we use commands in production:

# cleanup campaign_lead_event_log after 30 days 
sudo -u $LOCAL_USER $PHP_BIN $MAUTIC_DOCROOT/bin/console doctrine:query:sql 'DELETE clel FROM campaign_lead_event_log clel WHERE date_triggered < (NOW() - INTERVAL 30 DAY) and clel.id not in ( SELECT max_id FROM ( select max(id) as max_id FROM campaign_lead_event_log  group by lead_id, campaign_id) AS tmptable );'

and

# cleanup campaign history in lead profile after 30 days 
sudo -u $LOCAL_USER $PHP_BIN $MAUTIC_DOCROOT/bin/console doctrine:query:sql 'DELETE FROM lead_event_log where bundle="campaign" and object="campaign" AND date_added < (NOW() - INTERVAL 30 DAY);'

But please don't blame me for problems in your installation. Best thing would be to build a staging system, change emailer to a test one, delete data and check if the mauitc instance would trigger emails or campaign

Greetings
Sebastian

As a matter of interest are you running the above as a bash script?
@sebastian-fahrenkrog

Yes we do.

Like this:
`
crontab -l

0 0 * * 0 /bin/bash -c /home/m/web/m.mail.beispiel.de/scripts/cleanup.sh > /var/log/mautic_cleanup.log
`

@sebastian-fahrenkrog Just wondering if your scripts need this

sudo -u $LOCAL_USER $PHP_BIN

If your cleanup.sh is executed as the user in the cronjob, then you should not need to sudo the command.
I have just written my own script and it works like a charm, so trying to understand your variable logic... or your reasoning. ;-)

It depends on the context. If you run the script from the linux user mautic is running: skip the sudo part.

@sebastian-fahrenkrog Thanks for this. All is working well in a bash script, although I have taken a slightly different approach.

Just a note, when the table below has no campaign data, it throws an error. Is there a way to solve this?

# cleanup campaign history in lead profile after 30 days 
sudo -u $LOCAL_USER $PHP_BIN $MAUTIC_DOCROOT/bin/console doctrine:query:sql 'DELETE FROM lead_event_log where bundle="campaign" and object="campaign" AND date_added < (NOW() - INTERVAL 30 DAY);'

@sebastian-fahrenkrog Thanks for this. All is working well in a bash script, although I have taken a slightly different approach.

Just a note, when the table below has no campaign data, it throws an error. Is there a way to solve this?

# cleanup campaign history in lead profile after 30 days 
sudo -u $LOCAL_USER $PHP_BIN $MAUTIC_DOCROOT/bin/console doctrine:query:sql 'DELETE FROM lead_event_log where bundle="campaign" and object="campaign" AND date_added < (NOW() - INTERVAL 30 DAY);'

I could not confirm that issue. I truncated the table lead_event_log on my dev system and executed the command. The error code is still 0

The bash script would interrupt if the command returned a exit code > 0

Tested with Mautic 4.2.2

@sebastian-fahrenkrog let me see how it goes. I will report back I there are any issues. Thanks for providing the info.

@sebastian-fahrenkrog I use your commands (thanks). This helps to read the data, but still we've got problem with tables size. For example: for email_stats phpmyadmin shows less then 80gb, server more then 140gb. Probably it happens: https://dba.stackexchange.com/questions/11567/substantial-difference-between-database-size-under-mysql-and-actual-size-on-disk
How do you deal with this?

@automatyzuj In my opinion innodb_file_per_table is a good setting to use. We always use this setting and when I do the
OPTIMIZE LOCAL TABLE email_stats sql command it always frees the disc space on our server. So I don't see this bahviour in production so far.

So maybe change the setting and restart mysql?

But please don't blame me if anything goes wrong when you try ;-) Always backup

Hello @sebastian-fahrenkrog.

I would like to know if this can be applied to Mautic version 4.4.11.

Yes, all these crons are still operational.

Thank you

Hello @sebastian-fahrenkrog.

I would like to know if this can be applied to Mautic version 4.4.11.

Yes. We still use this cleanup commands for Mautic version 4.4.11

Also here is a mautic plugin for this: https://github.com/Leuchtfeuer/mautic-housekeeping-bundle

Hello @sebastian-fahrenkrog.
I would like to know if this can be applied to Mautic version 4.4.11.

Yes. We still use this cleanup commands for Mautic version 4.4.11

Also here is a mautic plugin for this: https://github.com/Leuchtfeuer/mautic-housekeeping-bundle

Thank you so much @sebastian-fahrenkrog.

I'm going to review this plugin.

Greetings

Hello @sebastian-fahrenkrog.
I would like to know if this can be applied to Mautic version 4.4.11.

Yes. We still use this cleanup commands for Mautic version 4.4.11

Also here is a mautic plugin for this: https://github.com/Leuchtfeuer/mautic-housekeeping-bundle

Hello @sebastian-fahrenkrog .

Checking the plugin I saw that this command has to be applied in the console "bin/console leuchtfeuer:housekeeping".

By default it maintains 365 days of data age. If I want only 90 days to be kept and also only email statistics to be deleted, should I put it like this?
bin/console view:housekeeping --days-old=90 --email-stats

You could create a cron task to run it, for example once a month. Or is it a very heavy process?

Thanks for your help.

Greetings

Hello @sebastian-fahrenkrog.
I would like to know if this can be applied to Mautic version 4.4.11.

Yes. We still use this cleanup commands for Mautic version 4.4.11
Also here is a mautic plugin for this: https://github.com/Leuchtfeuer/mautic-housekeeping-bundle

Hello @sebastian-fahrenkrog .

Checking the plugin I saw that this command has to be applied in the console "bin/console leuchtfeuer:housekeeping".

By default it maintains 365 days of data age. If I want only 90 days to be kept and also only email statistics to be deleted, should I put it like this? bin/console view:housekeeping --days-old=90 --email-stats

You could create a cron task to run it, for example once a month. Or is it a very heavy process?

Thanks for your help.

Greetings

I don‘t use this plugin bit i‘m think you are right with the params. Our bash script runs every midnight

Could you help me implement your script in two Mautic? Or do you only use it for yourself?