Why MariaDB try to preallocate a very large size of file when update table schema

Question

When my SpringBoot application try to update a table schema via JPA, the mariadb instance preallocated a very huge size of file(almost 100GB, at /rdsdbdata/tmp/#sql_4c44_0.MAI),then the server storage become full immediately and cause the db instance down. The log shows JPA just add 2 columns to that table(data type varchar(255)), and the table size is less than 5MB with only 5000 records.

The maridb server is AWS RDS, Mariadb coummity edition, 10.3.8, engine InnoDB

As I review the AWS CloudWatch log, the first error is

[ERROR] InnoDB: preallocating 103934853120 bytes for file ./mydbname/users.ibd failed with error 28

then the error

Disk is full writing '/rdsdbdata/log/binlog/mysql-bin-changelog.~rec~' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)

and

[ERROR] mysqld: Disk full (/rdsdbdata/tmp/#sql_4c44_0.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")

Is there anyone else met that situation before or have any idea about it.

DDL statement is quite simple, alter table users add column qr_url varchar(255) null default '' @RickJames — yimlu, Dec 23 '19 at 06:36
over 100GB space was allocated for /tmp ,but the table which I want to update is less than 5MB @RickJames — yimlu, Dec 24 '19 at 05:04

S. Doe · Answer 1 · 2023-07-11T19:16:15.247

I've got a similar observation and could reproduce it outside my SpringBoot application. MariaDB version is 10.5.9-MariaDB.

When executing ALTER TABLE MYSCHEMA.MYTABLE MODIFY COLUMN MYCOLUMN TEXT; (in the application or via CLI mysql -uroot) for a migration of column MYCOLUMN from tinytext to text, MariaDB tries to allocate 24 GB of disk space (which unfortunately is 2 GB more than what is available in the virtual machine), then this execution stops after a while with this message:

2023-07-11 17:55:26 68 [ERROR] InnoDB: preallocating 24083693568 bytes for file ./MYSCHEMA/#sql-alter-1-44.ibd failed with error 28
2023-07-11 17:55:26 68 [ERROR] mysqld: The table 'MYTABLE' is full

The workaround for me is not to MODIFY but to ADD a new column, copy existing content, RENAME the old column and RENAME the new column to replace the old one:

ALTER TABLE MYSCHEMA.MYTABLE ADD COLUMN NEWMYCOLUMN TEXT;
UPDATE MYSCHEMA.MYTABLE set NEWMYCOLUMN = MYCOLUMN;
ALTER TABLE MYSCHEMA.MYTABLE RENAME COLUMN MYCOLUMN TO DELETEMYCOLUMN;
ALTER TABLE MYSCHEMA.MYTABLE RENAME COLUMN NEWMYCOLUMN TO MYCOLUMN;

In my case the execution took two minutes and worked without allocating (too) much disk space.

Why MariaDB try to preallocate a very large size of file when update table schema

1 Answers1