Re: missing InnoDB (partitioned) tables(s) ?

[Miko M]

I've managed to replicate the error.
Deleting a partition when the the disk is full results in deletion of par/frm files.

Please find the logs below.

Thanks.


1) New records cannot be inserted into a table due to disk full:
ERROR 1114 (HY000) at line 1: The table 'event_info' is full
ERROR 1114 (HY000) at line 1: The table 'event_info' is full

root@eDEV ~]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 26198908 2907024 21954388 12% /
tmpfs 16440152 0 16440152 0% /dev/shm
/dev/sda1 487652 61011 401041 14% /boot
/dev/sdb1 51474364 48836568 16392 100% /database

[root@eDEV ~]# tail -f /var/log/mysqld_error.log
FAIL141216 11:07:43 [ERROR] mysqld: The table 'event_info' is full
FAIL141216 11:07:43 [ERROR] mysqld: The table 'event_info' is full

2) The aggregation table seems to be ok:
[root@eDEV dbs]# ll aggregation*
-rw-rw----. 1 mysql mysql 4642 Dec 16 11:03 aggregation.frm
-rw-rw----. 1 mysql mysql 580 Dec 16 11:03 aggregation.par
-rw-rw----. 1 mysql mysql 131072 Dec 15 10:44 aggregation#P#P1970010101.ibd
-rw-rw----. 1 mysql mysql 230686720 Dec 15 11:12 aggregation#P#P2014121511.ibd
-rw-rw----. 1 mysql mysql 1220542464 Dec 15 12:13 aggregation#P#P2014121512.ibd
-rw-rw----. 1 mysql mysql 1157627904 Dec 15 13:13 aggregation#P#P2014121513.ibd
-rw-rw----. 1 mysql mysql 973078528 Dec 15 14:03 aggregation#P#P2014121514.ibd
-rw-rw----. 1 mysql mysql 1149239296 Dec 15 15:12 aggregation#P#P2014121515.ibd
-rw-rw----. 1 mysql mysql 385875968 Dec 15 15:32 aggregation#P#P2014121516.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121517.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121518.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121519.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121520.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121521.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121522.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121523.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121600.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121601.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121602.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121603.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 15 15:06 aggregation#P#P2014121604.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121611.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121612.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121613.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121614.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121615.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121616.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121617.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121618.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121619.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121620.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121621.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121622.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121623.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 11:06 aggregation#P#P2014121700.ibd
[root@eDEV dbs]#

MariaDB [dbs]> show tables like '%aggregation%';
+----------------------------------+
| Tables_in_dbs (%aggregation%) |
+----------------------------------+
| aggregation |
+----------------------------------+
1 row in set (0.00 sec)

MariaDB [dbs]>

MariaDB [information_schema]> select * from tables where TABLE_NAME = 'aggregation';
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------------+
| def | dbs | aggregation | BASE TABLE | InnoDB | 10 | Compact | 17107676 | 180 | 3090006016 | 0 | 1621671936 | 36700160 | NULL | NULL | NULL | NULL | latin1_swedish_ci | NULL | partitioned | |
+---------------+--------------+----------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------------+
1 row in set (0.00 sec)

MariaDB [information_schema]>

3) The aggregation table seems to be ok until I try to drop a partition:
MariaDB [dbs]> alter table aggregation drop partition P2014121511;
ERROR 3 (HY000): Error writing file './dbs/#sql-aggregation.frm' (Errcode: 28 "No space left on device")
MariaDB [dbs]>

3a) The .frm and .par files have disappeared:
[root@eDEV dbs]# ll aggregation*
-rw-rw----. 1 mysql mysql 131072 Dec 15 10:44 aggregation#P#P1970010101.ibd
-rw-rw----. 1 mysql mysql 1220542464 Dec 15 12:13 aggregation#P#P2014121512.ibd
-rw-rw----. 1 mysql mysql 1157627904 Dec 15 13:13 aggregation#P#P2014121513.ibd
-rw-rw----. 1 mysql mysql 973078528 Dec 15 14:03 aggregation#P#P2014121514.ibd
-rw-rw----. 1 mysql mysql 1149239296 Dec 15 15:12 aggregation#P#P2014121515.ibd
-rw-rw----. 1 mysql mysql 385875968 Dec 15 15:32 aggregation#P#P2014121516.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121517.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121518.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121519.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121520.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121521.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121522.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121523.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121600.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121601.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121602.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 04:00 aggregation#P#P2014121603.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 15 15:06 aggregation#P#P2014121604.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121611.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121612.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121613.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121614.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121615.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121616.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121617.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121618.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121619.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121620.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121621.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121622.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 10:03 aggregation#P#P2014121623.ibd
-rw-rw----. 1 mysql mysql 131072 Dec 16 11:06 aggregation#P#P2014121700.ibd
[root@eDEV dbs]#

3b) The whole table is not available from the MySQL level
ariaDB [dbs]> alter table aggregation drop partition P2014121512;
ERROR 1146 (42S02): Table 'dbs.aggregation' doesn't exist
MariaDB [dbs]>

MariaDB [information_schema]> select * from tables where table_name = 'aggregation';
Empty set (0.00 sec)

MariaDB [information_schema]> use dbs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [dbs]> show tables like '%aggregation%';
Empty set (0.00 sec)

MariaDB [dbs]>