I can confirm the bug. It only seems to affect ROW_FORMAT=REDUNDANT, which is the name that was given to the original InnoDB format when I introduced a new default format in MySQL 5.0.3, called ROW_FORMAT=COMPACT. Starting with MySQL 5.7 and MariaDB Server 10.2, there is innodb_default_row_format, with the default value dynamic.
By default, DROP COLUMN is not rebuilding the table, but only marking the column as hidden. This was implemented in MDEV-15562. The error looks bogus, because the INSERT should be able to replace the hidden column value with an empty or NULL value. In ROW_FORMAT=REDUNDANT, NULL values for fixed-length columns reserve space for the fixed length (say, an INT that is NULL would occupy 4 bytes plus the null bit). I assume that there is a bug in how BLOB that are NULL are being handled for instantly dropped columns.
Here is a variation of test case with a work-around that allows the table to be rebuilt and hence the INSERT to succeed:
--source include/have_innodb.inc
|
CREATE TABLE t0 (c1 INT, c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
|
SET GLOBAL innodb_instant_alter_column_allowed=add_last;
|
ALTER TABLE t0 DROP c2;
|
INSERT INTO t0 SET c1=0;
|
Bug
Critical
MDEV-15562 Instant DROP COLUMN or changing the order of columns
I can confirm the bug. It only seems to affect ROW_FORMAT=REDUNDANT, which is the name that was given to the original InnoDB format when I introduced a new default format in MySQL 5.0.3, called ROW_FORMAT=COMPACT. Starting with MySQL 5.7 and MariaDB Server 10.2, there is innodb_default_row_format, with the default value dynamic.
By default, DROP COLUMN is not rebuilding the table, but only marking the column as hidden. This was implemented in
MDEV-15562. The error looks bogus, because the INSERT should be able to replace the hidden column value with an empty or NULL value. In ROW_FORMAT=REDUNDANT, NULL values for fixed-length columns reserve space for the fixed length (say, an INT that is NULL would occupy 4 bytes plus the null bit). I assume that there is a bug in how BLOB that are NULL are being handled for instantly dropped columns.Here is a variation of test case with a work-around that allows the table to be rebuilt and hence the INSERT to succeed:
--source include/have_innodb.inc