MySQL restarted due to Disk full

MySQL & MariaDB Percona Server for MySQL 5.6
1

Hi Team,

Got mysql restart on one mysql server due to adding the column to existing table.

  1. before initiating the alter i have 95 GB disk space free.

  2. the table X which is 58GB, am doing alter on X

  3. ALTER TABLE X ADD c_job_id bigint(20) NOT NULL default 0;

  4. the disk space decreased until 34GB on the server and the temporary file

also not yet starting consuming disk space(pasted the output below)

root@bagan-master-db:~# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/vda1 494G 435G 34G 93% /

none 4.0K 0 4.0K 0% /sys/fs/cgroup

udev 16G 4.0K 16G 1% /dev

tmpfs 3.1G 388K 3.1G 1% /run

none 5.0M 0 5.0M 0% /run/lock

none 16G 0 16G 0% /run/shm

none 100M 0 100M 0% /run/user

root@bagan-master-db:~# ls -lrth /var/lib/mysql/messaging/#sql*

-rw-rw---- 1 mysql mysql 14K Dec 4 11:18 /var/lib/mysql/messaging/#sql-

e5b_f1a041.frm

-rw-rw---- 1 mysql mysql 112K Dec 4 11:18 /var/lib/mysql/messaging/#sql-

ib6136-1798400046.ibd

  1. After that suddenly the disk space is decreasing drastically and it went

100% full and Db restarted…then space released…

6.at the time of alter no ther operations are happened except select

statements

  1. May i know why this was happen even though i have ample amount of space to

do alter.

2

Hi, what is the definition of the table? SHOW CREATE TABLE your_table\G

Assuming you are running 5.7 you can review the disk space requirements here https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html

3

Hi @igroene ,

mysql> show create table X\G

*************************** 1. row ***************************

Table: tbl_message

Create Table: CREATE TABLE X (

c_id bigint(20) NOT NULL AUTO_INCREMENT,

c_ecr_id bigint(20) NOT NULL,

c_module_name varchar(20) COLLATE utf8_bin NOT NULL,

c_message_name varchar(200) COLLATE utf8_bin NOT NULL,

c_message_type varchar(100) COLLATE utf8_bin NOT NULL,

c_channel varchar(250) COLLATE utf8_bin NOT NULL,

c_status varchar(50) COLLATE utf8_bin DEFAULT NULL,

c_time_to_live bigint(20) DEFAULT NULL,

c_priority smallint(6) DEFAULT NULL,

c_partner varchar(256) COLLATE utf8_bin DEFAULT NULL,

c_label varchar(256) COLLATE utf8_bin DEFAULT NULL,

c_pusblish_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

c_event_time timestamp NULL DEFAULT NULL,

c_comments varchar(250) COLLATE utf8_bin DEFAULT NULL,

c_updated_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,

c_transaction_id varchar(100) COLLATE utf8_bin NOT NULL DEFAULT ‘’,

c_reference_id varchar(100) COLLATE utf8_bin DEFAULT NULL,

c_template_group_id varchar(100) COLLATE utf8_bin DEFAULT NULL,

c_language varchar(100) COLLATE utf8_bin DEFAULT NULL,

c_jsonparams longtext COLLATE utf8_bin,

c_email_content longtext COLLATE utf8_bin,

c_template_c_id bigint(20) DEFAULT NULL,

c_subject longtext COLLATE utf8_bin,

c_template_id varchar(128) COLLATE utf8_bin DEFAULT NULL,

c_updated_by varchar(80) COLLATE utf8_bin DEFAULT ‘SYSTEM’,

c_created_by varchar(80) COLLATE utf8_bin DEFAULT ‘SYSTEM’,

c_created_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,

c_job_id bigint(20) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (c_ecr_id,c_module_name,c_message_name,c_transaction_id,c_channel,c_message_type,c_pusblish_time),

UNIQUE KEY c_id (c_id)

) ENGINE=InnoDB AUTO_INCREMENT=84533499 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘Master data for messaging service’

4

Hi @igroene

IT is MySQL 5.6 version

mysql Ver 14.14 Distrib 5.6.42, for Linux (x86_64) using EditLine wrapper