Attempting multi-user server mode

Discuss the database features
Post Reply
1gatomontes
Posts: 20
Joined: Sun May 14, 2017 3:48 am

Attempting multi-user server mode

Post by 1gatomontes »

Learning how to set up a database in server mode, using OpenOffice 4.1.3.

Created a new db using Split_HSQLDB_2.3.2_Wizard_v3d.odb on a Windows10 system.

The new db (along with 'database' and 'driver' folders and their respective files) have been copied on a Ubuntu system.

These are our tests results:

Whether at the console of the W10 system or the console of the Ubuntu system:
database opens fine (i.e. double clicking the .odb file, oobase opens up and we see the tables, queries, etc).

Whether from the W10 system or from the Ubuntu system, when setting up a "JDBC connection" to the database:
'Test Connection' button gives a 'successfull connection' message
and
database opens without any error message, but we see no tables, queries, etc.

On the server console (where the server was launched) we see the output of the connections made remotely, followed by lots of
CALL IS_READONLY_DATABASE()

The server files are set to execute+read+write for owner+group+others.

Any ideas?
Thanks
OpenOffice 4.1.2
Windows 10 (v.1607)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: attempting multi-user server mode

Post by Villeroy »

A connection URL like jdbc:hsqldb:file://path/db_name "opens" the database files named like db_name.* just like a normal desktop application opens a remote file or local file.
A connection URL like jdbc:hsqldb:hsql:host_name/db_name does not open any file. It connects to a running server program similar to a server for http, or ftp.
In order to run a server program, you need a start script and a stop script and one or two configuration files. HSQL comes with documentation for the configuration files and an init script for Linux which requires that you are familiar with the server initialization on your Linux system. The "mechanism" is the same for all services and similar between all distributions. For an integrated Java service on Windows you need a special tool "Apache Tomcat" or start HSQL without any system integration from a shell script. As far as I know, an autostart script implies that some user needs to log in.
All the rest depends on your system setup, firewalls, open ports, network topology etc.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: attempting multi-user server mode

Post by Villeroy »

A quick test on a Linux machine calling the default java which calls a hsqldb.jar which provides service org.hsqldb.server.Server serving one database #0:

Code: Select all

$ java  -cp /usr/local/lib/hsqldb/lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:database/Meera --dbname.0 xdb
From the current directory there are some database files in subdirectory database named Meera.script, Meera.properties, Meera.data. --dbname.0 xdb specifies just another alias name to be used with a connection URL.
If the current directory had a configuration file "server.properties" the server settings would be read from that file.
Now I can connect a Base document or any other jdbc client to jdbc:hsqldb:hsql:localhost:9001/xdb and get the tables and relations from the Meera.* database.

For an integrated hsql server I would copy the hsql.init script and a configuration file to the system wide configuation in /etc.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
1gatomontes
Posts: 20
Joined: Sun May 14, 2017 3:48 am

Re: attempting multi-user server mode

Post by 1gatomontes »

Reading your reply shows me how much details I forgot to put in my initial post!

The split database was launched on the Ubuntu system with a command at the console (and the shell remained open during the tests). The standard output showed a successful service launch listening on port 9001.

The firewall on the server and on the client were disabled during the tests, just to get this potential issue out of the way.

The network topology is most simple, the client and the server are only separated by a LAN switch, which runs no firewall or port rules.

From the client, we were trying to open the database using the OpenOffice GUI (Database -> Connect to an existing JDBC database -> ... etc).

Yes, on the server, the directory where the .odb file resides include a server.properties file that we tweaked for our use, as well as a subdirectory called database which includes the files you mention.

OK, we'll look into the hsql.init script and configuration file in /etc.

Thanks again Villeroy!
Happy New Year!
OpenOffice 4.1.2
Windows 10 (v.1607)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: attempting multi-user server mode

Post by Villeroy »

OK, I think I've got most parts of it, thanks to HSQL developer Fred Toussi who helped me back on track when I confused alias names.

As far as I can tell, the following applies to HSQLDB 2.x with some Java 7 or 8 on most of the recent Debian derivates. I use Ubuntu 16.4. In fact most of it applies to every Unix system including BSD and the MacIntosh because only the used helper tools and some paths differ but the config files and the start script are the same.
Nothing of that applies to Windows which is the only desktop OS that is completely different.

