This Report was made using the WinMySQLAdmin 1.3 Tool

12/03/2002 09:39:41 Description : FullText search with multiple index sometimes returns empty set How-To-Repeat : Create "search_table" table and fill it with two records (see MySql.log trace file for describe and data), then reproduce the bug using the fulltext queries

==== Here is MySQL.log (begin) ==== mysql> show create table search_table; +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | search_table | CREATE TABLE `search_table` ( `tvanum` int(11) NOT NULL default '0', `nom1` varchar(35) default NULL, `nom2` varchar(35) default NULL, `sigle` varchar(50) default NULL, `pers` char(1) default NULL, `rnb` varchar(76) default NULL, `mnemo` varchar(4) default NULL, `norm_nom` varchar(120) default NULL, `norm_rnb` varchar(76) default NULL, PRIMARY KEY (`tvanum`), FULLTEXT KEY `norm_nom` (`norm_nom`), FULLTEXT KEY `norm_rnb` (`norm_rnb`), KEY `mnemo` (`mnemo`) ) TYPE=MyISAM | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> describe search_table; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | tvanum | int(11) | | PRI | 0 | | | nom1 | varchar(35) | YES | | NULL | | | nom2 | varchar(35) | YES | | NULL | | | sigle | varchar(50) | YES | | NULL | | | pers | char(1) | YES | | NULL | | | rnb | varchar(76) | YES | | NULL | | | mnemo | varchar(4) | YES | MUL | NULL | | | norm_nom | varchar(120) | YES | MUL | NULL | | | norm_rnb | varchar(76) | YES | MUL | NULL | | +----------+--------------+------+-----+---------+-------+ 9 rows in set (0.00 sec)

mysql> show index from search_table; +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+ | search_table | 0 | PRIMARY | 1 | tvanum | A | 2 | NULL | NULL | | | search_table | 1 | norm_nom | 1 | norm_nom | A | NULL | NULL | NULL | FULLTEXT | | search_table | 1 | norm_rnb | 1 | norm_rnb | A | NULL | NULL | NULL | FULLTEXT | | search_table | 1 | mnemo | 1 | mnemo | A | NULL | NULL | NULL | | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----------+ 4 rows in set (0.00 sec)

mysql> select * -> from search_table -> ; +-----------+-----------------------------------+---------------------------+----------+------+---------------------------+-------+------------------------------------------------------------------+---------------------------+ | tvanum | nom1 | nom2 | sigle | pers | rnb | mnemo | norm_nom | norm_rnb | +-----------+-----------------------------------+---------------------------+----------+------+---------------------------+-------+------------------------------------------------------------------+---------------------------+ | 446101020 | PROMOTION DU SERVICE PUBLIC DE LA | LECTURE EN BRABANT WALLON | P.L.B.W. | M | PLACE ALBERT I 1 | NIVE | PROMOTION DU SERVICE PUBLIC DE LA LECTURE EN BRABANT WALLON PLBW | PLACE ALBERT I 1 | | 433742725 | EURO DB | | | M | PLACE DE L'UNIVERSITE, 16 | LLNE | EURO DB | PLACE DE L UNIVERSITE 16 | +-----------+-----------------------------------+---------------------------+----------+------+---------------------------+-------+------------------------------------------------------------------+---------------------------+ 2 rows in set (0.00 sec)

mysql> select * -> from search_table -> where match(norm_nom) against ('+promotion +lecture' in boolean mode) -> and match(norm_rnb) against ('+place +albert' in boolean mode) -> ; +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+ | tvanum | nom1 | nom2 | sigle | pers | rnb | mnemo | norm_nom | norm_rnb | +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+ | 446101020 | PROMOTION DU SERVICE PUBLIC DE LA | LECTURE EN BRABANT WALLON | P.L.B.W. | M | PLACE ALBERT I 1 | NIVE | PROMOTION DU SERVICE PUBLIC DE LA LECTURE EN BRABANT WALLON PLBW | PLACE ALBERT I 1 | +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+ 1 row in set (0.06 sec)

mysql> select * -> from search_table -> where match(norm_nom) against ('+promotion +lecture' in boolean mode) -> and match(norm_rnb) against ('+albert +place' in boolean mode) -> ; Empty set (0.06 sec)

mysql> select a.*, match(norm_nom) against ('+promotion +lecture' in boolean mode), match(norm_rnb) against ('+place +albert' in boolean mode) -> from search_table a -> where tvanum = 446101020 -> ; +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+-----------------------------------------------------------------+------------------------------------------------------------+ | tvanum | nom1 | nom2 | sigle | pers | rnb | mnemo | norm_nom | norm_rnb | match(norm_nom) against ('+promotion +lecture' in boolean mode) | match(norm_rnb) against ('+place +albert' in boolean mode) | +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+-----------------------------------------------------------------+------------------------------------------------------------+ | 446101020 | PROMOTION DU SERVICE PUBLIC DE LA | LECTURE EN BRABANT WALLON | P.L.B.W. | M | PLACE ALBERT I 1 | NIVE | PROMOTION DU SERVICE PUBLIC DE LA LECTURE EN BRABANT WALLON PLBW | PLACE ALBERT I 1 | 2 | 2 | +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+-----------------------------------------------------------------+------------------------------------------------------------+ 1 row in set (0.13 sec)

