Re: deleting old data from txrep
Hi, On Thu, Oct 4, 2018 at 2:54 AM Giovanni Bechis 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 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
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 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
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 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
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 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?
