Error
Error Code:
1797
MariaDB Error 1797: Incorrect Column Type for FULLTEXT Index
Description
Error 1797 indicates that you are attempting to create an InnoDB FULLTEXT index on a column whose data type is not supported for FULLTEXT indexing. InnoDB FULLTEXT indexes are designed to work with character-based data types like CHAR, VARCHAR, and TEXT, not numeric, date/time, or binary types.
Error Message
Column '%s' is of wrong type for an InnoDB FULLTEXT index
Known Causes
4 known causesNumeric Column Type
Attempting to create a FULLTEXT index on a column with a numeric data type (e.g., INT, BIGINT, DECIMAL), which is incompatible.
Date/Time Column Type
Using a date or time data type (e.g., DATE, DATETIME, TIMESTAMP) for a column intended for a FULLTEXT index.
Binary Column Type
Specifying a binary data type (e.g., BLOB, BINARY, VARBINARY) for an InnoDB FULLTEXT indexed column.
Unsupported String Type
Using certain string-like types (e.g., ENUM, SET) that, while containing text, are not directly supported for InnoDB FULLTEXT indexing.
Solutions
3 solutions available1. Change Column Type to TEXT or VARCHAR easy
Modify the column's data type to a compatible type like TEXT or VARCHAR.
1
Identify the column causing the error. The error message will usually specify the column name (e.g., '%s').
2
Check the current data type of the column.
SHOW CREATE TABLE your_table_name;3
If the column is not a supported type for FULLTEXT (e.g., INT, DATE, BLOB), alter the table to change its type to a TEXT variant (e.g., TEXT, MEDIUMTEXT, LONGTEXT) or a VARCHAR with a sufficient length.
ALTER TABLE your_table_name MODIFY COLUMN your_column_name TEXT;4
Recreate the FULLTEXT index on the modified column.
ALTER TABLE your_table_name ADD FULLTEXT(your_column_name);2. Drop and Recreate FULLTEXT Index easy
Remove the existing FULLTEXT index and then add it again, ensuring the column type is compatible.
1
Identify the table and column involved in the FULLTEXT index.
2
Drop the existing FULLTEXT index. The index name can often be inferred from the `SHOW CREATE TABLE` output or might be a default name like `idx_fulltext_column`.
ALTER TABLE your_table_name DROP INDEX your_fulltext_index_name;3
Verify the column type is compatible with FULLTEXT. If not, follow the steps in 'Change Column Type to TEXT or VARCHAR' first.
SHOW CREATE TABLE your_table_name;4
Re-add the FULLTEXT index to the column.
ALTER TABLE your_table_name ADD FULLTEXT(your_column_name);3. Convert Existing Data to Text Format medium
If the column stores numeric or date-like data that needs to be searchable with FULLTEXT, convert it to a text representation.
1
Identify the column that is causing the error and its current non-text data type.
2
Back up your data before making any significant changes.
3
Alter the column to a TEXT type. This might involve a temporary column or a direct alteration.
ALTER TABLE your_table_name MODIFY COLUMN your_column_name TEXT;4
Update the column with the string representation of its existing data. For example, if it's a DATE, you might convert it to 'YYYY-MM-DD'. If it's an INT, convert it to its string equivalent.
UPDATE your_table_name SET your_column_name = CAST(your_column_name AS CHAR);5
Add the FULLTEXT index to the now-text-based column.
ALTER TABLE your_table_name ADD FULLTEXT(your_column_name);