Hard upgrade failing?

[Kristian Waagan-4]

Evan Leonard wrote:

> Hello Derby Users,
>
> I have a question regarding hard-upgrading.  I'm trying to upgrade a
> database from 10.1 to 10.5.  I'm connecting using 10.5 (see sysinfo
> output below) using the ";upgrade=true" connection option. However
> after doing so I still see "10.1" being returned from this query:
>
> values syscs_util.syscs_get_database_property( 'DataDictionaryVersion' )
>
> Its as though the hard upgrade isn't upgrading at all, as far as I can
> tell. I don't have any error output in the console. Is there somewhere
> additional I should be looking for debug information?

Hi Evan,

Just out of curiosity, what happens if you try to boot the database with
10.1 after the upgrade attempt?

Another thing you could try is to go via an intermediate version, say
10.3, and upgrade the database in two steps.
I'm too tired to look up our upgrade policies right now, but I encourage
others on the list to pitch in with information and/or recommendations
on how to proceed with this issue :)


Regards,
--
Kristian

>
> Thank you
> Evan Leonard
>
>
> ----------------- Java Information ------------------
> Java Version:    1.6.0_07
> Java Vendor:     Apple Inc.
> Java home:       /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home
> Java classpath:
> /Volumes/SVN/mindreef/external/grails-1.0.1/lib/groovy-all-1.5.4.jar:/Volumes/SVN/mindreef/external/grails-1.0.1/dist/grails-cli-1.0.1.jar:/System/Library/Java
> OS name:         Mac OS X
> OS architecture: x86_64
> OS version:      10.5.7
> Java user name:  evan
> Java user home:  /Users/evan
> Java user dir:   /Volumes/SVN/mindreef/trunk/webapps/sos
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.6
> --------- Derby Information --------
> JRE - JDBC: Java SE 6 - JDBC 4.0
> [/Volumes/SVN/mindreef/trunk/webapps/sos/lib/derby.jar] 10.5.1.1 - (764942)
> [/Volumes/SVN/mindreef/trunk/webapps/sos/lib/derbytools.jar] 10.5.1.1 - (764942)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> ------------------------------------------------------

[evan.leonard]

Hello Derby Users,

I have a question regarding hard-upgrading.  I'm trying to upgrade a
database from 10.1 to 10.5.  I'm connecting using 10.5 (see sysinfo
output below) using the ";upgrade=true" connection option. However
after doing so I still see "10.1" being returned from this query:

values syscs_util.syscs_get_database_property( 'DataDictionaryVersion' )

Its as though the hard upgrade isn't upgrading at all, as far as I can
tell. I don't have any error output in the console. Is there somewhere
additional I should be looking for debug information?

Thank you
Evan Leonard


----------------- Java Information ------------------
Java Version:    1.6.0_07
Java Vendor:     Apple Inc.
Java home:       /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home
Java classpath:
/Volumes/SVN/mindreef/external/grails-1.0.1/lib/groovy-all-1.5.4.jar:/Volumes/SVN/mindreef/external/grails-1.0.1/dist/grails-cli-1.0.1.jar:/System/Library/Java
OS name:         Mac OS X
OS architecture: x86_64
OS version:      10.5.7
Java user name:  evan
Java user home:  /Users/evan
Java user dir:   /Volumes/SVN/mindreef/trunk/webapps/sos
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[/Volumes/SVN/mindreef/trunk/webapps/sos/lib/derby.jar] 10.5.1.1 - (764942)
[/Volumes/SVN/mindreef/trunk/webapps/sos/lib/derbytools.jar] 10.5.1.1 - (764942)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------

[Myrna van Lunteren]

On Mon, Jun 1, 2009 at 1:51 PM, Kristian Waagan <Kristian.Waagan@...> wrote:

