MySQL Bugs: #67123: error in mi

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

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

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 */;

forgott to copy "mandant" in the create table:
`mandant` varchar(15) NOT NULL DEFAULT '',

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?!

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...

as i testet - i would say mysql_upgrade and myisamchk kills my primary key...
every time reproducible...

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...

it seems it´s fixed in 5.5.28 BUG 13555854
can you confirm?

i can repeat
i myisamchk kills the table... can i send someone the corrupted MYI, created by myisamchk ?

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

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);

--> crashes at 
update customer set email='randomstring' where customer_id = 9588

after doing some myisamchecks and repair tables...

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

to reproduce

1) repair table
2) myisamchk -rev customers.myi
3) update as first comment

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?

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

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.

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

my.ini

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

Thank you for the feedback.

I see you use custom stopword file. Please upload this file too: probably it matters.

stopfile

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

attached

removing the stop word file does not change or fix the error...

does not occur in mysql 5.6.7...

Thank you for the feedback.

Verified as described. Actually option --ft_min_word_len=1 matters.

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.

okay - something do to for me now or just waiting for an update? ;)
i need the option --ft_min_word_len=1

No, no new additional action from you needed: just wait the fix.

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