Bug #67123 error in mi_update.c:226
Submitted: 7 Oct 2012 13:02 Modified: 12 Oct 2012 12:56
Reporter: Roman Zabransky Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.5.27, 5.5.29, 5.1.67, 5.7.0 OS:Any (MS Windows 2008 64bit, Linux)
Assigned to: CPU Architecture:Any
Tags: Incorrect key file, mi_update

[7 Oct 2012 13:02] Roman Zabransky
Description:
i recently importet a dump from 5.1.52 to 5.5.27 everything was fine running - import dump, mysql_upgrade and so on...
then i suddenly got these incorrect key file error for customer table...
i could not use the customertable... then i run myisamchk and repaired it...
then i saw this in my error log
121007 14:22:38 [ERROR] C:\MySQL5\bin\mysqld: Incorrect key file for table '.\customer.MYI'; try to repair it
121007 14:22:38 [ERROR] Got an error from thread_id=5915, ..\..\..\mysql-5.5.27\storage\myisam\mi_update.c:226
121007 14:22:38 [ERROR] MySQL thread id 5915, OS thread handle 0x460, query id 5214704 127.0.0.1 root Updating
update customer set email='dummy@dummy.com' where customer_id = 9588

i could reproduce the problem everytime when i run this update sql...
myisamck-->update-->table not usable anymore-->myisamck-->update-->table not usable anymore

now i dropped the primary key customer_id and re added it - i hope the problem is now solved? we are in production with over 400.000 customer entries ;)

How to repeat:
update customer set email='dummy@dummy.com' where customer_id = 9588

i could reproduce the problem everytime when i run this update sql...
myisamck-->update-->table not usable anymore-->myisamck-->update-->table not usable anymore
[7 Oct 2012 13:13] Roman Zabransky
this was the initial upgrade DUMP...