> Evan Leonard wrote:
>>
>> Hello Derby Users,
>>
>> I have a question regarding hard-upgrading.  I'm trying to upgrade a
>> database from 10.1 to 10.5.  I'm connecting using 10.5 (see sysinfo
>> output below) using the ";upgrade=true" connection option. However
>> after doing so I still see "10.1" being returned from this query:
>>
>> values syscs_util.syscs_get_database_property( 'DataDictionaryVersion' )
>>
>> Its as though the hard upgrade isn't upgrading at all, as far as I can
>> tell. I don't have any error output in the console. Is there somewhere
>> additional I should be looking for debug information?
>
> Hi Evan,
>
> Just out of curiosity, what happens if you try to boot the database with
> 10.1 after the upgrade attempt?
>
> Another thing you could try is to go via an intermediate version, say 10.3,
> and upgrade the database in two steps.
> I'm too tired to look up our upgrade policies right now, but I encourage
> others on the list to pitch in with information and/or recommendations on
> how to proceed with this issue :)
>
>
> Regards,
> --
> Kristian
>
>>
>> Thank you
>

This is puzzling. I did a very simple experiment;
- connect with ij with 10.1 jars in my classpath and created a db
   ij> connect 'jdbc:derby:tstdb;create=true';
- connect with upgrade with 10.5 jars in my classpath
   ij> connect 'jdbc:derby:tstdb;upgrade=true';
- with my 10.5 jars in the classpath, issue the query you mentioned:
   ij> values syscs_util.syscs_get_database_property( 'DataDictionaryVersion' );
   1
   ------------------------------------------------ (etc...)
   10.5

Perhaps you won't mind doing the same experiment to verify it works
for you this way?

Then, well, perhaps there was a simple mistake in your upgrade step?
I assume you made a copy of your db before the upgrade; so try it
again, (with a fresh copy), this time catching extra info in your
derby.log. You can do this by creating a derby.properties file in the
dir where you start derby, and adding the following to it:
    derby.stream.error.logSeverityLevel=0
    derby.infolog.append=true
    derby.language.logStatementText=true
This should catch all activity and give more details about any
possible warnings.

Apart from this, I can only theorize - perhaps a simple typo? Or
perhaps your 'upgrade=true' connection was not the first connection to
the db with 10.5 jars?
Once you're connected via soft upgrade, you cannot simply do a hard
upgrade; but you'd get an error if this is what happened...("URL
Attribute [update=true] Attribute is unknown to Derby").

HTH
Myrna

[evan.leonard]

Kristian, Myrna,
Thank you for the replies.

Before I try some of your suggestions, can you tell me more about this:

> Or perhaps your 'upgrade=true' connection was not the first  
> connection to
> the db with 10.5 jars?


I'm sure that right now I'm connecting in a soft upgrade mode first in  
our application. I've added the derby upgrade to the schema upgrade  
workflow when upgrading the application. As part of this workflow I  
first connect to the db to check the current schema version. If I  
understand what you're saying Myrna this won't work as the  
"upgrade=true" connection must be the very first one made in the JMV  
session to the derby db?

Best,
Evan



On Jun 2, 2009, at 8:10 AM, Myrna van Lunteren wrote:

