Hey - Progress! But first, I had to correct: can't use "-p <password>", must use the longer form: "--password=<password>"
Then I can run it! One problem. It deleted the first 1000 records, rather than leaving the last 1000 records in the db. What twist is needed to get that right? ? --- Scott H <[EMAIL PROTECTED]> wrote: > 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] > ===== -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __________________________________ 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]
