You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@spamassassin.apache.org by Alex <my...@gmail.com> on 2018/10/04 02:30:27 UTC
deleting old data from txrep
Hi,
I need to delete some of the old entries from my txrep database as
it's grown to 3GB, oops. When attempting to do this, it fails with
"error 14":
# rpm -q mariadb
mariadb-10.2.17-2.fc28.x86_64
# ls -l
total 3141664
-rw-rw---- 1 mysql mysql 65 Oct 19 2017 db.opt
-rw-rw---- 1 mysql mysql 2222 Oct 19 2017 txrep.frm
-rw-rw---- 1 mysql mysql 3217031168 Oct 3 22:29 txrep.ibd
MariaDB [txrepdb]> delete from txrep where last_hit <= '2018-01-01 00:00:00';
ERROR 14 (HY000): Can't change size of file (Errcode: -1048710496
"Internal error < 0 (Not system error)")
Searches show this can happen when the filesystem is full, but it's
not. Any ideas of what could be wrong? Maybe write the last, say, 120
days to another database then rename it?
Re: deleting old data from txrep
Posted by Alex <my...@gmail.com>.
Hi,
On Thu, Oct 4, 2018 at 2:54 AM Giovanni Bechis <gi...@paclan.it> wrote:
>
> On 10/04/18 04:30, Alex wrote:
> > Hi,
> >
> > I need to delete some of the old entries from my txrep database as
> > it's grown to 3GB, oops. When attempting to do this, it fails with
> > "error 14":
> >
> do you have enough space for tmp tables ? What if you try to delete less data ? Does mysqlcheck(1) spots anything wrong ?
There's nearly 500GB left on the partition and probably 50GB RAM free.
I was successful deleting about 15 days of data at a time.
mysqlcheck(1) reported no problems on the txrep table.
There's now less than 60 days of data in the database, but the file
still shows 3GB.
# ls -l
total 3141664
-rw-rw---- 1 mysql mysql 65 Oct 19 2017 db.opt
-rw-rw---- 1 mysql mysql 2222 Oct 4 11:39 txrep.frm
-rw-rw---- 1 mysql mysql 3217031168 Oct 4 15:51 txrep.ibd
I ran "optimize table txrep;" and it shrunk it down to 350MB. I'll add
an entry to cron to delete more regularly.
Re: deleting old data from txrep
Posted by Giovanni Bechis <gi...@paclan.it>.
On 10/04/18 04:30, Alex wrote:
> Hi,
>
> I need to delete some of the old entries from my txrep database as
> it's grown to 3GB, oops. When attempting to do this, it fails with
> "error 14":
>
do you have enough space for tmp tables ? What if you try to delete less data ? Does mysqlcheck(1) spots anything wrong ?
Cheers
Giovanni
> # rpm -q mariadb
> mariadb-10.2.17-2.fc28.x86_64
>
> # ls -l
> total 3141664
> -rw-rw---- 1 mysql mysql 65 Oct 19 2017 db.opt
> -rw-rw---- 1 mysql mysql 2222 Oct 19 2017 txrep.frm
> -rw-rw---- 1 mysql mysql 3217031168 Oct 3 22:29 txrep.ibd
>
> MariaDB [txrepdb]> delete from txrep where last_hit <= '2018-01-01 00:00:00';
> ERROR 14 (HY000): Can't change size of file (Errcode: -1048710496
> "Internal error < 0 (Not system error)")
>
> Searches show this can happen when the filesystem is full, but it's
> not. Any ideas of what could be wrong? Maybe write the last, say, 120
> days to another database then rename it?
>
Re: deleting old data from txrep
Posted by "Kevin A. McGrail" <km...@apache.org>.
Alex,
I use this command nightly: 'DELETE FROM txrep WHERE last_hit <= (now()
- INTERVAL 90 day);'
I would recommend you try that and then just drop and recreate the table
if you need. This sounds like a bad issue with the database.
Regards,
KAM
On 10/3/2018 10:30 PM, Alex wrote:
> Hi,
>
> I need to delete some of the old entries from my txrep database as
> it's grown to 3GB, oops. When attempting to do this, it fails with
> "error 14":
>
> # rpm -q mariadb
> mariadb-10.2.17-2.fc28.x86_64
>
> # ls -l
> total 3141664
> -rw-rw---- 1 mysql mysql 65 Oct 19 2017 db.opt
> -rw-rw---- 1 mysql mysql 2222 Oct 19 2017 txrep.frm
> -rw-rw---- 1 mysql mysql 3217031168 Oct 3 22:29 txrep.ibd
>
> MariaDB [txrepdb]> delete from txrep where last_hit <= '2018-01-01 00:00:00';
> ERROR 14 (HY000): Can't change size of file (Errcode: -1048710496
> "Internal error < 0 (Not system error)")
>
> Searches show this can happen when the filesystem is full, but it's
> not. Any ideas of what could be wrong? Maybe write the last, say, 120
> days to another database then rename it?
--
Kevin A. McGrail
VP Fundraising, Apache Software Foundation
Chair Emeritus Apache SpamAssassin Project
https://www.linkedin.com/in/kmcgrail - 703.798.0171