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; +-------+------+------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------+ | Error | 3 | Error writing file './sales3/db.opt' (Errcode: 28 - No space left on device) | +-------+------+------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
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.
The post Testing “disk full” conditions in MySQL appeared first on Azerbaijan MySQL UG.