Problem with deleting CEL logs

Hello all,

I have an freepbx15 with an asterisk 16 and i’m trying to delete some CEL and CDR logs ( older that 6 months etc ).

I found this post Periodically Delete CDRs Older than N Months and the delete on CDR went great but CEL returned an error :

wERROR 1206 (HY000) at line 3: The total number of locks exceeds the lock table size

and the delete didn’t happen. Any idea to resolve the issue?

delete from cel where eventtime between ‘2020-04-03 03:11:17’ and ‘2020-05-03 03:11:17’;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

cat /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
innodb_buffer_pool_size=64MB

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
MariaDB [asteriskcdrdb]> select count(1) from cel where eventtime = '2020-04-03 03:11:17';
+----------+
| count(1) |
+----------+
|       13 |
+----------+

MariaDB [asteriskcdrdb]> delete from cel where eventtime = ‘2020-04-03 03:11:17’;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

can’t even delete this lol.

I thought I read someplace where the default buffer pool size can be even 128MB. Did you restart the MySQL processes after making that config change? I usually cull these logs every January 1. Since it takes awhile and consumes resources I do it off-hours and a few months at a time. If there is a config option to just rotate the last X months or so that would be huge! :grinning:

Hello yes i did.

for now the workaround is to deleting by ID.

Bellow is an example.

select min(id), max(id) from cel where eventtime between ‘2020-04-03 03:11:17’ and ‘2020-05-03 03:11:17’;

#!/usr/bin/bash

ID=$1
ENDID=$2

while [ "$ID" -le "$ENDID" ]
do
        echo "mysql -e 'delete from asteriskcdrdb.cel where id='"$ID"'"
        mysql -e "delete from asteriskcdrdb.cel where id=$ID"
        ID=$[$ID + 1]
done
2 Likes

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