[MDEV-30916] FTS_DOC_ID check does not work upon ALTER .. ALGORITHM=COPY Created: 2023-03-23  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Full-text Search, Storage Engine - InnoDB
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Unresolved Votes: 0
Labels: None


 Description   

There is this fragment in innodb_fts.innodb-fts-fic which states that FTS_DOC_ID type is being checked deliberately upon ALTER:

# We will check validity of FTS_DOC_ID, which must be of an UNSIGNED
 
# NOT NULL bigint 
CREATE TABLE wp(
 
   FTS_DOC_ID bigint PRIMARY KEY,
 
   title VARCHAR(255) NOT NULL DEFAULT '',
 
   text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
 
 
 
...
 
 
 
--error ER_INNODB_FT_WRONG_DOCID_COLUMN
 
CREATE FULLTEXT INDEX idx ON wp(title, text);

However, this check is inconsistent. If the fulltext index is created with ALTER .. ALGORITHM=COPY, it doesn't fail.

--source include/have_innodb.inc
 
 
 
CREATE TABLE wp(
 
   FTS_DOC_ID bigint PRIMARY KEY,
 
   text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
 
 
 
--error ER_INNODB_FT_WRONG_DOCID_COLUMN
 
ALTER TABLE wp ADD FULLTEXT INDEX idx (text);
 
 
 
ALTER TABLE wp ADD FULLTEXT INDEX idx (text), ALGORITHM=COPY;
 
SHOW CREATE TABLE wp;
 
 
 
DROP TABLE wp;

10.4 ff3d4395

 
ALTER TABLE wp ADD FULLTEXT INDEX idx (text);
 
ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
 
ALTER TABLE wp ADD FULLTEXT INDEX idx (text), ALGORITHM=COPY;
 
SHOW CREATE TABLE wp;
 
Table	Create Table
 
wp	CREATE TABLE `wp` (
 
  `FTS_DOC_ID` bigint(20) NOT NULL,
 
  `text` mediumtext NOT NULL,
 
  PRIMARY KEY (`FTS_DOC_ID`),
 
  FULLTEXT KEY `idx` (`text`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

In MySQL 5.7 both fail.
Generated at Thu Feb 08 10:19:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.