0

I add a fulltext index to the existing db but my fulltext index query could not find the existing record.

Engine: InnoDB DB: mysql Fulltext mode: boolean mode

When I run the following query I could not find any record 

select * from user_data user
where user.is_deleted=0 and
MATCH(user.family_name,user.given_name) 
AGAINST('USS_FUBQ_gen_given_name' in boolean mode);

but when I run 

select * from user_data user
where user.is_deleted=0 and
MATCH(user.family_name,user.given_name) 
AGAINST('uss_fubq_gen_given_name' in boolean mode);

i am getting the expected output. Expected output: 

   familyName               |    given name

USS_FUBQ_gen_family_name    | USS_FUBQ_gen_given_name

My table definition:

CREATE TABLE user_data (
  user_id varchar(36) COLLATE utf8_bin NOT NULL, 
  family_name varchar(50) COLLATE utf8_bin DEFAULT NULL, 
  gender char(1) COLLATE utf8_bin DEFAULT NULL, 
  given_name varchar(50) COLLATE utf8_bin DEFAULT NULL, 
  middle_names varchar(100) COLLATE utf8_bin DEFAULT NULL, 
  PRIMARY KEY (user_id), 
  FULLTEXT KEY ix_full_text_index_by_name (family_name,given_name), 
  [...] ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Humaira Faheem
  • 1
  • 3
  • Can you post your table definition (`show create table user_data`)? This sounds like you used a case sensitive collation. – Solarflare Jul 29 '19 at 18:51
  • yes I am using utf8-utf_bin – Humaira Faheem Jul 29 '19 at 19:08
  • but my result of this query contain the family name and given name as USS_FUBQ_gen_family_name, USS_FUBQ_gen_given_name in the database – Humaira Faheem Jul 29 '19 at 19:10
  • 'user_data', 'CREATE TABLE `user_data` (\n `user_id` varchar(36) COLLATE utf8_bin NOT NULL, \n `family_name` varchar(50) COLLATE utf8_bin DEFAULT NULL, \n `gender` char(1) COLLATE utf8_bin DEFAULT NULL, \n `given_name` varchar(50) COLLATE utf8_bin DEFAULT NULL, \n `middle_names` varchar(100) COLLATE utf8_bin DEFAULT NULL, \n PRIMARY KEY (`user_id`), \n FULLTEXT KEY `ix_full_text_index_by_name` (`family_name`,`given_name`), ON DELETE NO ACTION ON UPDATE NO ACTION \n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin' – Humaira Faheem Jul 29 '19 at 19:14
  • But when I insert any new record then it works i.e : FU_gen_given_name – Humaira Faheem Jul 29 '19 at 19:27
  • I edited your table definition from your comment into the question. As expected, you are using a case sensitive column (`utf8_bin`). That means you are explicitly telling MySQL to make a difference between `USS` and `uss`. So MySQL does that. The question is: do you actually want that (it is uncommon for names/normal texts, specifically because of this problem)? See e.g. [this question](https://stackoverflow.com/q/23800616) for the opposite problem. – Solarflare Jul 29 '19 at 20:17
  • my question is that in the database the value of the given name store as USS not uss but my fulltext search by uss not USS! however, this is case sensitive fields so it should return the record search by the USS because in the database the record store as USS not uss – Humaira Faheem Jul 30 '19 at 12:55
  • That was not clear from your question (and actually I am still not sure if I understand correctly). Could you then add the actual "expected output" (instead of just writing that it returned the expected output, as the expected output is to get the lower case result when you search for the lower case string). To be specific: please add the case sensitive result for the columns `family_name`, `given_name` and `user_id` for a) both fulltext searches (even if empty) and b) for `select * from user_data where user_id = 'the primary key(s) you found'`. – Solarflare Jul 30 '19 at 14:02
  • I edit my question with the expected result. expected result are values with the capital letters not the lower letter. – Humaira Faheem Jul 30 '19 at 15:37
  • This should not be the case. While I still suspect that there is some misrepresentation or missing information somewhere (e.g. maybe you are updating that row in the same transaction as you are querying it, or maybe it is just a display problem), in case it is some weird bug/problem with your index, run `optimize table user_data`. This should refresh your index. – Solarflare Jul 30 '19 at 18:22

0 Answers0