Bug #35161 --myisam-recover does not work for partitioned MyISAM tables
Submitted: 8 Mar 2008 7:12 Modified: 15 Sep 2008 13:56
Reporter: Nicolae Namolovan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.23 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: D1 (Critical) / R2 (Low) / E2 (Low)

[8 Mar 2008 7:12] Nicolae Namolovan
Description:
I think I never saw auto-recovery myisam working under 5.1.*

Everything I get is:
080308  8:12:30 [ERROR] /usr/local/libexec/mysqld: Table './db/name#P#p2' is marked as crashed and should be repaired

And it never say anything about trying to repair itself..

I have in my.cnf
myisam-recover=BACKUP,FORCE

And It seem to be set properly:

mysql db> show variables like '%reco%';
+------------------------+--------------+
| Variable_name          | Value        |
+------------------------+--------------+
| myisam_recover_options | BACKUP,FORCE |

But still no action. Any idea ?

How to repeat:
myisam-recover=BACKUP,FORCE into my.cnf
Try to crash a table, and see if it try to recover
[18 Mar 2008 16:22] Susanne Ebrecht
Many thanks for reporting a bug.
Unfortunately I can't reproduce this.

For analyse this we need to know:
- Which operating system do you use?
- How do you install mysql?
- What did you do that the table crashed?
- Output from: show variables like '%char%';
- Output from: $ locale -a (if it is a Unix/Linux systems) from the owner of the data dir. If it is Windows we need to know the language settings
- Is there something in the error log why the table crashed?
[29 Mar 2008 19:42] Nicolae Namolovan
> - Which operating system do you use?
FreeBSD 6.2 and 7.0
> - How do you install mysql?
From ports of course
> - What did you do that the table crashed?
Forced system restart, maybe you can reproduce with a kill -9 ?
> - Output from: show variables like '%char%';
mysql (none)> show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

> - Output from: $ locale -a (if it is a Unix/Linux systems) from the owner of the data dir.
>If it is Windows we need to know the language settings

