Bugzilla Bug 111963

I've installed the 1.7.3.1-r1 version of hsqldb and I wanted to run it in the
so called 'Server Mode'. I've added it to the default runlevel. Then I've
noticed three things:

* the /etc/init.d/hsqldb script was unable to start the hsqldb server after
  reboot (I've posted a comment to the Bug #103024 where I described the
  workaround I've used)

* in /etc/hsqldb/server.properties the default database had incorrect path:
server.database.0=file:/var/lib/hsql/db1
  (note 'hsql/db1' instead of 'hsqldb/db1')

* and even worse: I was unable to use any additional database apart from
  default 'test', even if I put them in /etc/hsqldb/server.properties
  Connection testing worked, but any other database and a bummer...

After two days of struggling I gave up and I've tried to install by hand a new
version of hsqldb (1.8.0.2 to be correct) I downloaded from the net.
Then I've found two things: the /var/lib/hsqldb/bin/hsqldb script was unable
to start the server correctly, but at least it gave me an important error
message. It said:

Fix permissions on '/var/lib/hsqldb/sqltool.rc' like
'chmod 600 /var/lib/hsqldb/sqltool.rc'

Now it was easy. It turned out that the /var/lib/hsqldb/server.properties is
a symbolic link to the /etc/hsqldb/server.properties, with the same technique
used for sqltool.rc file. But look on the permissions:

# ls -l /etc/hsqldb
-rw-------  1 root   root    292 Nov  8 18:19 server.properties
-rw-------  1 root   root    339 Nov  8 16:55 sqltool.rc

The server is being run using the 'hsqldb' user, belonging to the 'hsqldb'
group. Now it's obvious why it didn't work.

So, following the Debian solutions which I knew, I reversed the symbolic links.
I've moved the server.properties and sqltool.rc files in the /var/lib/hsqldb.
I've changed the owner of these two files to hsqldb:hsqldb. Then I've created
the links to server.properties and sqltool.rc files in /etc/hsqldb. 

This solved the problem for me.


Reproducible: Always
Steps to Reproduce:
1. emerge hsqldb
2. rc-update add hsqldb default
3. add the following to the /etc/hsqldb/server.properties

server.database.1=file:/var/lib/hsqldb/jdo/jdo
server.dbname.1=jdo

4. /etc/init.d/hsqldb restart
5. tail -n 9 /var/log/hsqldb.log

[Server@19616c7]: Startup sequence initiated from main() method
[Server@19616c7]: Loaded properties from [/var/lib/hsqldb/server.properties]
[Server@19616c7]: Initiating startup sequence...
[Server@19616c7]: Server socket opened successfully in 13 ms.
[Server@19616c7]: Database [index=0, id=0, db=file:test, alias=] opened
sucessfully in 1584 ms.
[Server@19616c7]: Startup sequence completed in 1623 ms.
[Server@19616c7]: 2005-11-08 14:35:12.872 HSQLDB server 1.7.3 is online
[Server@19616c7]: To close normally, connect and execute SHUTDOWN SQL
[Server@19616c7]: From command line, use [Ctrl]+[C] to abort abruptly

6. java org.hsqldb.util.DatabaseManagerSwing
7. In the "Connect" window set:

Type: HSQL Database Engine Server
URL:  jdbc:hsqldb:hsql://localhost/jdo

Actual Results:  
The exeption is being thrown:

java.sql.SQLException: Database does not exists in statement [jdo]

Also notice that there is only one open database logged in hsqldb.log:

[Server@19616c7]: Database [index=0, id=0, db=file:test, alias=] opened
sucessfully in 1584 ms.


Expected Results:  
My solution is described above. Move the server.properties and sqltool.rc files
to the /var/lib/hsqldb directory (this is expected by the startup script).
Change the ownership of these files to hsqldb:hsqldb. Then create symbolic links
to them in /etc/hsqldb.


The same procedure would be probably needed for hsqldb-1.8.0

Also note that adding the /etc/init.d/hsqldb script to the default runlevel
is NOT enough to get it started after every reboot. The problem with
"$(which java)" described in the Bug #103024 still rears its ugly head.

And if you need an assistance on this of a developer with 5+ years of Java 
and 3+ years of Linux experience (that's me), don't hesitate to drop me a mail.
Thank you.
Wiktor Wandachowicz

(In reply to comment #1)
> And if you need an assistance on this of a developer with 5+ years of Java 
> and 3+ years of Linux experience (that's me), don't hesitate to drop me a mail.
> Thank you.
> Wiktor Wandachowicz
> 

If you want to help, just make a patch against the current ebuild in tree and
attach it to this bug.

Ok, I've already started working on it.
Now I can:
* install hsqldb-1.7.3.1-r2
* specify additional databases in server.properties
* successfully do 'rc-update add hsqldb default'
* and reboot without problems

I just need to solve only one remaining problem. In order to stop
the server properly all the databases have to be closed by issuing
SQL 'shutdown ;' command.

The original '/var/lib/hsqldb/bin/hsqldb' script only tries to shutdown
the default 'localhost' instance. This is not sufficient, because this
way the hsqldb server is not being stopped cleanly upon shutting down
or restarting the machine.

From my investigations it appears that all additional databases should be
also shutdown, but _before_ 'localhost' instance.

So, if I specify e.g. the 'jdo' database in server.configuration:
-----
server.database.0=file:/var/lib/hsqldb/db1
server.urlid.0=localhost

server.database.1=file:/var/lib/hsqldb/jdo/jdo
server.dbname.1=jdo
-----

and also in sqltool.rc:
-----
urlid localhost
url jdbc:hsqldb:hsql://localhost
username sa
password

urlid jdo
url jdbc:hsqldb:hsql://localhost/jdo
username sa
password
-----

Then this gives me an error:

==============================================================================
# /etc/init.d/hsqldb start
 * Starting HSQL Database ...
Success for instance 'localhost'
org.hsqldb.Server started with pid 23209                                [ ok ]

# /etc/init.d/hsqldb stop
 * Stopping HSQL Databse ...
Success for instance 'localhost'
WARNING:  hsqldb is still running!                                      [ !! ]

# /etc/init.d/hsqldb stop
 * Stopping HSQL Databse ...
Failed instances:   [localhost]
WARNING:  hsqldb is still running!                                      [ !! ]

# kill -9 $(cat /var/run/hsqldb.pid)
# /etc/init.d/hsqldb stop
 * Stopping HSQL Databse ...
Removing stale pid file
I don't know of any running hsqldb server.                              [ !! ]
# /etc/init.d/hsqldb zap
 * Manually resetting hsqldb to stopped state.
==============================================================================

But this works:

==============================================================================
# /etc/init.d/hsqldb start
 * Starting HSQL Database ...
Success for instance 'localhost'
org.hsqldb.Server started with pid 23508                                [ ok ]

# java org.hsqldb.util.SqlTool --noinput --sql 'shutdown ;' --rcfile \
/var/lib/hsqldb/sqltool.rc jdo

# /etc/init.d/hsqldb stop
 * Stopping HSQL Databse ...
Success for instance 'localhost'
Successful shutdown  (for the Server process)!                          [ ok ]
==============================================================================

I'll try to see if this is an upstream error and/or file a bug there.

Alternatively I can enhance the original '/var/lib/hsqldb/bin/hsqldb' script
to shutdown all additional databases before 'localhost' instance.

What do you think?

Ok, I've received a valuable feedback from the hsqldb-developers mailing list.
The issue with stopping the hsqldb server was a misconfiguration.

The server starts and stops fine if additional databases are mentioned correctly
in both 'server.properties' and 'sqltool.rc' files:

server.configuration:
-----
server.database.0=file:/var/lib/hsqldb/db1
server.urlid.0=localhost

server.database.1=file:/var/lib/hsqldb/jdo/jdo
server.dbname.1=jdo
server.urlid.1=jdo
-----

sqltool.rc:
-----
urlid localhost
url jdbc:hsqldb:hsql://localhost
username sa
password

urlid jdo
url jdbc:hsqldb:hsql://localhost/jdo
username sa
password
-----

Now the server denotes correctly all databases upon start and stop:

# /etc/init.d/hsqldb start
 * Starting HSQL Database ...
Success for instance 'localhost'
Success for instance 'jdo'
org.hsqldb.Server started with pid 982                                  [ ok ]

# /etc/init.d/hsqldb stop
 * Stopping HSQL Databse ...
Success for instance 'localhost'
Success for instance 'jdo'
Successful shutdown  (for the Server process)!                          [ ok ]


I will submit a new proposed 'hsqldb-1.7.3.1-r2.ebuild' file, with all the
required patches soon.

Created an attachment (id=72701) [edit]
hsqldb-1.7.3.1-r2.ebuild

This is a proposed ebuild for hsqldb-1.7.3.1-r2.
It solves two major and one minor problem.

Major:
* non-working $(which java) because of 'java' executable not being in the PATH
  (resolves for good Bug #103024, until there's a way of telling the init
  scripts where the system Java VM is located)
* non-working hsqldb configuration, because of incorre files location and
  permissions (resolves the Bug #111963, see comment number 3 there)

Minor:
* hard time figuring out how to configure additional databases for hsqldb
  (resolves for good the Bug #111963)

This ebuild requires three files:
- NEW conf.d-hsqldb-java-path.patch
- UPDATED-DEFAULT server.properties
- UPDATED-DEFAULT sqltool.rc

Note 1:
The "einfo" calls in the pkg_postinst() section can be removed in
favor of updated 'server.properties' and 'sqltool.rc' files.
New files also contain examples how to configure additional databases
and include useful explanations.
Let the maintainer decide what is best.

Note 2:
This ebuild is marked ~x86 only. However it could probably be keyworded
similarly as the -r1, but I don't have access to all the arches.
Let the maintainer(s) decide what is best.

Note 3:
This package compiles and runs fine on sun-jdk-1.5.0.05 (with only compiler
warnings about unsafe operations) and sun-jdk-1.4.2.09.

Created an attachment (id=72702) [edit]
conf.d-hsqldb-java-path.patch

Small and Gentoo-specific patch for the /etc/conf.d/hsqldb file
to make sure that 'java' executable is present in PATH variable.

Created an attachment (id=72703) [edit]
server.properties

A new default /var/lib/hsqldb/server.properties file.
Contains an example how to configure additional database
and an explanation.
The header is also updated and points the user to both
"UNIX Quick Start" and "Advanced topics" chapters of
the hsqldb documentation.

Created an attachment (id=72704) [edit]
sqltool.rc

A new default /var/lib/hsqldb/sqltool.rc file.
Contains an example how to configure additional database
and an explanation.

(In reply to comment #6)
> Created an attachment (id=72702) [edit]
> conf.d-hsqldb-java-path.patch
> 
> Small and Gentoo-specific patch for the /etc/conf.d/hsqldb file
> to make sure that 'java' executable is present in PATH variable.
> 

I don't see the benefit of this. You can't start hsqldb before /usr is mounted
any way and there is java-config --java.

> > Small and Gentoo-specific patch for the /etc/conf.d/hsqldb file
> > to make sure that 'java' executable is present in PATH variable.
> > 
> 
> I don't see the benefit of this. You can't start hsqldb before /usr is
> mounted any way and there is java-config --java.

Hmm. You're right. My /usr is on the same partition as /, so I'd never
spot a problem like that.

So, drop two lines from the .ebuild, at the end of src_unpack():

==========
        cp ${FILESDIR}/server.properties conf
        cp ${FILESDIR}/sqltool.rc conf
-       cd conf
-       epatch ${FILESDIR}/conf.d-hsqldb-java-path.patch
}

src_compile() {
==========

Then again, how this should be done correctly, to prevent the
$(which java) problem? (Bug #103024 - and it should be reopened, IMHO)

(In reply to comment #10)
> Then again, how this should be done correctly, to prevent the
> $(which java) problem? (Bug #103024 - and it should be reopened, IMHO)
> 

As comment #9 stated, java-config --java should be used to get the java
executable, ie  JAVA_EXECUTABLE=$(java-config --java)

Created an attachment (id=72714) [edit]
hsqldb-1.7.3.1-r2.ebuild (ver.2)

Obvious things are always hard to spot...

This updated version of ebuild removes the unneccessary $(which java)
in favor of $(java-config --java).

But one problem still remains.

The hsqldb init script (/var/lib/hsqldb/bin/hsqldb) expects the configuration
files (server.properties and sqltool.rc) to be in the /var/lib/hsqldb. This is
especially true for already released 1.8.0 family. The new init script even
contains an explicit check for the permissions of the two cfg files.

The proposed ebuild places only the symbolic links in /etc/hsqldb.
This makes those symbolic links to be config protected, but the original
configuration is being overwritten upon remerge or upgrade.

So, if the files are put this way:
/var/lib/hsqldb/server.properties -> /etc/hsqldb/server.properties
/var/lib/hsqldb/sqltool.rc -> /etc/hsqldb/sqltool.rc

the script is unable to read the files. So the configuration is useless.

But if the files are put this way:
/etc/hsqldb/server.properties -> /var/lib/hsqldb/server.properties
/etc/hsqldb/sqltool.rc -> /var/lib/hsqldb/sqltool.rc

the configuration is readable, but is _not_ config protected.

How should this problem be solved?

Created an attachment (id=72750) [edit]
hsqldb-1.7.3.1-r1.ebuild.patch

This is a patch against hsqldb-1.7.3.1-r1.ebuild.

I reconsidered the consequences and instead of complying with the hsqldb
requirements, I let the things be the Gentoo Way.
So, in order to protect the configuration files, the server.properties and
sqltool.rc are placed in /etc/hsqldb, like in the original 1.7.3.1-r1 ebuild.

This is enough for the 1.7.3.1, and it works well.

Note:
In the upcoming 1.8.0.* its init script (/var/lib/hsqldb/bin/hsqldb) will
have to be patched to use a different location (/etc/hsqldb + symlinks)
for the two configuration files.

Created an attachment (id=72751) [edit]
server.properties.patch

An update for the 1.7.3.1-r1 server.properties file.

Includes an updated documentation hint in the header and
an example of configuring additional database for running
hsqldb in Server mode.

Created an attachment (id=72752) [edit]
sqltool.rc.patch

An update for the 1.7.3.1-r1 sqltool.rc.patch file.

Includes an example of configuring additional database
for running hsqldb in Server mode.

As betelgeuse asked, I provided the patches for the current ebuild
and additional files it uses.

My work is done now, and after compiling hsqldb over 50 times during
the last two days I feel somewhat exhausted. I've spent a lot of cycles
on it, mostly because this was my first serious ebuild fixing.

Now I'd like to ask for testing the whole thing.
Please consider adding an updated ebuild to portage.
It works for me, so no credits needed ;-)

Fixed in CVS, thanks for the contribution!