Having some HSQL home directory, you need 3 configuration files in that directory, another one in /etc and an init script in /etc/init.d/
Example files with comments can be found in the samples directory shipped with hsql.
I used the hsql folder in my own /home directory which is OK for testing but not recommended, particularly when your personal /home directory is not availlable at boot time because it is encrypted.

Configuration Files
1) server.properties
my test configuration declares the server IP (localhost 127.0.0.1), an access control acl.txt (see below) and it maps the url name "test" to the path/name of the database files.

Code: Select all

server.address=127.0.0.1
server.acl=/home/andreas/hsql/acl.txt
server.database.0=/home/andreas/hsql/database/Konten;ifexists=true
server.dbname.0=test
The database files named /home/andreas/hsql/database/Konten.* will be addressable by the connection URL jdbc:hsqldb:hsql://127.0.0.1/test
The server address is the one which can be used to access the database from the client, Base for instance. In this test case it is the IP of the localhost. For a local network the IP would be the 192.168.X.Y address of the local machine.
Multiple databases can be declared as server.database.X with server.dbname.X

2) The above server.properties declares a file acl.txt which defines the network addresses from which the server is accessible.
My acl.txt looks like this. It refers to a file set Konten.* in subdirectory database/.

Code: Select all

allow 127.0.0.1
allow 192.168.15.0/24
allow 192.168.0.0/24
Just another barrier in addition to your firewall. There is also a commented acl.txt in the samples folder.

3) sqltool.rc
This config file basically defines which url belongs to another alias "urlid" and the user name + password that is needed to shutdown this database. The file contains log-in credentials and should not be readable by the outside world (e.g. owned by root).
My definition block for the server driven database localhost/test with admin password for user SA looks like this:

Code: Select all

urlid testserver
url jdbc:hsqldb:hsql://localhost/test
username SA
password villeroy
sqltool.rc actually belongs to the sqltool.jar which is basically a command line client for hsql. Having read access to sqltool.rc, you can use sqltool to access your databases without typing passwords. This is what our hsqldb deamon is supposed to do when it shuts down the databases by calling the sqltool.
Pitfalls: The // behind the protocol matters. No quotes around the password!
The declared user needs priviledges to call SQL SHUTDOWN on the database when the service stops.
This schema allows you to describe multipe users for the same database so you can access the same database with different user permissions (see documentation of hsql utils).

4) In the system wide configuration directory /etc I saved a configuration file hsqldb.conf derived from the commented hsqldb.cfg in the samples folder. Without the comments my /etc/hsqldb.conf looks like this:

Code: Select all

JAVA_EXECUTABLE=/usr/bin/java
SQLTOOL_JAR_PATH=/home/andreas/hsql/driver/sqltool.jar
SERVER_HOME=/home/andreas/hsql/
HSQLDB_OWNER=andreas
AUTH_FILE=/home/andreas/hsql/sqltool.rc
URLIDS='testserver'
This one declares some basic paths and picks up the AUTH_FILE sqltool.rc with the relevant URLIDS. Multiple URLIDS can be declared in single quotes separated by spaces like this: URLIDS='testserver otherserver thirdone'
The database driver hsqldb.jar is assumed to be in the same directory as sqltool.jar.

Configuration Summary
In server.properties I declared a file set /home/andreas/hsql/database/Konten.* as alias name test which will be the name part of the connection url jdbc:hsqldb:hsql://host_name/test
This url has another alias name, the urlid "testserver" in the authentification file sqltool.rc where I define one user account which is able to shutdown my database. Of course you may use the same names for all aliases (no spaces nor special chars) but it is important to know that the two alias names do not refer to the same thing. One name is part of the connection url, the other specifies a log-in to that url.
The system wide /etc/hsqldb.conf specifies the important paths for Java and the database plus which sqltool.rc is used for authentification with one or more urlids.

Starting the service
5) Finally we copy hsqldb.init from the samples directory to the system wide configuration /etc/init.d/hsqldb (without the .init suffix).
This init script does not require any modifications. It is written to work with all operating systems (except Windows of course).

Ubuntu has 2 differnt startup systems, the modern one is called "upstart" and the other one is the good old SystemV-Init which is entirely based on scripts and links. Our hsqldb script belongs to the SystemV world. The following command adds the hsqldb service to the "auto-start" services according to settings hard coded in the init script:

Code: Select all

$ sudo  update-rc.d hsqldb defaults
Start the server manually:

Code: Select all

$ sudo service hsqldb start
Check listening ports:

Code: Select all

$ netstat -tulpn
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp6       0      0 127.0.0.1:9001          :::*                    LISTEN      -
I don't know why the PID/Program name is missing in that listing.

There is a PID file for the running service. It contains the process id for the running service which indicates that the service is running or did not shut down properly.

Code: Select all

$ cat /var/run/hsqldb.pid
20973
And we have a log file for our service:

Code: Select all

$ tail -F /var/log/hsqldb.log 
[Server@27716f4]: Initiating startup sequence...
[Server@27716f4]: Server socket opened successfully in 2 ms.
[Server@27716f4]: Database [index=0, id=0, db=file:/home/hsqldb/database/Konten, alias=test] opened successfully in 542 ms.
[Server@27716f4]: Startup sequence completed in 546 ms.
[Server@27716f4]: 2018-01-01 15:10:49.081 HSQLDB server 2.3.4 is online on port 9001
[Server@27716f4]: To close normally, connect and execute SHUTDOWN SQL
[Server@27716f4]: From command line, use [Ctrl]+[C] to abort abruptly
[Server@27716f4]: Initiating shutdown sequence...
[Server@27716f4]: Shutdown sequence completed in 100 ms.
[Server@27716f4]: 2018-01-01 15:26:10.308 SHUTDOWN : System.exit() is called next
Check status of service hsqldb

Code: Select all

$ sudo service hsqldb status
● hsqldb.service - LSB: HyperSQL Database Server
   Loaded: loaded (/etc/init.d/hsqldb; bad; vendor preset: enabled)
   Active: active (running) since Mo 2018-01-01 15:10:49 CET; 3s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 20955 ExecStart=/etc/init.d/hsqldb start (code=exited, status=0/SUCCESS)
 Main PID: 20973 (java)
   CGroup: /system.slice/hsqldb.service
           ‣ 20973 /usr/bin/java -classpath /home/hsqldb/driver/sqltool.jar org.hsqldb.

Jan 01 15:10:48 andreas-ThinkPad-W510 systemd[1]: Starting LSB: HyperSQL Database Serve
Jan 01 15:10:48 andreas-ThinkPad-W510 su[20963]: Successful su for hsqldb by root
Jan 01 15:10:48 andreas-ThinkPad-W510 su[20963]: + ??? root:hsqldb
Jan 01 15:10:48 andreas-ThinkPad-W510 su[20963]: pam_unix(su:session): session opened f
Jan 01 15:10:49 andreas-ThinkPad-W510 hsqldb[20955]: Success for instance 'testserver'
Jan 01 15:10:49 andreas-ThinkPad-W510 hsqldb[20955]: org.hsqldb.server.Server started w
Jan 01 15:10:49 andreas-ThinkPad-W510 systemd[1]: hsqldb.service: Supervising process 2
Jan 01 15:10:49 andreas-ThinkPad-W510 systemd[1]: Started LSB: HyperSQL Database Server
And we can stop the service manually:

Code: Select all

$ sudo service hsqldb stop
After rebooting the system I try:

Code: Select all

$ service hsqldb status
● hsqldb.service - LSB: HyperSQL Database Server
   Loaded: loaded (/etc/init.d/hsqldb; bad; vendor preset: enabled)
   Active: failed (Result: exit-code) since So 2017-12-31 23:53:33 CET; 14min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 1412 ExecStart=/etc/init.d/hsqldb start (code=exited, status=2)

Dez 31 23:53:33 andreas-ThinkPad-W510 systemd[1]: Starting LSB: HyperSQL Database Server...
Dez 31 23:53:33 andreas-ThinkPad-W510 hsqldb[1412]: 
Dez 31 23:53:33 andreas-ThinkPad-W510 systemd[1]: hsqldb.service: Control process exited, code=exited status=2
Dez 31 23:53:33 andreas-ThinkPad-W510 systemd[1]: Failed to start LSB: HyperSQL Database Server.
Dez 31 23:53:33 andreas-ThinkPad-W510 systemd[1]: hsqldb.service: Unit entered failed state.
Dez 31 23:53:33 andreas-ThinkPad-W510 systemd[1]: hsqldb.service: Failed with result 'exit-code'.
SERVER_HOME variable in '/etc/hsqldb.conf' is set to a non-directory.
... which is /home/andreas/hsql and the message makes perfect sense since my personal home directory is encrypted and not availlable before I log in.
If I wanted to actually run my little server as a permanent service, I would have to move the server home to its own directory availlable at boot time.

