Bug #5686 #1034 - Incorrect key file for table - only utf8
Submitted: 21 Sep 2004 19:37 Modified: 30 Nov 2005 17:43
Reporter: Vaclav Vobornik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.1.8 OS:Linux (linux 2.4.26)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: corruption, myisam

[21 Sep 2004 19:37] Vaclav Vobornik
Description:
Today's BK version - Mysql-4.1.5-gamma, Linux 2.4.26

MyISAM table with utf8 fields, many indexes and fulltext:

CREATE TABLE items_utf8 (
  iid bigint(20) NOT NULL auto_increment,
  channel_id int(10) unsigned NOT NULL default '0',
  title varchar(255) collate utf8_unicode_ci NOT NULL default '',
  link varchar(255) collate utf8_unicode_ci NOT NULL default '',
  description text collate utf8_unicode_ci NOT NULL,
  pubdate datetime NOT NULL default '0000-00-00 00:00:00',
  active datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (iid),
  UNIQUE KEY link (link),
  KEY pubdate (pubdate),
  KEY channel_id (channel_id),
  KEY active (active),
  FULLTEXT KEY `full` (title,description)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='jednotlive
zpravy';

No of rows:450 805
Data (.MYD):130 MB
Index (.MYI): 124MB

And when I use this command:

UPDATE LOW_PRIORITY items_utf8 SET channel_id = '542',
title = 'Nove diskusni forum o mobiloch Motorola',
description = 'Administrator napisal novy clanok',
active = now( ) WHERE link = _binary'http://www.madaj.net/t720/' LIMIT 1 

MySQL generate error:  

#1034 - Incorrect key file for table 'items_utf8'; try to repair it 

mysql> repair table items_utf8 use_frm;
+------------+--------+----------+-------------------------------------------------+
| Table      | Op     | Msg_type | Msg_text                                       |
+------------+--------+----------+-------------------------------------------------+
| items_utf8 | repair | info     | Delete link points outside datafile at 12267504 |
| items_utf8 | repair | warning  | Number of rows changed from 0 to 450803        |
| items_utf8 | repair | status   | OK                                             |
+------------+--------+----------+-------------------------------------------------+

New "update" command corrupts this table again :-(

The same table in charset cp1250 works fine - does not produce any errors

How to repeat:
I have sent table files (frm, MYD, MYI) to
ftp://support.mysql.com/pub/mysql/Incoming/corrupted-myisam-20040920.tar.bz2 
Use "update items_utf8" command above for reproducing the error.
[13 Oct 2004 18:22] Matthew Lord
Hi,

Thanks for your bug report!

I'm having trouble repeating this using dummy data that I created.  The link that you provided 
for your table files is missing.  Do you think you could put those in place again?  

Can you think of anything I should know about your environment?

Best Regards
[19 Oct 2004 11:30] Hydar Dewachi
I'm having the same problem with utf8 data.

1. any change to the structure of a table,
2. a change in the "ft_min_word_len" followed by rebuilding the indexes

would corrupt the tables;  "Incorrect key file for table: 'ITEM'; try to repair it"

Tried repairing the tables in all sort of ways (REPAIR TABLE QUICK, myisamchk … –o –f [mySQL stopped]) but to no avail. I now get the error: "Duplicate entry 'en' for key 7" and other Duplicate entry error messages.

It seems that rebuilding the indexes of a UTF-8 table does triggers this corruption

Platform is:
Windows 2000 Server/ XP
Build 4.1.1a-alpha (even tried it with 4.1.5 and is still the same problem)

Please feel free to contact me for any further details about this error:
hydar.dewachi@knowledgeview.co.uk
[15 Nov 2004 12:45] Vaclav Vobornik
Mysql-4.1.8 the same problem:
-----------------------------------------------------------------------
# echo "repair table blogator.items_utf8 use_frm;"|mysql -p
Enter password: 
Table   Op      Msg_type        Msg_text
blogator.items_utf8     repair  warning Number of rows changed from 0 to 265921
blogator.items_utf8     repair  status  OK

# mysql -p blogator<crash.sql
Enter password: 
id      rss     categ_id
197     http://had.independent.sk/rss.php       9
960     http://politik.bloguje.cz/rss.xml       9
1012    http://www.blondyna.com/rss.xml 10
199     http://sweb.cz/morrisland/blog/rss.xml  9
229     http://www.webreference.com/webreference.rdf    12
627     http://minimag.cz/dupe/rss.xml  5
1171    http://www.ziak.sk/rss20.php    2
186     http://johno.host.sk/rssfeed.php        9
228     http://hotwired.lycos.com/webmonkey/meta/headlines.rdf  12
645     http://www.randolphlee.com/painter.rss  2
646     http://www.randolphlee.com/painter.xml  2
1239    http://freya.webz.cz/rss.xml    10
525     http://xml.newsisfree.com/feeds/91/1391.xml     11
528     http://xml.newsisfree.com/feeds/72/1672.xml     12
878     http://connect.cpress.cz/system/RSS.xml 2
ERROR 1034 (HY000) at line 15: Incorrect key file for table 'items_utf8'; try to repair it

# echo "check table blogator.items_utf8 ;" |mysql -p
Enter password: 
Table   Op      Msg_type        Msg_text
blogator.items_utf8     check   warning Table is marked as crashed
blogator.items_utf8     check   error   Found key at page 27988992 that points to record outside datafile
blogator.items_utf8     check   error   Corrupt

-----------------------------------------------------------------------
You can download *.MYD, *.MYI, *.frm,  crash.sql and configure options from http://www.blogator.com/bug/crashed_utf8_table.tgz
[15 Nov 2004 16:07] Sinisa Milivojevic
Hi again.

Thank you very much for your efforts.

A bug very similar to the one that you report has just been pushed into 4.8.

If you know how to get latest sources from our BK repository, would you be so 
kind and try to reproduce it with latest changest.

It would save us some time.

Otherwise, we shall have to test it , which would take some time ...
[17 Nov 2004 11:27] Vaclav Vobornik
I have downloaded yesterday's bk mysql-4.1.8. It is more better - table become crashed only sometimes. So it was more dificult to find sql commands for repeating this bug.  You can find it at http://www.blogator.com/bug/crashed_utf8_table.tar.bz2 (66 MB)

--------------------------------------------------------------------------
# echo "check table items_utf8;"|mysql -p blogator
Enter password: 
Table   Op   Msg_type Msg_text
blogator.items_utf8   check     status  OK

#  mysql -p blogator <sql.out >/dev/null
Enter password: 
ERROR 1034 (HY000) at line 38515: Incorrect key file for table 'items_utf8'; try to repair it
--------------------------------------------------------------------------
[17 Nov 2004 20:52] Victoria Reznichenko
Verified with 4.1.8-debug-log

crash.sql file from crashed_utf8_table.tar.bz2  contains SELECT queries with tables that are not in the archive.
So I've changed crash.sql file a little and upload it as crash.zip.
[17 Nov 2004 20:59] Victoria Reznichenko
crash.zip was uploaded to ftp.mysql.com/pub/mysql/upload
[13 Jan 2005 16:54] Christoph Gockel
Got this error too.
I'm working with v4.1.8, updated from 3.23.58.

Got over it with the following procedure.
- dumped the whole db
- DROPed it
- re-imported it

Maybe there was some incorrectness from the version update?
Because now the verison 4.18 created all datafiles and indexes (with the import).

Nevertheless, its working.

regards
christoph.
[1 May 2005 16:28] Ron Kass
it seems MySQL full-text indexes do not like UTF non-english data.
I get the same error, simply when inserting data into a table. Before the insert the table is 100% fine. At the insert, it apparently gets corrupted.
Any idea if this bug is handled? If I can be of assistance, please do not hesitate to ask me for info/data.
[4 May 2005 16:18] Nicholas Thompson
I am getting this error now too - happened today (and to my predecessor of my job back in January).

Everything was fine, then we notice we cannot update the database anymore. Upon closer inspection we notice its Error #1034.

System details:
Linux cobalt126.fm.netbenefit.co.uk 2.4.19C13_V #1 Fri Feb 20 01:55:03 PST 2004 i686
PHP 4.39
Apache/1.3.29 Sun Cobalt (Unix) mod_ssl/2.8.16 OpenSSL/0.9.6m PHP/4.3.9 FrontPage/5.0.2.2510 mod_perl/1.26
MySQL:  4.0.18-standard

There is a chance that the table contains non-english characters, we know there ARE tables that do - but not sure about this one. It probably doesn't, but it cant be garunteed as recently a client has started making entried and they MIGHT have had non-english characters in them.

What else can cause this as its happened before when there were certainly no non-english characters.
[5 May 2005 14:47] Sergey Vojtovich
Hi there,

I wonder if anybody could provide some more information (I can't reproduce this bug with mysql-4.1.12):
- What MySQL version is it? Actually it was specified, but it was half year ago.
- Some data I could insert into table to reproduce it. It was supplied too, but again - no one link works.
[7 May 2005 10:46] Nicholas Thompson
Unfortauntely, I have no idea what data would cause this and I have also not been given permission to release the data for testing purposes as its private content on the site (ie for members only).

However, this is the structure of the table that crashed...
CREATE TABLE `PageVersion` (
  `Id` int(12) unsigned NOT NULL auto_increment,
  `Name` text NOT NULL,
  `Content` text NOT NULL,
  `Page` int(12) unsigned NOT NULL default '0',
  PRIMARY KEY  (`Id`),
  FULLTEXT KEY `Content` (`Content`)
) TYPE=MyISAM;

Hope this helps.. I will walk with people at work to see if I can get some information to post - but there is nothing non-standard in there. Just normal ASCII text.

Our webhosts fixed the problem by running a repair (so they said). They claimed the problem was due to the server running out of RAM when running a query and it ended up corrupting the database. I'm not sure this is the real reason as (a) the server have 512Mb RAM + loads of swap file space, (b) Why has it only happened twice at relatively quiet times of the day?
[13 May 2005 10:37] Victoria Reznichenko
Vaclav,

I so sorry, but we somehow lost uploaded data :(
Is it possible to upload data again?
[10 Jun 2005 11:09] Dietmar Zoerner
This bug is reproduceable with 4.1.12 standard Linux using latin1 charset.
I use none-ascii-chars and fulltext-keys.

What seems to be helpful for my db is: changing ft_min_word_len=3 -> 4
(4 is default)
hint:
http://bugs.mysql.com/bug.php?id=5828

It is possible that the bug disappears only because some critical chars are inside of words len < 4.

I hope this helps
[13 Jun 2005 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".
[14 Jun 2005 18:55] Dietmar Zoerner
After a few days of testing I can now say that the parameter ft_min_word_len with value 3 results to this bug.
[14 Jun 2005 21:30] Sergei Golubchik
Dietmar, we need your table that exhibits this bug, to be able to repeat it.
(ideally, of course, we'd like a small test case - e.g. your table without columns and rows that are not absolutely necessary to repeat a bug).
If it's small you can attach it to the bugreport. Otherwise you can upload it to
ftp.mysql.com/pub/mysql/upload/
(please, be sure that bug number is apparent from the filename - e.g. bug-5686.zip)
[14 Jul 2005 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".
[20 Jul 2005 20:06] Vaclav Vobornik
You can downloaded it again from http://www.blogator.com/bug/crashed_utf8_table.tar.bz2

I can confirm, that increasing of ft_min_word_len (2->3) temporary resolves this bug.
[20 Jul 2005 20:12] Vaclav Vobornik
Sorry, 
change wasn't 2->3 but 2->4.
[20 Jul 2005 20:53] Dietmar Zoerner
Yes, I change from 3->4, wich is the default value of this parameter.
After my change, our system now runs absolute stable - not one db-crash since many weeks. With ft_min_word_len=3, this problems comes up after a few update operations one tables with free text index.
Off cause, it should bei possible to have free text searches one items with less than 4 characters!!

It is not very helpful to have a damaged database in result. I would expect to get an errror msg returned if an update is not possible.
[26 Jul 2005 17:19] Sergey Vojtovich
Hi, thanks for reporting this bug.

Have you properly launched repair table right after changing ft_min_word_len? An excerpt from manual:

The problem occurs because these parameters are known only by the
server.  They are not stored in `MyISAM' index files.  To avoid the
problem if you have modified the minimum or maximum word length or the
stopword file in the server, specify the same `ft_min_word_len',
`ft_max_word_len', and `ft_stopword_file' values to `myisamchk' that
you use for `mysqld'. For example, if you have set the minimum word
length to 3, you can repair a table with `myisamchk' like this:

     shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI
[26 Jul 2005 18:07] Vaclav Vobornik
Hi Sergey,
did you read the whole bug thread carefully?

Changing ft_min_word_len resolves this bug. Not makes it.
[26 Jul 2005 18:36] Sergey Vojtovich
Hi Vaclav,

yes, I did. And it was best explanation I found.

My thoughts were:
1. Full-text index was created while ft_min_word_len!=2 (looks like it was default - 4)
2. ft_min_word_len was changed (=2).
3. myisamchk wasn't properly configured and repaired table using default value of ft_min_word_len.
4. Next intrusive query caused error#1034.
5. ft_min_word_len was changed back and everything worked.

I will doublecheck this issue with your data if I'm not right.

Regards,
Sergey
[26 Jul 2005 19:02] Dietmar Zoerner
>Changing ft_min_word_len resolves this bug. Not makes it.
>>>>>>>>
Sorry, but I can't agree with this.
It is true that changing ft_min_word_len resolves this problem.
But: The problem appears even if myisamchk is nether used.
update-calls comes back with errors and damaged table results after ft_min_word_len is changed.
After this, even select-calls comes back with errors - the table is damaged an have to be created new. No recovery worked.

Is it neckessary to change ft_min_word_len before any ft-index is created?

Any way - the server should create new indexs if neccary after changing ft_min_word_len - but there should never appear a damaged table throu this way.
[26 Jul 2005 19:44] Vaclav Vobornik
Hi Sergey,
It would be probably source of problem, if it could be done as you have written.
But, no changes of any variables was done after creating table. 
Every recover command was calling directly from the mysql environment, not shell:
mysql> repair table xxx use_frm;
I was also trying dump of the whole db, DROP it and re-import it back. No success - after several INSERTs or UPDATEs the error #1034 was appeared again.

I've switched all tables back into win1250 :-(, so I am not ready to reproduce this error once more, just now. 

But if you want, I am ready to:
- update mysql to recent 4.1 version (my server runs on 4.1.8)
- switch ft_min_word_len=2
- reload mysql server
- dump actual win1250 tables
- create new utf8 tables 
- re-import data to these ones 
- start gathering new data and store it to these new utf8 tables
It would be done during this or next week.
[30 Aug 2005 9:33] Luca Lani
Hi, we have serius bug using utf-8 charset and Myisam. 
We have corrupted table every day.
I think it's a repeatible bug.

Procedure : 

OS LINUX RH ent with 2.4.21-27.EL kernel.
Mysql   4.1.14-max-log  (we tried binary and also compiled by source)
phpmyadmin  2.6.1-pl3

We have a table : 

CREATE TABLE `nucleus_item3` (
  `inumber` int(11) NOT NULL auto_increment,
  `ititle` varchar(160) default NULL,
  `iurltitle` varchar(160) default NULL,
  `ibody` text NOT NULL,
  `imore` text,
  `iblog` int(11) NOT NULL default '0',
  `iauthor` int(11) NOT NULL default '0',
  `itime` datetime NOT NULL default '0000-00-00 00:00:00',
  `iclosed` tinyint(2) NOT NULL default '0',
  `idraft` tinyint(2) NOT NULL default '0',
  `ikarmapos` int(11) NOT NULL default '0',
  `icat` int(11) default NULL,
  `ikarmaneg` int(11) NOT NULL default '0',
  `editor` int(2) default '0',
  PRIMARY KEY  (`inumber`),
  UNIQUE KEY `inumber` (`inumber`),
  KEY `itime` (`itime`),
  KEY `iurltitle` (`iurltitle`),
  FULLTEXT KEY `ibody` (`ibody`,`ititle`,`imore`),
  FULLTEXT KEY `iurltitle_2` (`iurltitle`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Items' AUTO_INCREMENT=2341 ;

(i post datas with csv)

All work fine. 
We delete with phpmyadmin the first 10 record.

we have the error : 

#1034 - Incorrect key file for table 'nucleus_item3'; try to repair it

So, via shell we look with myisamchck : 

[root@plain lanas]# ../../bin/myisamchk nucleus_item3.MYI
Checking MyISAM file: nucleus_item3.MYI
Data records:     980   Deleted blocks:       6
myisamchk: warning: Table is marked as crashed
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 979 keys of 980
- check record links
myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table 'nucleus_item3.MYI' is corrupted
Fix it using switch "-r" or "-o"

Ok, we fix it :

[root@plain lanas]# ../../bin/myisamchk -o nucleus_item3.MYI
- recovering (with keycache) MyISAM-table 'nucleus_item3.MYI'
Data records: 980

We check if is all OK

[root@plain lanas]# ../../bin/myisamchk nucleus_item3.MYI
Checking MyISAM file: nucleus_item3.MYI
Data records:     980   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check data record references index: 4
- check data record references index: 5
- check data record references index: 6
- check record links

We delete again the firte 3 record : and we start again with errors.

===> We ChANGE COLLATION TO   latin1_general_ci
all  problem are solved, and table nver corrupted.

So there is clearly a problem with some character and collation utf-8

Regards
luca lani
[30 Aug 2005 9:45] Luca Lani
Ops,  the url to take the csv file with DATA :

http://lanas.giovani.it/nucleus_item3.csv.gz

Regards,
luca lani  |  luca AT studenti.it
[9 Sep 2005 4:10] noah williamsson
I've been seeing this on 4.1.12, 4.1.13 and 4.1.14 recently, on a machine 3ghz/2GB DL380 running  2.6.11-grsec with utf8 tables with FT indices.
Would anyone mind writing up a short summary of when it happens, how you fix it and how you avoid keep getting this error?
Thanks in advance!

(Oh, and it's amazing how an S1 bug can sit around for almost a year without being closed..)
[27 Sep 2005 7:20] noah williamsson
Any update on this bug yet?

After adding "ft_min_word = 4" under [mysqld] i now get errors like these instead of "incorrect key file": "Record at: 198459472  Can't find key for index:  3".

Seems like something's still broken in 4.1.14.
[18 Oct 2005 14:54] Andrea Gangini
Test case reported by Luca Lani is fully reproducible on 4.1.14 and a CentOS 4.1 Linux Platform. 
*every*night* I must repair several tables, which get corrupted.
I also use utf8 tables and multi-column FT indexes.
Please try to assign max priority to this bug, since it has been reported from more than a year and it's reproducible on the latest Mysql version.
[18 Oct 2005 15:03] noah williamsson
I'm glad to see someone else is having this problem aswell! I too use a multicolumn index, over three columns. 
I've filed bug #13712 (no comments displayed to the public though).

Are you having the same problem as I do?
Since i haven't been able to been able to isolate what causes the corruption yet i would be more than happy if you could add comments on what kills your table either to this bug or to mine.

Thanks!
[19 Oct 2005 9:02] Andrea Gangini
I have a somewhat simplier test case.

Download this export http://62.149.226.152/bugreport.sql.gz and import in any database you like.

Just by trying to delete a record, mysql argues about index corruption:
mysql> delete from TESTCASE where inumber=375;
ERROR 1034 (HY000): Incorrect key file for table 'TESTCASE'; try to repair it

Let's repair the table then:
mysql> repair table TESTCASE;
+----------------+--------+----------+----------+
| Table          | Op     | Msg_type | Msg_text |
+----------------+--------+----------+----------+
| PROVA.TESTCASE | repair | status   | OK       |
+----------------+--------+----------+----------+
1 row in set (1.67 sec)

Ok, now we change the default collation of the column involved in the FT index:
ALTER TABLE `TESTCASE` CHANGE `ititle` `ititle` VARCHAR( 160 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL , CHANGE `ibody` `ibody` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , CHANGE `imore` `imore` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ;
Query OK, 900 rows affected (2.88 sec)
Records: 900  Duplicates: 0  Warnings: 0

Trying to delete one record again:
mysql> delete from TESTCASE where inumber=375;
Query OK, 1 row affected (0.01 sec)

Gotcha! Like other people said, it must be something involved in the default 'utf8_general_ci' collation.

I suggest as a temporary workaround to change the collation of the column which are used in a FT multi-column index.
I did so and the nightly integrity check did'nt found any corruption.
I don't know if there are any drawbacks changing the default collation (if any); I found none so far.
[19 Oct 2005 9:22] noah williamsson
I imported your dump and then CHECK/REPAIR a couple of times.

mysql> check table TESTCASE extended;
+--------------+-------+----------+---------------------------------------+
| Table        | Op    | Msg_type | Msg_text                              |
+--------------+-------+----------+---------------------------------------+
| tng.TESTCASE | check | error    | Key in wrong position at page 1519616 |
| tng.TESTCASE | check | error    | Corrupt                               |
+--------------+-------+----------+---------------------------------------+
2 rows in set (0.09 sec)

mysql> repair table TESTCASE;
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| tng.TESTCASE | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (2.30 sec)

mysql> check table TESTCASE extended;
+--------------+-------+----------+---------------------------------------+
| Table        | Op    | Msg_type | Msg_text                              |
+--------------+-------+----------+---------------------------------------+
| tng.TESTCASE | check | error    | Key in wrong position at page 1517568 |
| tng.TESTCASE | check | error    | Corrupt                               |
+--------------+-------+----------+---------------------------------------+
2 rows in set (0.08 sec)

mysql> repair table TESTCASE;
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| tng.TESTCASE | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (2.34 sec)

mysql> check table TESTCASE extended;
+--------------+-------+----------+---------------------------------------+
| Table        | Op    | Msg_type | Msg_text                              |
+--------------+-------+----------+---------------------------------------+
| tng.TESTCASE | check | error    | Key in wrong position at page 1517568 |
| tng.TESTCASE | check | error    | Corrupt                               |
+--------------+-------+----------+---------------------------------------+
2 rows in set (0.15 sec)

After a repair SHOW CREATE TABLE TESTCASE shows no default collation.

When i run your ALTER TABLE query and change the default collation to either utf8_unicode_ci or utf8_swedish_ci (which is what we use here) CHECK TABLE stops complaining and your DELETE won't crash the table anymore.
[19 Oct 2005 9:23] noah williamsson
Forgot to mention that i'm running 4.1.15 from BK repo Sept 30.
[24 Oct 2005 14:58] Andrea Gangini
Just tested on mysql 5.0.15. 

mysql> delete from TESTCASE where inumber=375;
ERROR 126 (HY000): Incorrect key file for table './test/TESTCASE.MYI'; try to repair it

God I hate this error :-)

Also: the workarounds suggested worked only in this testcase. My database still showed after few days corruption: so changing the collation or increasing ft_min_word_length is not a solution (it merely decreases the chanche of a corruption, not eliminates it).

Now all of our tables are latin1, which is a very limited charset, but we have no more corruption problems.
[12 Nov 2005 7:27] 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/internals/32203
[14 Nov 2005 13:44] Nicholas Thompson
Does this bug effect 3.23? We recently migrated servers and the server we run now does not run 4.x, but 3.23 (something to do with 4.x not being officially supported by RH ES3? Not sure though). Anywho, we have no experienced any further problems under 3.23... Although what we seemed to have gained in stability, we have definately lost in features.
[14 Nov 2005 13:56] noah williamsson
Just tried the patch on MySQL 4.1.15 and it still crashes MySQL on an UPDATE.
Our FT index spans over three columns and the columns contains are TEXT in utf8_swedish_ci.

Incorrect key file for table 'art_main'; try to repair it
Form data was:
array: (pair: (-keyfield)=(id)), (pair: (-keyvalue)=(103670)), (-update), (pair: (source_id)=(TT1155)), (pair: (pub_status)=(1)), (pair: (rubrik)=(Svagt uppåt på börsen)), (pair: (ingress)=(Stockholmsbörsen steg något på tisdagen. Banker var bästa bransch. Vid stängning hade Sax-index stigit med 0,2 procent till 276,3. Det smalare OMXS30 stängde på oförändrade 882,8. Aktier för 18,5 miljarder kronor bytte ägare.)), (pair: (brodtext)=(Stockholmsbörsen steg något på tisdagen. Banker var bästa bransch.

Vid stängning hade Sax-index stigit med 0,2 procent till 276,3. Det smalare OMXS30 stängde på oförändrade 882,8. Aktier för 18,5 miljarder kronor bytte ägare.)), (pair: (dt_published)=(2005-09-27 10:35:43)), (pair: (dt_updated)=(2005-09-27 17:34:42))
[14 Nov 2005 14:37] Sergey Vojtovich
Nicholas, this bug doesn't affect 3.23.

noah, have you repaired table after applying this patch?
[14 Nov 2005 14:59] noah williamsson
Awesome!
The UPDATE worked just fine after a REPAIR TABLE art_main QUICK.
[24 Nov 2005 3:04] alex
hello
i checked the patch, and find 2 similar codes in ft_parser.c, as below

----1------------
    mwc= length= 0;
    for (word->pos=doc; doc<end; length++, mbl=my_mbcharlen(cs, *(uchar *)doc), doc+=(mbl ? mbl : 1))
      if (true_word_char(cs,*doc))
        mwc= 0; 
      else if (!misc_word_char(*doc) || mwc) 
        break;  
      else    
        mwc++;  

    word->len= (uint)(doc-word->pos) - mwc;

----2------------
    mwc=length=0;
    for (word->pos=doc; doc<end; length++, mbl=my_mbcharlen(cs, *(uchar *)doc), doc+=(mbl ? mbl : 1))
      if (true_word_char(cs,*doc))
        mwc=0;
      else if (!misc_word_char(*doc) || mwc++)
        break;

    param->prev='A'; /* be sure *prev is true_word_char */
    word->len= (uint)(doc-word->pos) - mwc;
------------------------

the patch only affects code 1.
shall i make changes on code 2 as code 1?

thanks
[26 Nov 2005 18:37] 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/internals/32740
[26 Nov 2005 23:55] Vaclav Vobornik
Fine. It seems that 4.1 tree will be ok now. And what about 5.0? Do you know if there is also a similar bug there? I'd like to upgrade to 5.0 soon.
[27 Nov 2005 0:34] noah williamsson
Fwiw, i still experienced FT index corruptions even after applying this patch (and i did a REPAIR TABLE after applying it).
Might be another bug though (reported as #13712).
[27 Nov 2005 8:32] Sergei Golubchik
Thanks for spotting this!
Yes, it should be fixed in both places.
Though the original fix is enough to close the bug - index corruption. The second loop belongs to the parser that understand boolean operators and it's only invoked to parse query string in MATCH ... AGAINST (... IN BOOLEAN MODE).

I now fixed the second place too.

Both fixed in 4.1.16
[28 Nov 2005 10:05] Sergei Golubchik
Just to clarify - this bug has nothing to do with utf-8, but rather with words that ends with more than one apostrophe (e.g. the test case in the patch uses testword'')
[30 Nov 2005 17:43] Paul Dubois
Noted in 4.1.16 changelog.
[5 Jan 2006 20:30] Philip Turner
Did this fix get into the 5.0 branch? I upgraded my replication server (the slave) from 4.0.18 to 5.0.17 and started popping these errors on the slave.

On the slave (5.0.17), I ran myisamchk tablename and it said the table was corrupted. I upgraded to 5.0.18 and same thing.

I loaded 4.1.16 and myisamchk fixes the table just fine.

The key in error is a fulltext key and I have instances of words ending with double apostophes.
[25 Feb 2006 22:48] Aleksey
ОS FreeBSD 5.4
MySQL  4.1.16

our web project intensively works with the table

table consists of approximately 2 500 000 records,
6 index fields.

In some moment the table crashes

check table box;
Key in wrong position at page 20601856 corrupt

After recovery
repair table
in some time crashes again.

Please, give us advice on how we can solve this problem?
[26 Feb 2006 11:10] noah williamsson
Upgrade to 4.1.18 and you'll be fine.
[27 Feb 2006 16:51] Aleksey
Ok! We've don it! Thank you!
I'll post here about the result - if we will experience problems or not with 4.1.18.

ps. I wonder, how this problem might be solved if using 4.1.16 or previous versions, without upgradingig to 4.1.18?
[27 Feb 2006 20:12] Aleksey
Now we are using 4.1.18 and the problem still exists. The table crashes
[6 Mar 2006 21:18] Aleksey
Finally we solved the problem. It was not mysql bug - the operative memory on server has been working not correctly causing problems in tables.
We changed the memory for an a new one. Now it is all fine.
[7 Aug 2006 12:39] Wim Roffel
I had this error too after I added "#ft_min_word_len=3" to my my.ini file. After I read this bug information I upgraded to 4.1.23 (from an earlier 4.1 version). However, the problem remained.

The problem happens when I delete a record. I can delete most records, but some are impossible to delete: I always get a 1034 error. I have repaired the table many times - after each failed deletion it finds some errors that it repairs.

I have uploaded a testcase that includes a test php file with one such deletion.

After I changed the settings I forgot immediately to run myisamchk after the reboot and instead added some records. I am not sure if that might have caused problems. Another possibility are foreign characters. The data contains a wide range of accented characters (scandinavian, french, slavic, etc.).

I have given up and deleted the word length setting... Immediately the problem disappeared.