mysql> select a.*, match(norm_nom) against ('+promotion +lecture' in boolean mode), match(norm_rnb) against ('+albert +place' in boolean mode) -> from search_table a -> where tvanum = 446101020 -> ; +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+-----------------------------------------------------------------+------------------------------------------------------------+ | tvanum | nom1 | nom2 | sigle | pers | rnb | mnemo | norm_nom | norm_rnb | match(norm_nom) against ('+promotion +lecture' in boolean mode) | match(norm_rnb) against ('+albert +place' in boolean mode) | +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+-----------------------------------------------------------------+------------------------------------------------------------+ | 446101020 | PROMOTION DU SERVICE PUBLIC DE LA | LECTURE EN BRABANT WALLON | P.L.B.W. | M | PLACE ALBERT I 1 | NIVE | PROMOTION DU SERVICE PUBLIC DE LA LECTURE EN BRABANT WALLON PLBW | PLACE ALBERT I 1 | 2 | 0 | +-----------+-----------------------------------+---------------------------+----------+------+------------------+-------+------------------------------------------------------------------+-------------------+-----------------------------------------------------------------+------------------------------------------------------------+ 1 row in set (0.00 sec) ==== MySQL.log (end) ====

Fix : Don't know ! Synopsis : When using multiple index (one fulltext), fulltext searches may return empty results ... depending on the order of the words. Submitter-Id : Laurent Merckx Originator : Organization : Euro DB MySQL support : none Severity : serious Priority : high Category : mysqld Class : sw-bug OS Platform : Windows 2000 Service Pack 2 Machine description: Pentium III 1Ghz Compiler : VC++ 6.0 Architecture : i686 Total Memory : 130616 KB RAM

My.ini File

[mysqld] basedir=C:/mysql datadir=C:/mysql/data

Server Status Values

Server Info 4.0.1-alpha-nt Host Info . via named pipe Client Info 3.23.36 Proto Info 10 Aborted_clients 1 Aborted_connects 0 Bytes_received 0 Bytes_sent 0 Com_admin_commands 0 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_change_db 0 Com_change_master 0 Com_check 0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 2 Com_delete 0 Com_delete_multi 0 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 0 Com_flush 0 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_insert 0 Com_insert_select 4 Com_kill 0 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize 0 Com_purge 0 Com_rename_table 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_rollback 0 Com_select 22 Com_set_option 0 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_create 2 Com_show_databases 2 Com_show_fields 0 Com_show_grants 0 Com_show_keys 0 Com_show_logs 0 Com_show_master_stat 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_processlist 1 Com_show_slave_hosts 0 Com_show_slave_stat 0 Com_show_status 186 Com_show_tables 0 Com_show_variables 2 Com_slave_start 0 Com_slave_stop 0 Com_truncate 0 Com_unlock_tables 0 Com_update 0 Connections 6 Created_tmp_disk_tables 0 Created_tmp_tables 0 Created_tmp_files 0 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_delete 0 Handler_read_first 3 Handler_read_key 4 Handler_read_next 14 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 410437 Handler_update 0 Handler_write 2 Key_blocks_used 45 Key_read_requests 46332 Key_reads 40 Key_write_requests 22 Key_writes 11 Max_used_connections 2 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables 2 Open_files 4 Open_streams 0 Opened_tables 9 Questions 114 Qcache_queries_in_cache 0 Qcache_inserts 0 Qcache_hits 0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 0 Select_full_range_join 0 Select_range 0 Select_range_check 0 Select_scan 2 Slave_running OFF Slave_open_temp_tables 0 Slow_launch_threads 0 Slow_queries 0 Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 Table_locks_immediate 20 Table_locks_waited 0 Threads_cached 0 Threads_created 5 Threads_connected 2 Threads_running 1 Uptime 915

Variables Values

back_log 50 basedir C:\mysql\ binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 datadir C:\mysql\data\ delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 1800 ft_min_word_len 2 ft_max_word_len 254 ft_max_word_len_for_sort 20 ft_boolean_syntax + -><()~*:""&| have_bdb NO have_innodb NO have_isam NO have_raid NO have_symlink YES have_openssl NO init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8388600 language C:\mysql\share\english\ large_files_support ON log OFF log_update OFF log_bin OFF log_slave_updates OFF log_long_queries OFF long_query_time 10 low_priority_updates OFF lower_case_table_names 1 max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_bulk_insert_tree_size 8388608 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options OFF myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file C:\mysql\data\LM01.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 rpl_recovery_rank 0 query_buffer_size 0 query_cache_limit 1048576 query_cache_size 0 query_cache_startup_type 1 safe_show_database OFF server_id 0 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket MySQL sort_buffer 2097144 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone Romance Standard Time tmp_table_size 33554432 tmpdir C:\DOCUME~1\lmerckx\LOCALS~1\Temp\ version 4.0.1-alpha-nt wait_timeout 28800

Last Lines from Err File