We have used the following script for years, to trim our CDR to only the last 6 months:
mysql << EOF
use asteriskcdrdb;
repair table cdr;
repair table cel;
delete from cdr where calldate < DATE_SUB(NOW(), INTERVAL 6 MONTHS);
delete from cel where eventtime < DATE_SUB(NOW(), INTERVAL 6 MONTHS);
optimize table cdr;
optimize table cel;
EOF
We upgraded our PBXes from FreePBXv13 to FreePBX v15; in doing so we migrated the script to the new PBXes. Now when we run it on v15 we get the following error:
ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘MONTHS)’ at line 1
Table Op Msg_type Msg_text
asteriskcdrdb.cdr repair status OK
Table Op Msg_type Msg_text
asteriskcdrdb.cel repair status OK
Any help as to how to correct this error and allow CDR DB trimming to happen again?