> On Mon, Jun 1, 2009 at 1:51 PM, Kristian Waagan <Kristian.Waagan@...
> > wrote:
>> Evan Leonard wrote:
>>>
>>> Hello Derby Users,
>>>
>>> I have a question regarding hard-upgrading.  I'm trying to upgrade a
>>> database from 10.1 to 10.5.  I'm connecting using 10.5 (see sysinfo
>>> output below) using the ";upgrade=true" connection option. However
>>> after doing so I still see "10.1" being returned from this query:
>>>
>>> values  
>>> syscs_util.syscs_get_database_property( 'DataDictionaryVersion' )
>>>
>>> Its as though the hard upgrade isn't upgrading at all, as far as I  
>>> can
>>> tell. I don't have any error output in the console. Is there  
>>> somewhere
>>> additional I should be looking for debug information?
>>
>> Hi Evan,
>>
>> Just out of curiosity, what happens if you try to boot the database  
>> with
>> 10.1 after the upgrade attempt?
>>
>> Another thing you could try is to go via an intermediate version,  
>> say 10.3,
>> and upgrade the database in two steps.
>> I'm too tired to look up our upgrade policies right now, but I  
>> encourage
>> others on the list to pitch in with information and/or  
>> recommendations on
>> how to proceed with this issue :)
>>
>>
>> Regards,
>> --
>> Kristian
>>
>>>
>>> Thank you
>>
>
> This is puzzling. I did a very simple experiment;
> - connect with ij with 10.1 jars in my classpath and created a db
>   ij> connect 'jdbc:derby:tstdb;create=true';
> - connect with upgrade with 10.5 jars in my classpath
>   ij> connect 'jdbc:derby:tstdb;upgrade=true';
> - with my 10.5 jars in the classpath, issue the query you mentioned:
>   ij> values  
> syscs_util.syscs_get_database_property( 'DataDictionaryVersion' );
>   1
>   ------------------------------------------------ (etc...)
>   10.5
>
> Perhaps you won't mind doing the same experiment to verify it works
> for you this way?
>
> Then, well, perhaps there was a simple mistake in your upgrade step?
> I assume you made a copy of your db before the upgrade; so try it
> again, (with a fresh copy), this time catching extra info in your
> derby.log. You can do this by creating a derby.properties file in the
> dir where you start derby, and adding the following to it:
>    derby.stream.error.logSeverityLevel=0
>    derby.infolog.append=true
>    derby.language.logStatementText=true
> This should catch all activity and give more details about any
> possible warnings.
>
> Apart from this, I can only theorize - perhaps a simple typo? Or
> perhaps your 'upgrade=true' connection was not the first connection to
> the db with 10.5 jars?
> Once you're connected via soft upgrade, you cannot simply do a hard
> upgrade; but you'd get an error if this is what happened...("URL
> Attribute [update=true] Attribute is unknown to Derby").
>
> HTH
> Myrna

[Kathey Marsden]

Evan Leonard wrote:

>
> Kristian, Myrna,
> Thank you for the replies.
>
> Before I try some of your suggestions, can you tell me more about this:
>
>> Or perhaps your 'upgrade=true' connection was not the first
>> connection to
>> the db with 10.5 jars?
>
>
> I'm sure that right now I'm connecting in a soft upgrade mode first in
> our application. I've added the derby upgrade to the schema upgrade
> workflow when upgrading the application. As part of this workflow I
> first connect to the db to check the current schema version. If I
> understand what you're saying Myrna this won't work as the
> "upgrade=true" connection must be the very first one made in the JMV
> session to the derby db?
>

I think Myrna is right that the upgrade=true has to be the connection
that  boots the database, so you can't expect upgrade to work if you
have already connected in soft upgrade.   I don't see the error Myrna
describes though.  For me it just seems to silently connect but leaves
the database in 10.1 format as you described.  Of course if I shutdown
the database and reconnect with upgrade=true it upgrades properly.  
Below are some ij experiments.  I do think it would be good to make this
clearer in the documentation and perhaps issue a warning when
upgrade=true is specified but no upgrade can be performed.

ij version 10.5
ij> connect 'jdbc:derby:wombat';
ij> values syscs_util.syscs_get_database_property(
'DataDictionaryVersion' ) ;
1

--------------------------------------------------------------------------------------------------------------
--------
10.1


1 row selected
ij> connect 'jdbc:derby:wombat;upgrade=true';
ij(CONNECTION1)> values syscs_util.syscs_get_database_property(
'DataDictionaryVersion' ) ;
1

--------------------------------------------------------------------------------------------------------------
--------
10.1


1 row selected
ij(CONNECTION1)> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij(CONNECTION1)>  connect 'jdbc:derby:wombat;upgrade=true';
ij(CONNECTION2)> values syscs_util.syscs_get_database_property(
'DataDictionaryVersion' ) ;
1

--------------------------------------------------------------------------------------------------------------
--------
10.5


