Bug #80319 "flush tables" semantics deviate from manual
Submitted: 9 Feb 2016 15:04 Modified: 7 Aug 2017 14:06
Reporter: Jörg Brühe (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1 and up OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Tags: flush, innodb, manual

[9 Feb 2016 15:04] Jörg Brühe
The MySQL manual (section "Flush syntax") clearly states in all versions since (at least) 5.1:

    Closes all open tables, forces all tables in use to be closed, and flushes the query cache. ...

    FLUSH TABLES tbl_name [, tbl_name] ...
    With a list of one or more comma-separated table names, this statement is like FLUSH TABLES with no names except that the server flushes only the named tables. ...

However, for an InnoDB table this is not true, the table is not closed.

This is relevant especially on XFS: On an XFS file system, the Linux kernel automatically pre-allocates further pages to a growing file, so the free disk space becomes less and less. Closing the file is the only documented way to reclaim those pre-allocated but still unused pages.

Similar, flushing the InnoDB pages and closing the file might be important if a LVM snapshot is used to take a backup.

How to repeat:
Run the MySQL server daemon under strace control and issue a "flush table DB.innotab": There will not be a close() system call.

Alternatively, on an XFS file system, grow an InnoDB table by a loop of insert statements.
In parallel, use "ls -l --block-size=K DB/innotab.ibd" and "du -k DB/innotab.ibd" to see the file size and the space used, notice the difference.
Then, issue a "flush table DB.innotab" and re-check: The difference remains, because the data file was not closed.

Suggested fix:
Make the manual come true and let InnoDB really close the data file.

As a minimum, let the statement issue a warning saying it is a no-op for InnoDB tables, and document that in the manual.
[10 Feb 2016 15:26] MySQL Verification Team
Hi Jorg,

I must say that you are quite right and this command is not explained with all its implications for the various storage engines.

A verified documentation bug.
[7 Aug 2017 14:06] Paul Dubois
Posted by developer:
Updated FLUSH TABLES description for InnoDB.