locale -a
C
POSIX
af_ZA.ISO8859-1
af_ZA.ISO8859-15
af_ZA.UTF-8
am_ET.UTF-8
be_BY.CP1131
be_BY.CP1251
be_BY.ISO8859-5
be_BY.UTF-8
bg_BG.CP1251
bg_BG.UTF-8
ca_ES.ISO8859-1
ca_ES.ISO8859-15
ca_ES.UTF-8
cs_CZ.ISO8859-2
cs_CZ.UTF-8
da_DK.ISO8859-1
da_DK.ISO8859-15
da_DK.UTF-8
de_AT.ISO8859-1
de_AT.ISO8859-15
de_AT.UTF-8
de_CH.ISO8859-1
de_CH.ISO8859-15
de_CH.UTF-8
de_DE.ISO8859-1
de_DE.ISO8859-15
de_DE.UTF-8
el_GR.ISO8859-7
el_GR.UTF-8
en_AU.ISO8859-1
en_AU.ISO8859-15
en_AU.US-ASCII
en_AU.UTF-8
en_CA.ISO8859-1
en_CA.ISO8859-15
en_CA.US-ASCII
en_CA.UTF-8
en_GB.ISO8859-1
en_GB.ISO8859-15
en_GB.US-ASCII
en_GB.UTF-8
en_IE.UTF-8
en_NZ.ISO8859-1
en_NZ.ISO8859-15
en_NZ.US-ASCII
en_NZ.UTF-8
en_US.ISO8859-1
en_US.ISO8859-15
en_US.US-ASCII
en_US.UTF-8
es_ES.ISO8859-1
es_ES.ISO8859-15
es_ES.UTF-8
et_EE.ISO8859-15
et_EE.UTF-8
eu_ES.ISO8859-1
eu_ES.ISO8859-15
eu_ES.UTF-8
fi_FI.ISO8859-1
fi_FI.ISO8859-15
fi_FI.UTF-8
fr_BE.ISO8859-1
fr_BE.ISO8859-15
fr_BE.UTF-8
fr_CA.ISO8859-1
fr_CA.ISO8859-15
fr_CA.UTF-8
fr_CH.ISO8859-1
fr_CH.ISO8859-15
fr_CH.UTF-8
fr_FR.ISO8859-1
fr_FR.ISO8859-15
fr_FR.UTF-8
he_IL.UTF-8
hi_IN.ISCII-DEV
hr_HR.ISO8859-2
hr_HR.UTF-8
hu_HU.ISO8859-2
hu_HU.UTF-8
hy_AM.ARMSCII-8
hy_AM.UTF-8
is_IS.ISO8859-1
is_IS.ISO8859-15
is_IS.UTF-8
it_CH.ISO8859-1
it_CH.ISO8859-15
it_CH.UTF-8
it_IT.ISO8859-1
it_IT.ISO8859-15
it_IT.UTF-8
ja_JP.SJIS
ja_JP.UTF-8
ja_JP.eucJP
kk_KZ.PT154
kk_KZ.UTF-8
ko_KR.CP949
ko_KR.UTF-8
ko_KR.eucKR
la_LN.ISO8859-1
la_LN.ISO8859-15
la_LN.ISO8859-2
la_LN.ISO8859-4
la_LN.US-ASCII
lt_LT.ISO8859-13
lt_LT.ISO8859-4
lt_LT.UTF-8
mn_MN.UTF-8
nb_NO.ISO8859-1
nb_NO.ISO8859-15
nb_NO.UTF-8
nl_BE.ISO8859-1
nl_BE.ISO8859-15
nl_BE.UTF-8
nl_NL.ISO8859-1
nl_NL.ISO8859-15
nl_NL.UTF-8
nn_NO.ISO8859-1
nn_NO.ISO8859-15
nn_NO.UTF-8
no_NO.ISO8859-1
no_NO.ISO8859-15
no_NO.UTF-8
pl_PL.ISO8859-2
pl_PL.UTF-8
pt_BR.ISO8859-1
pt_BR.UTF-8
pt_PT.ISO8859-1
pt_PT.ISO8859-15
pt_PT.UTF-8
ro_RO.ISO8859-2
ro_RO.UTF-8
ru_RU.CP1251
ru_RU.CP866
ru_RU.ISO8859-5
ru_RU.KOI8-R
ru_RU.UTF-8
sk_SK.ISO8859-2
sk_SK.UTF-8
sl_SI.ISO8859-2
sl_SI.UTF-8
sr_YU.ISO8859-2
sr_YU.ISO8859-5
sr_YU.UTF-8
sv_SE.ISO8859-1
sv_SE.ISO8859-15
sv_SE.UTF-8
tr_TR.ISO8859-9
tr_TR.UTF-8
uk_UA.CP1251
uk_UA.ISO8859-5
uk_UA.KOI8-U
uk_UA.UTF-8
zh_CN.GB18030
zh_CN.GB2312
zh_CN.GBK
zh_CN.UTF-8
zh_CN.eucCN
zh_HK.Big5HKSCS
zh_HK.UTF-8
zh_TW.Big5
zh_TW.UTF-8

> - Is there something in the error log why the table crashed?
No, usualy it's because of forced anormal system restart.

And today again.. It's too lazy to repair itself..

080329 20:07:02 [ERROR] /usr/local/libexec/mysqld: Table './db/table#P#p0' is marked as crashed and should be repaired
080329 20:07:03 [ERROR] /usr/local/libexec/mysqld: Table './db/table#P#p0' is marked as crashed and should be repaired
[3 Apr 2008 5:59] Sveta Smirnova
Thank you for the feedback.

