Adding DEFAULT constraint to existing not nullable column in MariaDB takes too long

by

I have a table in MariaDB with a NOT NULL column that has no DEFAULT constraint. I am trying to add a DEFAULT constraint to the column and it is taking way too long. The table contains only 20k rows, which shouldn’t even matter as no data should be updated.
The exact DDL query is:

ALTER TABLE my_table MODIFY COLUMN external_id int(11) DEFAULT 0 NOT NULL;

Please note that the column is already not nullable and no column is being added, so no data needs to be changed. The new default should apply only to new records inserted from now on and should not affect any existing record in any way.

Is this a quirk of MariaDB and it tries to update all rows in the table even though it doesn’t need to?

Update:

Version is 10.6.17-MariaDB-1:10.6.17+maria~deb11-log

Output of SHOW CREATE TABLE my_table (only relevant parts shown for brevity):

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ...
  `external_id` int(11) NOT NULL,
  ...
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=805 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

  • please edit your question to show (as text, not images) output of select version() and show create table my_table

    – 

  • have you tried alter online table my_table...?

    – 

  • Minutes after posting the question it just hit me that MariaDB thinks I’m trying to change the length of the column (see my answer below). Instead of deleting the question I thought I’d rather answer it in case someone else gets bitten by this nuance of the DDL statement

    – 

As it turns out, using the following command to add the default, finishes instantly:

ALTER TABLE my_table MODIFY COLUMN external_id int DEFAULT 0 NOT NULL;

Note the column length missing in this case.
However the following:

DESCRIBE my_table;

Shows:

...
external_id int(11) NOT NULL
...

So even though to MariaDB the column length is 11 setting the length to 11 (i.e. no change) triggers a full update of the entire table. I am not sure if I can call it a bug, it surely is an annoyance.

The original modification DDL was created by a visual tool, which used DESCRIBE to get the column definition and adjust it accordingly. Due to this shortcoming of MariaDB, such tools may fail miserably in cases like this.

Of course another alternative is to use the following:

ALTER TABLE my_table ALTER COLUMN external_id SET DEFAULT 0;

Leave a Comment