Checking for active transactions won't do what I need. The reason is this: if User A has our application open but they are not currently doing any updates to the database and User B starts the upgrade process the upgrade could do something like adding a column to a table that is a required column. Now User A who still has the previous version of the application tries to save some data to that table but their version of the application knows nothing about the new column. Their update will fail. What I would like to do is make sure that everyone is logged out of their application making it so that the next time they attempt to log in if their front end application version doesn't match with the back end database version they will be forced to upgrade. This way we can be sure that the front end and back end code is always in sync.
Our clients do not handle connection failures or timeouts and deadlocks very well at all. The application is used 24/7 and many of our users are VERY computer illiterate. If anything goes wrong with the system at all they phone us, even at 3:00 in the morning. :(
I took a quick look at JMX and it does sound like that could be a solution. There is a jira related to adding JMX support to Derby (https://issues.apache.org/jira/browse/DERBY-1387).
I am very new to Derby and jira's and this whole thing, but it sounds to me like JMX support is still being worked on and isn't ready for production yet. Unfortunately I have a very short time line to get the conversion to Derby completed and ready for production.
I am thinking that when JMX support is implemented that I will use that feature, but in the meantime I think I will create a table that tracks which user has logged in and the date and time that they logged in, as well as when they have logged out. When I need to perform a database upgrade I will query the table and see if there are any users that have logged in but have not yet logged out. I will raise a warning message telling the user that is performing the upgrade that there may be users currently logged into the database and list the names of the users that are currently showing as logged in. I will let the user choose to continue the upgrade if they have determined that none of those users are actually logged in any more. I will have to allow them to continue anyways because there may be cases where someone logs into the system which would create the "logged in" record but they shut their computer down without logging out, or they logged into the system and then left their computer turned on and went home for the weekend.
I will then set a flag in the database that disallows anyone from connecting to it during the upgrade. Is there a system property for this? And then reboot the database, which will kick everyone out of the database.
After performing the upgrade I will restore the flag that allows users to connect to the database.
If anyone has any better suggestions than this I am open to ideas. JMX does sound like a great solution but I don't have the time or knowledge to roll my own right now and the Derby JMX support sounds like it is not completed yet. Maybe for a future release.
> Date: Wed, 13 Feb 2008 10:15:46 +0100 > From: Kristian.Waagan@Sun.COM > Subject: Re: Methods for upgrading database and application > To: email@example.com > > Hello Brad, > > I don't have a good solution for you, but a few suggestions. > > Is checking if there are any active transactions good enough? > This can by done by querying syscs_diag.transaction_table. > If there are no transactions, can you freeze the database (stops > updates, not selects I believe) or just take down the network server? > > The update could then be made by reconnecting from the update tool with > the embedded driver. When done, bring the network server back up. > > Far from perfect, but will it do for you? > Another option is taking exclusive locks on "crucial" tables in your > application, but this might not be feasible with your application. > Further, I don't know how well your clients handle connection failures > or timeouts/deadlocks. > > If not nothing of the above is helpful, and nobody else chimes in with > suggestions, I think you have to go for an application specific solution > and/or file a feature request in Jira. Getting the current connection > count sounds like a useful feature. > (https://issues.apache.org/jira/browse/DERBY) > > In the scenario of getting info from/about the server, would JMX be an > option for you? > > > -- > Kristian > > > Brad Moore wrote: > > > > Thanks for your response. I think I need to clarify a couple of things > > though. > > > > We are not using Access as the front end, we were using it for the > > database and now that has been converted to Derby. The front end is > > written in another language that can connect to the database using ODBC > > or JDBC. Moving to Postgre now is not possible with the timelines that > > we have, and the fact that the conversion to Derby is nearly completed now. > > > > Unfortunately the way our program works I can not just upgrade the > > database behind the scenes without our users being aware of it. Our > > program is a product that we sell and our clients install on their > > servers and client machines. I do not have access to their computers to > > do the database upgrades. We use a third party installer app that we > > can create scripts in to perform the upgrades (Indigo Rose TrueUpdate). > > > > TrueUpdate is able to check a script on our web server to determine if > > there is an upgrade available. If there is an upgrade available it > > informs the user and asks them if they want to upgrade or not. If they > > choose to upgrade then it runs our scripts which includes upgrading the > > database as well as the front end application. > > > > The main reason I need to ensure that everyone is out of the database > > while the database upgrade is being performed is because I could be > > adding/dropping columns or tables in the upgrade and I don't want the > > previous version's front end application accessing a partly or fully > > upgraded database. If all of the users are disconnected from the > > database while the database upgrade is performed then the next time they > > try to connect to the database the application checks to make sure that > > the front end application version matches the back end database version, > > and if it doesn't it forces them to upgrade their front end application > > so that the front end and back end is always in sync. > > > > This has worked very well for us for several years using this method > > with the Access database, but I don't know how to ensure that all of the > > users our logged out of the Derby database. I'm starting to think I may > > have to add a table to the database that tracks who has logged in and > > when, and when they have logged out. There could be issues with this > > however, where if the user shuts down their computer without logging out > > of the application the table would still have a record in it saying that > > they are currently logged in when they are not. > > > > Any other suggestions? > > > > > > Thanks, > > > > Brad > > > > > From: firstname.lastname@example.org > > > To: email@example.com > > > Subject: Re: Methods for upgrading database and application > > > Date: Tue, 12 Feb 2008 12:23:53 +0200 > > > > > > > > > > > --------------------------------------------------------------------------- > > > HARBOR: http://coolharbor.100free.com/index.htm > > > The most powerful application server on earth. > > > The only real POJO Application Server. > > > Making the Java dream come true. > > > > > --------------------------------------------------------------------------- > > > ----- Original Message ----- > > > From: Brad Moore > > > To: Derby Discussion > > > Sent: Tuesday, February 12, 2008 2:51 AM > > > Subject: Methods for upgrading database and application > > > > > > > > > Hi, > > > > > > I am currently working on converting our client/server application > > from an > > > MS Access database to using Derby. We have approximately 40 sites > > that use > > > our software and each one can have from 1 to 10 client computers that > > > connect to the central database. > > > > > > The latest issue I have come across is how to handle the application and > > > database upgrades at our client sites. > > > > > > Our current process is automated and works like this: > > > 1. Check on our website to see if there is a new release available > > > 2. If there is a new release available, download the new client > > > application and the scripts to upgrade the database > > > 3. Determine if anyone is currently connected to the database prior to > > > performing the upgrade so we are not altering tables while someone is > > doing > > > work. > > > 4. If anyone is connected to the database we abort the upgrade and > > > inform the user that they should make sure all other users are > > disconnected > > > before performing the upgrade. > > > 5. If there are no users connected to the database then we rename the > > > Access file so that no users can connect to the database while we're > > > performing the upgrade. > > > 6. Perform the database upgrade. > > > 7. Rename the Access database file back to it's original name so it is > > > available for use again. > > > 8. Upgrade the client application files. > > > > > > ======================================================= > > > Brad MS Access is the best prototyping tool in the world, and the worst > > > database > > > I vaguely remember writing tons of code to make it user friendly, > > getting > > > around exclusive locks > > > and other stuff > > > > > > It sounds like a big system you have there, and I think you should > > also look > > > at big > > > systems like Postgres > > > > > > I say this becaus I'm not sure of Derby's ODBC support, I dont know > > if there > > > is an official > > > driver... and coming from MS I think you "better" make sure that > > works well. > > > > > > On Postgres using MS Access as a frontend works, I not sure thats > > Derby's > > > thing.... > > > > > > Coming from MS Access, I think you thinking small... on these big dB's > > > things like worrying about > > > users in the dB before you start routing maintenance... is done for you. > > > Setting flags that say... when all the users are finished, lock down the > > > dB... are just flags > > > > > > Also in most area's now the bigger dB's are MS friendly (Run Setup) but > > > can also do linux > > > > > > Anyway > > > Derby is a great dB... and its hard to classify them... but making an > > > application that needs > > > embedded dB (Derby is great), making an Accounting application where a > > > networked dB > > > just falls out of the JRE... Derby and Sun make it easy.. > > > > > > Aim at sitting in your control tower... and just doing it all, no more > > > download, and users banging their head. > > > The user mustnt even know you in the dB and changing stuff... > > > Running 10 branch offices.... do you homework is all I wanted to say.... > > > > > > ======================================================= > > > > > > Currently we are able to determine if anyone is connected to the Access > > > database by just trying to rename the .mdb file. If this fails then the > > > database is in use. I have been unable to find a good way to > > determine if > > > there are any users currently connected to the Derby database. I have > > found > > > mentions of calling NetworkServerControl runtimeinfo and looking at the > > > Active Sessions, but I don't know of a way to call that function and > > return > > > the Active Sessions results to the installer program that I am using > > (Indigo > > > Rose TrueUpdate). > > > > > > Is there any other method for determining the number of users that are > > > currently connected to a Derby database? > > > > > > Also, is there a way to lock the database so that no users can > > connect to it > > > while the upgrade is being performed? > > > > > > Is there anyone else that has a similar type of situation, and how > > did you > > > handle it? > > > > > > > > > Thanks, > > > > > > Brad > > > > > >