Please try on FreeBSD 6.2 with our binaries to check if this is problem of MySQL or port. If you are able to repeat please upload *MYI, *MYD and *frm file of crashed table and your configuration file.
[3 May 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[9 May 2008 14:52] Guilhem Bichot
Hello Nicolae! You found a bug, thanks!
The corrupted table is named './db/table#P#p0', this is probably a subtable of a partitioned table.
After testing and looking at code, I see that --myisam-recover does not work with partitioned MyISAM tables.
Description: if a partitioned table uses MyISAM as the underlying engine, and one of the underlying MyISAM tables is corrupted, even if using --myisam-recover this corrupted table will not be automatically repaired. This is against the definition of --myisam-recover and has to be fixed.
How-to-repeat:
mysql>create table t(a int, key(a)) partition by list (a) (partition p0 values in (1)); insert into t values(1); system killall -9 mysqld;
# destroy the index file of one underlying table:
shell>head -c1024 /m/mysql-5.1-opt/mysql-test/var/master-data/test/t#P#p0.MYI >/tmp/foo;
shell>mv /tmp/foo /m/mysql-5.1-opt/mysql-test/var/master-data/test/t#P#p0.MYI
Now restart mysqld with --myisam-recover and do "select * from t":
ERROR 145 (HY000): Table './test/t#P#p0' is marked as crashed and should be repaired
and that's all. Table is not repaired, as repeating the SELECT leads to the same message.
Contrast this with if the table is non-partitioned: then the table is automatically repaired:
mysql> select * from t;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set, 4 warnings (3.03 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message                                                      |
+-------+------+--------------------------------------------------------------+
| Error |  145 | Table './test/t' is marked as crashed and should be repaired |
| Error | 1194 | Table 't' is marked as crashed and should be repaired        |
| Error | 1034 | 1 client is using or hasn't closed the table properly        |
| Error | 1034 | Size of indexfile is: 1024        Should be: 2048            |
+-------+------+--------------------------------------------------------------+
Suggested fix: probably by implementing ha_partition::check_and_repair(), etc.
[9 May 2008 16:29] Nicolae Namolovan
Hey Guilhem, thank you for attention and for make this reproducible..

5.1.x (the new dolphin) need a lot of love and attention from sun %)
[20 May 2008 10:01] Mattias Jonsson
This is related to Bug#20129, which is about repair table (from within mysqld server). Will add this to WL#4176.
[9 Jun 2008 12:18] 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/47596

2661 Mattias Jonsson	2008-06-09
      Bug#35161: --myisam-recover does not work for partitioned MyISAM tables
      
      Problem was that auto_repair, is_crashed and check_and_repair was not
      implemented in ha_partition.
      
      Solution, implemented them as loop over all partitions for is_crashed and
      check_and_repair, and using the first partition for auto_repair.
[7 Jul 2008 15:55] 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/49110

2661 Mattias Jonsson	2008-07-07
      Bug#35161: --myisam-recover does not work for partitioned MyISAM tables
      
      Problem was that auto_repair, is_crashed and check_and_repair was not
      implemented in ha_partition.
      
      Solution, implemented them as loop over all partitions for is_crashed and
      check_and_repair, and using the first partition for auto_repair.
      
      (Recommit after fixing review comments)
[13 Aug 2008 16:02] Mattias Jonsson
Pushed into mysql-6.0-bugteam and mysql-5.1-bugteam

(I will modify mtr_report.pl in at least 6.0 to filter out the warning:
'Got an error from unknown thread, ha_myisam.cc:804')
[25 Aug 2008 10:52] Georgi Kodinov
Pushed in 5.1.28
[25 Aug 2008 19:34] Paul Dubois
Noted in 5.1.28 changelog.

MyISAM recovery enabled with the --myisam-recover option did not work
for partitioned MyISAM tables.

Setting report to NDI pending push of fix into 6.0.x.
[14 Sep 2008 2:07] Bugs System
Pushed into 6.0.6-alpha  (revid:mattiasj@mysql.com-20080813193345-wr4swbqoz1xm00so) (version source revid:hakan@mysql.com-20080716160034-sdexuyp3qow7zlc6) (pib:3)
[15 Sep 2008 13:56] Jon Stephens
Also documented in 6.0.6 changelog.