📋

Description

This error indicates that a specified column cannot be included in a FULLTEXT index due to incompatible data types, storage engine limitations, or other structural constraints. It typically occurs when attempting to create or modify a table with a FULLTEXT index on an unsupported column.

💬

Error Message

Column '%s' cannot be part of FULLTEXT index

🔍

Known Causes

4 known causes

⚠️

Unsupported Data Type

FULLTEXT indexes only support CHAR, VARCHAR, or TEXT columns. Attempting to index numeric, date, or binary types will fail.

⚠️

Incompatible Storage Engine

FULLTEXT indexing is primarily supported by the InnoDB and MyISAM storage engines. Using other engines like MEMORY or CSV for FULLTEXT will result in this error.

⚠️

Column Length Restrictions

MariaDB has internal limits on the maximum length of columns that can be included in a FULLTEXT index. Very long TEXT or VARCHAR columns might exceed these limits.

⚠️

Incorrect Character Set/Collation

While less common, specific character sets or collations might not be fully compatible with FULLTEXT indexing, especially in older MariaDB versions.

🛠️

Solutions

3 solutions available

1. Remove Unsupported Column from FULLTEXT Index easy

Identify and remove columns that do not support FULLTEXT indexing from your index definition.

1

Identify the column(s) causing the error. The error message will typically indicate the problematic column name. You can also inspect your table's index definition.

SHOW CREATE TABLE your_table_name;

2

Determine if the column is of a supported data type. Supported types for FULLTEXT indexes in MariaDB include CHAR, VARCHAR, and TEXT (including TINYTEXT, MEDIUMTEXT, LONGTEXT).

DESCRIBE your_table_name;

3

If the column is not a supported type (e.g., a numeric type, BLOB, DATE, etc.), you must remove it from the FULLTEXT index definition. First, drop the existing FULLTEXT index.

ALTER TABLE your_table_name DROP INDEX your_fulltext_index_name;

4

Then, recreate the FULLTEXT index including only the supported columns.

ALTER TABLE your_table_name ADD FULLTEXT INDEX your_fulltext_index_name (supported_column1, supported_column2);

2. Convert Column to a Supported Data Type medium

Change the data type of the problematic column to a type compatible with FULLTEXT indexing.

1

Identify the unsupported column and its current data type using `DESCRIBE`.

DESCRIBE your_table_name;

2

If the column's data is textual in nature but stored in an unsupported type (e.g., a large integer representing a code that can be described textually), you will need to convert it. This might involve creating a new column of a supported type (e.g., VARCHAR) and populating it with the converted data, or directly altering the existing column's type if feasible and safe for your data.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255); -- Example: Converting to VARCHAR

3

After successfully altering the column's data type, you can proceed to create or recreate the FULLTEXT index including this column.

ALTER TABLE your_table_name ADD FULLTEXT INDEX your_fulltext_index_name (supported_column1, your_column_name);

3. Create a Separate Textual Representation Column medium

If direct conversion is not possible or desirable, create a new column for textual data and index that.

1

Identify the column causing the error and understand why its current data type is not suitable for FULLTEXT indexing.

DESCRIBE your_table_name;

2

Add a new column to your table with a supported text data type (e.g., VARCHAR or TEXT). This column will store the textual representation of the data from the problematic column.

ALTER TABLE your_table_name ADD COLUMN textual_representation VARCHAR(500);

3

Populate the new `textual_representation` column with data derived from the original problematic column. This might require custom logic or SQL queries, depending on the data.

UPDATE your_table_name SET textual_representation = CONCAT('Some prefix: ', CAST(your_column_name AS CHAR)); -- Example: For a numeric column

4

Once the new column is populated, drop the old FULLTEXT index if it exists and recreate it, including the new `textual_representation` column.

ALTER TABLE your_table_name DROP INDEX your_fulltext_index_name; -- If it exists

5

Create the new FULLTEXT index.

ALTER TABLE your_table_name ADD FULLTEXT INDEX your_fulltext_index_name (supported_column1, textual_representation);

🔗

Related Errors

5 related errors

MariaDB Error 1283: Invalid FULLTEXT Column