mysql directory grow to 246G after one query, which failed due to table is full
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 &quot;The table ... is full&quot; with &quot;innodb_file_per_table&quot;?)

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)