MySQL Bugs: #37616: Restore Big BackUp file regulairly fails

Bug #37616	Restore Big BackUp file regulairly fails
Submitted:	24 Jun 2008 20:54	Modified:	25 Feb 2009 20:06
Reporter:	Louis Breda van	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	guitools r12 server 5.1.25	OS:	Windows (VISTA64 / XP32)
Assigned to:		CPU Architecture:	Any
Tags:	restore

Description:
Hello,

Since I back-up my MySQL DB (~ 2 month), I notice on a regular base failing restores. That happens on both my VISTA64 system as well on two XP32-systems.

Restarting the restore procedure mostly leads to a correct restore (as far as I can see without exact verification).

Louis

How to repeat:
- The problem occur during restore and since the restore is mostly sucessfull the second try, I assume the backup is correct;
- I noticed the problem is independend of the fact if the backup file was created and restored on the same or on different windows machines;
Example: backup generated under vista64 and restored under XP32 or backuped under vista and restored under vista
- backup files used are relatively big > 1GByte
- I think it happens about every third restore
- I noticed this with a couple of 5.1.xx server versions, including the latrest 5.1.25

Thank you for the bug report. Have you noticed if is showed some kind of error message, it happens on specif table?. Thanks in advance.

Hello,

As known I noticed the problem a couple of times on a couple of different windows machines. I did not always check the errorlog, but when I did I did not notice any related messages.

I just sended the errorlog from the machine on which I saw the problem two days ago. What I did was:
- update 5.1.23 to 5.1.25 (you can see when that happend in the log)
- restored some mysql backups as create on another machine
- during that restore I noticed the problem again, in fact that triggered me to create the testreport.
- I did not notice any related error message.
- but I do notice some other bugs to be fixed :->

Sincerely,

Louis

Hi,

I need more details to repeat this issue, a backup of 1.5GB I created myself on Windows Vista 64-bit or XP 32-bit not reproduces the behavior reported, please verify if occurs in a particular table and inform us. Thanks in advance.

Miguel,

I noticed this problem many times during the past month, but can not instandly reproduce it. Test effort is also high, since the backup and restore take hours.

My impression is that it is *not* related to a particular table.

If it is related to a particular table or so (I doubt), it is hard to conclude since the chance that the problem will occur in relation with a very big table is by far the highest. Also note that before you can draw any conclusions in this respect I would need a history of many error occurences also hard to do.

An easier way to go seems to be to look in the restore code and to check which conditions lead to a break in the resore proces. 

I also think that an improved error logging in that code part and an (automatic) retry in that code part would help.

Louis

Louis,

MySQL Administrator is made for MySQL 5.0 and not for MySQL 5.1. Did you try it with server version 5.0?

Susanne,

I have been using 5.1.xx versions for a long time now, already before I issed the bug report (as writen in the version info)

Be aware however that:
- I use MySQL as a backend datastore
- only having large, but not so special tables
- *NO* stored procedures, usertypes etc !!!
- it simply fails "on a more or less regular base" during a table restore as far as I can see.

Louis

Louis,

I can't repeat this. We need a test case from you.

Maybe also it would help, when you paste the corrupted backup file and the error file here.

Susanne,

There is no corrupt backup file as far as I can see!

The problem occurs when trying to restore, not when creating a backup file.

Given the fact that a next time restore try based on the same backup file is almost certain sucesessfull. In fact I never met a situation that I could not with one ore more retrails restore the file.

So, IMHO the problem is related to the resore proces. Perhaps a read error as more ore less sugested by the error message, but more likely something completely different. I can *for instance* imagine that it is a performance issue time out or something like that, or a restore buffer over run, what ever!

Hope this helps,

Louis

Hello,

For information, this bug is still there running 5.1.30 server and guitools r15.

This morning I noticed the problem again on my windows XP32 machine during restore. The restore was sucsesfull with the second trial.

For information, the backup was created on my vista64 system (running 5.1.30 64 bit server)

I can not reproduce the problem, but it happens relatively often
 

Sincerely,

Louis

Louis

Hello,

It still regulairly happens that a restore fails, using latest server (5.1.30) and gui tools (5.15). 

I would really appriciate if MySQL / SUN could fix this on short terms

Sincerely,

Louis

Louis, scanning through your error file it appears that there are many InnoDB dumps and occasionally there are errors like:

080519 13:50:08InnoDB: Error: trying to access a stray pointer 821A3FF8
InnoDB: buf pool start is at 02194000, end at 22194000
InnoDB: Probable reason is database corruption or memory
InnoDB: corruption. If this happens in an InnoDB database recovery, see
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: how to force recovery.
080519 13:50:08 InnoDB: Assertion failure in thread 3348 in file F:\build\mysql-5.1.23-rc-winbuild\mysql-community-nt-5.1.23-rc-build\storage\innobase\include\buf0buf.ic line 264
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
080519 13:50:08 [ERROR] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Got signal 11. Aborting!

That does not look good at all and could explain why you see the problem only occasionally. Have you tried to set up a completely new server and restore into that?

Further, there are errors like this:

080624 18:01:36 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
080624 18:01:36 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type

which result from the fact that MySQL Administrator tries to use structures which existed before server version 5.1 but do no longer exist beginning with 5.1. This is also a problem when using MA with such a server. However I think this has nothing to do with backup/restore.

Hello,

I agree with you about the errors. But do not understand!

Some remarks:
- I do have restore problems quite regulairy on three different machines
- I did see the problem a lot of times with with different MySQL server versions
(5.1.23 and up)
- A couple of times I did start with a fresh MySQL install, but I did that for other reasons
- A written before, almost ever the second try (same backupfile, same destination server) is sucsessfull
- I know that if you perform a fresh install, that install (does or did??) have missing tables (thats a bug), I did fix it ones by restoring a table from an 32 bit system or so, do not precisely remember. Not sure the problem still exists
- I think that a restore drops the table and create a new one so which corruption ? I would not be surprised that there is a bug which create a corruption !
- I use separate files for separate tables. I think I did use that settings on all three machines.

I will look for a recent errorlog later.

But I do not understand, and really assume one ore more bugs, if not related to the restore itself than probably related to INNODB or its (windows) memory handling 

Sincerely

Louis

Hello,

just to keep attention, I would like to state here, 
that only today I did run four times into this problem.

Perhaps an idea for solving this problem,
is to start with better errorloging / messages.

The workarround for this problem is simple but annoying
just try again (and if not, which is seldom, try again again)

Louis
PS. Some time there is some kind of error message like 
unkown object, failed, (aborted connection in the errorlog which
is may be related). But it are probably concequences of a deeper error.