Bug #2190 Table corruption with MySQL-4.1.x and fulltext index
Submitted: 22 Dec 2003 11:11 Modified: 12 Jan 2004 13:29
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:MySQL-4.1.2 latest pull OS:
Assigned to: Sergei Golubchik

[22 Dec 2003 11:11] jocelyn fournier
Description:
Hi,

I get regularly corruptions on one of my tables with a fulltext index on it.

The table structure is quite simple :

CREATE TABLE `config` (
  `pseudo` varchar(35) NOT NULL default '',
  `config` text NOT NULL,
  PRIMARY KEY  (`pseudo`),
  FULLTEXT KEY `config` (`config`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

ft configuration is the following :

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

The request I'm doing on it are the following :

- SELECT pseudo FROM config WHERE MATCH (config) AGAINST ('$word' /*!40001 IN BOOLEAN MODE */) ORDER BY pseudo ASC;

- INSERT INTO config (pseudo,config) VALUES ('$pseudo','$configuration')

- SELECT email,password,nbrpost_forum,image_forum,signature_forum,icq,IP,homepage,dateinscr,hobby,occupation,status,image,mailv,validation,citation,ville,smilies,config.config,js,zlib,online,topicpp,lastpost,birthday, oldquote,modomp,validsm FROM inscrit LEFT JOIN config USING (pseudo) WHERE inscrit.pseudo='$pseudo'

(inscrit is an innodb table)

- REPLACE INTO config (pseudo,config) VALUES ('$pseudo','$configuration')

I'm quite sure there is something wrong with MySQL since problems happens only with this table (all other table are OK), and it's the only table I use with a fulltext index.

How to repeat:
Download

ftp://support.mysql.com/pub/mysql/secret/configbug.tar.gz

This is the table in a crashed state (CHECK TABLE indicates "Key in wrong position at page 380928" ).
Unfortunately I've not yet succeeded in making a reproductible testcase, but perhaps you'll find some clues with the crashed table and the SQL requests...

Thanks !
  Jocelyn
[5 Jan 2004 5:21] Vaclav Vobornik
Like bug #1166 (now used cp1250 instead of utf8)
I am not able to work with fulltext index. I have to use slow fulltext search "in boolean mode" without fulltext index :-((

This sql make table corrupted:
http://www.blogator.com/bug/corrupt_mysql_table.zip 

root@linux:~# mysql -D test <items.sql 
root@linux:~# mysql -D test -e "repair table items;"
+------------+--------+----------+----------+
| Table      | Op     | Msg_type | Msg_text |
+------------+--------+----------+----------+
| test.items | repair | status   | OK       |
+------------+--------+----------+----------+

root@linux:~# mysql -D test -e "check table items;"
+------------+-------+----------+------------------------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                                         |
+------------+-------+----------+------------------------------------------------------------------+
| test.items | check | error    | Found key at page 1276928 that points to record outside datafile |
| test.items | check | error    | Corrupt                                                          |
+------------+-------+----------+------------------------------------------------------------------+
[8 Jan 2004 14:26] jocelyn fournier
Hi again,

I also experienced crashes when executed some queries on the corrupted table :

You can download the table here :

ftp://support.mysql.com/pub/mysql/secret/configbug1.tar.gz

And then execute :

SELECT pseudo FROM Hardwarefr.config WHERE MATCH (config) AGAINST ('cran LCD 17" Compaq TFT 7020 ' /*!40001 IN BOOLEAN MODE */) ORDER BY pseudo ASC;

MySQL will crash with the following stack trace :

0x80bd13d handle_segfault + 653
0x8346f18 pthread_sighandler + 176
0x8309edb _ftb_climb_the_tree + 427
0x8309942 ft_boolean_find_relevance + 514
0x830970a ft_boolean_read_next + 666
0x813dd9e ha_myisam::ft_read(char*) + 46
0x81352f1 FT_SELECT::get_next() + 33
0x81366a5 find_all_keys(st_sort_param*, SQL_SELECT*, unsigned char**, st_io_cache*, st_io_cache*, st_io_cache*) + 197
0x81361d5 filesort(THD*, st_table*, st_sort_field*, unsigned, SQL_SELECT*, unsigned long, unsigned long*) + 1061
0x810124d create_sort_index(THD*, JOIN*, st_order*, unsigned long, unsigned long) + 317
0x80f61d0 JOIN::exec() + 3584
0x80f6b9d _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 173
0x80f34a6 handle_select(THD*, st_lex*, select_result*) + 326
0x80cf1fe mysql_execute_command(THD*) + 814
0x80d52da mysql_parse(THD*, char*, unsigned) + 266
0x80cde0e dispatch_command(enum_server_command, THD*, char*, unsigned) + 1038
0x80cd9a6 do_command(THD*) + 134
0x80cd128 handle_one_connection + 888
0x834493a pthread_start_thread + 218
0x8378b6a thread_start + 4

Regards,
  Jocelyn
[12 Jan 2004 8:34] Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

actually it was a bug in CHECK TABLE, that wasn't able to check correctly 2-level fulltext index.
Fixed in 4.1.2
[12 Jan 2004 12:07] jocelyn fournier
Hi Sergei,

Unfortunately, I've stilly have problem even with the latest code including your fix.
I repaired the table 1 hour ago, and the table is corrupted again.
CHECK TABLE returns :

mysql> CHECK TABLE config;
+-------------------+-------+----------+---------------------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                          |
+-------------------+-------+----------+---------------------------------------------------+
| Hardwarefr.config | check | warning  | Table is marked as crashed                        |
| Hardwarefr.config | check | error    | Wrong pagepointer: 4294967033 at page: 4294967033 |
| Hardwarefr.config | check | error    | Corrupt                                           |
+-------------------+-------+----------+---------------------------------------------------+
3 rows in set (0.04 sec)

So it seems there's another problem :/
Do you want me to upload my latest corrupted table ?

Regards,
  Jocelyn
[12 Jan 2004 13:29] Sergei Golubchik
yes, it looks like a different problem.

Did you succeed in creating a test case ?

If it's a bug in CHECK TABLE, than CHECK TABLE should report a corruption even if run right after REPAIR TABLE.

And, please, as it's a different bug, could you create a new entry for it ?