Bug #19702 Using myisampack/myisamchk on a FULLTEXT indexed table results in table corrupt
Submitted: 10 May 2006 22:56 Modified: 25 Sep 2006 0:06
Reporter: Gregory Harrison Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.23-BK, 5.0.18 OS:Linux (Linux RHEL4)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: corruption, myisam

[10 May 2006 22:56] Gregory Harrison
Description:
Problem: Simple MyISAM table that includes a FULLTEXT index on a char(55) field.

Prior to packing, the full text index works fine on MATCH...AGAINST queries.

Following the myisampack and myisamchk steps, although it appears to work fine, any SQL query will report “ERROR 126 (HY000): Incorrect key file for table './anydir/table001.MYI'; try to repair it”

CHECK TABLE reports:

Size of indexfile is: 362588160        Should be: 452273152
Corrupt

MySQL is comparing the pre-packed index file size to the packed index size and seeing a difference, then reporting as table corruption?

How to repeat:
I perform the following on the table:

/site/mysql5.0.18/bin/myisamchk -dvv table001

(processes normally - output excluded from this email to save space)

/site/mysql5.0.18/bin/myisamchk -rq table001.MYI

(processes normally - output excluded from this email to save space)

/site/mysql5.0.18/bin/myisampack table001.MYI

Compressing table001.MYD: (2630711 records)
- Calculating statistics
- Compressing file
84.47%
Remember to run myisamchk -rq on compressed tables

/site/mysql5.0.18/bin/myisamchk -rq site001.MYI

- check record delete-chain
- recovering (with sort) MyISAM-table 'efficacy001.MYI'
Data records: 2630711
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6
- Fixing index 7
- Fixing index 8
- Fixing index 9
- Fixing index 10

/site/mysql5.0.18/bin/myisamchk -dvv table001

(processes normally - output excluded from this email to save space)

Here is what MySQL reports on a few checks:

mysql>  show table status where name = "table001"\G

*************************** 1. row ***************************

           Name: table001
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 2630711
 Avg_row_length: 70
    Data_length: 185458669
Max_data_length: 281474976710655
   Index_length: 379548672
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-05-10 13:39:20
    Update_time: 2006-05-10 13:40:43
     Check_time: 2006-05-10 14:30:48
      Collation: utf8_general_ci
       Checksum: 553382493
 Create_options:
        Comment:

1 row in set (0.00 sec)

 

mysql> SELECT count(*) FROM table001 WHERE MATCH (Title) AGAINST ("+world" IN BOOLEAN MODE)\G

ERROR 126 (HY000): Incorrect key file for table './anydir/table001.MYI'; try to repair it

mysql> check table table001;
+-----------------------+-------+----------+-------------------------------------------------------------+
| Table                 | Op    | Msg_type | Msg_text                                                    |
+-----------------------+-------+----------+-------------------------------------------------------------+
| anydir.table001 | check | error    | Size of indexfile is: 362588160        Should be: 452273152 |
| anydir.table001 | check | error    | Corrupt                                                     |
+-----------------------+-------+----------+-------------------------------------------------------------+
2 rows in set (0.53 sec)

mysql> check table table001\G
*************************** 1. row ***************************
   Table: anydir.table001
      Op: check
Msg_type: error
Msg_text: Table './anydir/table001' is marked as crashed and should be repaired
1 row in set (0.00 sec)
[10 May 2006 23:01] Gregory Harrison
FYI: The DB is shutdown while performing all myisamchk/myisampack commands.
[11 May 2006 10:30] Valeriy Kravchuk
Thank you for a problem report. Please, send the SHOW CREATE TABLE table001 results, for completeness, and try to repeat with a newer version, 5.0.21.
[11 May 2006 17:26] Gregory Harrison
We are experiencing the exact same behavior on MySQL Max v5.0.21.

(Other private submissions to dev team containing schema and mysqladmin version and variables info from MySQL Max v5.0.21)
[12 May 2006 0:15] Gregory Harrison
An EXTREMELY small and simple example SQL to create a DB, table, and table data to reproduce the issue

Attachment: brownfox.sql (text/plain), 4.34 KiB.

[12 May 2006 3:01] Gregory Harrison
FYI: Changing the CHAR(55) column to a TEXT column appears to circumvent this issue.
[11 Jun 2006 14:08] Valeriy Kravchuk
Verified just as described with 5.0.23-BK. After running your brownfox.sql (uploaded):

openxs@suse:~/dbs/5.0> bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/5.0/var/suse.pid
060611 12:54:45  mysqld ended

[1]+  Done                    bin/mysqld_safe
openxs@suse:~/dbs/5.0> cd var/TI/
openxs@suse:~/dbs/5.0/var/TI> ../../bin/myisamchk -dvv table001

MyISAM file:         table001
Record format:       Fixed length
Character set:       latin1_swedish_ci (8)
File-version:        1
Creation time:       2006-06-11  9:53:39
Status:              changed
Data records:                   70  Deleted blocks:                 0
Datafile parts:                 70  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:             11620  Keyfile length:              4096
Max datafile length: 46724846133968894  Max keyfile length: 288230376151710719
Recordlength:                  166

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   5     254 fulltext ? packed                       0         3072       1024
    1     4           float                          0

Field Start Length Nullpos Nullbit Type
1     1     1
2     2     165
openxs@suse:~/dbs/5.0/var/TI> ../../bin/myisamchk -rq table001.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table 'table001.MYI'
Data records: 70
- Fixing index 1
openxs@suse:~/dbs/5.0/var/TI> ../../bin/myisampack table001.MYI
Compressing table001.MYD: (70 records)
- Calculating statistics
- Compressing file
92.67%
Remember to run myisamchk -rq on compressed tables
openxs@suse:~/dbs/5.0/var/TI> ../../bin/myisamchk -rq table001.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table 'table001.MYI'
Data records: 70
- Fixing index 1
openxs@suse:~/dbs/5.0/var/TI> cd ../..
openxs@suse:~/dbs/5.0> bin/mysqld_safe &
[1] 30592
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

openxs@suse:~/dbs/5.0> bin/mysql -uroot TI
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show table status where name = 'table001'\G
*************************** 1. row ***************************
           Name: table001
         Engine: MyISAM
        Version: 10
     Row_format: Compressed
           Rows: 70
 Avg_row_length: 12
    Data_length: 852
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-06-11 12:53:39
    Update_time: 2006-06-11 12:53:50
     Check_time: 2006-06-11 12:55:32
      Collation: utf8_general_ci
       Checksum: 3028215939
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> SELECT count(*) FROM table001 WHERE MATCH (field2) AGAINST ("+world" IN
BOOLEAN MODE);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql> check table table001;
+-------------+-------+----------+------------------------------------+
| Table       | Op    | Msg_type | Msg_text                           |
+-------------+-------+----------+------------------------------------+
| TI.table001 | check | error    | Key in wrong position at page 1024 |
| TI.table001 | check | error    | Corrupt                            |
+-------------+-------+----------+------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT count(*) FROM table001 WHERE MATCH (field2) AGAINST ("+over" IN B
OOLEAN MODE);
ERROR 145 (HY000): Table './TI/table001' is marked as crashed and should be repa
ired
mysql> check table table001;
+-------------+-------+----------+-------------------------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                     |
+-------------+-------+----------+-------------------------------------------------------------------+
| TI.table001 | check | error    | Table './TI/table001' is marked as crashed and should be repaired |
+-------------+-------+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
[6 Jul 2006 16:59] Gregory Harrison
This may be as simple as clarifying when FLUSH TABLES is mandatory.  In the documented procedure to reproduce this error, if you insert a step to FLUSH TABLES immediately after performing the index repair, MySQL no longer reports the table as corrupt.   Please verify.
[26 Jul 2006 15:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9605
[17 Aug 2006 16:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10593

ChangeSet@1.2528, 2006-08-17 21:23:00+05:00, svoj@may.pils.ru +1 -0
  BUG#19702 - Using myisampack/myisamchk on a FULLTEXT indexed
              table results in table corrupt
  
  Fulltext key has always two keysegs, thus we need to update
  FT_SEGS (last) element from seg array in case of compressed table.
  Also we must update ft2_keyinfo.
[21 Sep 2006 7:56] Ingo Strüwing
Pushed to 5.1.12.
[21 Sep 2006 17:10] Ingo Strüwing
Pushed to 5.0.26.
[22 Sep 2006 8:16] Sergey Vojtovich
Fix will also be available in 4.1.22.
[22 Sep 2006 12:35] Ingo Strüwing
Pushed to 4.1.22.
[25 Sep 2006 0:06] Paul Dubois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.