DROP TABLE IF EXISTS `customers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `customers` (
  `customer_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vorname` varchar(100) NOT NULL DEFAULT '',
  `nachname` varchar(100) NOT NULL DEFAULT '',
  `firma` varchar(100) NOT NULL DEFAULT '',
  `strasse` varchar(100) NOT NULL DEFAULT '',
  `plz` varchar(10) NOT NULL DEFAULT '',
  `ort` varchar(100) NOT NULL DEFAULT '',
  `land` varchar(40) NOT NULL DEFAULT '',
  `tel` varchar(70) NOT NULL DEFAULT '',
  `fax` varchar(70) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `username` varchar(100) NOT NULL DEFAULT '',
  `passwort` varchar(50) NOT NULL DEFAULT '',
  `timestamp` datetime DEFAULT NULL,
  `uid` varchar(20) NOT NULL DEFAULT '',
  `newsletter` int(1) NOT NULL DEFAULT '1',
  `kundenausweis_erhalten` datetime DEFAULT NULL,
  `gesperrt` int(1) NOT NULL DEFAULT '0',
  `hold_kunde` int(1) NOT NULL DEFAULT '0',
  `kunden_bemerkung` text NOT NULL,
  `many_cards_allowed` int(1) NOT NULL DEFAULT '0',
  `geb_datum` datetime DEFAULT NULL,
  `wishlist_public` int(1) NOT NULL DEFAULT '0',
  `not_used` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `username` (`username`,`mandant`),
  KEY `mandant` (`mandant`),
  KEY `username_2` (`username`),
  KEY `passwort` (`passwort`),
  KEY `email` (`email`),
  KEY `timestamp` (`timestamp`),
  KEY `land` (`land`),
  KEY `kunden_id` (`kunden_id`,`username`,`passwort`),
  KEY `burner_username` (`burner_username`),
  KEY `hold_kunde` (`hold_kunde`),
  KEY `apid` (`apid`),
  KEY `newsletter` (`newsletter`),
  KEY `newsletter_2` (`newsletter`,`mandant`),
  FULLTEXT KEY `vorname` (`vorname`,`nachname`,`ort`,`username`,`email`,`firma`)
) ENGINE=MyISAM AUTO_INCREMENT=397997 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
[7 Oct 2012 13:15] Roman Zabransky
forgott to copy "mandant" in the create table:
`mandant` varchar(15) NOT NULL DEFAULT '',
[7 Oct 2012 14:13] Roman Zabransky
maybe i should add the information, that we testet the windows recovery of a created windows backup image before we went in production...
maybe thw windows 2008 windows backup modified the customers index file?!
[7 Oct 2012 23:00] Roman Zabransky
everytime after i start
myisamchk -v -r customers.myi
it crashes the index file... same error as in my first post...
it only works again, if i manually drop and recreate the primary key on customer_id...
[7 Oct 2012 23:29] Roman Zabransky
as i testet - i would say mysql_upgrade and myisamchk kills my primary key...
every time reproducible...
[8 Oct 2012 0:17] Roman Zabransky
mysql_upgrade is not crashing the index file - has to be, optimize or repairtable - but 100% myisamchk

after this the index file was not usable anymore:

- recovering (with sort) MyISAM-table 'customers.MYI'
Data records: 398232
- Fixing index 1
  - Searching for keys, allocating buffer for 95253 keys
  - Last merge and dumping keys
- Fixing index 2
  - Searching for keys, allocating buffer for 15760 keys
  - Last merge and dumping keys
- Fixing index 3
  - Searching for keys, allocating buffer for 67642 keys
  - Last merge and dumping keys
- Fixing index 4
  - Searching for keys, allocating buffer for 18070 keys
  - Last merge and dumping keys
- Fixing index 5
  - Searching for keys, allocating buffer for 31767 keys
  - Last merge and dumping keys
- Fixing index 6
  - Searching for keys, allocating buffer for 18070 keys
  - Last merge and dumping keys
- Fixing index 7
  - Searching for keys, allocating buffer for 91171 keys
  - Last merge and dumping keys
- Fixing index 8
  - Searching for keys, allocating buffer for 37441 keys
  - Last merge and dumping keys
- Fixing index 9
  - Searching for keys, allocating buffer for 11907 keys
  - Merging 398232 keys
  - Last merge and dumping keys
- Fixing index 10
  - Searching for keys, allocating buffer for 17916 keys
  - Last merge and dumping keys
- Fixing index 11
  - Searching for keys, allocating buffer for 116499 keys
  - Last merge and dumping keys
- Fixing index 12
  - Searching for keys, allocating buffer for 7674 keys
  - Merging 398232 keys
  - Last merge and dumping keys
- Fixing index 13
  - Searching for keys, allocating buffer for 116499 keys
  - Last merge and dumping keys
- Fixing index 14
  - Searching for keys, allocating buffer for 59910 keys
  - Last merge and dumping keys
- Fixing index 15
  - Searching for keys, allocating buffer for 39980 keys
  - Merging 2104421 keys
  - Last merge and dumping keys
  - Adding exceptions

---------

after that the table is only usable with drop and create primary key...
[8 Oct 2012 10:53] Roman Zabransky
it seems it´s fixed in 5.5.28 BUG 13555854
can you confirm?
[8 Oct 2012 23:41] Roman Zabransky
i can repeat
i myisamchk kills the table... can i send someone the corrupted MYI, created by myisamchk ?
[8 Oct 2012 23:52] Roman Zabransky
now i got also repair table to kill the indexfile...
where can i find BUG 13555854?
is it mentioned from 5.5.27 to 5.5.28...
but i am still able to kill my index file with repair table or myisamchk
[9 Oct 2012 0:09] Roman Zabransky
dropped now every index...
repair table and myisamcheck - everything is ok...

this table always crash

CREATE TABLE IF NOT EXISTS `customers` (
  `kunden_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vorname` varchar(100) NOT NULL DEFAULT '',
  `nachname` varchar(100) NOT NULL DEFAULT '',
  `firma` varchar(100) NOT NULL DEFAULT '',
  `strasse` varchar(100) NOT NULL DEFAULT '',
  `plz` varchar(10) NOT NULL DEFAULT '',
  `ort` varchar(100) NOT NULL DEFAULT '',
  `land` varchar(40) NOT NULL DEFAULT '',
  `tel` varchar(70) NOT NULL DEFAULT '',
  `fax` varchar(70) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `burner_username` varchar(100) DEFAULT NULL,
  `username` varchar(100) NOT NULL DEFAULT '',
  `passwort` varchar(50) NOT NULL DEFAULT '',
  `timestamp` datetime DEFAULT NULL,
  `uid` varchar(20) NOT NULL DEFAULT '',
  `newsletter` int(1) NOT NULL DEFAULT '1',
  `kundenausweis_erhalten` datetime DEFAULT NULL,
  `gesperrt` int(1) NOT NULL DEFAULT '0',
  `hold_kunde` int(1) NOT NULL DEFAULT '0',
  `KK_proofed_kunde` int(1) NOT NULL DEFAULT '0',
  `darf_nachnahme` int(1) NOT NULL DEFAULT '0',
  `mandant` varchar(15) NOT NULL DEFAULT '',
  `kunden_bemerkung` text NOT NULL,
  `many_cards_allowed` int(1) NOT NULL DEFAULT '0',
  `immer_versandkostenfrei` int(1) NOT NULL DEFAULT '0',
  `geb_datum` datetime DEFAULT NULL,
  `apid` varchar(255) DEFAULT NULL,
  `wishlist_public` int(1) NOT NULL DEFAULT '0',
  `not_used` int(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `username` (`username`,`mandant`),
  KEY `mandant` (`mandant`),
  KEY `username_2` (`username`),
  KEY `passwort` (`passwort`),
  KEY `email` (`email`),
  KEY `timestamp` (`timestamp`),
  KEY `land` (`land`),
  KEY `kunden_id` (`kunden_id`,`username`,`passwort`),
  KEY `burner_username` (`burner_username`),
  KEY `hold_kunde` (`hold_kunde`),
  KEY `apid` (`apid`),
  KEY `newsletter` (`newsletter`),
  KEY `newsletter_2` (`newsletter`,`mandant`),
  FULLTEXT KEY `vorname` (`vorname`,`nachname`,`ort`,`username`,`email`,`firma`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;

INSERT INTO `customers` (`kunden_id`, `vorname`, `nachname`, `firma`, `strasse`, `plz`, `ort`, `land`, `tel`, `fax`, `email`, `burner_username`, `username`, `passwort`, `timestamp`, `uid`, `newsletter`, `kundenausweis_erhalten`, `gesperrt`, `hold_kunde`, `KK_proofed_kunde`, `darf_nachnahme`, `mandant`, `kunden_bemerkung`, `many_cards_allowed`, `immer_versandkostenfrei`, `geb_datum`, `apid`, `wishlist_public`, `not_used`) VALUES
(9588, 'firstname', 'sirname', 'Firma', 'chernostreet 3', '58878', 'stae', 'slovakia', '123456789', '', '2012-10-09 02:01:03', 'dodl47', 'zabby', 'a8b1377d0edaece4c15136dfed447', '2007-02-08 14:17:10', '', 1, NULL, 0, 0, 0, 0, 'highest', '', 0, 0, NULL, 'c009-4096-b15e-b955457383f2', 1, 0);
[9 Oct 2012 0:11] Roman Zabransky
--> crashes at 
update customer set email='randomstring' where customer_id = 9588

after doing some myisamchecks and repair tables...
[9 Oct 2012 10:06] Roman Zabransky
myisamchck output @ crashing the index file...

- recovering (with sort) MyISAM-table 'customers.MYI'
Data records: 1
- Fixing index 1
  - Searching for keys, allocating buffer for 13 keys
Wrong bytesec:  37-  0-  0 at          8; Skipped
Wrong bytesec: 115-107-121 at         28; Skipped
Wrong bytesec:  32- 51- 55 at         48; Skipped
Wrong bytesec:  97-115-100 at         60; Skipped
Wrong bytesec:  54- 48- 55 at         80; Skipped
Wrong bytesec:  57- 32- 49 at        100; Skipped
Wrong bytesec:  98- 98-121 at        120; Skipped
Wrong bytesec:  99- 49- 53 at        140; Skipped
Wrong bytesec:  64- 18-  0 at        160; Skipped
Wrong bytesec:  65- 78- 68 at        180; Skipped
Wrong bytesec:  57- 45- 52 at        200; Skipped
Wrong bytesec:  56- 51-102 at        220; Skipped
Wrong bytesec:   1-  0-  0 at        224; Skipped
  - Dumping 1 keys
- Fixing index 2
  - Searching for keys, allocating buffer for 59 keys
  - Dumping 5 keys
[9 Oct 2012 14:08] Roman Zabransky
to reproduce

1) repair table
2) myisamchk -rev customers.myi
3) update as first comment
[11 Oct 2012 19:57] Sveta Smirnova
Thank you for the report.

> i recently importet a dump from 5.1.52 to 5.5.27 everything was fine running - import dump, mysql_upgrade and so on...

Could you please provide us dump of this problematic table?
[11 Oct 2012 20:17] Roman Zabransky
as i postet in the comment from [9 Oct 0:09] Roman Zabransky

create table and one insert... still happening on my test vm running on 5.5.28
[11 Oct 2012 21:52] Sveta Smirnova
Thank you for the feedback.

I could not repeat described behavior with this dump.

Please repeat instructions of how to repeat one more time, but with details. I understood you did:

1. Loaded dump to version 5.5.28: CREATE TABLE, then INSERT
2. Run myisamchk. But with which options?
3. Run update, get error.

Did I miss anything? Please also send your configuration file.
[12 Oct 2012 5:34] Roman Zabransky
as i wrote in comment above...

1) create table
2) insert data
3) repair table
4) myisamchk -rev customers.myi
5) update as first comment (example update customers set email=CURRENT_TIMESTAMP where kunden_id = 9588

i will attach my my.ini
[12 Oct 2012 5:36] Roman Zabransky
my.ini

Attachment: my.ini (application/octet-stream, text), 9.46 KiB.

[12 Oct 2012 11:50] Sveta Smirnova
Thank you for the feedback.

I see you use custom stopword file. Please upload this file too: probably it matters.
[12 Oct 2012 11:53] Roman Zabransky
stopfile

Attachment: stop_woerter.c (application/octet-stream, text), 775 bytes.

[12 Oct 2012 11:53] Roman Zabransky
attached
[12 Oct 2012 11:59] Roman Zabransky
removing the stop word file does not change or fix the error...
[12 Oct 2012 12:23] Roman Zabransky
does not occur in mysql 5.6.7...
[12 Oct 2012 12:41] Sveta Smirnova
Thank you for the feedback.

Verified as described. Actually option --ft_min_word_len=1 matters.
[12 Oct 2012 12:42] Sveta Smirnova
test case for MTR, don't forget to create option file with --ft_min_word_len=1

Attachment: bug67123.test (application/octet-stream, text), 2.85 KiB.

[12 Oct 2012 12:56] Roman Zabransky
okay - something do to for me now or just waiting for an update? ;)
i need the option --ft_min_word_len=1
[12 Oct 2012 16:10] Sveta Smirnova
No, no new additional action from you needed: just wait the fix.

Thank you for providing all information, necessary to create repeatable test case!