BI Database

• Introduction to the miniPACS
• Example of using Create BI database
• Nasty root password
• Setting up Read from BI database
• Technical details to set up mySQL server over LAN
• Backing up the database
• Recovering from a disaster

The BI database is a miniPACS which is based upon a database like mySQL or Microsoft SQL Server, together with a shared directory on a server accessible to all computers on the LAN. There can be multiple databases, each with its own shared directory, for multiple hospitals. This is the case for BI which services remote hospitals.

There is a distinction between mySQL server and the individual databases it serves. At BI there is one database server and several different named databases, each with its own shared directory. One could equally well put the database servers at different locations so that each location is running its own database server.

MySQL Community Server is the recommended database since it is free and very robust. (In Linux you may obtain MySQL Server from the repository.) You may download createBIdatabase, which has been tested with mySQL, to properly setup the needed tables. You should also include Read_BI_Studies.jar in your automatic updates since it is updated from time to time.

Introduction to the miniPACS

Our miniPACS started as a jukebox of CDs where each CD had to be positively identified so as to confirm studies were being written to the correct location. This is still in use today as even the RAID disk filled up and another one was added. Each shared directory needs to have a file called "label" with some text which uniquely identifies the location. In our example the file "label" contained a line of text saying "archive created 3.11.2013". The only real requirement is that the text be unique.

The database has a table called disks which contains an entry for each location in use. There is another table called write_disk which contains a single entry to tell which of the disks is currently in use. All previous disks are also in use for reading, but only write_disk is in use for writing.

The Create BI database program will automatically set up the tables for you, on the assumption that mySQL is being used. Any other database will have to be created by other methods. It will assume that you have only a single location and it will copy the contents of the label file into the disks table. It will also set write_disk to the first entry in disks. In other words, you will be ready to start working.

Example of using Create BI database

There are 2 possible ways to set up mySQL server. The default way is to have the root user be able to access mySQL only from the local machine. I have set up my database so that the root can access the database over my local LAN as well as from the local machine. To create the tables and user, the root access is needed so that you may need to run the program on the machine which contains the mySQL. The user which you create will have access from all machines on the LAN so that your normal work isn't limited.

In order to check you connection parameters you can operate Create BI database from each remote machine. The first thing it will do is check to see if the database already exists. If so you will receive a message saying the database already exists and no further action is taken. If you made a typo in one of the entries, the root user entry will fail (assuming the root only runs from the machine with mySQL). You will receive an error message and again no further action can be taken. What you need to do is find and fix the typo and run it again to confirm that all is well.

The database is chosen as //192.168.1.100:3306/mytest. Mytest should be replaced by a more appropriate name for you. It is the name of the database, perhaps the hospital name or something similar. Similarly myuser and mypw should be replaced by something more appropriate for you. The shared directory is the location you have chosen to store all of the images. It will most likely be a network address in Windows. In Linux and Macintosh remote drives are "mounted" so this is the mount location. In any case this is the location where the label file will be sought, when the Create tables button is pressed. The root user password will only be used when the database is actually created.

Please note that if this is the first time around and the root access is only permitted on the local machine then you would not use the network address, but rather you would set the Datebase field to //localhost:3306/mytest. Afterwards you could test it on remote machines with the network address.

Nasty root password

I had to reformat my system partition and got MySql 8.0 which I had difficulty getting access to with an unknown password. On Linux Ubuntu, this worked:

As of Ubuntu 20.04 with MySql 8.0 : the function PASSWORD do not exists any more, hence the right way is:

1. login to mysql with sudo mysql -u root
2. change the password:

    USE mysql;
    UPDATE user set authentication_string=NULL where User='root';
    FLUSH privileges;
    ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'My-N7w_And.5ecure-P@s5w0rd';
    FLUSH privileges;
    QUIT

Now you should be able to login with mysql -u root -p (or to phpMyAdmin with username root) and your chosen password.

Setting up Read from BI database

The final job is to set up Read from BI database on each remote machine. Basically you use the same parameters you used in the above Create BI database. It is at this stage that it is useful to run Create BI database on the remote machine to verify that the connection is working. I have set up the example on database 8 out of 10, but you will start with database 1.

Note that the parameters are the same as Create BI database. Java MySQL is chosen as the database. All studies up to 30 days old will appear in green and older studies in red. The Display "test db" is a label which will appear to identify multiple databases once more than just a single database is defined.

Saving studies for a conference is a feature which allows you to choose one of your Read from CD directories to save studies you want to take to a conference. At the conference you will not have access to the database so you will be able to pull up the chosen studies from your local disk.

Technical details to set up mySQL server over LAN

Just to help anyone having troubles setting up mySQL to work over the LAN, you need to edit the my.cnf file. Comment out the line bind-address by using the # character, as shown below.

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1

Note: In recent versions of Linux the bind-address is no longer in my.cnf. Instead modify the same line in /etc/mysql/mysql.conf.d

Furthermore, you need to grant "root" all privileges on the proper address

mysql> GRANT ALL ON *.* to root@'192.168.1.100' IDENTIFIED BY 'your-root-password';
mysql> FLUSH PRIVILEGES;

Backing up the database

MySQL is a most reliable database. Still, if you need to format the disk partition where the database is stored, you will lose the database contents. Thus it is a good idea to back up the database contents on a regular basis. The backup tab is used:

The database name, user and password are taken from the Create tab. Create is done once, whereas backup is done regularly. It is inconvenient to have to enter the information each time. The Auto load button is pressed whenever you have changed something on the Create tab. The Auto load will save the values so there will be no need to manually enter them.

The Backup location and name has to be defined once as well. It would need to be changed only if you wish to keep several backups from different dates, for example. If you keep the same name, the file will be overwritten with the latest data.

The only button you need to press is the Backup database. As visual feedback that the backup has been performed, the Backup database button will be disabled. There is no reason to backup the database again, if you have just done so.

Recovering from a disaster

It is important to note that any studies added since your last database backup will not be included in the file, and thus will not be restored. Thus it is important to back up fairly frequently, and most especially before you do any major software updates.

So you have backed up and your software update wiped out your operating system partition. What do you do? The first thing is to reinstall mySql. It will be without any of your important databases or tables. You have to first create your empty databases and users. This is explained above in creating a database. Create both the database and the user as explained above. There is no need to create the tables. We will use the backup file to both create the tables and populate them with all the data.

This is done in a terminal. First you change directory to the location of the backup file, which in our example is called sqlSave. Then you give the command

mysql -u root -p[root user password] mytest < sqlSave

The database which we created in the example was mytest and the user myuser. In the best case you will never have to do this, but if you get into trouble, the above works.

The root user password in the above example needs clarification. This is the password you defined when you first created the database. Suppose we used kishkush as a password. Then the command would be

mysql -u root -pkishkush mytest < sqlSave

(Note: you will receive a message that it is unsafe to specify the password on the command line. So a better way is to type:

mysql -u root -p mytest < sqlSave

Then you will be prompted to enter the password, which will not be shown.)