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
>>> (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));
>
> ^^^^
>
> 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. Since your
> server is working normally, and this particular table and only this
> particular table was corrupted ab ovo, then the problem seems to be
> specific to the table. In general, use of a reserved word as a column
> name is definitely a problem.
>
>> 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 <[EMAIL PROTECTED]>
>>>>>> To unsubscribe, e-mail
>>>>>> <[EMAIL PROTECTED]>
>>>>>> 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 <[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<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 <[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<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 <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
>> <[EMAIL PROTECTED]>
>> 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 <[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<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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
