MySQL Bugs: #26072: Table with FT index is marked as crashed quite often

Bug #26072	Table with FT index is marked as crashed quite often
Submitted:	5 Feb 2007 8:14	Modified:	27 Jun 2007 19:47
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	5.0.27, 5.1, 5.2	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	corruption, fulltext, myisam, qc

Description:
Hi,

I've been experiencing for a while now index corruption on a table with a FT index.
This has occured once again this morning, so I'm uploading the corrupted file (only 209 rows).
You can get it at : 

ftp://ftp.mysql.com/pub/mysql/upload/corrupted.tar.gz

Perhaps with the MYI file it'll be obvious why the corruption occurs.

How to repeat:
Some configuration information :

| ft_boolean_syntax                   | + -><()~*:""&|                                                               
| ft_max_word_len                     | 84                                                                           
| ft_min_word_len                     | 1                                                                            
| ft_query_expansion_limit            | 20                                                                           
| ft_stopword_file                    | (built-in)                                                                   

The only queries executed on the table are :

UPDATE config SET pseudo='foo' WHERE pseudo='bar';
INSERT INTO config (pseudo,config) VALUES ('foo','bar');
REPLACE INTO config (pseudo,config) VALUES ('foo','bar');
DELETE FROM config WHERE pseudo='foo';
SELECT pseudo FROM config WHERE MATCH (config) AGAINST ('foo' /*!40001 IN BOOLEAN MODE */) ORDER BY pseudo ASC
SELECT config FROM config WHERE pseudo='foo'

Hope this could help to track down the issue.

Regards,
  Jocelyn

jocelyn, thanks for the info.  I couldn't repeat corruption after a few hundred thousand queries, as you mentioned above.  please upload your server's my.cnf, so I can use the same configuration in my test. also, upload complete error log of mysql (compress + upload as private attachment).

Hi Shane,

On my case it could take weeks before reaching corruption.
That's why I uploaded the MYI to perhaps have a better idea of what happened internally.
Note that the corruption occurs on the majority of my servers with really different configuration.

There's nothing really usefull in the .err: 
070203 23:56:29 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table './mutu_toulouse/config.MYI'; try to repair it
070203 23:56:29 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect key file for table './mutu_toulouse/config.MYI'; try to repair it
070204  4:43:35 [ERROR] /usr/local/mysql/bin/mysqld: Table './mutu_toulouse/config' is marked as crashed and should be repaired

Usefull informations from the my.cnf :

[mysqld]
skip-locking
skip-name-resolve
skip-innodb

set-variable    = ft_min_word_len=1
set-variable    = key_buffer_size=1G
set-variable    = table_cache=2400
set-variable    = read_buffer_size=1M
set-variable    = read_rnd_buffer_size=1M
set-variable    = back_log=300
set-variable    = thread_cache_size=40
set-variable    = query_cache_limit=1M
set-variable    = query_cache_size=16M
set-variable    = delayed_insert_limit=80
set-variable    = max_delayed_threads=20
set-variable    = join_buffer_size=32M
set-variable    = sort_buffer_size=1M
set-variable    = delayed_insert_timeout=20
set-variable    = wait_timeout=10
set-variable    = max_connections=200
set-variable    = myisam_sort_buffer_size=512M
set-variable    = thread_concurrency=4

Regards,
  Jocelyn

jocelyn, please send output of SHOW TABLE STATUS LIKE 'config'.  I want to get an idea of the number of records and average row sizes.

How many concurrent threads are normally accessing this table, and do you use CHECK/OPTIMIZE/ANALYZE/REPAIR/LOCK TABLE ever ?

I haven't repeated a corruption yet.

thanks,

Hi Shane,

*************************** 1. row ***************************
           Name: config
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 213
 Avg_row_length: 223
    Data_length: 47656
Max_data_length: 281474976710655
   Index_length: 131072
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-01-12 01:12:49
    Update_time: 2007-02-15 09:55:52
     Check_time: 2007-02-05 08:56:40
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysqldump is runned each days with the following options : 

--compress --extended-insert --complete-insert --lock-tables=0

Did you find any clues in the MYI file ?

  Jocelyn

Hi,

FYI, I've just hit the same corruption on the same table structure in another db :

mysql> show table status like 'config'\G;
*************************** 1. row ***************************
           Name: config
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4370
 Avg_row_length: 43
    Data_length: 189376
Max_data_length: 281474976710655
   Index_length: 275456
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-01-12 01:12:23
    Update_time: 2007-02-16 23:25:19
     Check_time: 2007-02-16 23:25:19
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

I can upload the corrupted MYI/MYD/frm file if you think it could be usefull.
I can also upload the table in the state it has yesterday so that you could figure out what has happened between yesterday and today.
I'll also check on my side which rows are differents between today and yesterday.

Regards,
  Jocelyn

Hi,

Unfortunately I only have the sql dump of the table yesterday, and it has the same content as the table today.
However I know that the query which has returned the "incorrect key file" result is :

DELETE FROM config WHERE pseudo='OLIVIER'

I'll try to monitor precisely which combinations of update/delete/insert lead to an index corruption, and keep you inform of the results.

Thanks,
  Jocelyn

thanks, the table is small enough so if you can get a dump of data from a known point in time, and the binlogs to replay the corruption would help alot to pinpoint the problem.

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

Any ideas on how to repeat are still needed. Please, send the results of:

getconf GNU_LIBC_VERSION
getconf GNU_LIBPTHREAD_VERSION

Is it possible to try with thread_cache_size=0 and check if the problem still occurs?

Hi Valeriy,

So far I've not experienced any new corruption yet, and unfortunately thread_cache_size=0 is not an option.

getconf GNU_LIBC_VERSION
glibc 2.3.2
getconf GNU_LIBPTHREAD_VERSION
NPTL 0.60

(however I'm using linuxthread, not NPTL on those servers).

  Jocelyn

I repeated a corruption today on 5.0.27.

Version: '5.0.27-community-debug'  socket: ''  port: 3306  MySQL Community Edition - Debug (GPL)
070514  9:43:48 [ERROR] mysqld-debug: Incorrect key file for table '.\test\config.MYI'; try to repair it

I am trying now 5.0.42 and also a reliable testcase.

Well, we have a case (on a production machine) where this bug happens on a MySQL version 4.1.20 in production, whereas we have yet been unable to reproduce on a testcase (I am currently working on this reproduction). It is said to happen about every 3 hours on the production machine and seems to be quite related to the machines'load (though we have no formal proof of that : the bug has multiplied its instance with the number of users).
I am willing to contribute to this, I'll publish a test case as I find one. FYI, I began by adding a backtrace() call to log the backtrace whenever the index gets corrupted (a modification I can send you if you wish).

Perhaps related to this - I filed bug #29299 today.
I will try it on 4.1 later once we know a cause.

I don't think 29299 is related in my case : I'm only using latin1 charset.