Timestamp problem.
I have the following part in a schema describe feeds . | pubdate | timestamp| YES | | CURRENT_TIMESTAMP | | show create table feeds; `pubdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, when i make an INSERT INTO feeds(, pubdate) VALUES (,NOW()+1*RAND()) is there any chance to have: mysql> select count(*) from feeds where pubdate = 0; +--+ | count(*) | +--+ | 593923 | +--+ 1 row in set (5.90 sec) mysql> select count(*) from feeds where pubdate > 0; +--+ | count(*) | +--+ | 287532 | +--+ 1 row in set (5.33 sec) -- Sometimes life hits you in the head with a brick. Don't lose faith. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search Question
Oson take a look at mysql documentantion. You can do this with match against. A IR system is somewhat different than a DB. But mysql is both an IR and a DB. Oson, Chris M. wrote: > Hello, > > I have a site that I'm trying to implement a search engine on existing and > archived news stories on a medium text datatype in a database. > > I read the documentation and got it running, but unless I missed something > it's not doing what I want it to do. > > For example, if I put in the keywords [Alpine Fire], it will return all > stories with Alpine Fire in it. That is what I want, but if there's > another story about a fire, the search will return that also. Or if > there's a story about an incident on Alpine street, that will also > be returned. > > What I want is only stories that contain the keywords [Alpine Fire]. Is > there a way to do that with a full text search? I'd rather not have to > do a search using [LIKE '%Alpine Fire%'] because that doesn't seem like > the most efficient way to do a search. > > chris > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- -- Antonio Gulli'Ideare S.p.a tel: (+39) 050 575300 [EMAIL PROTECTED] Lungarno Mediceo 56 fax: (+39) 050 575583 whois: AG2-ORG I-56126 Pisa, Italy http://www.ideare.com net: print pack"C*",split/\D+/,`echo "16iII*o\U@{$/=$z;[(pop,pop,unpack"H*",<> )]}\EsMsKsN0[lN*1lK[d2%Sa2/d0http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't recover a bad corrupeted table...
Un update, the problem was solved thanks to Gerald Clarke and Bob Hall. They said: t(6), date date, origine varchar(16)); "The use of a reserved word as a column name probably is the reason that your table is corrupted as soon as it is created." I also declared some fields to be NOT NULL as cried by TYPE=ISAM table (why not by TYPE=MYISAM, which i currently use ?) now the schema is mysql> describe keywordsn; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | querystring | varchar(255) | | MUL | | | | numref | int(11) | YES | | NULL| | | multiword | smallint(6) | YES | | NULL| | | dt | date | YES | | NULL| | | origine | varchar(16) | | MUL | | | +-+--+--+-+-+---+ 5 rows in set (0.03 sec) mysql> show index from keywordsn; +---++--+--+-+---+ -+--++--+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car dinality | Sub_part | Packed | Comment | +---++--+--+-+---+ -+--++--+ | keywordsn | 1 | qfindex |1 | querystring | A | NULL | NULL | NULL | FULLTEXT | | keywordsn | 1 | qindex |1 | querystring | A | NULL | NULL | NULL | | | keywordsn | 1 | orindex |1 | origine | A | NULL | NULL | NULL | | +---++--+--+-+---+ -+--++--+ 3 rows in set (0.00 sec) but still i create a table from scratch, the import data then mysql> use queries Database changed mysql> delete from keywordsn where numref=1; ERROR 1034: Incorrect key file for table: 'keywordsn'. Try to repair it mysql> CHECK TABLE keywordsn; +---+---+--++ | Table | Op| Msg_type | Msg_text | +---+---+--++ | keywordsn | check | warning | Table is marked as crashed | | keywordsn | check | error| Key in wrong position at page 92947456 | | queries.keywordsn | check | error| Corrupt | +---+---+--++ 3 rows in set (6.28 sec) mysql> REPAIR TABLE keywordsn; +---++--+--+ | Table | Op | Msg_type | Msg_text | +---++--+--+ | queries.keywordsn | repair | status | OK | +---++--+--+ 1 row in set (27 min 56.34 sec) mysql> CHECK TABLE keywordsn; +---+---+--++ | Table | Op| Msg_type | Msg_text | +---+---+--++ | keywordsn | check | warning | Table is marked as crashed | | keywordsn | check | error| Key in wrong position at page 92947456 | | queries.keywordsn | check | error| Corrupt | +---+---+--++ [root@ideare <mailto:root@ideare> queries]# myisamchk -o keywordsn - recovering (with keycache) MyISAM-table 'keywordsn.MYI' Data records: 2849895 mysql> use queries Database changed mysql> CHECK TABLE keywordsn; +---+---+--+--+ | Table | Op| Msg_type | Msg_text | +---+---+--+--+ | queries.keywordsn | check | status | OK | +---+---+--+--+ 1 row in set (48.14 sec) mysql> delete from keywordsn where numref=1; Query OK, 1556747 rows affected (11 min 56.94 sec) mysql> OPTIMIZE TABLE keywordsn; +---+--+--+--+ | Table | Op | Msg_type | Msg_text | +---+--+--+--+ | queries.keywordsn | optimize | status | OK | +---+--+--+--+ 1 row in set (11 min 26.70 sec) Antonio gulli wrote: Bob Hall wrote: >> Hello Bob, first at all thank you for your support. >> >> Bob Hall wrote: >> >>> Let me see if I understand you correctly. You created a new table >>> (CREAT
Re: How to have faster select?
Read the manual, the most frequent words are stopped in index. Chai-Hup Chen wrote: > Hi, > Thanks a lot, the select is much faster now. But I find something > weird, it return nothing when match against the string "the", which > return 255568 entries when select "% the %", is it possible mysql > does not indexed some words for some reason? > > > select count(*) from Books where match Title against ('cisco'); > +--+ > | count(*) | > +--+ > | 222 | > +--+ > 1 row in set (1.76 sec) > > > > > select count(*) from Books where Title like "% the %"; > +--+ > | count(*) | > +--+ > | 255568 | > +--+ > 1 row in set (1 min 23.17 sec) > > select count(*) from Books where match Title against ('the'); > +--+ > | count(*) | > +--+ > |0 | > +--+ > 1 row in set (0.00 sec) > >> SELECT count(*) FROM Books WHERE MATCH Title AGAINST 'cisco' >> >> We use a FULLTEXT index search in a couple of our table for business referral >> searches, and it works like a charm! >> >> Hope this helps >> >>>> Hi, >>>> Here is the fulltext index I add: >>>> >>>> CREATE TABLE Books ( >>>> isbn char(10) NOT NULL default '', >>>> Title char(255) NOT NULL default '', >>>> FULLTEXT KEY full_title (Title) >>>> ) TYPE=MyISAM; >>>> >>>> select count(*) from Books --> 2028971 >>>> select count(*) from Books where Title like "% cisco %" >>>> +--+ >>>> | count(*) | >>>> +--+ >>>> | 86 | >>>> +--+ >>>> 1 row in set (1 min 15.58 sec) >>>> >>>> Look like the fulltext index is not functioning ... Any idea? >>> >> Mike(mickalo)Blezien >> >> Thunder Rain Internet Publishing >> Providing Internet Solutions that work! >> http://www.thunder-rain.com >> Tel: 1(225) 686-2002 >> = >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> - >> Before posting, please check: >>http://www.mysql.com/manual.php (the manual) >>http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- -- Antonio Gulli'Ideare S.p.a tel: (+39) 050 575300 [EMAIL PROTECTED] Lungarno Mediceo 56 fax: (+39) 050 575583 whois: AG2-ORG I-56126 Pisa, Italy http://www.ideare.com net: print pack"C*",split/\D+/,`echo "16iII*o\U@{$/=$z;[(pop,pop,unpack"H*",<> )]}\EsMsKsN0[lN*1lK[d2%Sa2/d0
Re: Can't recover a bad corrupeted table...
Hello Bob, first at all thank you for your support. Bob Hall wrote: > Let me see if I understand you correctly. You created a new table > (CREATE TABLE?) and you imported data from a file that had nothing to > do with MySQL (comma or tab delimited file?), and therefore couldn't > be affected by whatever crashed your server. And this brand new table > with pristine data was corrupted? Yep this is whats happen. I first i got a corrupted table, i tried to repair the table following all known strategies... then i create a table from scratch and use a perl program for importing data, but at the end i got a corrupted table and no way to rapair it. This is the schema: 1) use queries drop table keywords; create table keywords (querystring varchar(255), numref int(11), multiword smallin t(6), date date, origine varchar(16)); create FULLTEXT INDEX qfindex ON keywords(querystring); create INDEX qindex ON keywords(querystring); create INDEX orindex ON keywords(origine); 2) Importing data with perl program 3) Corrupted table > > I have limited experience maintaining MySQL databases on Linux, and I > have very limited knowledge of the internal workings of MySQL. The > only thing that I know of that you haven't mentioned trying is > recreating the table description file from backup. However, if CREATE > TABLE statements are creating corrupted tables, then I believe that > the server itself is corrupted. If I were in your shoes, I would > reinstall, after backing up everything short of the refrigerator. > Perhaps someone else can give you better advice. The strange thing is that this is a production server used by a lot of programs and they works. Now i'm tring to use ISAM (instead of MYISAM) table with such schema: use queries drop table keywords; create table keywords (querystring varchar(255) NOT NULL, numref int(11), multiwor d smallint(6), date date, origine varchar(16) NOT NULL) TYPE = ISAM; create INDEX qindex ON keywords(querystring); create INDEX orindex ON keywords(origine); I don't know if this work at the moment it is importing... Notice a strange thing, ISAM ask for not null definition of indexed fields, MYISAM not .. why ? > > Bob Hall > >> I tried -r >> I tried -o >> I tried to truncate the table and re-build the index from scratch. >> I tried to create ex-novo a table, import data from scratch and it >> result in a corrupted table. >> >> None of these seems to work >> >> Bob Hall wrote: >> >>> Sir, I looked quickly through the mass of data supplied below, and >>> it looks like you only tried m >> > > > >>> with the -r -q option combination. Try it with just -r. If that >>> doesn't work, try it with -o. If that doesn't work, restore from >>> backup. >>> >>> Got backup? >>> >>> Bob Hall >>> >>>> Antonio gulli wrote: >>>> >>>> > Any help is appreciated >>>> > >>>> > Welcome to the MySQL monitor. Commands end with ; or \g. >>>> > Your MySQL connection id is 158 to server version: 3.23.36-log >>>> > >>>> > myisamchk -V >>>> > myisamchk Ver 1.45 for pc-linux-gnu at i686 >>>> > >>>> > a) Trying a recovery. >>>> > >>>> > myisamchk -r -q -Osort_key_blocks=16 keywords >>>> > - check key delete-chain >>>> > - check record delete-chain >>>> > - recovering (with keycache) MyISAM-table 'keywords.MYI' >>>> > Data records: 2062985 >>>> > 7000 >>>> > [root@ideare queries]# myisamchk -r -q -Osort_key_blocks=16 >>>> > -Okey_buffer_size=256M -Oread_buffer_size=256M >>>> -Osort_buffer_size=256M >>>> > keywords >>>> > - check key delete-chain >>>> > - check record delete-chain >>>> > - recovering (with keycache) MyISAM-table 'keywords.MYI' >>>> > Data records: 2062985 >>>> > >>>> > - >>>> > >>>> > - check key delete-chain >>>> > - check record delete-chain >>>> > - recovering (with keycache) MyISAM-table 'keywords.MYI' >>>> > Data records: 2062985 >>>> > >>>> > b) Trying a check >>>> > >>>> > Database changed >>>> > mysql> CHECK TABLE keywords; >>>> > >>>> +--+---+--+--- >>>> -+| >>>>
Re: Can't recover a bad corrupeted table...
I tried -r I tried -o I tried to truncate the table and re-build the index from scratch. I tried to create ex-novo a table, import data from scratch and it result in a corrupted table. None of these seems to work Bob Hall wrote: > Sir, I looked quickly through the mass of data supplied below, and it > looks like you only tried myisamchk with the -r -q option combination. > Try it with just -r. If that doesn't work, try it with -o. If that > doesn't work, restore from backup. > > Got backup? > > Bob Hall > >> Antonio gulli wrote: >> >> > Any help is appreciated >> > >> > Welcome to the MySQL monitor. Commands end with ; or \g. >> > Your MySQL connection id is 158 to server version: 3.23.36-log >> > >> > myisamchk -V >> > myisamchk Ver 1.45 for pc-linux-gnu at i686 >> > >> > a) Trying a recovery. >> > >> > myisamchk -r -q -Osort_key_blocks=16 keywords >> > - check key delete-chain >> > - check record delete-chain >> > - recovering (with keycache) MyISAM-table 'keywords.MYI' >> > Data records: 2062985 >> > 7000 >> > [root@ideare queries]# myisamchk -r -q -Osort_key_blocks=16 >> > -Okey_buffer_size=256M -Oread_buffer_size=256M >> -Osort_buffer_size=256M >> > keywords >> > - check key delete-chain >> > - check record delete-chain >> > - recovering (with keycache) MyISAM-table 'keywords.MYI' >> > Data records: 2062985 >> > >> > - >> > >> > - check key delete-chain >> > - check record delete-chain >> > - recovering (with keycache) MyISAM-table 'keywords.MYI' >> > Data records: 2062985 >> > >> > b) Trying a check >> > >> > Database changed >> > mysql> CHECK TABLE keywords; >> > >> +--+---+--+--- >> -+| >> > Table| Op| Msg_type | >> > Msg_text >> > >> |+--+---+--+-- >> --+| >> > keywords | check | error| Key in wrong position at page >> > 24942592 || queries.keywords | check | error| >> > Corrupt >> > >> |+--+---+--+-- >> --+2 >> > rows in set (10.12 sec) >> > >> > yisamchk -a -Osort_key_blocks=16 -Okey_buffer_size=256M >> > -Oread_buffer_size=256M -Osort_buffer_size=256M keywords >> > \Checking MyISAM file: keywords >> > Data records: 2062985 Deleted blocks: 0 >> > myisamchk: warning: Table is marked as crashed >> > - check file-size >> > - check key delete-chain >> > - check record delete-chain >> > - check index reference >> > - check data record references index: 1 >> > - check data record references index: 2 >> > - check data record references index: 3 >> > myisamchk: error: Key in wrong position at page 24942592 >> > - check record links >> > myisamchk: error: Keypointers and record positions doesn't match >> > MyISAM-table 'keywords' is corrupted >> > >> > 3) Describe this table ... >> > >> > myisamchk -dvv keywords >> > >> > MyISAM file: keywords >> > Record format: Packed >> > Character set: latin1 (8) >> > File-version:1 >> > Creation time: 2001-03-28 10:02:22 >> > Recover time:2001-03-29 11:01:02 >> > Status: crashed >> > Data records: 2062985 Deleted blocks: 0 >> > Datafile parts:2062985 Deleted data: 0 >> > Datafile pointer (bytes):4 Keyfile pointer (bytes):4 >> > Datafile length: 79274620 Keyfile length: 99454976 >> > Max datafile length:4294967294 Max keyfile length: 4398046510079 >> > Recordlength: 281 >> > >> > table description: >> > Key Start Len Index Type Rec/key Root >> > Blocksize >> > 1 5 254 fulltext varchar packed 0 >> > 26022912 2048 >> > 1 4 float 0 >> > 2 266 16 multip. char packed stripped NULL 0 >> > 56983552 1024 >>
Re: mysql over nfs on netapp filer
> > Hi, > > my problem is that now the second time the performance of my f740 > goes down every day. > i solved the prob with increasing maxfiles (no to the max for the > volume). > after that the cpu of the filer, which went up from > 25% peak to 90% peak in 5 days, goes down again. > > any ideas why this happens? > next time i will have no possiblity to increase maxfiles again. > i am sure it depends on mysql cause the other things running > there at the moment are doing nearly nothing. > > thanks > regards > > Sven Huster > Senior Unix System Administrator > *BSD, Linux, Solaris > Sven, 1) what is ther server you are using? 2) what kind of nfs ? 3) have you played with read_buffer and write_buffer nfs options ? 4) What kind of tables are you defining ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to have faster select?
Why don't use FULLTEXT index ? This select doesn't use normal index so you have a sort of linear search Chai-Hup Chen wrote: > Hi, > I find in our database, select "% string %" is very slow (>1 > minute). The database size have only 2M rows, any idea how could I get > faster response? Thanks a lot. > > CREATE TABLE Books ( > isbn char(10) NOT NULL default '', > Title char(255) NOT NULL default '', > KEY isbn (isbn), > KEY Title (Title) > } > > select count(*) from Books -> 2028971 > > select count(*) from Books where Title like "% cisco %"; > +--+ > | count(*) | > +--+ > | 86 | > +--+ > 1 row in set (1 min 25.09 sec) > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- -- Antonio Gulli'Ideare S.p.a tel: (+39) 050 575300 [EMAIL PROTECTED] Lungarno Mediceo 56 fax: (+39) 050 575583 whois: AG2-ORG I-56126 Pisa, Italy http://www.ideare.com net: print pack"C*",split/\D+/,`echo "16iII*o\U@{$/=$z;[(pop,pop,unpack"H*",<> )]}\EsMsKsN0[lN*1lK[d2%Sa2/d0http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql over nfs on netapp filer
Yep, what do you need ? Sven Huster wrote: > Hi there, > > i anybody out there running mysql on a netapp filer as data storage? > > regards > > Sven Huster > Senior Unix System Administrator > *BSD, Linux, Solaris > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- -- Antonio Gulli'Ideare S.p.a tel: (+39) 050 575300 [EMAIL PROTECTED] Lungarno Mediceo 56 fax: (+39) 050 575583 whois: AG2-ORG I-56126 Pisa, Italy http://www.ideare.com net: print pack"C*",split/\D+/,`echo "16iII*o\U@{$/=$z;[(pop,pop,unpack"H*",<> )]}\EsMsKsN0[lN*1lK[d2%Sa2/d0http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can't recover a bad corrupeted table...
Antonio gulli wrote: > Any help is appreciated > > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 158 to server version: 3.23.36-log > > myisamchk -V > myisamchk Ver 1.45 for pc-linux-gnu at i686 > > a) Trying a recovery. > > myisamchk -r -q -Osort_key_blocks=16 keywords > - check key delete-chain > - check record delete-chain > - recovering (with keycache) MyISAM-table 'keywords.MYI' > Data records: 2062985 > 7000 > [root@ideare queries]# myisamchk -r -q -Osort_key_blocks=16 > -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M > keywords > - check key delete-chain > - check record delete-chain > - recovering (with keycache) MyISAM-table 'keywords.MYI' > Data records: 2062985 > > - > > - check key delete-chain > - check record delete-chain > - recovering (with keycache) MyISAM-table 'keywords.MYI' > Data records: 2062985 > > b) Trying a check > > Database changed > mysql> CHECK TABLE keywords; > +--+---+--++| > Table| Op| Msg_type | > Msg_text > |+--+---+--++| > keywords | check | error| Key in wrong position at page > 24942592 || queries.keywords | check | error| > Corrupt > |+--+---+--++2 > rows in set (10.12 sec) > > yisamchk -a -Osort_key_blocks=16 -Okey_buffer_size=256M > -Oread_buffer_size=256M -Osort_buffer_size=256M keywords > \Checking MyISAM file: keywords > Data records: 2062985 Deleted blocks: 0 > myisamchk: warning: Table is marked as crashed > - check file-size > - check key delete-chain > - check record delete-chain > - check index reference > - check data record references index: 1 > - check data record references index: 2 > - check data record references index: 3 > myisamchk: error: Key in wrong position at page 24942592 > - check record links > myisamchk: error: Keypointers and record positions doesn't match > MyISAM-table 'keywords' is corrupted > > 3) Describe this table ... > > myisamchk -dvv keywords > > MyISAM file: keywords > Record format: Packed > Character set: latin1 (8) > File-version:1 > Creation time: 2001-03-28 10:02:22 > Recover time:2001-03-29 11:01:02 > Status: crashed > Data records: 2062985 Deleted blocks: 0 > Datafile parts:2062985 Deleted data: 0 > Datafile pointer (bytes):4 Keyfile pointer (bytes):4 > Datafile length: 79274620 Keyfile length: 99454976 > Max datafile length:4294967294 Max keyfile length: 4398046510079 > Recordlength: 281 > > table description: > Key Start Len Index Type Rec/key Root > Blocksize > 1 5 254 fulltext varchar packed 0 > 26022912 2048 > 1 4 float 0 > 2 266 16 multip. char packed stripped NULL 0 > 56983552 1024 > 3 2 255 multip. char packed stripped NULL 0 > 30499840 2048 > > Field Start Length Nullpos Nullbit Type > 1 1 1 > 2 2 2551 1 no endspace > 3 257 4 1 2 no zeros > 4 261 2 1 4 no zeros > 5 263 3 1 8 no zeros > 6 266 16 1 16 no endspace > > 4) Re-check it > > mysql> CHECK TABLE keywords; > +--+---+--++| > Table| Op| Msg_type | > Msg_text > |+--+---+--++| > keywords | check | warning | Table is marked as > crashed || keywords | check | error| Key in > wrong position at page 74375168 || queries.keywords | check | error| > Corrupt > |+--+---+--++3 > rows in set (9.12 sec) > > 5) http://www.mysql.com/doc/R/e/Repair.html Stage 3: Difficult repair > > francesca> mysql queries > mysql> SET AUTOCOMMIT=1; > mysql> TRUNCATE TABLE keywords; > mysql> quit > > myisamchk -r -q -Osort_key_blocks=16 -Okey_buffer_size=256M > -Oread_buffer_size=256M -Osort_buffer_size=256M keywords > - check key delete-chain > - check record delete-chain > - recovering (with keycache) MyISAM-table 'keywords.MYI
Can't recover a bad corrupeted table...
Any help is appreciated Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 158 to server version: 3.23.36-log myisamchk -V myisamchk Ver 1.45 for pc-linux-gnu at i686 a) Trying a recovery. myisamchk -r -q -Osort_key_blocks=16 keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 7000 [root@ideare queries]# myisamchk -r -q -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 - - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 2062985 b) Trying a check Database changed mysql> CHECK TABLE keywords; +--+---+--++| Table| Op| Msg_type | Msg_text |+--+---+--++| keywords | check | error| Key in wrong position at page 24942592 || queries.keywords | check | error| Corrupt |+--+---+--++2 rows in set (10.12 sec) yisamchk -a -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords \Checking MyISAM file: keywords Data records: 2062985 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 myisamchk: error: Key in wrong position at page 24942592 - check record links myisamchk: error: Keypointers and record positions doesn't match MyISAM-table 'keywords' is corrupted 3) Describe this table ... myisamchk -dvv keywords MyISAM file: keywords Record format: Packed Character set: latin1 (8) File-version:1 Creation time: 2001-03-28 10:02:22 Recover time:2001-03-29 11:01:02 Status: crashed Data records: 2062985 Deleted blocks: 0 Datafile parts:2062985 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):4 Datafile length: 79274620 Keyfile length: 99454976 Max datafile length:4294967294 Max keyfile length: 4398046510079 Recordlength: 281 table description: Key Start Len Index Type Rec/key Root Blocksize 1 5 254 fulltext varchar packed 0 26022912 2048 1 4 float 0 2 266 16 multip. char packed stripped NULL 0 56983552 1024 3 2 255 multip. char packed stripped NULL 0 30499840 2048 Field Start Length Nullpos Nullbit Type 1 1 1 2 2 2551 1 no endspace 3 257 4 1 2 no zeros 4 261 2 1 4 no zeros 5 263 3 1 8 no zeros 6 266 16 1 16 no endspace 4) Re-check it mysql> CHECK TABLE keywords; +--+---+--++| Table| Op| Msg_type | Msg_text |+--+---+--++| keywords | check | warning | Table is marked as crashed || keywords | check | error| Key in wrong position at page 74375168 || queries.keywords | check | error| Corrupt |+--+---+--++3 rows in set (9.12 sec) 5) http://www.mysql.com/doc/R/e/Repair.html Stage 3: Difficult repair francesca> mysql queries mysql> SET AUTOCOMMIT=1; mysql> TRUNCATE TABLE keywords; mysql> quit myisamchk -r -q -Osort_key_blocks=16 -Okey_buffer_size=256M -Oread_buffer_size=256M -Osort_buffer_size=256M keywords - check key delete-chain - check record delete-chain - recovering (with keycache) MyISAM-table 'keywords.MYI' Data records: 0 mysql> use queries; Database changed mysql> CHECK TABLE keywords; +--+---+--++| Table| Op| Msg_type | Msg_text |+--+---+--++| keywords | check | error| Key in wrong position at page 65639424 || queries.keywords | check | error| Corrupt |+--+---+--++2 rows in set (9.71 sec) mysql> describe keywords; ERROR 1016: Can't open file: 'keywords.MYD'. (errno: 145) - Before posting, please che