1 row selected
ij(CONNECTION2)>

[Bryan Pendleton]

> clearer in the documentation and perhaps issue a warning when
> upgrade=true is specified but no upgrade can be performed.

+1 to both suggestions. I definitely didn't understand this behavior til now.

thanks,

bryan

[evan.leonard]

Kathey,

This sounds exactly like what I'm seeing. I don't get an error message  
either, though I haven't yet turned up the logging. From your examples  
here it looks like I could issue a shutdown connection before the  
upgrade connection to get the database to actually upgrade. So for my  
scenario does it sound like this would work:

1. startup application
2. use softupgrade connection to determine database schema version
3. user confirms upgrade via UI
4. issue db shutdown connection
5. issue db upgrade connection
6. continue on using the application with the upgraded database

This would all be in one JVM instance, without ever restarting the  
JVM.  Anyone know of a reason this won't work?

Evan



On Jun 2, 2009, at 9:37 AM, Kathey Marsden wrote:

> Evan Leonard wrote:
>>
>> Kristian, Myrna,
>> Thank you for the replies.
>>
>> Before I try some of your suggestions, can you tell me more about  
>> this:
>>
>>> Or perhaps your 'upgrade=true' connection was not the first  
>>> connection to
>>> the db with 10.5 jars?
>>
>>
>> I'm sure that right now I'm connecting in a soft upgrade mode first  
>> in our application. I've added the derby upgrade to the schema  
>> upgrade workflow when upgrading the application. As part of this  
>> workflow I first connect to the db to check the current schema  
>> version. If I understand what you're saying Myrna this won't work  
>> as the "upgrade=true" connection must be the very first one made in  
>> the JMV session to the derby db?
>>
> I think Myrna is right that the upgrade=true has to be the  
> connection that  boots the database, so you can't expect upgrade to  
> work if you have already connected in soft upgrade.   I don't see  
> the error Myrna describes though.  For me it just seems to silently  
> connect but leaves the database in 10.1 format as you described.  Of  
> course if I shutdown the database and reconnect with upgrade=true it  
> upgrades properly.  Below are some ij experiments.  I do think it  
> would be good to make this clearer in the documentation and perhaps  
> issue a warning when upgrade=true is specified but no upgrade can be  
> performed.
>
> ij version 10.5
> ij> connect 'jdbc:derby:wombat';
> ij> values  
> syscs_util.syscs_get_database_property( 'DataDictionaryVersion' ) ;
> 1
>
> --------------------------------------------------------------------------------------------------------------
> --------
> 10.1
>
>
> 1 row selected
> ij> connect 'jdbc:derby:wombat;upgrade=true';
> ij(CONNECTION1)> values  
> syscs_util.syscs_get_database_property( 'DataDictionaryVersion' ) ;
> 1
>
> --------------------------------------------------------------------------------------------------------------
> --------
> 10.1
>
>
> 1 row selected
> ij(CONNECTION1)> connect 'jdbc:derby:wombat;shutdown=true';
> ERROR 08006: Database 'wombat' shutdown.
> ij(CONNECTION1)>  connect 'jdbc:derby:wombat;upgrade=true';
> ij(CONNECTION2)> values  
> syscs_util.syscs_get_database_property( 'DataDictionaryVersion' ) ;
> 1
>
> --------------------------------------------------------------------------------------------------------------
> --------
> 10.5
>
>
> 1 row selected
> ij(CONNECTION2)>
>
>

[Kathey Marsden]

Evan Leonard wrote:

>   So for my scenario does it sound like this would work:
>
> 1. startup application
> 2. use softupgrade connection to determine database schema version
> 3. user confirms upgrade via UI
> 4. issue db shutdown connection
> 5. issue db upgrade connection
> 6. continue on using the application with the upgraded database
>
> This would all be in one JVM instance, without ever restarting the
> JVM.  Anyone know of a reason this won't work?

That sounds like a good plan, but just remember that your existing
connections that were established before shutdown will no longer be
usable and you will have to make new ones.

