| 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: | |
| 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 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.
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)