Skip to main content

Using MySQL as handle.net database back-end


I've recently been working on getting a CNRI handle (aka persistent identifier) server up and running. We wanted to use MySQL as our database back-end. Since the documentation didn't cover how to do this in sufficient detail so here is what I did.

PS: We are using a pretty old version of the handle.net software - v 6.2. Hopefully this configuration still applies to the newer versions.

Background

By default the handle.net software stores its data in a Java jdb database. In my opinion this is less than ideal since there are no tools to manipulate or even view your data. You can, I guess, write your own java code to do this, but this is a pain.

Everybody knows MySQL so we wanted to use it as our back-end. The handle server does support SQL but the document only provides a config example for postgres. (Strangely, it does provide a table layout for MySQL) Google also didn't provide any useful answers.

So, in case it is useful to anybody else, here is my configuration.

Setup MySQL

Install the MySQL server package. Use apt-get or yum depending on your linux distribution.

Create a database. I decided to call mine handle.

 mysqladmin -u root -p create handle

Create a MySQL user and give it access to the database.

 grant all on handle.* to handle@'localhost' identified by 'supersecretpassword';

Setup the table structure as per the MySQL example in the documentation.

 create table nas (  
   na varchar(255) not null,  
   PRIMARY KEY(na)  
 ) ;  
 create table handles (  
   handle varchar(255) not null,  
   idx int4 not null,  
   type blob,  
   data blob,  
   ttl_type int2,  
   ttl int4,  
   timestamp int4,  
   refs blob,  
   admin_read bool,  
   admin_write bool,  
   pub_read bool,  
   pub_write bool,  
   PRIMARY KEY(handle, idx)  
 );

Download MySQL's JDBC jconnector and put it somewhere useful. I put it in /opt/mysql-connector-java-5.1.25. Uncompress the archive and you should end up with a sub-directory all sorts of stuff but most importantly a jar file - in my case, mysql-connector-java-5.1.25-bin.jar.

Configure Handle Software

I assume that you already have it up and running using the Java jdb database. If not follow the documentation and get this working first.

I'll just describe what I did to change from the local jdb database to MySQL.

Edit your startup script to add the jconnector JAR to your classpath.

 #!/bin/bash  
 java_path=/opt/jdk1.7.0_21/bin  
 $java_path/java -cp "/hs/bin/handle.jar:/opt/mysql-connector-java-5.1.25/mysql-connector-java-5.1.25-bin.jar" net.handle.server.Main /hs/svr_1

Notice the colon (:) between the two paths which make up your classpath (cp). If you are running this on Windows you need to use a semi-colon (;) delimiter.

Update your config.dct. Add the following config snippet to the existing "server_config" section. 

 "storage_type" = "sql"  
 sql_settings = {  
  sql_url = "jdbc:mysql://localhost:3306/handle"  
  sql_driver = "com.mysql.jdbc.Driver"  
  sql_login = "handle"  
  sql_passwd = "supersecretpassword"  
  sql_read_only = "no"}  
  }

The things to note - which differ from the Postgres example in the docs - is the sql_url and sql_driver.

Fire up your handle server and make sure it starts properly. If you get a MySQL error the server either cannot find the jar file (check your classpath) or something in your config.dct is wrong.

Fire up the handle admin tool and use it to re-home your server by clicking Server Admin | Home Naming Authority. This will put your naming authority into the nas table.

And you're done!

Of course, your handle table is now completely empty and you will need to get your digital repository to re-populate it. (Unless of course you are up to writing your own java code to read the existing data our of the jdb and putting it into the MySQL handle table).

In our case - where we use Digitool - it was just a matter of running a Publish Persistent Identifier job and all of our handles got republished into the MySQL tables.


Labels:

Comments

Post a Comment

Popular posts from this blog

Sorting a list of IP addresses in Python

As I work a lot with network data, one of my favourite python modules is iplib . It takes care of quite a few of things I want to do with IP addresses but lacks a lot of functionality of perl's Net::Netmask which I relied on extensively when perl was my favourite language. One of the iplib missing features is a method for sorting a list of IP addresses, or at the very least, a method for comparing two addresses. Luckily this is easy enough to implement yourself in python using a customised sort function. See the Sorting Mini-HOW TO for a well written document on sorting in python. Here is my attempt at a custom function for sorting IP addresses. import iplib ips = ["192.168.100.56", "192.168.0.3", "192.0.0.192", "8.0.0.255"] def ip_compare(x, y): """ Compare two IP addresses. """ # Convert IP addresses to decimal for easy comparison dec_x = int(iplib.convert(x, "dec")) dec_y = int(ipl...
4 comments
Read more

Normalizing a MAC address string

Over the last few days, I have been spending some time working on my python - reading the sections of Diving into Python that I have never got around to and refactoring parts of some of my python scripts to make better use of the features of language and, ultimately, to make them more robust (i.e. usable by people other than me). The script I have started with is a simple one for registering hosts for DHCP access. Basically, it takes two command line arguments - a fully qualified hostname and a MAC address - and then does some validation, checks that neither address is already in use, normalizes the output to the correct format, constructs a properly formatted host stanza and appends it to the end of our ISC DHCP servers dhcpd.conf configuration file. I have made improvements to various parts of the code but the changes I am most conflicted about are those I have made to the MAC address normalization function which works reliably and therefore probably isn't a good candidate for...
Post a Comment
Read more

Recursive Descent Parsers and pyparsing

Yesterday while browsing the table of contents of the May 2008 issue of Python Magazine I came across a reference to the pyparsing module - a python module for writing recursive descent parsers using familiar python grammar. O'Reilly's Python DevCenter has an excellent introduction to using this module entitled Building Recursive Descent Parsers with Python . Well worth a read. It just so happens that I have a number of projects which are stalled because writing code to parse complexly structured data is not my strong point. I enjoy parsing up text line by line as much as the next guy but this recursive stuff I find tedious. The ISC DHCP configuration file is, in my opinion, a good example of parsing complexity. It's configuration directives can contain many optional directives, can be nested, and can be all on a single line or broken up move multiple lines. Writing the parser using pyparsing makes this much simpler. Here is a simple example of using pyparsing to parse...
4 comments
Read more