MySQL Bugs: #31988: mysqlhotcopy should FLUSH TABLES on _copy/_copy

Bug #31988	mysqlhotcopy should FLUSH TABLES on _copy/_copy_old, else disk space not freed
Submitted:	31 Oct 2007 14:51	Modified:	20 Apr 2008 9:36
Reporter:	Adam Curtin	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This caused serious problems as our mysql data disk doesn't have space for one more copy of the database.
Using mysqlhotcopy --keepold --allowold has allowed us to reserve space, as the _copy_old is deleted, the _copy moved to _copy_old, and then the db copied to _copy.

A very useful aspect of mysqlhotcopy is that the resulting _copy and _copy_old are usable databases, hence allowing v quick access to backedup data, e.g.

  INSERT INTO live.important SELECT * FROM live_copy.important;

It was just such a recovery which resulted in this problem: during mysqlhotcopy the disk filled.

Further investigation showed that the mysqld process had open file descriptors for the (deleted) _copy_old tables/indexes, so the disk space wasn't freed.

How to repeat:
# generate $datadir/test_copy and $datadir/copy_old
$ mysqlhotcopy --allowold --keepold -u root -p $PW test
$ mysqlhotcopy --allowold --keepold -u root -p $PW test

# open some file descriptors
mysql> use test_copy; use test_copy_old;

$ ps -e|grep mysqld
 5729 ?        02:31:01 mysqld

# verify all _copy and _copy_old files are open
$ lsof -p 5729
COMMAND  PID  USER   FD   TYPE             DEVICE     SIZE      NODE NAME
mysqld  5729 mysql   12u   REG               8,10     1024   7390065 /apps/mysql/test_copy/poo.MYI
mysqld  5729 mysql   13u   REG               8,10        0   7390038 /apps/mysql/test_copy/poo.MYD
mysqld  5729 mysql   14u   REG               8,10     1024   7389885 /apps/mysql/test_copy_old/poo.MYI
mysqld  5729 mysql   15u   REG               8,10        0   7389857 /apps/mysql/test_copy_old/poo.MYD
mysqld  5729 mysql   16u   REG               8,10     1024   1245200 /apps/mysql/test/poo.MYI
mysqld  5729 mysql   17u   REG               8,10        0   1245205 /apps/mysql/test/poo.MYD

# another mysqlhotcopy - delete _copy_old and rename _copy
$ mysqlhotcopy --allowold --keepold -u root -p $PW test

# now what's open -
# Note that the deleted _copy_old files are still open,
# and the files which were opened as _copy are now in _copy_old

$ lsof -p 5729
COMMAND  PID  USER   FD   TYPE             DEVICE     SIZE      NODE NAME
mysqld  5729 mysql   12u   REG               8,10     1024 7390065 /apps/mysql/test_copy_old/poo.MYI
mysqld  5729 mysql   13u   REG               8,10        0 7390038 /apps/mysql/test_copy_old/poo.MYD
mysqld  5729 mysql   14u   REG               8,10     1024 7389885 /apps/mysql/test_copy_old/poo.MYI (deleted)
mysqld  5729 mysql   15u   REG               8,10        0 7389857 /apps/mysql/test_copy_old/poo.MYD (deleted)
mysqld  5729 mysql   16u   REG               8,10     1024 1245200 /apps/mysql/test/poo.MYI
mysqld  5729 mysql   17u   REG               8,10        0 1245205 /apps/mysql/test/poo.MYD

# oops.

Suggested fix:
FLUSH TABLES for _copy and _copy_old tables prior to renaming/deleting them

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We still need to know if you will get this problems with our newest version (MySQL 5.0.51a) too.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".