📋

Description

Error 1850 indicates that MariaDB cannot perform an `ALTER TABLE` operation to change a column's data type using the `INPLACE` algorithm. This typically occurs when the requested column type conversion or modification is too complex or incompatible for an in-place alteration, requiring a full table rebuild instead.

💬

Error Message

Cannot change column type INPLACE

🔍

Known Causes

3 known causes

⚠️

Incompatible Data Type Conversion

Attempting to change a column from one data type to another that MariaDB considers incompatible for an in-place conversion, such as certain numeric to string or complex type changes.

⚠️

Altering Specific Column Properties

Modifying certain column attributes, like character set, collation, or nullability in specific contexts, which implicitly forces an out-of-place (COPY) algorithm.

⚠️

MariaDB Version Limitations

The specific `ALTER TABLE` operation might not support `INPLACE` algorithms in your current MariaDB version, requiring a newer version for such optimizations.

🛠️

Solutions

3 solutions available

1. Recreate Table with New Schema medium

Create a new table with the desired schema and copy data over.

1

Create a new table with the same structure as the original, but with the desired column type. Replace `your_table`, `old_column`, `new_column_type`, and `new_column_name` with your actual table and column names.

CREATE TABLE your_table_new LIKE your_table;
ALTER TABLE your_table_new MODIFY COLUMN old_column new_column_type;
-- If you're renaming the column as well:
-- ALTER TABLE your_table_new CHANGE COLUMN old_column new_column_name new_column_type;

2

Copy the data from the original table to the new table. Ensure all columns are accounted for. Adjust `col1, col2, ...` to match your actual column list.

INSERT INTO your_table_new (col1, col2, ...) SELECT col1, col2, ... FROM your_table;

3

Verify the data in the new table is correct.

SELECT COUNT(*) FROM your_table;
SELECT COUNT(*) FROM your_table_new;

4

Rename the original table to a backup name.

RENAME TABLE your_table TO your_table_backup;

5

Rename the new table to the original table's name.

RENAME TABLE your_table_new TO your_table;

6

Optionally, drop the backup table after confirming everything is working.

DROP TABLE your_table_backup;

2. Use pt-online-schema-change Tool advanced

Employ the Percona Toolkit's online schema change utility for minimal downtime.

1

Install Percona Toolkit if you haven't already. This is typically done by downloading and installing the package for your operating system.

sudo apt-get update && sudo apt-get install percona-toolkit  # For Debian/Ubuntu
sudo yum install percona-toolkit # For RHEL/CentOS

2

Execute the `pt-online-schema-change` command. This command will create a new table, trigger data synchronization, and then swap the tables. Replace placeholders with your actual values.

pt-online-schema-change --alter "MODIFY COLUMN old_column new_column_type" --execute h=your_host,D=your_database,t=your_table,u=your_user,p=your_password

3

Monitor the output of the `pt-online-schema-change` command for any errors.

text

3. Disable `innodb_online_alter_log_file_max_size` (Temporary Workaround) easy

Temporarily disable a specific InnoDB setting to allow in-place alteration.

1

Connect to your MariaDB server.

mysql -u your_user -p

2

Temporarily set `innodb_online_alter_log_file_max_size` to 0. This is a temporary measure and should be reverted after the operation.

SET GLOBAL innodb_online_alter_log_file_max_size = 0;

3

Attempt your `ALTER TABLE` statement again.

ALTER TABLE your_table MODIFY COLUMN old_column new_column_type;

4

Immediately after the `ALTER TABLE` completes, revert `innodb_online_alter_log_file_max_size` to its previous sensible value (e.g., 256M or higher). A value of 0 is not recommended for regular operation.

SET GLOBAL innodb_online_alter_log_file_max_size = 256*1024*1024; -- Example value, use your previous setting

🔗

Related Errors

5 related errors

MariaDB Error 1850: Unsupported In-place Column Alteration