For now I disable the auto-start of that service:

Code: Select all

$ sudo  update-rc.d hsqldb disable
P.S. How I moved my test server to an appropriate home
Like any other service, this one deserves its own system account with a dedicated directory owned by that user and availlable at boot time.
1) new user hsqldb at home in /var/hsqldb/

Code: Select all

$ sudo useradd --create-home --home-dir /var/hsqldb --comment 'HSQLDB Database Owner' hsqldb
Copy over relevant database files, server.properties, sqltool.rc and acl.txt to the new home /var/hsqldb and pass ownership to user hsqldb, EXCEPT the confidential sqltool.rc which must not be world readable.

Code: Select all

$ cd /var/hsqldb
$ sudo -u hsqldb mkdir database
$ sudo  cp ~andreas/hsql/database/Konten.* database/ && sudo chown hsqldb:hsqldb database/*
$ sudo  cp ~andreas/hsql/server.properties . && sudo chown hsqldb:hsqldb server.properties
$ sudo  cp ~andreas/hsql/acl.txt . && sudo chown hsqldb:hsqldb acl.txt
$ sudo  cp ~andreas/hsql/sqltool.rc .
Modify the paths in server.properties, sqltool.rc and /etc/hsqldb.conf so they point to the right files in the new home and to the right Java libs hsqldb.jar and sqltool.jar at some place availlable at boot time. In case of errors, service hsqldb status gives enough info about wrong paths.
This loads the relevant files into an "easy editor" where you can edit, save and close the files one by one:

Code: Select all

$ sudo nano acl.txt server.properties sqltool.rc /etc/hsqldb.conf

Code: Select all

$ sudo service hsqldb start
$ sudo service hsqldb status
If everything works fine you can enable the auto-start

Code: Select all

$ sudo  update-rc.d hsqldb enable
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
1gatomontes
Posts: 20
Joined: Sun May 14, 2017 3:48 am

Re: Attempting multi-user server mode

Post by 1gatomontes »

All configuration files seem alright, but not sure about the init file
/etc/init.d/hsqldb
which we downloaded from hsqldb.org.


The command
$ sudo service hsqldb start
gives the following error message
Job for hsqldb.service failed because the control process exited with error code. See "systemctl status hsqldb.service" and "journalctl -xe" for details.


Using these 2 commands we got:



$ systemctl status hsqldb.service
● hsqldb.service - LSB: HyperSQL Database Server
Loaded: loaded (/etc/init.d/hsqldb; bad; vendor preset: enabled)
Active: failed (Result: exit-code) since Wed 2018-02-07 13:54:58 EST; 3min 44s ago
Docs: man:systemd-sysv-generator(8)
Process: 3917 ExecStart=/etc/init.d/hsqldb start (code=exited, status=203/EXEC)




$ sudo journalctl -xe
Feb 07 13:22:16 As wpa_supplicant[831]: wlp2s0: WPA: Group rekeying completed with f8:32:e4:b0:0b:30 [GTK=CCMP]
Feb 07 13:54:58 As sudo[3893]: db : TTY=pts/0 ; PWD=/home/db ; USER=root ; COMMAND=/usr/sbin/service hsqld
Feb 07 13:54:58 As sudo[3893]: pam_unix(sudo:session): session opened for user root by db(uid=0)
Feb 07 13:54:58 As systemd[1]: Starting LSB: HyperSQL Database Server...
-- Subject: Unit hsqldb.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/li ... temd-devel
--
-- Unit hsqldb.service has begun starting up.
Feb 07 13:54:58 As systemd[3917]: hsqldb.service: Failed at step EXEC spawning /etc/init.d/hsqldb: No such file
-- Subject: Process /etc/init.d/hsqldb could not be executed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/li ... temd-devel
--
-- The process /etc/init.d/hsqldb could not be executed and failed.
--
-- The error number returned by this process is 2.
Feb 07 13:54:58 As systemd[1]: hsqldb.service: Control process exited, code=exited status=203
Feb 07 13:54:58 As systemd[1]: Failed to start LSB: HyperSQL Database Server.
-- Subject: Unit hsqldb.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/li ... temd-devel
--
-- Unit hsqldb.service has failed.
--
-- The result is failed.
Feb 07 13:54:58 As systemd[1]: hsqldb.service: Unit entered failed state.
Feb 07 13:54:58 As systemd[1]: hsqldb.service: Failed with result 'exit-code'.
Feb 07 13:54:58 As sudo[3893]: pam_unix(sudo:session): session closed for user root
Feb 07 13:59:51 As sudo[3943]: db : TTY=pts/0 ; PWD=/home/db ; USER=root ; COMMAND=/bin/journalctl -xe
Feb 07 13:59:51 As sudo[3943]: pam_unix(sudo:session): session opened for user root by db(uid=0)
lines 2204-2234/2234 (END)



The only info we found online was related to the permissions of
/etc/init.d/hsqldb
... but this file has the same permissions as all the other files in this directory.
OpenOffice 4.1.2
Windows 10 (v.1607)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Attempting multi-user server mode

Post by Villeroy »

1gatomontes wrote:All configuration files seem alright, but not sure about the init file
That's what I thought too when I got the same error. In fact my configuration was not alright.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
1gatomontes
Posts: 20
Joined: Sun May 14, 2017 3:48 am

Re: Attempting multi-user server mode

Post by 1gatomontes »

So what did you change in your /etc/init.d/hsqldb?
OpenOffice 4.1.2
Windows 10 (v.1607)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Attempting multi-user server mode

Post by Villeroy »

Nothing. I just put that script into /etc/init.d/. The script is so complicated because it adjusts itself to do the right thing on dozends of different Unix variants including MacOS.
The error message tells us, that the start script could start the service but the service stopped with some exit code != 0. All the tests for directories, config files, log files, PID, java and hsql successfully finished and the script calls its final command

Code: Select all

eval exec "$JAVA_EXECUTABLE" $SERVER_JVMARGS -classpath "${SERVER_ADDL_CLASSPATH}${SQLTOOL_JAR_PATH}"  org.hsqldb.util.MainInvoker $TARGET_CLASS $INVOC_ADDL_ARGS
This command actually could be started but stopped with an error.
In my case, the problem was a bad name or a bad URL in sqltool.rc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Attempting multi-user server mode

Post by Villeroy »

OK on my system, the above command copied from /etc/init.d/hsqldb evaluates to

Code: Select all

sudo java -classpath /usr/local/lib/hsqldb/lib/sqltool.jar org.hsqldb.util.MainInvoker org.hsqldb.server.Server --props /var/hsqldb/server.properties
and it actually starts the service.
-----
The forum software changes the word "Main_In_voker" to "Mainvoker". Just another funny bug in some other software. It is "M a i n I n v o k e r" (camel case, without spaces)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Attempting multi-user server mode

Post by Villeroy »

Hello, it's me again ;)

On my little test server, I can demonstrate another problem that may prevent that you ever get a hsqldb server started.

1) Add an error to sqltool.rc. I added an "X" to the line
url jdbc:hsqldb:hsql://localhost/test
url jdbc:hsqldb:hsql://localhost/testX

2) $ sudo hsqldb service start
Job for hsqldb.service failed because the control process exited with error code. See "systemctl status hsqldb.service" and "journalctl -xe" for details.

3) Fixing the error and retrying to start the server raises the same error because there is still a process open from our first attempt in 2)

4) Show all processes of user hsqldb:
$ ps -U hsqldb
PID TTY TIME CMD
10638 ? 00:00:00 systemd
10640 ? 00:00:00 (sd-pam)
10645 ? 00:00:03 java

5) kill them starting with the last java process 10645
$ sudo -u hsqldb kill 10645

6) OK, all processes of user hsqldb where killed with that java process:
$ ps -U hsqldb
PID TTY TIME CMD

Shorter method to kill all processes of user hsqldb without evaluating any process number:

Code: Select all

$ sudo killall -u hsqldb
Having fixed the config file(s) and having killed any remaining hsqldb process, I can start my hsqldb server successfully.

I think, this is a glitch with the start script. IMHO, it should do a better clean-up.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply