mysql directory grow to 246G after one query, which failed due to table is full

Question

I was trying to run the following statement with the hope to create a join of two existing tables.

create table CRS_PAIR
select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, concat_ws(',', a.YTG, b.YTG) as YTG
from CRS_TESTING a, CRS_TRAINING b
where a.TESTING_C=b.TRAINING_C;

Currently the size of these two tables are:

mysql> SELECT table_name, round(((data_length + index_length) / (1024*1024)),2) as "size in megs" FROM information_schema.tables WHERE table_schema = "crs";
+----------------+---------------+
| table_name     | size in megs  |
+----------------+---------------+
| CRS_TESTING    |         36.59 |
| CRS_TRAINING   |        202.92 |
+----------------+---------------+

After a little over a day, The query finished and I got the following result.

140330  2:53:50 [ERROR] /usr/sbin/mysqld: The table 'CRS_PAIR' is full
140330  2:53:54  InnoDB: ERROR: the age of the last checkpoint is 9434006,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

It turned out that the size of /var/lib/mysql has grown to 246GB in disk space, and the disk run out of space. However, for some reason, the CRS_PAIR table does not show up in the shell. Even when I try to get the size of all databases.

mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / (1024 * 1024) "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ; 
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| crs                |            1426.4531 |
| information_schema |               0.0088 |
| mysql              |               0.6453 |
| performance_schema |               0.0000 |
+--------------------+----------------------+
4 rows in set (0.74 sec)

This is the show tables command.

mysql> show tables;
+----------------+
| Tables_in_crs  |
+----------------+
| CRS_TESTING    |
| CRS_TRAINING   |
 some other tables
+----------------+
9 rows in set (0.00 sec)

CRS_PAIR is not there.

May I ask if anyone can help me figure out where this mysterious table went to so that I can clean up my disk space?

Answer 1

The table simply does not exist. How can you verify this ?

Goto the operating system and run the following

cd /var/lib/mysql/crs
ls -l CRS_PAIR.frm

If that file does not exist, the table does not exist. If that is the case, then what's all that 246G bloated space ? You need to look the following

InnoDB Architecture

Take a closer look at ibdata1 ( system tablespace ). What does it comprise of ?

• Data Dictionary
• Double Write Buffer (support data consistency; used for Crash Recovery)
• Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
• Rollback Segments
• Undo Space (where the most uncontrolled growth can happen)

What you are the victim of is a large transaction that cannot be rolled back effectively.

I discussed InnoDB wild growth back on April 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

You are probably wondering....

But I got a Table Full Error !!!

Think about what you were doing

• You create an empty InnoDB table via DDL (it's technically a temp table at this point)
• You attempted to load that empty table with 246G of BLOB/TEXT. There are tons of transactional records (for MVCC and Transaction Isolation Support) being accumulated in the interim.
• Since you logically ran out of space due to BLOB/TEXT records in a single transaction, the query would have to rollback and the temp table would vanish, leaving no .frm to show for it.. What you are left with is a 246G undo log entry that could not hold records as wide as your query demands. InnoDB is notorious for not reclaiming space (See my very first StackOverflow post ever from October 29, 2010 on how to reclaim that wasted space).

CAVEAT #1

Table Full Errors for InnoDB is not that far-fetched because it is a logical failure to manipulate the Storage Engine. In your case, it would be due to BLOB/TEXT fields passing through logs and a Log Buffer that simply not big enough. I have another post about how you can get a "Table Full" Error when consuming all the Rollback Segments (How to solve "The table ... is full" with "innodb_file_per_table"?)

CAVEAT #2

If you insist on running that query and trying to make the table, you will have to expand the Transaction Logs (innodb_log_file_size) and the Log Buffer Size (innodb_log_buffer_size) (Look back at the InnoDB Architecture at the Log Buffer and the two Log Files).

• My Post about doing so
• Where my inspiration for that post came from (someone else's 5 years old post)