MySQL Bugs: #2392: Restore of tables with deleted record always reports an error

Bug #2392	Restore of tables with deleted record always reports an error
Submitted:	14 Jan 2004 15:49	Modified:	17 Jan 2004 11:04
Reporter:	Shekar Pasumarthi	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0	OS:
Assigned to:		CPU Architecture:	Any

Description:
When I do a restore of backuped up tables with deleted records I always get an error.

We are using 3.23.49 but I have tried this on 4.0.17-nt production release as well, and I get the same error. The weird thing is sometimes even after the error things work fine but most of the time it has a problem and my application has a problem to start after restore.

Also I have tried to stop my application after deleting the records and have backedup tables from executing commands from mysql prompt and have tried the restore and I have the same problem.

How to repeat:
I support backup and restore from my application.

For Backup I execute the following statements

"flush tables with read lock"
"backup tables table1, table2 to 'c:/backup'"
"unlock tables"

backup reports successful.

Then I do restore with my application not running so I donot lock the tables and just execute the following

"restore tables table1, table2 from 'c:/backup'"

Things work fine if nothing is deleted from the tables. But if something deleted from the tables, backup works but the restore always reports the following error:

example "table1" had a deleted record.

provision.table1 repair error Couldn't fix table with quick recovery: Found wrong number of deleted records
provision.table1 repair error Run recovery again without -q

and sometimnes have the following status info.

ProVision.table1 repair info Delete link points outside datafile at 61056

Suggested fix:
I have a feeling in creation of index file there is a bug, and not sure how to fix it.

Unfortunately it cannot be fixed, as it's not a bug per se a but a design problem in BACKUP TABLE. We are making BACKUP TABLE obsolete and introducing a new "online backup" code that is able to backup a table without holding a lock during the backup process (and it doesn't have a bug you reported, of course). Thus, BACKUP TABLE is obsolete and won't be fixed. We'll update the manual about it.

Workarounds are - OPTIMIZE TABLE before BACKUP TABLE. Or (better) use mysqlhotbackup script.

mysqlhotcopy, sorry

is there a mysqlhotcopy for NT/Windows 2000. Also when will the "online backup" be available. Will be available as a fix for 3.23 or will it be online availabe for 4.x

Unfortunately online backup is hardly to appear before MySQL-5.1.

As for mysqlhotcopy, you have two possibilities.

1. mysqlhotcopy is a perl script, you can install perl and all the required modules and it will work.

2. You may do manually what mysqlhotcopy does, that is:

   - LOCK TABLES table1 READ, table2 READ, ...
   - FLUSH TABLES table1, table2, ...

copy table files, that is table1.MYI, table1.MYD, table1.frm, etc... to a backup directory

   - UNLOCK TABLES