I have a mysql table with 2.8GB data+index size. I wanted to add a column to the table and it was failing due to insufficient space on /tmp. When I looked at the size available on /tmp it was 3.9GB.
See the below mysql error which the alter command was failing with
2016-03-17 01:47:53 7f8962739700 InnoDB: Error: Write to file (merge) failed at offset 140509184.
InnoDB: 1048576 bytes should have been written, only 667648 were written.
InnoDB: Operating system error number 0.
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 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
My Question is why did the mysql alter command fail due to insufficient size when there is more than 2.8GB of space on /tmp.
To see how much space is mysql consuming to run the alter command. I changed the /tmp partition to a partition which has more space like 15GB.
when I ran the alter command, mysql took up 5.4GB of tmp space to process the command. This is almost twice the size of the table.
Can anybody help me understand this behaviour. I am using Server version: 5.6.22-log MySQL Community Server (GPL)
show create table jjkl_sec\G
*************************** 1. row ***************************
Table: jjkl_sec
Create Table: CREATE TABLE `jjkl_sec` (
`ia_second_UTC` datetime NOT NULL,
`ia_second` datetime NOT NULL,
`ia_device_id` int(11) NOT NULL,
`ia_job_id` int(10) unsigned zerofill DEFAULT NULL,
`ia_time` bigint(19) NOT NULL DEFAULT '0',
`ia_network_type` varchar(45) DEFAULT NULL,
`ia_mcc` int(10) DEFAULT NULL,
`ia_mnc` int(10) DEFAULT NULL,
`ia_lac_id` int(10) DEFAULT NULL,
`ia_cell_id` int(10) DEFAULT NULL,
`ia_signal_strengh_rssi` int(10) DEFAULT NULL,
`ia_testcase_id` varchar(128) DEFAULT NULL,
`ia_lte_pci` int(10) DEFAULT NULL,
`ia_lte_rsrp` int(10) DEFAULT NULL,
`ia_lte_rsrq` int(10) DEFAULT NULL,
`ia_lte_rssnr` int(10) DEFAULT NULL,
`ia_dbm` int(10) DEFAULT '-1',
`ia_asu` int(10) DEFAULT '-1',
`ping_avg` varchar(50) DEFAULT '0',
`ia_ping_rtt_min` varchar(50) DEFAULT '0',
`ia_ping_ip_address` varchar(100) DEFAULT '0',
`ping_id` int(10) DEFAULT NULL,
`ftpdl_id` varchar(128) DEFAULT NULL,
`httpdl_id` varchar(128) DEFAULT NULL,
`ftpul_id` varchar(128) DEFAULT NULL,
`httpul_id` varchar(128) DEFAULT NULL,
`ftpdl` float(12,0) DEFAULT '0',
`httpdl` float(12,0) unsigned DEFAULT '0',
`ftpul` float(12,0) DEFAULT '0',
`httpul` float(12,0) DEFAULT '0',
`x_param` float(16,8) DEFAULT '0.00000000',
`y_param` float(16,8) DEFAULT '0.00000000',
`x_indoor` float(16,8) DEFAULT '0.00000000',
`y_indoor` float(16,8) DEFAULT '0.00000000',
`ia_last_location_time` datetime DEFAULT NULL,
`ia_location_provider` varchar(128) DEFAULT NULL,
`ia_collect_altitude` float(12,0) DEFAULT '0',
`ia_location_tags` varchar(50) DEFAULT NULL,
`ia_arfcn` int(10) DEFAULT NULL,
`ia_pci` int(10) DEFAULT NULL,
`ia_rsrp_rx0` float(12,0) DEFAULT NULL,
`ia_rsrq_rx` float(12,0) DEFAULT NULL,
`ia_rssi_rx0` float(12,0) DEFAULT NULL,
`ia_sinr_rx0` float(12,0) DEFAULT NULL,
`ia_cqi_pucch` int(10) DEFAULT NULL,
`ia_cqi_pusch` int(10) DEFAULT NULL,
`ia_wcdma_rx0` float(12,0) DEFAULT NULL,
`ia_evdo_rx0` float(12,0) DEFAULT NULL,
`ia_wcdma_psc` float(12,0) DEFAULT NULL,
`ia_cdma_rf_pn` float(12,0) DEFAULT NULL,
`ia_wcdma_ec_io` float(12,0) DEFAULT NULL,
`ia_evdo_asp_pilotpn_0` float(12,0) DEFAULT NULL,
`ia_evdo_asp_pilotstr_0` float(12,0) DEFAULT NULL,
`ftpdl_i` float(20,0) DEFAULT NULL,
`ftpul_i` float(20,0) DEFAULT NULL,
`httpdl_i` float(20,0) DEFAULT NULL,
`httpul_i` float(20,0) DEFAULT NULL,
`ia_wifi_ssid` varchar(150) DEFAULT NULL,
`ia_wifi_bssid` varchar(150) DEFAULT NULL,
`ia_wifi_signal_level` int(10) DEFAULT NULL,
`ia_wifi_frequency` int(10) DEFAULT '0',
`ia_wifi_linkspeed` varchar(255) DEFAULT NULL,
`wholepage_duration` bigint(19) DEFAULT '0',
`setup_time` bigint(19) DEFAULT NULL,
`call_event` varchar(15) DEFAULT NULL,
`call_id` varchar(128) DEFAULT NULL,
`video_ref_file` varchar(300) DEFAULT '0',
`video_test_estimated_FPS` float(9,4) DEFAULT NULL,
`video_jitter` int(10) DEFAULT NULL,
`video_PEVQ_MOS` float(9,4) DEFAULT NULL,
`udp_datagram_size` int(10) DEFAULT NULL,
`udp_Jitter` double(22,0) DEFAULT NULL,
`udp_direction` varchar(30) DEFAULT NULL,
`udp_thoughput` float(12,0) DEFAULT NULL,
`MOSLQO` float(9,4) DEFAULT NULL,
`vqt_call_id` varchar(128) DEFAULT NULL,
`ia_file_url` varchar(500) DEFAULT NULL,
`MOSLQO_UL` float(9,4) DEFAULT NULL,
`vqt_call_id_ul` varchar(128) DEFAULT NULL,
`ia_file_url_ul` varchar(500) DEFAULT NULL,
`video_throughput` float(19,1) DEFAULT NULL,
`voice_call_type` varchar(20) DEFAULT NULL,
`voice_handover` varchar(20) DEFAULT NULL,
`voice_jitter` int(11) DEFAULT NULL,
`voice_jitter_ul` int(11) DEFAULT NULL,
`playback_time` bigint(19) DEFAULT NULL,
`data_timestamp_change` bigint(20) DEFAULT NULL,
`data_change_type` varchar(80) DEFAULT NULL,
`data_duration` bigint(20) DEFAULT NULL,
`ftpdl_filesize` varchar(100) DEFAULT NULL,
`ftpdl_progress` int(11) DEFAULT NULL,
`ftpul_filesize` varchar(100) DEFAULT NULL,
`ftpul_progress` int(11) DEFAULT NULL,
PRIMARY KEY (`ia_second_UTC`,`ia_device_id`),
KEY `MOSLQO` (`MOSLQO`),
KEY `MOSLQO_UL` (`MOSLQO_UL`),
KEY `udp_thoughput` (`udp_thoughput`),
KEY `udp_direction` (`udp_direction`),
KEY `video_PEVQ_MOS` (`video_PEVQ_MOS`),
KEY `ftpdl` (`ftpdl`),
KEY `httpdl` (`httpdl`),
KEY `ftpul` (`ftpul`),
KEY `httpul` (`httpul`),
KEY `ia_job_id` (`ia_job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
The alter command took 6 1/2 min to complete.
(root@localhost) [smart_report]> ALTER TABLE `jjkl_sec`
ADD COLUMN `datagram_lost` INT DEFAULT NULL AFTER `udp_direction`;
Query OK, 0 rows affected (6 min 25.76 sec)
Records: 0 Duplicates: 0 Warnings: 0
FLOAT(m,n)is generally a mistake and should be avoided -- it causes truncation and double-rounding. Either useDECIMAL(m,n)or plainFLOAT. – Rick James Mar 20 '16 at 23:36