Bug #110265 ALTER TABLE a DROP INDEX `ft` , ADD FULLTEXT INDEX `ft`(col1,col2) WITH PARSER `

• Submitted: 3 Mar 2023 2:34
• Modified: 3 Mar 2023 14:31
• Reporter: haobiao Xu
• Status: Not a Bug
• Category: MySQL Server: FULLTEXT search
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Linux
• CPU Architecture: x86
• Tags: DDL, fulltext

[3 Mar 2023 2:34] haobiao Xu

Description:
IF I create the FULLTEXT INDEX without "WITH PARSER `ngram`" ,

like "ALTER TABLE  a ADD FULLTEXT INDEX `ft_x` (`col1`, `col2`) ;",

and then I drop and readd the FULLTEXT INDEX with "WITH PARSER `ngram`", 

like "ALTER TABLE  a DROP INDEX `ft_x` , ADD FULLTEXT INDEX `ft_x` (`col1`, `col2`)  WITH PARSER `ngram` ;", 

the FULLTEXT INDEX cannot be transfromed to "FULLTEXT INDEX `ft_x` (`col1`, `col2`)  WITH PARSER `ngram`"

How to repeat:
CREATE TABLE `a_station` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT ,
  `STATION_NAME` varchar(256) DEFAULT NULL ,
  `STATION_ADDR` varchar(256) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  ;

-- 按业务原步骤复现
-- 先创建不指定 ngram 全文解析器的全文索引
ALTER TABLE  a_station
ADD FULLTEXT INDEX `ft_name_addr` (`STATION_NAME`, `STATION_ADDR`) ;

-- 再重建索引
ALTER TABLE  a_station
DROP INDEX `ft_name_addr` ,
ADD FULLTEXT INDEX `ft_name_addr` (`STATION_NAME`, `STATION_ADDR`)  WITH PARSER `ngram` ;

-- 独立两个ALTER
ALTER TABLE  a_station DROP INDEX `ft_name_addr` ;
ALTER TABLE  a_station ADD FULLTEXT INDEX `ft_name_addr` (`STATION_NAME`, `STATION_ADDR`)  WITH PARSER `ngram` ;

-- the FULLTEXT INDEX changed sucessfully
CREATE TABLE `a_station` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT ,
  `STATION_NAME` varchar(256) DEFAULT NULL ,
  `STATION_ADDR` varchar(256) DEFAULT NULL ,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_name_addr` (`STATION_NAME`,`STATION_ADDR`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB ;

[3 Mar 2023 14:31] MySQL Verification Team

Hi Mr. Xu,

Thank you for your bug report.

We have tested it with the latest release and it works like a charm.

We added SHOW CREATE TABLE after both ALTER TABLE commands:

Table	Create Table
test	CREATE TABLE `test` (\n  `id` bigint NOT NULL AUTO_INCREMENT,\n  `STATION_NAME` varchar(256) DEFAULT NULL,\n  `STATION_ADDR` varchar(256) DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  FULLTEXT KEY `ft_name_addr` (`STATION_NAME`,`STATION_ADDR`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Table	Create Table
test	CREATE TABLE `test` (\n  `id` bigint NOT NULL AUTO_INCREMENT,\n  `STATION_NAME` varchar(256) DEFAULT NULL,\n  `STATION_ADDR` varchar(256) DEFAULT NULL,\n  PRIMARY KEY (`id`),\n  FULLTEXT KEY `ft_name_addr` (`STATION_NAME`,`STATION_ADDR`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Not a bug.