mmokrejs> On Sun, 9 Dec 2001, Michael Widenius wrote: mmokrejs> Hi, mmokrejs> I think I've reproduced it. However, it's ugly long and at some
places I don't know mostly mmokrejs> what I'm really doing. :( At some place you will see reported error
127. mmokrejs> Thih just remonds me, that see time to time these error in mysql
errorlog file, mmokrejs> as I've once already posted a long time ago (Linux 2.2.19, 3.23.44). mmokrejs> As far as I remeber, whenever I've seen those errors in errorlog and I
ran mmokrejs> check table or probably even myisamchk, they were fine. But, this
might really mmokrejs> have some meaning.

mmokrejs> 011205 13:09:29 read_const: Got error 127 when reading table
./Anabaena/contig_data mmokrejs> 011205 13:09:55 read_const: Got error 127 when reading table
./Aquifex_aeolicus_VF5_pEce1/prot_data mmokrejs> 011205 13:09:56 read_const: Got error 127 when reading table
./Aquifex_aeolicus_VF5_pEce1/orf_data mmokrejs> 011205 13:21:42 read_const: Got error 127 when reading table
./Bacteroides_fragillis_NCTC9343/contig_data mmokrejs> 011205 13:25:21 read_const: Got error 127 when reading table
./Bordetella_parapertussis_12822/contig_data

mmokrejs> They always appeared to be random messages. Definitely, right now if I
check the above tables mmokrejs> they appear fine via CHECK TABLE.

If you can get error 127 and check table dosn't give an error, then there is definitely a bug in MySQL.

Can you produce a repeatable test case of this ? What type of table is the above tables ?

mmokrejs> Here you just recreate index. Is somehow modified the .MYD file in
this mmokrejs> example (i.e. why did you omit the q option?).

Yes, I just recreated the index, but if there would have been any errors in the compressed data file myisamchk would have found it.

The -q option will just ensure that the .MYD file is not touched. If the data file is ok (like the .MYD file is above) then -q will just make things slightly faster. The end result is the same.

mysql> check table known3d_data; mmokrejs>
+-----------------------------------------------+-------+----------+----------+ mmokrejs> | Table | Op | Msg_type |
Msg_text | mmokrejs>
+-----------------------------------------------+-------+----------+----------+ mmokrejs> | Burkholderia_pseudomallei_K96243.known3d_data | check | status |
OK | mmokrejs>
+-----------------------------------------------+-------+----------+----------+ mmokrejs> 1 row in set (3.22 sec)

mmokrejs> switch to another xterm:

This is wrong. You must do 'flush tables' BEFORE running myisampack on the table!

mmokrejs> mysql@kulan$ myisampack --backup
/home/mysql/Burkholderia_pseudomallei_K96243/known3d_data.MYI mmokrejs> Compressing
/home/mysql/Burkholderia_pseudomallei_K96243/known3d_data.MYD: (24646 records) mmokrejs> - Calculating statistics mmokrejs> - Compressing file mmokrejs> 36.61% mmokrejs> Remember to run myisamchk -rq on compressed tables mmokrejs> mysql@kulan$ myisamchk -rq
/home/mysql/Burkholderia_pseudomallei_K96243/known3d_data.MYI mmokrejs> - check key delete-chain mmokrejs> - check record delete-chain mmokrejs> - recovering (with sort) MyISAM-table
'/home/mysql/Burkholderia_pseudomallei_K96243/known3d_data.MYI' mmokrejs> Data records: 24646 mmokrejs> - Fixing index 1 mmokrejs> - Fixing index 2 mmokrejs> - Fixing index 3 mmokrejs> - Fixing index 4 mmokrejs> - Fixing index 5 mmokrejs> mysql@kulan$

mmokrejs> switch back to previous xterm into the mysql session:

mysql> flush tables; mmokrejs> Query OK, 0 rows affected (0.01 sec)

The above 'could' destroy your .MYI file if the table had been modified in MySQL.

Anyway, what did you try to do above ?

You can't just replace a compressed .MYD file with a not compressed. The .MYI and .MYD files must always be kept together. This is because all meta information (like the record type) is stored in the .MYI file.

mmokrejs> kulan:/home/users/pedant> mysql Burkholderia_pseudomallei_K96243 mmokrejs> Welcome to the MySQL monitor. Commands end with ; or \g. mmokrejs> Your MySQL connection id is 45131 to server version: 3.23.44-log

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

mysql> lock table known3d_data write; mmokrejs> ERROR 1016: Can't open file: 'known3d_data.MYD'. (errno: 127)

This happens because MyISAM thinks (based on the information in the .MYI file) that the data file is compressed while it isn't.

The only way to fix this is to generate a new empty .MYI file with the correct header information (Just run 'truncate table ..' on it) and then copy the old .MYD file over the empty one.

mysql> repair table known3d_data;

mmokrejs> Now it runs already about 7 minutes with no output. In the backgroung
I see:

If the repair is done through the key cache then it will take some time.

<cut>

mmokrejs> I pressed ctrl+c and cancelled interactive session with mysql, tried
`mysqladmin flush-tables` mmokrejs> andam again waiting forever. Establishing new connection to mysqld and
listing processes I see:

mmokrejs> | 45131 | root | kulan | Burkholderia_pseudomallei_K96243
| Query | 561 | Repair with keycache | repair table known3d_data
|

Yes, even if you quit mysql the repair will continue.

mmokrejs> OK, lets kill them at least:

mmokrejs> killing the flush-tables thread worked. However, I cannot kill the

mmokrejs> | 45131 | root | kulan | Burkholderia_pseudomallei_K96243
| Killed | 780 | Repair with keycache | repair table known3d_data
|

mmokrejs> thread. It says:

mysql> kill 45131; mmokrejs> Query OK, 0 rows affected (0.05 sec)

The kill will mark the thread to be killed, but it will take some time

depending on in which pass the repair table is. I don't even know if it's a good thing to kill a repair table as the table will be unusable after that...

mmokrejs> So, I really don't know what to do now. We have nice example how to
get into this stage, mmokrejs> I know that .MYD is untouched, uncompressed, but when I delete .MYI
completely mmokrejs> as I've tried somewhere above I'm not able to do anything. But, with
this .MYI file mmokrejs> I also cannot do anything. I have the backup, you have it on ftp, but
that's solution. ;)

If you delete the .MYI table you have to generate it from the .frm file with 'truncate table'.

mmokrejs> Perfect. I was looking for --backup option for myisamchk but at least
the built-in mmokrejs> help doesn't contain it.

mmokrejs> Mine too. ;) I've missed it in the Repair option. I'm somehow confused
because the Check option also mmokrejs> contains options which modify the data, it's not just read-only check, mmokrejs> so I expected such option more in the Global section.

The check option will not modify the data file, so there is no need to do a backup of it.

mmokrejs> But is it in my case/example right now really corrupted?

In your example you where using the .MYI file both from MySQL and from myisamchk/myisampack and this is a sure way to get a corrupted file.

I think I did figure out what is going on :)

- You must do FLUSH TABLES before you manipulate the .MYI or .MYD files outside of mysqld.

- You can't just replace a .MYD file with another; You have to always use the .MYD and .MYI files together. (Actually, if you want to save backup space, you can go away from this restrictions by just copying the first 8K from the .MYI file; This should contain all the meta-information that myisamchk / REPAIR TABLE needs to reconstruct the rest of this file.