Migrating HSQLDB to MySQL

HSQLDB migration to MySQL is not out of the box supported by mysql. The MySQL Workbench can only handle with dumps made by other mysql server. HSQLDB Transfer Tool is no longer bundled with the standard hsqldb jar file in the 1.8.x or later releases. After downloading and building with

ant hsqldbutil

you get in lib an additional hsqldbutil jar. With the hsqldb.jar, the hsqldbutil.jar and and mysql connector jar in my classpath i tried the Database Manager (From Tools menu choose transfer) from hsql with no luck. The MySQL Migration Toolkit has reached the EOL phase but works perfect for me. After downloading add the hsqldb jar to the lib folder C:\Program Files (x86)\MySQL\MySQL Tools for 5.0\java\lib. I update as well the mysql connector to the latest one.

  1. Start your hsqldb server from command line
    1. java -cp hsqldb-1.8.0.10.jar org.hsqldb.Server -database <path to your hsqldb files>\<database name>
  2. Start MySQL Migration Toolkit
  3. Choose direct migration
  4. Choose as Source a generic jdbc
  5. Enter “org.hsqldb.jdbcDriver” as classname
  6. Enter “jdbc:hsqldb:hsql://localhost” as connection string
  7. Enter “sa” as username and leave password empty
  8. click next
  9. Configure your mysql connection
  10. click next
  11. choose public as schema
  12. click next several time till screen “object creation options”
  13. choose “create script file..” instead of “create objects online”
  14. click next several time till screen “data mapping options”
  15. choose “create script file..” instead of “transfer data online”
  16. click next several time till screen “Summary”
  17. click finish

With the sql you can use tools like phpmyadmin or the mysql workbench to import your data into the mysql server.

Start and stop windows services with powershell scripts

With Powershell you can easily start or stop specific windows services depending on current status. An example is starting the VMWare Services only if they are currently stopped. The command

get-service

list all windows service with their status, short – and display name. Create a new File startVMWare.ps1 and paste the following

foreach ($svc in Get-Service){
  if(($svc.displayname.StartsWith("VMware")) -AND ($svc.Status -eq "Stopped")) {
    echo $svc.DisplayName
    Start-Service $svc.name
  }
}

Sign the script as shown in my previous post to run self signed scripts. Run the script in an powershell with administrator rights.

.\startVMWare.ps1

The script starts only stopped VMWare services. To run this script directly you can write a small dos file startVMWare.cmd with the following content

powershell -file <FULL PATH TO YOUR SCRIPT>\startVMWare.ps1

Start the Dos file with right click and admin rights to execute the vmware start powershell script.

Howto run self signed powershell scripts

Windows command line scripts was for a long time the only way for scripting windows. With the Windows PowerShell you have can write scripts more like an program in object oriented way. Starting with Windows 7 it is preinstalled with version 2.0 but can as well installed under Windows XP or Vista. Windows 8 will ship with version 3.0 which adds windows work flow foundation functionality. Windows 7 ships with an IDE for PowerShell called Windows PowerShell ISE”. Scripts stored in files with .ps1 suffix. A sample hello world looks like this:

echo "hello world"

Save the content in a file called hello.ps1. Start the powershell by searching for powershell in the windows 7 search box above the start button and with right click to run as administrator. Change the current folder with cdto the one where you saved your first powershell script. Run your script with

.\hello.ps1

Unfortunately you get a PSSecurityException because powershell script execution is controlled by an security policy. Like Java signed jars you must sign your scripts and set the policy to execute only signed scripts

Set-ExecutionPolicy Restricted

Powershell accepts self signed certificates and can be created by the makecert command. Makecert is part of the windows SDK. Download the installer and run through the wizard steps. At the last screen deselect all parts and check only the tools under first section called Windows Native Code Development. Open a command shell with shift and right click on the folder C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin. Enter the following commands to create a authority:

makecert -n "CN=PowerShell Local Certificate Root" -a sha1 -eku 1.3.6.1.5.5.7.3.3 -r -sv root.pvk root.cer -ss Root -sr localMachine

and the following command to create a self signed certificate

makecert -pe -n "CN=PowerShell User" -ss MY -a sha1 -eku 1.3.6.1.5.5.7.3.3 -iv root.pvk -ic root.cer

The following command in the powershell show your created certificate:

Get-ChildItem cert:\CurrentUser\My -codesign

Now we can sign our first script with the following command

Set-AuthenticodeSignature .\hello.ps1 @(Get-ChildItem cert:\CurrentUser\My -codesigning)[0]

Run now your signed script with

.\hello.ps1

with prints

hello world

Powershell scripts can use profile scripts to store common functions for own scripts, functions,.. Enter the following command to your standard profile file

$profile

Open the file or create it with a text editor and paste the following

function sign ($filename) {
 $cert = @(gci cert:\currentuser\my -codesigning)[0]
 Set-AuthenticodeSignature $filename $cert
 }

Sign the profile file

Set-AuthenticodeSignature $profile @(Get-ChildItem cert:\CurrentUser\My -codesigning)[0]

Write now your own little powershell script like test.ps1. The sign function can now be used like this

sign .\test1.ps1

Configuring solr, tomcat 7 with mod_jk and apache 2.2

As follow up post to setting up solr i show you how to integrate tomcat into Apache as main web server:

