Description:
Hi,
When running an ALTER TABLE that fills the disk, we can either get "ERROR 1114" (most of the time) or "OS errno 28" (not very often, needs determination to get this), see How to repeat for details. This also applies to OPTIMIZE TABLE.
I am sure these different errors can be explained by different failure conditions, but for the user, these two have the same cause: the disk is full / No space left on device. I would expect to have the same error message in both cases, and I would suggest using "OS errno 28" as I believe the other is misleading.
IMHO, "ERROR 1114 (HY000) at line 1: The table 't' is full" is misleading because the table is not full, it is the disk that is full. I understand that not having Free Pages in an InnoDB table might be a "full table", but MySQL users should not have to know InnoDB internals to understand errors.
This bug could be reported as category InnoDB, but I am reporting this as Server, see Suggested fix for details.
Many thanks for looking into this,
Jean-François Gagné
How to repeat:
# Environment details.
uname -a
Linux ip-<hidden>.us-east-2.compute.internal 6.1.52-71.125.amzn2023.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Sep 12 21:41:38 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
# Create a 8.0.34 sandbox.
dbdeployer deploy single mysql_8.0.34
# Create a schema and table.
./use <<< "
create database test_jfg;
create table test_jfg.t(id bigint not null auto_increment primary key)
"
# Fill the table.
# n chosen for almost filling the disk.
# "paste | sed" to insert many rows per trx for speed.
n=$(( (1024 + 256) * 1024))
time yes "(null)" | head -n $n |
paste -s -d "$(printf ',%.0s' {1..1000})\n" |
sed -e 's/.*/INSERT INTO t (id) values &;/' |
./use test_jfg
# Grow the rows for having ALTER FORCE fills the disk.
time { echo "ALTER TABLE t ADD COLUMN c0 CHAR(240) DEFAULT ''"
seq -f " ADD COLUMN c%.0f CHAR(240) DEFAULT ''" 1 3
} | paste -s -d "," | ./use test_jfg
# Actually grow the table (because instant ADD COLUMN).
time ./use test_jfg <<< "ALTER TABLE t FORCE"
# Check table size and free space (table must be bigger than free space,
# and ideally not leave much free space left for quick failure of ALTER).
f="data/test_jfg/t.ibd"; du -sh $f; df -h $f
##################################################
# Output of above, comments added for readability.
# Inserts.
real 0m17.062s
user 0m0.473s
sys 0m0.175s
# ALTER ADD.
real 0m0.108s
user 0m0.014s
sys 0m0.001s
# ALTER FORCE.
real 0m55.288s
user 0m0.007s
sys 0m0.003s
1.6G data/test_jfg/t.ibd
Filesystem Size Used Avail Use% Mounted on
/dev/nvme2n1 2.0G 1.8G 143M 93% /mnt/jgagne_sandboxes
##########################################################################
# Rewrite the TABLE m times in a loop with ALTER FORCE and OPTIMIZE TABLE.
# "grep | sed | sort | uniq" for counting each error.
m=10; (
for i in $(seq 1 $m); do ./use test_jfg <<< "ALTER TABLE t FORCE"; done
for i in $(seq 1 $m); do ./use test_jfg <<< "OPTIMIZE TABLE t"; done
) 2>&1 | grep -e ERROR -e error | sed -e 's|tmp/........|tmp/x|' | sort | uniq -c
# Output of above, only getting ERROR 1114.
10 ERROR 1114 (HY000) at line 1: The table 't' is full
10 test_jfg.t optimize error The table 't' is full
# Setting m to 100, and running this 10 times.
# Sometimes, no luck: only ERROR 1114.
# Sometimes getting one OS errno 28, sometimes more.
# No OS errno 28 for both ALTER and OPTIMIZE, I guess it is possible, but no more time.
m=100; for f in {1..10}; do echo; (
for i in $(seq 1 $m); do ./use test_jfg <<< "ALTER TABLE t FORCE"; done
for i in $(seq 1 $m); do ./use test_jfg <<< "OPTIMIZE TABLE t"; done
) 2>&1 | grep -e ERROR -e error | sed -e 's|tmp/........|tmp/x|' | sort | uniq -c; done
100 ERROR 1114 (HY000) at line 1: The table 't' is full
100 test_jfg.t optimize error The table 't' is full
100 ERROR 1114 (HY000) at line 1: The table 't' is full
1 test_jfg.t optimize error Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device)
99 test_jfg.t optimize error The table 't' is full
35 ERROR 1 (HY000) at line 1: Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device)
65 ERROR 1114 (HY000) at line 1: The table 't' is full
100 test_jfg.t optimize error The table 't' is full
100 ERROR 1114 (HY000) at line 1: The table 't' is full
100 test_jfg.t optimize error The table 't' is full
100 ERROR 1114 (HY000) at line 1: The table 't' is full
100 test_jfg.t optimize error The table 't' is full
100 ERROR 1114 (HY000) at line 1: The table 't' is full
29 test_jfg.t optimize error Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device)
71 test_jfg.t optimize error The table 't' is full
30 ERROR 1 (HY000) at line 1: Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device)
70 ERROR 1114 (HY000) at line 1: The table 't' is full
100 test_jfg.t optimize error The table 't' is full
100 ERROR 1114 (HY000) at line 1: The table 't' is full
100 test_jfg.t optimize error The table 't' is full
100 ERROR 1114 (HY000) at line 1: The table 't' is full
100 test_jfg.t optimize error The table 't' is full
100 ERROR 1114 (HY000) at line 1: The table 't' is full
4 test_jfg.t optimize error Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device)
96 test_jfg.t optimize error The table 't' is full
Suggested fix:
This could either be fixed in InnoDB to report the same error, but might be better to have InnoDB report different errors (and probably put different messages in the error log) and the Server report a single error to the user: full disk / No space left on device.
Description: Hi, When running an ALTER TABLE that fills the disk, we can either get "ERROR 1114" (most of the time) or "OS errno 28" (not very often, needs determination to get this), see How to repeat for details. This also applies to OPTIMIZE TABLE. I am sure these different errors can be explained by different failure conditions, but for the user, these two have the same cause: the disk is full / No space left on device. I would expect to have the same error message in both cases, and I would suggest using "OS errno 28" as I believe the other is misleading. IMHO, "ERROR 1114 (HY000) at line 1: The table 't' is full" is misleading because the table is not full, it is the disk that is full. I understand that not having Free Pages in an InnoDB table might be a "full table", but MySQL users should not have to know InnoDB internals to understand errors. This bug could be reported as category InnoDB, but I am reporting this as Server, see Suggested fix for details. Many thanks for looking into this, Jean-François Gagné How to repeat: # Environment details. uname -a Linux ip-<hidden>.us-east-2.compute.internal 6.1.52-71.125.amzn2023.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Sep 12 21:41:38 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux # Create a 8.0.34 sandbox. dbdeployer deploy single mysql_8.0.34 # Create a schema and table. ./use <<< " create database test_jfg; create table test_jfg.t(id bigint not null auto_increment primary key) " # Fill the table. # n chosen for almost filling the disk. # "paste | sed" to insert many rows per trx for speed. n=$(( (1024 + 256) * 1024)) time yes "(null)" | head -n $n | paste -s -d "$(printf ',%.0s' {1..1000})\n" | sed -e 's/.*/INSERT INTO t (id) values &;/' | ./use test_jfg # Grow the rows for having ALTER FORCE fills the disk. time { echo "ALTER TABLE t ADD COLUMN c0 CHAR(240) DEFAULT ''" seq -f " ADD COLUMN c%.0f CHAR(240) DEFAULT ''" 1 3 } | paste -s -d "," | ./use test_jfg # Actually grow the table (because instant ADD COLUMN). time ./use test_jfg <<< "ALTER TABLE t FORCE" # Check table size and free space (table must be bigger than free space, # and ideally not leave much free space left for quick failure of ALTER). f="data/test_jfg/t.ibd"; du -sh $f; df -h $f ################################################## # Output of above, comments added for readability. # Inserts. real 0m17.062s user 0m0.473s sys 0m0.175s # ALTER ADD. real 0m0.108s user 0m0.014s sys 0m0.001s # ALTER FORCE. real 0m55.288s user 0m0.007s sys 0m0.003s 1.6G data/test_jfg/t.ibd Filesystem Size Used Avail Use% Mounted on /dev/nvme2n1 2.0G 1.8G 143M 93% /mnt/jgagne_sandboxes ########################################################################## # Rewrite the TABLE m times in a loop with ALTER FORCE and OPTIMIZE TABLE. # "grep | sed | sort | uniq" for counting each error. m=10; ( for i in $(seq 1 $m); do ./use test_jfg <<< "ALTER TABLE t FORCE"; done for i in $(seq 1 $m); do ./use test_jfg <<< "OPTIMIZE TABLE t"; done ) 2>&1 | grep -e ERROR -e error | sed -e 's|tmp/........|tmp/x|' | sort | uniq -c # Output of above, only getting ERROR 1114. 10 ERROR 1114 (HY000) at line 1: The table 't' is full 10 test_jfg.t optimize error The table 't' is full # Setting m to 100, and running this 10 times. # Sometimes, no luck: only ERROR 1114. # Sometimes getting one OS errno 28, sometimes more. # No OS errno 28 for both ALTER and OPTIMIZE, I guess it is possible, but no more time. m=100; for f in {1..10}; do echo; ( for i in $(seq 1 $m); do ./use test_jfg <<< "ALTER TABLE t FORCE"; done for i in $(seq 1 $m); do ./use test_jfg <<< "OPTIMIZE TABLE t"; done ) 2>&1 | grep -e ERROR -e error | sed -e 's|tmp/........|tmp/x|' | sort | uniq -c; done 100 ERROR 1114 (HY000) at line 1: The table 't' is full 100 test_jfg.t optimize error The table 't' is full 100 ERROR 1114 (HY000) at line 1: The table 't' is full 1 test_jfg.t optimize error Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device) 99 test_jfg.t optimize error The table 't' is full 35 ERROR 1 (HY000) at line 1: Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device) 65 ERROR 1114 (HY000) at line 1: The table 't' is full 100 test_jfg.t optimize error The table 't' is full 100 ERROR 1114 (HY000) at line 1: The table 't' is full 100 test_jfg.t optimize error The table 't' is full 100 ERROR 1114 (HY000) at line 1: The table 't' is full 100 test_jfg.t optimize error The table 't' is full 100 ERROR 1114 (HY000) at line 1: The table 't' is full 29 test_jfg.t optimize error Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device) 71 test_jfg.t optimize error The table 't' is full 30 ERROR 1 (HY000) at line 1: Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device) 70 ERROR 1114 (HY000) at line 1: The table 't' is full 100 test_jfg.t optimize error The table 't' is full 100 ERROR 1114 (HY000) at line 1: The table 't' is full 100 test_jfg.t optimize error The table 't' is full 100 ERROR 1114 (HY000) at line 1: The table 't' is full 100 test_jfg.t optimize error The table 't' is full 100 ERROR 1114 (HY000) at line 1: The table 't' is full 4 test_jfg.t optimize error Can't create/write to file '/home/jgagne/sandboxes/msb_mysql_8_0_34/tmp/x' (OS errno 28 - No space left on device) 96 test_jfg.t optimize error The table 't' is full Suggested fix: This could either be fixed in InnoDB to report the same error, but might be better to have InnoDB report different errors (and probably put different messages in the error log) and the Server report a single error to the user: full disk / No space left on device.