UPDATE of a BLOB column with long DEFAULT value fails

thiru pointed out that the following test fails on the UPDATE statement:

--source include/have_innodb.inc

create table t1 (a int primary key) engine=innodb;

insert into t1 set a=1;

alter table t1 add column b blob default repeat('aaa', 9500);

begin;

update t1 set b = null;

rollback;

drop table t1;

mysqltest: At line 5: query 'update t1 set b = null' failed: 1713: Undo log record is too big

If the ALTER TABLE statement is amended with , FORCE or , ALGORITHM=COPY, qualifier to prohibit instant ALTER, then the test will pass.

Apparently the reason for the failure is that InnoDB is trying to write the default value of the instantly added column into the undo log record. Because undo log records cannot span multiple pages, this will fail.