List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 2 2001 1:49pm
Subject:Re: Can't recover a bad corrupeted table...
View as plain text  
'date' is a reserved word.
Try renaming your date column, and see if the problem remains.

Antonio Gulli 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
> > (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@stripped>
> >>>> To unsubscribe, e-mail
> >>>> <mysql-unsubscribe-rjhalljr=starpower.net@stripped>
> >>>> 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@stripped>
> >>> To unsubscribe, e-mail
> >>> <mysql-unsubscribe-gulli=ideare.it@stripped>
> >>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >>>
> >>>
> >>
> >> --
> >> --
> >> Antonio Gulli'        Ideare S.p.a          tel: (+39) 050  575300
> >> gulli@stripped      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@stripped>
> > To unsubscribe, e-mail
> > <mysql-unsubscribe-gulli=ideare.it@stripped>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
> 
> --
> --
> Antonio Gulli'        Ideare S.p.a          tel: (+39) 050  575300
> gulli@stripped      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@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thread
Can't recover a bad corrupeted table...Antonio gulli29 Mar
  • Can't recover a bad corrupeted table...Antonio gulli29 Mar
    • Re: Can't recover a bad corrupeted table...Bob Hall30 Mar
  • Re: Can't recover a bad corrupeted table...Antonio Gulli30 Mar
    • Re: Can't recover a bad corrupeted table...Bob Hall31 Mar
  • Re: Can't recover a bad corrupeted table...Antonio Gulli31 Mar
    • Re: Can't recover a bad corrupeted table...Bob Hall2 Apr
  • Re: Can't recover a bad corrupeted table...Gerald Clark2 Apr
  • Re: Can't recover a bad corrupeted table...Antonio Gulli3 Apr