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
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!
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