Testing “disk full” conditions in MySQL

By | April 24, 2015

How MySQL will act if there is no space left on hard disk? To answer this question, let’s test it: Our first test is with MySQL 5.6.24-debug with disabled binary log: Trying to import huge dump, after while it says table is full:

Query OK, 12725 rows affected (2.46 sec) Records: 12725 Duplicates: 0 Warnings: 0
Query OK, 12724 rows affected (2.40 sec) Records: 12724 Duplicates: 0 Warnings: 0
Query OK, 12726 rows affected (2.53 sec) Records: 12726 Duplicates: 0 Warnings: 0
ERROR 1114 (HY000): The table 'sales' is full 
ERROR 1114 (HY000): The table 'sales' is full ERROR 1114 (HY000): The table 'sales' is full

In error log you will see something like:

[root@localhost mysql]# tail -f /opt/mysql/datadir/error.err 
Version: '5.6.24-debug' socket: '/opt/mysql/datadir/mysqld-new.sock' port: 3307     Shahriyar Rzayev's MySQL 
2015-04-24 03:56:09 7fabeffff700 InnoDB: Error: Write to file ./sales2/sales.ibd failed at offset 34603008. InnoDB: 1048576 bytes should have been written, only 1011712 were written. InnoDB: Operating system error number 11. InnoDB: Check that your OS and file system support files of this size. 
InnoDB: Check also that the disk is not full or a disk quota exceeded. 
InnoDB: Error number 11 means 'Resource temporarily unavailable'. 
InnoDB: Some operating system error numbers are described at 
2015-04-24 03:56:09 21838 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full 
2015-04-24 03:56:12 21838 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full 
2015-04-24 03:56:15 21838 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full 
2015-04-24 03:56:19 21838 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full 
2015-04-24 03:56:20 21838 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full 

At this point “Operating system error number 11.” and OS disk usage is:

[root@localhost ~]# df -h 
Filesystem Size Used Avail Use% Mounted on 
/dev/mapper/VolGroup-lv_root 6.7G 6.3G 24M 100% / 
tmpfs 246M 0 246M 0% /dev/shm 
/dev/sda1 485M 55M 405M 12% /boot

If we continue our import process with another database.
Error will change:

mysql> create database sales3; 
Query OK, 
1 row affected, 1 warning (0.08 sec) 
mysql> use sales3; 
Database changed 
ERROR 3 (HY000): Error writing file './sales3/Product_Codes.frm' (Errcode: 28 - No space left on device) 
ERROR 1146 (42S02): Table 'sales3.Product_Codes' doesn't exist 
ERROR 1146 (42S02): Table 'sales3.Product_Codes' doesn't exist Query OK, 0 rows affected (0.00 sec) 
ERROR 1146 (42S02): Table 'sales3.Product_Codes' doesn't exist Query OK, 0 rows affected (0.00 sec) 
ERROR 3 (HY000): Error writing file './sales3/account_balance.frm' (Errcode: 28 - No space left on device) 
ERROR 1146 (42S02): Table 'sales3.account_balance' doesn't exist 
ERROR 1146 (42S02): Table 'sales3.account_balance' doesn't exist 
ERROR 1146 (42S02): Table 'sales3.account_balance' doesn't exist  

Disk usage is:

[root@localhost ~]# df -h 
Filesystem Size Used Avail Use% Mounted on 
/dev/mapper/VolGroup-lv_root 6.7G 6.3G 16M 100% / 
tmpfs 246M 0 246M 0% /dev/shm 
/dev/sda1 485M 55M 405M 12% /boot

Of course there will be NO entry in error log because disk is full.
That’s why let’s change error log path to another directory and start from here:

[root@localhost error_log_dir]# setenforce 0 (use only in test environment)
[root@localhost error_log_dir]# chown mysql:mysql /home/error_log_dir/
[root@localhost mysql]# /opt/mysql/bin/mysqld_safe --defaults-file=/opt/mysql/my.cnf --user=mysql --datadir=/opt/mysql/datadir --socket=/opt/mysql/datadir/mysqld-new.sock --pid-file=/opt/mysql/datadir/mysqld-new.pid --port=3307 --log-error=/home/error_log_dir/error.err & 
[root@localhost mysql]# 150424 05:03:06 mysqld_safe Logging to '/home/error_log_dir/error.err'. 150424 05:03:06 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/datadir 150424 05:03:13 mysqld_safe Number of processes running now: 0 150424 05:03:13 mysqld_safe mysqld restarted  

It will continuously restart, because of related 28 – No space left on device error:

2015-04-24 05:03:36 22371 [ERROR] /opt/mysql/bin/mysqld: Error writing file '/opt/mysql/datadir/mysqld-new.pid' (Errcode: 28 - No space left on device)
2015-04-24 05:03:36 22371 [ERROR] Can't start server: can't create PID file: No space left on device

If we change PID file path it will start as usual:

[root@localhost mysql]# /opt/mysql/bin/mysqld_safe --defaults-file=/opt/mysql/my.cnf --user=mysql --datadir=/opt/mysql/datadir --socket=/opt/mysql/datadir/mysqld-new.sock --pid-file=/home/error_log_dir/mysqld-new.pid --port=3307 --log-error=/home/error_log_dir/error.err & 

Another thing we should note that, after getting -> Errcode: 28 – No space left on device , if you try to create new database:

mysql> create database sales3; Query OK, 1 row affected, 1 warning (0.12 sec)
mysql> show warnings;  
| Error | 3 | Error writing file './sales3/db.opt' (Errcode: 28 - No space left on device) | 

db.opt file will be created as empty file. If you want to create new table:

mysql> create table t1(id int); 
ERROR 3 (HY000): Error writing file './sales3/t1.frm' (Errcode: 28 - No space left on device)

And now let’s enable binary log and then try to import dump. Related to Binary Log error’s due full disk there was a BUG fixed from 5.6.23 version of MySQL: #73365 And another one still waiting as OPEN:#72437 Where i got an error: [ERROR] Error in Log_event::read_log_event(): ‘read error’, data_len: 2070, event_type: 29 And interesting warning: [Warning] Error reading GTIDs from binary log: -1

For activating binary log and GTID add followings to my.cnf file:

log_bin = /opt/mysql/datadir/mysql-bin 
log_bin_index = /opt/mysql/datadir/mysql-bin 
sync_binlog = 1 
binlog_format = 
row gtid-mode = on 
log_slave_updates = 1 
enforce-gtid-consistency = true  

Then drop all previous imports(databases) and again try to import dump. You will get a new problem which i have reported, while writing this article: #76825 To reproduce this report, read “How to repeat” section. Another interesting thing, i want to note is, after detecting full disk error, if we try to create view, we will hit another reported issue: #76827 (I have disabled GTID/binary log while testing views)

UPDATE 1

If you try to create a procedure while disk is full, you will get a table corruption of mysql.proc table. From error log:

2015-04-27 05:30:22 1485 [Warning] Disk is full writing './mysql/proc.MYD' (Errcode: 28 - No space left on device). Waiting for someone to free space...
2015-04-27 05:30:22 1485 [Warning] Retry in 60 secs. Message reprinted in 600 secs 
2015-04-27 05:39:23 1485 [ERROR] /opt/mysql/bin/mysqld: Incorrect key file for table './mysql/proc.MYI'; try to repair it 
2015-04-27 05:39:23 1485 [ERROR] Got an error from thread_id=1, /root/mysql-5.6.24/storage/myisam/mi_write.c:223 
2015-04-27 05:39:23 1485 [ERROR] MySQL thread id 1, OS thread handle 0x7fe47ed20700, query id 756 localhost root System lock  

If you select from this table:

mysql> select * from mysql.proc; 
ERROR 1194 (HY000): Table 'proc' is marked as crashed and should be repaired 

Same thing if you create a function. And even with events:

2015-04-27 05:56:34 1485 [ERROR] /opt/mysql/bin/mysqld: Incorrect key file for table './mysql/event.MYI'; try to repair it 
2015-04-27 05:56:34 1485 [ERROR] Got an error from thread_id=7, /root/mysql-5.6.24/storage/myisam/mi_write.c:223 
2015-04-27 05:56:34 1485 [ERROR] MySQL thread id 7, OS thread handle 0x7fe47ed20700, query id 789 localhost root System lock

UPDATE 2

When disk full condition occurs you will not be able to create and drop indexes even from empty tables:

mysql> select count(*) from customers;
+----------+ 
| count(*) | 
+----------+ 
| 0 | 
+----------+ 
1 row in set (0.04 sec) 

mysql> alter table customers add index(COMMENT_ID); 
ERROR 3 (HY000): Error writing file './proc_func/#sql-5cd_a.frm' (Errcode: 28 - No space left on device) mysql> alter table customers drop index `customer_name_i`; 
ERROR 3 (HY000): Error writing file './proc_func/#sql-5cd_a.frm' (Errcode: 28 - No space left on device) 

UPDATE 3

It is impossible drop database after crashing ./mysql/proc table:

mysql> drop database proc_func; 
ERROR 145 (HY000): Table './mysql/proc' is marked as crashed and should be repaired mysql> drop database sales; 
ERROR 145 (HY000): Table './mysql/proc' is marked as crashed and should be repaired mysql> drop database sales2; 
ERROR 145 (HY000): Table './mysql/proc' is marked as crashed and should be repaired 

After repairing proc table if you try to drop database, there will be warnings about mysql.event table:

mysql> drop database test_disk; 
Query OK, 0 rows affected, 2 warnings (0.10 sec) 
mysql> show warnings; +-------+------+-------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------+ | Error | 145 | Table './mysql/event' is marked as crashed and should be repaired | 
| Error | 1194 | Table 'event' is marked as crashed and should be repaired | +-------+------+-------------------------------------------------------------------+ 
2 rows in set (0.02 sec)  

At this moment, that’s all. Will update this article every time, when i find related issues. Thank you for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *