When I run: mysql -p <password> -u <username> <log_db_name> < deleteold.sql
...and within deleteold.sql, I have only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq limit 1000,1; delete from syslogTB where seq < @aa; I just get back a screen full of syntax suggestions. Where is my mistake here? --- Dan Greene wrote: > for example your script would be > mysql --user=myuserid --password=mypassword -h > hostname < deleteold.sql > > see: > http://www.mysql.com/doc/en/Batch_mode.html > > > -----Original Message----- > > From: Scott H > [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, November 12, 2003 4:22 PM > > To: Dan Greene > > Cc: MySQL Mailing List > > Subject: RE: maintaining size of a db > > > > > > Cool idea, but I don't think you can really > do > > it. When I try, mysql just gives me back the > > syntax stuff. <sigh> I'm a bit perplexed - > I > > would have thought this would be a > semi-common > > issue in db maintenance, but no one seems to > have > > set this up. I'm still trying, please send > any > > other ideas/suggestions my way... thx!! > > > > --- Dan Greene wrote: > > > you may be able to put both statements to a > > > text file, let's call it deleteold.sql > > > > > > then your cron job would be : > > > > > > mysql (put your connect stuff here) < > > > deleteold.sql > > > > > > > From: Scott H > > > > Well, it sort of helps. But that section > is > > > > about future enhancements intended for > mysql. > > > > I need to set something up now, with the > > > current > > > > stable version. One thing I read (can't > find > > > it > > > > now) indicated that the current version > (I'm > > > > actually running 4.0.15a) has "limited" > > > support for subqueries - but I don't know > > > >exactly how far that goes. > > > > > > > > So let me set the stage a bit more - I'll > > > assume > > > > for now there is no reasonably simple way > to > > > work > > > > with the actual size of the database on > disk, > > > and > > > > instead will go with the idea that I can > > > expect > > > > the size of any one record to be of some > > > average. > > > > So, according to Dan's suggestion, if I > do a > > > > little math, and control the number of > > > records, I > > > > can control the size of the db -- > > > approximately. > > > > That's fine. Thus if have "seq" as an > > > > autoincrement field, and I wanted to stay > > > around > > > > say 1000 records, deleting the oldest > > > records, I > > > > would need to run a cron job that would > > > somehow > > > > nest or relate these 2 sql statements: > > > > > > > > select (@aa:=seq) as low_seq from > logtable > > > order > > > > by seq limit 1000,1 > > > > > > > > delete from logtable where seq < @aa > > > > > > > > I've tried putting this into a subquery > > > format > > > > but no luck. But I'm a noob, so I keep > > > trying, > > > > thinking I might hit on the right syntax. > > > > Or, is > > > > there some way to pull the value of > "low_seq" > > > > into an environment variable and use it > in a > > > > script file to run the 2nd statement? > Other > > > > ideas? > > > > > > > > thanks, scott > > > > > > > > --- Michael McTernan wrote: > > > > > >From the manual: > > > > > > > > > > 1.8.4.1 Subqueries > > > > > > > > > > Subqueries are supported in MySQL > version > > > 4.1. > > > > > See section 1.6.1 Features > > > > > Available in MySQL 4.1. > > > > > > > > > > Hope that helps, > > > > > > > > > > Mike > > > > > > > > > > > From: Scott H > > > > > > OK, I *THINK* I follow you here. > Couple > > > of > > > > > > questions. I'm reading an online > > > tutorial > > > > > trying > > > > > > to figure this out, and I am led to > > > believe > > > > > mysql > > > > > > can't do nested queries, aka > sub-queries. > > > But > > > > > you > > > > > > say it can? Is this recent? And I > don't > > > have > > > > > a > > > > > > timestamp field, I have an > autoincrement > > > > > field, > > > > > > but what do you mean by the > "(@aa:=id)" > > > > > thing? I > > > > > > don't follow that. thanks. > > > > > > > > > > > > --- Dan Greene > > > > > <[EMAIL PROTECTED]> > > > > > > wrote: > > > > > > > What I would do is a classical > > > > > guesstimate.... > > > > > > > > > > > > > > find the average size per record > (data > > > file > > > > > > > size + index file(s) size / # > records > > > in > > > > > table) > > > > > > > > > > > > > > using that, find the data used per > day > > > > > > > > > > > > > > using that, figure out how many > days, > > > on > > > > > > > average it takes to hit 20GB > > > > > > > > > > > > > > let's say it's 89 days. > > > > > > > > > > > > > > right off the top, take 10% off for > > > safety, > > > > > now > > > > > > > we're at 80 days > > > > > > > > > > > > > > presuming your table has a > timestamp > > > field: > > > > > > > > > > > > > > delete from log_table WHERE > > > TO_DAYS(NOW()) > > > > > - > > > > > > > TO_DAYS(date_col) > 80 > > > > > > > > > > > > > > if you don't have a timestamp > field, > > > but > > > > > you do > > > > > > > have an autoincrement id field: > > > > > > > > > > > > > > figure out number of records on > average > > > = > > > > > 20gb > > > > > > > (say it's 2M) > > > > > > > again, use 10% for safety (1.8M) > > > > > > > > > > > > > > select (@aa:=id) as low_id from > > > logtable > > > > > order > > > > > > > by id limit 18000000,1 > > > > > > > delete from logtable where id < @aa > > > > > > > > > > > > > > (do subqueries work with a limit > > > clause?) > > > > > > > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > > > From: Scott H > > > > > > > [mailto:[EMAIL PROTECTED] > > > > > > > > Sent: Wednesday, November 12, > 2003 > > > 11:19 > > > > > AM > > > > > > > > To: Dan Greene; MySQL Mailing > List > > > > > > > > Subject: RE: maintaining size of > a db > > > > > > > > > > > > > > > > > > > > > > > > Yes sir, exactly. It's just > that's > > > what > > > > > I'm > > > > > > > > looking for, and can't figure > out. I > > > can > > > > > set > > > > > > > up > > > > > > > > a cron job, but what exactly > would > > > the > > > > > SQL > > > > > > > delete > > > > > > > > statement be that would allow me > to > > > > > delete > > > > > > > old > > > > > > > > records in such a way that the db > > > > > maintains > > > > > > > an > > > > > > > > approximately constant size on > disk? > > > > > > > (Failing > > > > > > > > that perhaps a delete statement > that > > > > > would > > > > > > > just > > > > > > > > have it maintain a constant # of > > > records? > > > > > > > > ...maybe this would be much > simpler?) > > > > > > > > > > > > > > > > --- Dan Greene wrote: > > > > > > > > > cronjob a sql script that runs > a > > > delete > > > > > > > > > statement for old jobs daily > > > > > > > > > > > > > > > > > > > --- Egor Egorov wrote: > > > > > > > > > > > Scott H wrote: > > > > > > > > > > >> Can't seem to find this > one in > > > the > > > > > > > manual > > > > > > > > > or > > > > > > > > > > >> archives - how do I > control a > > > db > > > > > to > > > > > > > > > maintain > > > > > > > > > > >> its size to an arbitrary > > > value, > > > > > say 20 > > > > > > > GB? > > > > > > > > > I > > > > > > > > > > >> want to just rotate > records, > > > > > deleting > > > > > > > > > those > > > > > > > > > > >> that are oldest. > > > > > > > > > > > > > > > > > > > > > > You can't restrict size of > the > > > > > database > > > > > > > > > only > > > > > > > > > > > with MySQL, use disk > quotas. > > > > > > > > > > > > > > > > > > > > No! That would just stop > mysql > > > right > > > > > in > > > > > > > its > > > > > > > > > > tracks (so to speak...) when > it > > > got > > > > > too > > > > > > > > > large. > > > > > > > > > > But I want old records > sloughed > > > off > > > > > and > > > > > > > the > > > > > > > > > db to > > > > > > > > > > continue running. (This is > for a > > > > > central > > > > > > > > > syslog > > > > > > > > > > box.) > > > > > > > > __________________________________ > > Do you Yahoo!? > > Protect your identity with Yahoo! Mail > AddressGuard > > http://antispam.yahoo.com/whatsnewfree > > __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