The setup was made under ubuntu 11.10 with the following prerequisites:

  • CATALINA_HOME is /usr/share/tomcat7
  • JAVA_HOME is /usr/lib/jvm/default-java
  • HOSTNAME in my case is ubuntu-vm.localdomain

 

  1. apt-get install libapache2-mod-jk
  2. nano /etc/apache2/workers.properties with the following contents
workers.tomcat_home=/usr/share/tomcat7
workers.java_home=/usr/lib/jvm/default-java
ps=/
worker.list=ajp13
worker.ajp13.port=8009
worker.ajp13.host=localhost
worker.ajp13.type=ajp13
worker.ajp13.lbfactor=1
  1. nano /etc/apache2/mods-available/jk.conf with the following contents

        # Where to find workers.properties
        JkWorkersFile /etc/apache2/workers.properties
        # Where to put jk shared memory
        JkShmFile     /var/log/apache2/mod_jk.shm
        # Where to put jk logs
        JkLogFile     /var/log/apache2/mod_jk.log
        # Set the jk log level [debug/error/info]
        JkLogLevel    info
        # Select the timestamp log format
        JkLogStampFormat "[%a %b %d %H:%M:%S %Y] "
        # solr redirect
        JkMount /solr* ajp13

  1. less /etc/apache2/mods-available/jk.load to see the following contents
    1. LoadModule jk_module /usr/lib/apache2/modules/mod_jk.so
  2. run a2enmod jk to see if mod_jk is enabled
    1. Module jk already enabled
  3. less /usr/share/tomcat7/conf/server.xml to see a line like this
    1. <Connector port=”8009″ protocol=”AJP/1.3″ redirectPort=”8443″ />
  4. nano /etc/apache2/sites-available/default and insert a line into the virtualHost:80 section
    1. JKMountCopy On
  5. restart apache2 with service apach2 restart
  6. Open a browser on your client to open http://ubuntu-vm.localdomain/solr/admin/

Installing Solr 3.5 under Tomcat 7

Solr is a open source Enterprise Search Engine. It can be deployed as war file in servlet containers like tomcat or jetty.

This short howto show you run solr with the current Tomcat 7 version:

  1. Download Solr as Zip
  2. Unzip it e.g. in /usr/share/ with folder structure
    1. mv apache-solr-3.5.0.zip /usr/share
    2. cd /usr/share
    3. unzip apache-solr-3.5.0.zip
    4. result is a folder /usr/share/apache-solr-3.5.0
  3. Create a new folder e.g /usr/share/solr as base folder for the configuration
    1. cp -a /usr/share/apache-solr-3.5.0/example/solr /usr/share/solr
    2. cp /usr/share/apache-solr-3.5.0/dist/*.war /usr/share/solr
    3. ln -s /usr/share/solr/apache-solr-3.5.0.war /usr/share/solr.war
    4. mkdir /usr/share/solr/data< for index data/li>
    5. mkdir /usr/share/solr/lib for additional jars
    6. cp /usr/share/apache-solr-3.5.0/dist/apache-solr-velocity-3.5.0.jar /usr/share/solr/lib
    7. cp -a /usr/share/apache-solr-3.5.0/contrib/ /usr/share/solr/
    8. nano /usr/share/solr/conf/solrconfig.xml and change the lib settings
    // ...
      
    
    
      
      
      
      
    
    
    1. chown -R tomcat:tomcat /usr/share/solr if tomcat server runs as user tomcat
  4. Add URIEncoding to tomcat connector settings
    1. nano /usr/share/tomcat7/conf/server.xml
    2. search for the connector on port 8080
    3. add the URIEncoding like this:
    
    
  5. Create a tomcat configuration file for solr (CATALINA_HOME is /usr/share/tomcat7)
    1. cd /usr/share/tomcat7/conf
    2. mkdir Catalina (if not exists)
    3. cd Catalina
    4. mkdir localhost (if not exists)
    5. cd localhost
    6. nano solr.xml
  6. Paste the following configuration and save the file


  

  1. Set properties for tomcat and solr inside /usr/share/tomcat7/bin/setenv.sh
  2. Paste the following configuration and save the file
export JAVA_OPTS="$JAVA_OPTS -Dsolr.solr.home=/usr/share/solr"
export JAVA_OPTS="$JAVA_OPTS -Dsolr.data.dir=/usr/share/solr/data"
export JAVA_OPTS="$JAVA_OPTS -Dsolr.velocity.enabled=true"
export JAVA_HOME="/usr/lib/jvm/default-java"
  1. (Re)start tomcat
    1. /etc/init.d/tomcat7 restart
  2. Open browser and go to http://YOUR_SERVER_HOSTNAME:8080/solr/admin/ assuming that tomcat runs under default port 8080

I use the provided sample xml files to see if import and query functionality works:

  1. cd /usr/share/apache-solr-3.5.0/example/exampledocs
  2. nano post.sh
  3. change the URL parameter to your environment e.g. to URL=http://localhost:8080/solr/update
  4. ./post.sh *.xml
  5. Open the admin ui under http://YOUR_SERVER_HOSTNAME:8080/solr/admin/
  6. click the search button

see my follow up post for Configuring solr, tomcat 7 with mod_jk and apache 2.2