Kathey

[evan.leonard]

Thank you. I've implemented the plan below and it works well.

I agree that the requirement to have "upgrade=true" be on the first  
connection to a db be added to the documentation. Perhaps also include  
this trick about shutting down the db first to be able to upgrade the  
db in a single jvm instance that's already been connected to the db.

Thank everyone for your help.

Evan




On Jun 2, 2009, at 10:07 AM, Kathey Marsden wrote:

> Evan Leonard wrote:
>>  So for my scenario does it sound like this would work:
>>
>> 1. startup application
>> 2. use softupgrade connection to determine database schema version
>> 3. user confirms upgrade via UI
>> 4. issue db shutdown connection
>> 5. issue db upgrade connection
>> 6. continue on using the application with the upgraded database
>>
>> This would all be in one JVM instance, without ever restarting the  
>> JVM.  Anyone know of a reason this won't work?
> That sounds like a good plan, but just remember that your existing  
> connections that were established before shutdown will no longer be  
> usable and you will have to make new ones.
>
> Kathey
>
>

[Kathey Marsden]

Evan Leonard wrote:
>
> I agree that the requirement to have "upgrade=true" be on the first
> connection to a db be added to the documentation. Perhaps also include
> this trick about shutting down the db first to be able to upgrade the
> db in a single jvm instance that's already been connected to the db.
>
I filed three Jira Issues as the result of this thread.
DERBY-4253 - documentation should indicate that a database cannot be
upgraded if already booted in soft upgrade mode
DERBY-4254 - If upgrade is not performed when connecting with the
upgrade=true attribute specified, Derby should produce a warning
DERBY-4255 - DatabaseMetaData getDatabaseMajorVersion() and
getDatabaseMinorVersion() return software version rather than database
version

I was going to suggest you use the DatabaseMetaData methods instead of
the undocumented property, but found they didn't work. #:(

Thank you Evan for the useful input.

Kathey

[evan.leonard]

You're welcome!
Perhaps the DatabaseMetaData should have methods for both the driver  
version and the actual db version on disk?

Evan


On Jun 2, 2009, at 2:37 PM, Kathey Marsden wrote:

> Evan Leonard wrote:
>>
>> I agree that the requirement to have "upgrade=true" be on the first  
>> connection to a db be added to the documentation. Perhaps also  
>> include this trick about shutting down the db first to be able to  
>> upgrade the db in a single jvm instance that's already been  
>> connected to the db.
>>
> I filed three Jira Issues as the result of this thread.
> DERBY-4253 - documentation should indicate that a database cannot be  
> upgraded if already booted in soft upgrade mode
> DERBY-4254 - If upgrade is not performed when connecting with the  
> upgrade=true attribute specified, Derby should produce a warning
> DERBY-4255 - DatabaseMetaData getDatabaseMajorVersion() and  
> getDatabaseMinorVersion() return software version rather than  
> database version
>
> I was going to suggest you use the DatabaseMetaData methods instead  
> of the undocumented property, but found they didn't work. #:(
>
> Thank you Evan for the useful input.
>
> Kathey
>

[Kathey Marsden]

Evan Leonard wrote:
>
> You're welcome!
> Perhaps the DatabaseMetaData should have methods for both the driver
> version and the actual db version on disk?
There are lots of version methods such as getDatabaseProductVersion()
getDriverMajorVersion(), getDriverMinorVersion(), and
getDriverVersion()  which I think are currently returning the right thing.

Kathey

[Dag H. Wanvik]

Evan Leonard <evan.leonard@...> writes:

> Thank you. I've implemented the plan below and it works well.
>
> I agree that the requirement to have "upgrade=true" be on the first
> connection to a db be added to the documentation. Perhaps also include
> this trick about shutting down the db first to be able to upgrade the
> db in a single jvm instance that's already been connected to the db.

Cf. similar issue for encryption
https://issues.apache.org/jira/browse/DERBY-2409

Dag