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:
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0 OS:
Assigned to: CPU Architecture:Any

[14 Jan 2004 15:49] Shekar Pasumarthi
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.
[15 Jan 2004 10:45] Sergei Golubchik
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.
[15 Jan 2004 10:55] Sergei Golubchik
mysqlhotcopy, sorry
[15 Jan 2004 11:15] Shekar Pasumarthi
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
[17 Jan 2004 11:04] Sergei Golubchik
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