MySQL Bugs: #10634: REPAIR TABLE ... USE_FRM incorrectly reports errors

Bug #10634	REPAIR TABLE ... USE_FRM incorrectly reports errors
Submitted:	13 May 2005 20:13	Modified:	14 May 2005 12:21
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Linux (Linux)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Hi,

When trying to run a REPAIR TABLE ... USE_FRM on a table with a FULLTEXT index, it reports a lot of garbarge, whereas CHECK TABLE doesn't report anything wrong.

mysql> CHECK TABLE art_subparts;
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| testbug.art_subparts | check | status   | OK       |
+----------------------+-------+----------+----------+
1 row in set (0.23 sec)

mysql> REPAIR TABLE art_subparts USE_FRM;
+----------------------+--------+----------+------------------------------------------------+
| Table                | Op     | Msg_type | Msg_text                                       |
+----------------------+--------+----------+------------------------------------------------+
| testbug.art_subparts | repair | info     | Delete link points outside datafile at 6019028 |
| testbug.art_subparts | repair | info     | Delete link points outside datafile at 6026124 |
| testbug.art_subparts | repair | info     | Delete link points outside datafile at 6049336 |
| testbug.art_subparts | repair | warning  | Number of rows changed from 0 to 1239          |
| testbug.art_subparts | repair | status   | OK                                             |
+----------------------+--------+----------+------------------------------------------------+

How to repeat:
Download ftp://ftp.mysql.com/pub/mysql/secret/usefrmbug1.tar.gz

And execute REPAIR TABLE art_subparts USE_FRM;

My ft configuration :

| ft_boolean_syntax               | + ^><()~*:""&|                         |
| ft_max_word_len                 | 84                                     |
| ft_min_word_len                 | 1                                      |
| ft_query_expansion_limit        | 20                                     |
| ft_stopword_file                | /dev/null                              |

Regards,
  Jocelyn

mysql> REPAIR TABLE art_subparts USE_FRM;
+----------------+--------+----------+------------------------------------------------+
| Table          | Op     | Msg_type | Msg_text                                       |
+----------------+--------+----------+------------------------------------------------+
| a.art_subparts | repair | info     | Delete link points outside datafile at 6019028 |
| a.art_subparts | repair | info     | Delete link points outside datafile at 6026124 |
| a.art_subparts | repair | info     | Delete link points outside datafile at 6049336 |
| a.art_subparts | repair | warning  | Number of rows changed from 0 to 1239          |
| a.art_subparts | repair | status   | OK                                             |
+----------------+--------+----------+------------------------------------------------+
5 rows in set (11.27 sec)

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See http://dev.mysql.com/doc/mysql/en/repair-table.html:

Note: Use this mode only if you cannot use regular REPAIR modes. .MYI header contains important table metadata (in particular, current AUTO_INCREMENT value and Delete link) that are lost in REPAIR ... USE_FRM. Don't use USE_FRM if the table is compressed, as this information is also stored in .MYI file.

And your table has deleted blocks (myisamchk -dvv shows "Deleted blocks:11")

Hi Sergei,

Why those deleted blocks points outside of the datafile ?
As well on those some of my table, I get

| presence.news | repair | info     | Found block that points outside data file at 17174596 |
| presence.news | repair | info     | Found block that points outside data file at 17174604 |
| presence.news | repair | info     | Found block that points outside data file at 17174632 |
| presence.news | repair | info     | Found block that points outside data file at 17174660 |

with this kind of repair, is this a normal behaviour as well ?

Thanks,
  Jocelyn

I'll try to explain.

1. When something is deleted from MYD file, the space is not reclaimed immediately, instead it's marked as so called "deleted block". These blocks are linked in the list - each deleted block points to a previous one, and MYI header contains a reference to the first block in the chain.

2. MYI header stores the length of the data file, and number of records in it.

3. MYI header stores current AUTO_INCREMENT value. (that's why auto_incr values are not reused after DELETE)

4. MYI header tells whether MYD is compressed or not.

Well, with USE_FRM you tell MyISAM to ignore the information from MYI header *completely* ! thus:

3. AUTO_INCREMENT value is lost (reusal may happen)

4. compressed tables cannot be read at all

2. You see a warning "number of records changed from 0 to xxx". Deleted records will appear to point "outside of the datafile" because datafile size appears to be 0.

1. MyISAM will find deleted blocks while MYI headers tells there should be none - and it'll appear as corruption too.

In short - USE_FRM should be used only when you really need to discard MYI file for whatever reason - e.g. when it's corrupted and regular REPAIR does not work.

Thanks a lot for the explanations !

  Jocelyn