[prev in list] [next in list] [prev in thread] [next in thread]
List: mysql
Subject: Re: Can't recover a bad corrupeted table...
From: Antonio Gulli <gulli () ideare ! com>
Date: 2001-03-31 15:57:08
[Download RAW message or body]
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;
>>>> >
>>>> +------------------+-------+----------+-------------------------------
>>>> ---------+|
>>>> > 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 255 1 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 check:
>>>> http://www.mysql.com/manual.php (the manual)
>>>> http://lists.mysql.com/ (the list archive)
>>>>
>>>> To request this thread, e-mail <mysql-thread69923@lists.mysql.com>
>>>> To unsubscribe, e-mail
>>>> <mysql-unsubscribe-rjhalljr=starpower.net@lists.mysql.com>
>>>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>>
>>>
>>>
>>> Know thyself? Absurd direction!
>>> Bubbles bear no introspection. -Khushhal Khan Khatak
>>>
>>> ---------------------------------------------------------------------
>>> 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 <mysql-thread70040@lists.mysql.com>
>>> To unsubscribe, e-mail
>>> <mysql-unsubscribe-gulli=ideare.it@lists.mysql.com>
>>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>>
>>>
>>
>> --
>> --
>> Antonio Gulli' Ideare S.p.a tel: (+39) 050 575300
>> gulli@ideare.com 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<X+d*lMLa^*lN%0]dsXx++lMlN/dsM0<J]dsJxp"|dc`
>>
>
>
> Know thyself? Absurd direction!
> Bubbles bear no introspection. -Khushhal Khan Khatak
>
> ---------------------------------------------------------------------
> 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 <mysql-thread70129@lists.mysql.com>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gulli=ideare.it@lists.mysql.com>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
--
--
Antonio Gulli' Ideare S.p.a tel: (+39) 050 575300
gulli@ideare.com 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<X+d*lMLa^*lN%0]dsXx++lMlN/dsM0<J]dsJxp"|dc`
---------------------------------------------------------------------
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 <mysql-thread70134@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-mysql=progressive-comp.com@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic