Bug #67606 MySQL crashes with segmentation fault when disk quota is reached
Submitted: 16 Nov 2012 10:15 Modified: 28 Dec 2012 23:47
Reporter: Ovais Tariq Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.5.28 OS:Linux (CentOS 5.8)
Assigned to:
Triage: Needs Triage: D5 (Feature request)

[16 Nov 2012 10:15] Ovais Tariq
Description:
MySQL crashes with segmentation fault when mysqld tries to write data to a table that would mean extending the tablespace file, and disk quota is reached outside the database directory, but on the same volume.

-- OS Info
[root@centos58_01 mnt]# cat /etc/redhat-release 
CentOS release 5.8 (Final)

[root@centos58_01 mnt]# uname -a
Linux centos58_01 2.6.18-308.el5 #1 SMP Tue Feb 21 20:06:06 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

-- Mounts and Filesystem info
[root@centos58_01 mnt]# cat /proc/mounts | grep mnt
/dev/vdb /mnt xfs rw,usrquota,prjquota,grpquota 0 0

-- MySQL version
Server version:		5.5.28 MySQL Community Server (GPL)

How to repeat:
-- create a new group and set quota of 10M for testing purposes
groupadd free2
xfs_quota -x -c 'limit -g bsoft=10m bhard=10m free2' /vol/ebs1

-- create database and change the group to free2 and setgid for the database directory so that all files created in the directory are owned by the user free2
mysql [(none)]> create database free2test;

chgrp -R free2 /vol/ebs1/mysql/free2test
chmod +s /vol/ebs1/mysql/free2test

-- create the table as follows in the database free2test
mysql [(none)]> use free2test;
Database changed
mysql [free2test]> CREATE TABLE `t1` ( `i` int(11) not null auto_increment primary key, c char(255) default 'dummy_text' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

-- now create a 9M file outside of the database directory but in /vol/ebs1 where the quota is applied and change its group to free2 (the one on which quota is set)
dd if=/dev/zero of=/vol/ebs1/testfile_9M bs=9000000 count=1
chgrp free2 /vol/ebs1/testfile_9M

-- now insert values into the table till MySQL crashes
insert into t1(i) values (null);
insert into t1 select null, c from t1; <-- 11th time this is executed and MySQL crashes
[16 Nov 2012 10:16] Ovais Tariq
The gdb backtrace of the crash is attached. The section to look at is "Thread 1 (Thread 0x4987c940 (LWP 5669)):"

Attachment: gdb_backtrace_mysql_5528.txt (text/plain), 38.05 KiB.

[16 Nov 2012 11:25] Miguel Solorzano
Thank you for the bug report. The error log file has a message is out of space before to crash?. Thanks.
[16 Nov 2012 11:37] Ovais Tariq
Error log file of crashed MySQL

Attachment: mysql_5528_error_log.log (application/octet-stream, text), 6.12 KiB.

[16 Nov 2012 13:27] Miguel Solorzano
Thank you for the feedback. Looks like duplicate with http://bugs.mysql.com/bug.php?id=66683 . Please check.
[16 Nov 2012 13:53] Shane Bester
and check http://bugs.mysql.com/bug.php?id=54430 also :)
[17 Nov 2012 7:24] Ovais Tariq
Miguel,

This does not look similar to 66683 as the traces are different, also InnoDB crashes but comes back up unlikes 66683 where I think it keeps crashing?
[17 Nov 2012 7:36] Ovais Tariq
Shane,

Indeed the messages reported in the error log are similar to what is mentioned in 54430. However, according to the fix suggested in that bug InnoDB is still going to crash after the read/write is tried a couple of times. 
I think more correct behaviour would be to emit the message "Table is full" and not proceed any further with a write query instead of crashing. 

This is the behaviour that happens under normal circumstances when there is no space available on disk:

121110  8:06:14  InnoDB: Error: Write to file ./ibdata1 failed at offset 0 113246208.
InnoDB: 1048576 bytes should have been written, only 983040 were written.
InnoDB: Operating system error number 122.
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 122 means 'Disk quota exceeded'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
121110  8:06:14 [ERROR] /usr/sbin/mysqld: The table 't1' is full
121110  8:06:24 [ERROR] /usr/sbin/mysqld: The table 't1' is full
121110  8:06:29 [ERROR] /usr/sbin/mysqld: The table 't1' is full
121110  8:07:08 [ERROR] /usr/sbin/mysqld: The table 't1' is full

See how InnoDB did not crash but just emitted messages to the error log notifying that table is full.

I think for my test case the behaviour should be similar, instead of InnoDB crashing.
[28 Dec 2012 23:47] Sveta Smirnova
Thank you for the feedback.

But version 5.7 does not crash in this case anymore:

mysql> CREATE TABLE `t1` ( `i` int(11) not null auto_increment primary key, c char(255) default 'dummy_text' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(i) values (null);
Query OK, 1 row affected (0.00 sec)

...
mysql> insert into t1 select null, c from t1;
ERROR 1114 (HY000): The table 't1' is full

Content of the error log file:

Version: '5.7.1-m11-debug-log'  socket: '/home/sveta/src/mysql-trunk/mysql-test/var/tmp/mysqld.1.sock'  port: 13000  Source distribution
2012-12-29 02:45:23 30915 [Warning] InnoDB: InnoDB: 1048576 bytes should have been written. Only 385024 bytes written. Retrying again to  write the remaining bytes.
2012-12-29 02:45:23 30915 [Warning] InnoDB: InnoDB: 663552 bytes should have been written. Only 4096 bytes written. Retrying again to  write the remaining bytes.
2012-12-29 02:45:23 30915 [Warning] InnoDB: InnoDB: 659456 bytes should have been written. Only 4096 bytes written. Retrying again to  write the remaining bytes.
2012-12-29 02:45:23 30915 [Warning] InnoDB: InnoDB: 655360 bytes should have been written. Only 4096 bytes written. Retrying again to  write the remaining bytes.
2012-12-29 02:45:23 30915 [Warning] InnoDB: InnoDB: 651264 bytes should have been written. Only 4096 bytes written. Retrying again to  write the remaining bytes.
2012-12-29 02:45:23 30915 [Warning] InnoDB: InnoDB: 647168 bytes should have been written. Only 4096 bytes written. Retrying again to  write the remaining bytes.
2012-12-29 02:45:23 30915 [Warning] InnoDB: Retry attempts for writing partial data failed.
2012-12-29 02:45:23 7fa018353700 InnoDB: Error: Write to file ./ibdata1 failed at offset 12582912.
InnoDB: 1048576 bytes should have been written, only 405504 were written.
InnoDB: Operating system error number 28.
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 28 means 'No space left on device'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2012-12-29 02:45:23 30915 [ERROR] /home/sveta/src/mysql-trunk/sql/mysqld: The table 't1' is full
2012-12-29 02:45:24 30915 [Warning] InnoDB: InnoDB: 1048576 bytes should have been written. Only 12288 bytes written. Retrying

So I am closing this report as duplicate of bug #54430