MySQL Bugs: #114449: Rebuilding FULLTEXT index not working properly on master/slave replication

Bug #114449	Rebuilding FULLTEXT index not working properly on master/slave replication
Submitted:	22 Mar 2024 10:30	Modified:	27 Mar 2024 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

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

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?

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

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?

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

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

Hi,

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