-----Original Message----- From: Cathy Luo [mailto:Cat...@lipstream.com] Sent: Thursday, May 04, 2000 3:22 PM To: 'mys...@lists.mysql.com' Subject: Corruption of Compressed Tables

Hello everyone,

I'm running into a problem with compressed MyISAM tables and I hope someone may be able to help. Basically, I converted ISAM tables to MyISAM tables (worked fine) because I need to use myisampack to compress tables with TEXT columns in them. After compression, the results of SELECT statements returns corrupted data for certain records. It did not matter whether the tables had indexes or not. However, data from a dump using SELECT INTO OUTFILE is fine (no difference from the original data).

Details System: Redhat Linux 6.0 on Compaq Proliant 1850R(Linux 2.2.5-15) MySQL: Was using MySQL 3.22.16-1, updated with MySQL 3.23.14-1 Structure of table joininfo19909: | account | varchar(8) | | MUL | | | | field1 | tinytext | | | | | | field2 | varchar(128) | | PRI | | | | field3 | datetime | | PRI | 0000-00-00 00:00:00 | | | field4 | datetime | | | 0000-00-00 00:00:00 | | | field5 | int(10) unsigned | | PRI | 0 | | | field6 | varchar(20) | | | | | | field7 | varchar(15) | | | | | | field8 | char(3) | | | | | | field9 | varchar(20) | | | | | | field10 | tinytext | | | | | | field11 | tinytext | | | | | | field12 | enum('Y','N') | | | Y | | | field13 | varchar(128) | | | | | | field14 | varchar(10) | | | | | | field15 | varchar(56) | | | | | | field16 | varchar(56) | | | | | | field17 | varchar(30) | | | | | | field18 | int(10) unsigned | | | 0 | | | field19 | varchar(128) | | | | |

Steps Taken 1. Converted table to type myisam using "ALTER TABLE joininfo199909 TYPE=mysiam" 2. Data in table looks ok; CHECK TABLE indicated no errors. 3. Compressed table using myisampack: myisampack -v joininfo199909 Compressing joininfo199909.MYD: (4881521 records) - Calculating statistics

normal: 4 empty-space: 1 empty-zero: 0 empty-fill: 3 pre-space: 0 end-space: 5 intervall-fields: 8 zero: 0 Original trees: 20 After join: 19 - Compressing file Min record length: 73 Max length: 572 Mean total length: 138 53.51%

4. Rebuilt index files: myisamchk -r joininfo199909 5. CHECK TABLE shows errors: mysql> check table joininfo199909; +-------------------------+-------+----------+------------------------------ -----------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------+-------+----------+------------------------------ -----------------------------+ | joininfo199909 | check | warning | Size of datafile is: 676724395 Should be: 676724388 | | joininfo199909 | check | error | Wrong bytesec: 254-254-8 at linkstart: 0 | | lipusage.joininfo199909 | check | status | Corrupt | +-------------------------+-------+----------+------------------------------ -----------------------------+ 3 rows in set (49.11 sec)

Get Error 127 when trying to do SELECTs on table.

6. Try to repair table: myisamchk -r joininfo199909 7. Check table indicates table is ok. However: - When I do "SELECT * INTO OUTFILE...", the output is uncorrupted. - When I select certain records (SELECT * FROM joininfo199909 WHERE ... ), the output for some of those records are corrupted. - SELECT COUNT(*) FROM joininfo19909 WHERE ACCOUNT="1234" gives a different number of results than SELECT * FROM joininfo199909 WHERE ACCOUNT="1234"

------ I'm also having problems using mysqlimport (server shutdowns everytime I try to import a file into a new table), but that's another email!

Thanks for any help, and let me know if you need more info. Meanwhile, I reverted to the old version of MySQL because of a deadline.

Cathy

To unsubscribe, send a message to: <mysql-unsubscribe-Cathy=lips...@lists.mysql.com>