MySQL Bugs: #75706: alter table import tablespace creates a temporary table

Bug #75706	alter table import tablespace creates a temporary table
Submitted:	31 Jan 2015 0:43	Modified:	5 Feb 2015 20:55
Reporter:	BJ Quinn	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6.22	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
It appears that importing a tablespace creates a temporary table rather than rewriting the table in place.  I'm not completely sure what "import tablespace" is doing, but my assumption was that it was doing nothing other than changing the file space header and adding the table to the data dictionary in the ibdata file.  Possibly there's something else going on there, but if not, it seems like this operation could be done in place.  The benefits for this would be that "import tablespace" would run effectively instantaneously instead of being a slower operation for large tables.  In my case, I'm using ZFS as the underlying file system, and copying/rewriting the file changes all the blocks, so it causes inefficient snapshot creation -- effectively you've created an entirely new file in the eyes of the filesystem.  Updating the file in place would prevent the file system from thinking all the blocks have changed.

How to repeat:
flush tables originaltable for export;
unlock tables;
create table tablecopy like originaltable;
alter table tablecopy discard originaltable;
[copy the ibd file on the server's filesystem]
alter table tablecopy import tablespace;

Suggested fix:
Perhaps I misunderstand what else is going on during an "import tablespace", but if it's possible to even optionally skip that (is it doing an implicit optimize table?) and to update the .ibd file in place without making a copy to import the tablespace, that would improve speed and data storage efficiency for file systems that take snapshots (which also means that these types of backups/restores are faster).

I should add that I understand why other ALTER TABLE operations use a temporary table, especially when read/write operations may still be directed to the table in question.  However, when discarding and importing a tablespace, it seems like this wouldn't be an issue.  If you've discarded the tablespace, I believe all read/write operations to that table fail anyway, so there's no need to preserve the old tablespace (it's gone, you've discarded it!) while preparing the new one.  Just modify the new one that you're importing in place without making a copy.

Edit: The "discard" line in the "how to repeat" section should read "discard tablespace", not "discard originaltable".

Thank you for the reasonable feature request: make import tablespace online.

Each page stores the position in the relay log files at the time it was last modified. This number continuously increases over time. It is used to determine whether it is necessary to apply changes to the page during crash recovery. The tablespace ID is also stored and must be changed from the ID on the source server to the new ID on the destination server. . These are described at https://dev.mysql.com/doc/internals/en/innodb-fil-header.html .

When doing an import there are then two main possible ways to do it:

1. Rewrite every page in place. 

2. Write a new copy.

Method 1 would leave a tablespace in an inconsistent state if there was a crash or power outage. It would also be high risk if there was damage to the tablespace that could cause additional corruption due to the damage. Recovering data after corruption somewhere in a server is one of the common uses of IMPORT TABLESPACE, loading the presumed undamaged tables into a new server. The safer way is to create a copy, method 2. It's reliable and leaves the original data intact until the operation is known to be safely completed.

However, there can be cases where there is not enough disk space to create a copy so an option to do it in-place would be useful sometimes.

So the original reasoning for wanting this isn't correct - all the pages really are changed - but it's still worthwhile sometime as an option, just not a safe default.

James Day, MySQL Senior Principal Support engineer, Oracle

That's strange. I have tested that on versions 5.6.21, 5.6.33, and 5.7.18, and I cannot confirm that a temporary table is created during ALTER TABLE x IMPORT TABLESPACE operation. No additional file seems created in the database directory nor the tmpdir whatsoever. Checked also with lsof -p mysqlpid and can't see nothing while the import is in progress (tested on 1.3G file so it takes ~20s on my test box).
When I test another ALTER, which does full table rewrite, there is temporary file like #sql-ib27-1745030855.ibd as expected.

Did you use any specific table schema? Can you share exact test case?