Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Message-ID: <41A40CDB.1030808@thatone.com>
Date: Tue, 23 Nov 2004 20:23:55 -0800
From: Calvin Smith <calvin@thatone.com>
User-Agent: Mozilla Thunderbird 0.8 (X11/20041014)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Unable to restore a Derby database
References: <41A28890.1040203@thatone.com> <41A38825.6070909@sbcglobal.net>
In-Reply-To: <41A38825.6070909@sbcglobal.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Mike,

Thanks for the response. More comments interspersed below...

Mike Matrigali wrote:

>I am not sure what is going on, here are some things I can think of to
>try to learn more.
>
>o can you try using ij directly and not using the network server, just
>  to isolate the issue.
>  
>
Behavior is identical using ij and the embedded server. I've included at 
the bottom a complete transcript of what I tried.

>o how do you back up the database?  Are you using the derby system
>  procedure?
>  
>
I backed up using SYSCS_UTIL.SYSCS_BACKUP_DATABASE. See command recorded 
in session below.

>o is there any chance that there are still connections to the database
>  when the restoreFrom connection is made?
>  
>
I verified this time that there were no other connections both from 
within ij and using 'ps aux |grep -i java' before launching java, and am 
pretty certain there were no other connections this time. I've now tried 
this on 2 machines, and observed the same behavior (both pretty much 
same config though) through the network server and through ij embedded, 
as below.

>o Did you look at error log files, was there anything in there?
>  
>

The error file is whatever the default is, which I believe is 
'derby.log' in the system home dir, and I see nothing in there but 
indications of success. No errors are reported. Interestingly, though, I 
don't see any indication that a restore attempted either, but the backup 
of the database definitely does get created.

>Can anyone comment if this is the correct syntax for passing the
>restoreFrom attribute through the network server?
>  
>
 
Or if the syntax below is correct for the embedded database from ij?

Here is what I tried this time, exactly:

calvins@nihilo derby-test $ pwd
/home/calvins/derby-test
calvins@nihilo derby-test $ java -classpath 
lib/derby.jar:lib/derbytools.jar org.apache.derby.tools.ij
ij version 10.0 (C) Copyright IBM Corp. 1997, 2004.
ij> connect 'jdbc:derby:test';
ERROR XJ004: Database 'test' not found.
ij> connect 'jdbc:derby:test;create=true';
ij> create table asdf (id smallint primary key not null);
0 rows inserted/updated/deleted
ij> insert into asdf values (1);
1 row inserted/updated/deleted
ij> insert into asdf values (2);
1 row inserted/updated/deleted
ij> select * from asdf;
ID
------
1
2

2 rows selected
ij> disconnect;
ij> connect 'jdbc:derby:test';
ij> select * from asdf;
ID
------
1
2

2 rows selected
ij> CALL 
SYSCS_UTIL.SYSCS_BACKUP_DATABASE('/home/calvins/derby-test/derby-backups');
0 rows inserted/updated/deleted
ij> disconnect;
ij> exit;
calvins@nihilo derby-test $ ls -a /home/calvins/derby-test/derby-backups
./  ../  test/
calvins@nihilo derby-test $ java -classpath 
lib/derby.jar:lib/derbytools.jar org.apache.derby.tools.ij
ij version 10.0 (C) Copyright IBM Corp. 1997, 2004.
ij> connect 'jdbc:derby:test';
ij> select * from asdf;
ID
------
1
2

2 rows selected
ij> insert into asdf values (3);
1 row inserted/updated/deleted
ij> select * from asdf;
ID
------
1
2
3

3 rows selected
ij> disconnect;
ij> connect 
'jdbc:derby:test;restoreFrom=/home/calvins/derby-test/derby-backups';
ij> select * from asdf;
ID
------
1
2
3

3 rows selected
ij> disconnect;
ij> connect 
'jdbc:derby:test;restoreFrom=/home/calvins/derby-test/derby-backups';
ij> select * from asdf;
ID
------
1
2
3

3 rows selected
ij> disconnect;
ij> exit;
calvins@nihilo derby-test $

The restoreFrom is apparently doing something, since moving the backup 
directory results in the following if I try to restore:

ij> connect 
'jdbc:derby:test;restoreFrom=/home/calvins/derby-test/derby-backups';
ERROR XJ040: Failed to start database 'test', see the next exception for 
details.
ERROR XBM0Y: Backup database directory 
/home/calvins/derby-test/derby-backups not found. Please make sure that 
the specified backup path is right.
ij>

And if the backup directory is there, but the database backup 
sub-directory is not there, I get the following:

> ij> connect 
> 'jdbc:derby:test;restoreFrom=/home/calvins/derby-test/derby-backups';
> ERROR XJ040: Failed to start database 'test', see the next exception 
> for details.
> ERROR XBM0Q: File 
> /home/calvins/derby-test/derby-backups/service.properties not found. 
> Please make sure that backup copy is the correct one and it is not 
> corrupted.
> ij>
>

Just in case this is helpful, here are the contents of the backed up 
BACKUP.HISTORY and service.properties files in the backup sub-directory:

calvins@nihilo test $ cat BACKUP.HISTORY
Tue Nov 23 20:03:59 PST 2004:Backup started for database located at 
/home/calvins/derby-test/test

calvins@nihilo test $ cat service.properties
#/home/calvins/derby-test/test
# ********************************************************************
# ***                Please do NOT edit this file.                 ***
# *** CHANGING THE CONTENT OF THIS FILE MAY CAUSE DATA CORRUPTION. ***
# ********************************************************************
#Tue Nov 23 20:01:32 PST 2004
SysschemasIndex2Identifier=225
SyscolumnsIdentifier=144
SysconglomeratesIndex1Identifier=49
SysconglomeratesIdentifier=32
SyscolumnsIndex2Identifier=177
SysschemasIndex1Identifier=209
SysconglomeratesIndex3Identifier=81
SystablesIndex2Identifier=129
SyscolumnsIndex1Identifier=161
derby.serviceProtocol=org.apache.derby.database.Database
SysschemasIdentifier=192
derby.storage.propertiesId=16
SysconglomeratesIndex2Identifier=65
derby.serviceLocale=en_US
SystablesIdentifier=96
SystablesIndex1Identifier=113


Any more ideas?

Thanks,

Calvin