📋

Description

This error indicates that you are attempting to create or modify a table to include BLOB or TEXT data types, but the chosen storage engine does not support them. It commonly occurs during CREATE TABLE or ALTER TABLE statements when an incompatible engine is specified.

💬

Error Message

The used table type doesn't support BLOB/TEXT columns

🔍

Known Causes

3 known causes

⚠️

MEMORY Engine Limitation

The MEMORY (formerly HEAP) storage engine is designed for high-speed, temporary tables and explicitly does not support BLOB or TEXT data types due to its in-memory nature.

⚠️

CSV Engine Incompatibility

The CSV storage engine, which stores tables as comma-separated value files, is not designed to handle BLOB or TEXT data types directly within its structure.

⚠️

Invalid Table Engine Conversion

This error can occur when attempting to convert an existing table containing BLOB or TEXT columns to a storage engine that lacks support for these data types using an ALTER TABLE statement.

🛠️

Solutions

3 solutions available

1. Convert Table to InnoDB easy

Change the table's storage engine to InnoDB, which supports BLOB/TEXT data types.

1

Identify the table causing the error. You can often infer this from the context where the error occurred, or by examining your schema.

2

Alter the table to use the InnoDB storage engine. Replace `your_table_name` with the actual name of your table.

ALTER TABLE your_table_name ENGINE=InnoDB;

3

Verify the table's storage engine has been updated. Replace `your_table_name` with the actual name of your table.

SHOW CREATE TABLE your_table_name;

2. Create a New Table with InnoDB medium

Create a new table with the same schema but using the InnoDB engine, then migrate data.

1

Get the `CREATE TABLE` statement for the problematic table.

SHOW CREATE TABLE your_table_name;

2

Modify the `CREATE TABLE` statement to explicitly set `ENGINE=InnoDB`. Then, execute this modified statement to create the new table. Replace `your_new_table_name` with a desired name.

CREATE TABLE your_new_table_name (
  -- existing columns...
  your_blob_column BLOB,
  your_text_column TEXT
  -- other columns...
) ENGINE=InnoDB;

3

Copy data from the old table to the new table. Replace `your_table_name` and `your_new_table_name`.

INSERT INTO your_new_table_name SELECT * FROM your_table_name;

4

Once you've confirmed the data migration is successful, you can drop the old table and rename the new table.

DROP TABLE your_table_name;
RENAME TABLE your_new_table_name TO your_table_name;

3. Review and Adjust Application Schema medium

Modify your application's database schema definition to use a compatible storage engine.

1

Locate the SQL script or ORM configuration that defines your database tables.

2

Find the `CREATE TABLE` statement for the table that includes BLOB/TEXT columns.

3

Ensure the `ENGINE` clause specifies `InnoDB` or another engine that supports BLOB/TEXT types. If no `ENGINE` is specified, MariaDB might default to an older, incompatible engine like MyISAM.

CREATE TABLE your_table_name (
  -- column definitions...
) ENGINE=InnoDB;

4

Apply the corrected schema definition to your database.

🔗

Related Errors

5 related errors

MariaDB Error 1163: Unsupported BLOB/TEXT Table Type