Bug #114449 Rebuilding FULLTEXT index not working properly on master/slave replication
Submitted: 22 Mar 10:30 Modified: 27 Mar 14:03
Reporter: gael Sainson Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Cluster: Replication Severity:S3 (Non-critical)
Version:8.0.34 OS:Other (AWS DB cluster)
Assigned to: MySQL Verification Team CPU Architecture:Any

[22 Mar 10:30] gael Sainson
Description:
On AWS, I have a MySQL DB cluster with one writer and one reader
After running a query to rebuild a FULLTEXT index, a select call using this index works on the writer but fail on the reader.

Note 
- splitting the ALTER query into two queries "fix" the issue (eg one query to drop the index the one to recreate the index)
- dropping and recreating the reader node also "fix" the issue

How to repeat:

create table if not exists `test`.`jobs` (`ID` BIGINT NOT NULL AUTO_INCREMENT, `DESCRIPTION` TEXT NULL DEFAULT NULL, `ADDRESS_ADDRESS` TEXT NULL DEFAULT NULL,  `ADDRESS_CITY` TEXT NULL DEFAULT NULL, `ADDRESSEND_ADDRESS` TEXT NULL DEFAULT NULL,  `ADDRESSEND_CITY` TEXT NULL DEFAULT NULL, PRIMARY KEY (`ID`) , FULLTEXT KEY `IdxFULLTEXT` (`ADDRESSEND_ADDRESS`, `ADDRESSEND_CITY`, `ADDRESS_ADDRESS`, `ADDRESS_CITY`, `DESCRIPTION`)) COLLATE='utf8mb4_general_ci';
 
 
ALTER TABLE `test`.`jobs` ADD COLUMN `ATTRIBUTES_A` VARCHAR(255) NULL ;

ALTER TABLE `test`.`jobs` DROP INDEX `IdxFULLTEXT` , ADD FULLTEXT INDEX `IdxFULLTEXT` (`ADDRESSEND_ADDRESS`, `ADDRESSEND_CITY`, `ADDRESS_ADDRESS`, `ADDRESS_CITY`, `DESCRIPTION`, `ATTRIBUTES_A`) ;

select SQL_CALC_FOUND_ROWS  jobs.ID,jobs.ATTRIBUTES_A from `test`.`jobs`  where MATCH(jobs.ADDRESS_ADDRESS, jobs.ADDRESSEND_ADDRESS, DESCRIPTION, jobs.ATTRIBUTES_A, jobs.ADDRESSEND_CITY, jobs.ADDRESS_CITY) AGAINST ('+paris* ' IN BOOLEAN MODE)  order by jobs.ID limit 0,300

on the writer, the select succeed
on the reader, the select returns : SQL Error [1191] [HY000]: Can't find FULLTEXT index matching the column list
[25 Mar 19:02] MySQL Verification Team
Hi
I am not sure I understand. You cannot do create/alter on the read node? What exactly are you trying to execute on the reader?
[25 Mar 20:56] gael Sainson
Nop, I don't, a reader is obviously readonly :-)
But if I do an update on the writer, I'm expecting the reader to be updated through the binlogs ... and I'm expecting a select to run seamlessly on the writer or the reader ...
In my case, I run an alter table on the writer .... after this update the select works on the writer but not on the reader ...
[25 Mar 21:07] MySQL Verification Team
Hi,
I am failing to reproduce this (on 8.0.36 but should not matter), the alter is normally replicated to the replica server.

What error do you see on the replica, what does show slave status\G show on replica?
[25 Mar 21:33] gael Sainson
The error is on the select call

select SQL_CALC_FOUND_ROWS  jobs.ID,jobs.ATTRIBUTES_A from `test`.`jobs`  where MATCH(jobs.ADDRESS_ADDRESS, jobs.ADDRESSEND_ADDRESS, DESCRIPTION, jobs.ATTRIBUTES_A, jobs.ADDRESSEND_CITY, jobs.ADDRESS_CITY) AGAINST ('+paris* ' IN BOOLEAN MODE)  order by jobs.ID limit 0,300

on the writer, the select succeed

on the reader, the select returns : SQL Error [1191] [HY000]: Can't find FULLTEXT index matching the column list

I had this issue initially when running my automated test then I've simplified the case to the queries in my initial message. The issue was systematic.

I've dropped my cluster since so I can't run a  show slave status for now. I try to do it tomorrow. But AWS was not reporting any issue ...
[27 Mar 14:03] gael Sainson
Hi,
I've made another test and the issue is quite systematic. 
Now I've realised the writer reader synchronisation on AWS is not bin-log based so this issue is likely to not be related to MySQL itself. 
I've open the same case on AWS ....
[27 Mar 15:04] MySQL Verification Team
Hi,

Thanks for confirmation. Could be AWS problem as I cannot reproduce with our binaries.