> The table has
> mysql> select count(*) from repository;
> +----------+
> | count(*) |
> +----------+
> | 5673838 |
> +----------+
> records inside.
>
> 1. I get table a "table crashed" whenever I try to fix it or check it with MYISAMCHK
> using -r, -o or -f parameters. And the table is then no longer usable.
Don't do this when the table is in the cache!
The correct way (open two xterms and do commands in this order):
mysql> flush tables;
mysql> lock table repository write;
$ myisamchk -f -d -v repository.MYI # this will tell you the status of
the table based on the index file
If the output says the table is broken, then
myisamchk -r $table
myisamchk -f -d -v $table
mysql> unlock tables;
mysql> flush tables;
mysql> check table $table;
If it it looks fine (see the line Status), then
mysql> unlock tables;
mysql> flush tables;
mysql> check table $table;
if the check table complains about bad number of rows present versus
expect, like below:
+------------+-------+----------+---------------------------------------------------+
+
| Table | Op | Msg_type |
Msg_text |
+------------+-------+----------+---------------------------------------------------+
+
| $db.$table | check | warning | Size of datafile is: 679740
Should be: 0 |
| $db.$table | check | error | Record-count is not ok; is 501
Should be: 0 |
| $db.$table | check | warning | Found 501 parts Should
be: 0 parts |
| $db.$table | check | error |
Corrupt |
+------------+-------+----------+---------------------------------------------------+
+
then do
mysql> flush tables;
mysql> lock table $table write;
$ myisamchk -rq $table.MYI # fix the index while NOT modifying the
.MYD file with data
$ myisamchk -f -d -v $table # check the result
mysql> unlock tables;
mysql> flush tables;
mysql> check table $table;
In any case, you should have working table. I believe this is your
current problem. But you should upgrade to 3.23.49a at least as there're
some bugfixes in previous versions. There're probably still some (I've
reported some ;) ), so you should look for 3.23.51 to get more fixes.
I've learned a lot from this thread:
http://lists.mysql.com/cgi-ez/ezmlm-cgi?9:msn:11533:nkocbgofapjhfkjheipi
To quote the summarized secret from that thread:
"- 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.
Regards,
Monty"
"> 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.
Is this nice explanation of truncate table mentioned somewhere in the
myisamchk section in manual? I was missing this very serious
information -
that I cannot re-create .MYI via myisamchk but have to use some
`truncate
table`, which really doesn't sound to be the command I need when
repairing
table. ;)
[...]
> If you delete the .MYI table you have to generate it from the .frm
> file with 'truncate table'.
Perfect explanation what to do in such case. I just missed this idea
from
you example"
" -- me
">> 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> OK, I thought the index can be re-created safely from .MYD and
.frm.
mmokrejs> With your explanation somewhere below that .MYI I can recreate
with
mmokrejs> `truncate table` explains it well. And them myisamchk can fix
the .MYI
mmokrejs> according the current status of .MYD. I thought that `truncate
table`
mmokrejs> truncates the .MYD file, and this I didn't want to happen
intentionally
in
mmokrejs> these experiments.
Yes. TRUNCATE TABLE does truncate the .MYD file.
If you want to use this to generate an index the way I used it,
you must first do a backup of the .MYD file and then copy it back
after TRUNCATE TABLE.
>> 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.
mmokrejs> Is this nice explanation of truncate table mentioned somewhere
in the
mmokrejs> myisamchk section in manual? I was missing this very serious
information
-
mmokrejs> that I cannot re-create .MYI via myisamchk but have to use
some `truncate
mmokrejs> table`, which really doesn't sound to be the command I need
when repairing
mmokrejs> table. ;)
Check under 'How to repair tables', section 'difficult repair'
<cut>
mmokrejs> BTW: Can I lock whole database, not just one table?
For the moment the only way to do that is to do a
LOCK TABLES table1 READ, table2 READ,
for all tables in the database.
>> - 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.
mmokrejs> Nice information, but I'd better stay with complete .MYI or in
worst
mmokrejs> case use truncate command to recreate from .MYD and .frm. I
get the same
mmokrejs> result, right? --maybe with some extra CPU cycles...
Normally you should never have to use the 'truncate table' trick.
Note the the script 'mysqlhotcopy' only copies the header of the .MYI
file..
Regards,
Monty"
BTW: And here's status of my current knowledges ... ;)
http://lists.mysql.com/cgi-ez/ezmlm-cgi?9:mss:11886:200204:jjojehdjpfcddnaofmmb
--
Martin Mokrejs
<[EMAIL PROTECTED]>
---------------------------------------------------------------------
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
