MySQL Bugs: #10138: Restore of small database never starts

Bug #10138	Restore of small database never starts
Submitted:	25 Apr 2005 8:25	Modified:	2 Aug 2005 10:36
Reporter:	Philip Widan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S1 (Critical)
Version:	1.0.20	OS:	Windows (Win2k)
Assigned to:		CPU Architecture:	Any

Description:
I recently upgraded to the latest version of MySQL Administrator (1.0.20). When I try to restore a database backup (only 38MB's), the Analyse File Content action shows Total Number of Bytes = 0, and Number of Bytes Processed = 0. It never goes past this stage. My CPU is 99% idle. There's no progress if I leave it for several hours. A previous version of MySQL Administrator restored the same backup file (although it would have been a bit smaller as our database is growing slowly over time). The backup is made with the command-line backup method, not the Administrator. I accept the default values (except database) when doing the restore in the Administrator.

The only work-around that I've found is to split the file into several smaller files, and restore them one at a time. I moved from the command-line restore method, as I found it frequently hung.

Bug #6433 appears to be similar, but with that bug there is at least some progress. It is also on WinXP.

I'm running the database server (4.0.17) and using MySQL Administrator (1.0.20) on the same machine, which is Win2k 5.00.2195 (SP3). It's a Pentium 3 800MHz with 392MB RAM. Most of this RAM is available, and no procesor-intensive actions are running at the same time.

How to repeat:
Backup a small database (40MB) with the command-line backup method. Restore the database on a Win2k OS, running the Administrator on the same machine.

Previously I used 1.0.18. I'm experiencing the same problem.

When I trimmed down the restore file to one table and some inserts, the analysis would reach a few bytes (consistently the same amount). 

If I trim down the restore file to the following lines (1 table, 10 inserts), the analysis takes place, but the restore never starts:

CREATE TABLE academicquery (
  querythread_id int(11) NOT NULL auto_increment,
  page int(11) NOT NULL default '0',
  section_id int(11) default NULL,
  context varchar(255) NOT NULL default '',
  PRIMARY KEY  (querythread_id)
) TYPE=InnoDB;

INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (1,16,NULL,'span b Task:');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (2,5,NULL,'body biscotti:vbpagenumber 5   h1 OBJECTIVE   p The student must:   ul li have a broad and objective perspective of land management and planning li realise the importance of sustainability as a key decision making principle li have a functional knowledge');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (3,7,NULL,'p Land-use planning can be  b defined  as the process of determining optimal use in terms of land capabilities, people´s wants and needs, and factors such as market forces, landownership, and uses of related lands.');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (4,9,NULL,'p Taking into account the  b social aspects were neglected.  Social planning attempts to capture the values of populations in the lower economic and social strata of society and must receive more attention. This lower strata was often most heavily affect');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (5,12,NULL,'p b Deep soils  have a large moisture store and so can  b buffer fluctuations  in rainfall. As a consequence, tree growth on these sites is unlikely to be affected by year-to-year fluctuations in rainfall. By contrast, trees growing on sites with  b shal');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (6,22,NULL,'body biscotti:vbpagenumber 22   img h3 2.3.2  span Planning approaches   p 2.3.2.1  span Multiple resource utilisation   p Multiple resource utilisation can be defined as the  b wise use  of available resources in a sustainable manner. Planted areas on f');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (7,15,NULL,'p Forestry labour is more likely to emphasise social and economic  b empowerment  rather merely conditions of service. In effect, the ´pool´ of people potentially affected by forestry activities has grown enormously. And now this pool of people has the p');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (8,20,NULL,'b Task 2.1:');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (9,22,NULL,'li increased revenue');
INSERT INTO academicquery (querythread_id, page, section_id, context) VALUES (10,24,NULL,'body biscotti:vbpagenumber 24   p 2.3.2.3  span Apply a management philosophy that is based on the ecology and  span the characteristics of the area.   p Planning must have the  b characteristics of the area  as starting point and the nature of the land');

Hi

Can you please send us a copy of your backup files so that we can see whats happening ?

thanks

Here's the top 800 lines of the backup file. I have the problems with this small file.

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".

The example file was uploaded, but I think I forgot to change the status to Open. I've now changed it.

It's been 8 weeks and I haven't had any response yet. I'm getting nervous - I have backups of my production MySQL database, but I can't restore them. If my system goes down, I'm going to have to run almost 100MB of commands manually to work around this problem.

Please help.

backup you provided seems to be consistent. Did you try to restore it from mysql command line client (not in mysql administrator)?

Once I changed the Character Set from utf-8 to latin-1 the restore worked. I'm surprised this would have been necessary, given that the backup file was simply plain text. Nonetheless, I'm glad it's working.

Thank you for your assistance.