Table Column order seems to play a role after migration from PostgreSQL 8.1 to 9.1 if sequences are used

TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to reorder the columns of a table to make sure the primary key column that is automatically filled by a sequence, is the first one in the table. This was not an issue with 8.1. The problem – during INSERT – only occurs via JDBC. When using the CLI (i.e. psql), it works fine.

The details of what I’ve observed:

In preparing a major system upgrade for a legacy application, I tested the migration of the PostgreSQL server from version 8.1 to 9.1 (on Ubuntu LTS 12.04). On top of the database I use JBoss AS 7.1 with Hibernate, JDBC and Java 1.7.
I tested with different but recent JDBC drivers for the Java 1.7 platform (i.e. postgresql-9.3-1102.jdbc41.jar, postgresql-9.4.1208.jre7.jar). The entity beans are specified to have their primary key (Integer value) generated by the database via a sequence:

In the bean:

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id")
 public java.lang.Integer getId()
 {
   return id;
 }

In the table:

\d phrases
                                     Table "phrases"
  Column   |         Type         |                              Modifiers
-----------+----------------------+----------------------------------------------------------------------
 phrase    | text                 |
 frequency | integer              | default 4
 language  | character varying(3) |
 id        | bigint               | not null default nextval('phrases_id_seq'::regclass)
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)

However, after switching to the 9.1 server, I got following error message:

$WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0, SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for type int : lklkh

Increasing the log levels provided me with following details:

DEBUG [org.hibernate.SQL] insert into phrases (frequency, language, phrase) values (?, ?, ?)
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [1] as [INTEGER] - 4
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [2] as [VARCHAR] - ger
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [3] as [VARCHAR] - lklkh
WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0, SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for type int : lklkh

Performing the INSERT via CLI worked nicely:

INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger', 'lklh');
INSERT 0 1

This led me to the assumption that there was something wrong with the JDBC driver or the hibernate layer, but none of the tested modifications made the problem go away. When searching for the given SQL error code & state, I stumbled on a stackoverflow post, and indeed, after I have re-ordered the columns in the table moving the id column to the first position, it works without a flaw.

\d phrases
                                     Table "phrases"
  Column   |         Type         |                              Modifiers
-----------+----------------------+----------------------------------------------------------------------
 id        | bigint               | not null default nextval('phrases_id_seq'::regclass)
 phrase    | text                 |
 frequency | integer              | default 4
 language  | character varying(3) |
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)

As it took me quite a while to figure out this work around, I wanted to share this. I am really curious about what the actual root cause is. I hope this might help others in similar situations.

How to get IP-address and port in JBoss AS 7.1 programmatically.

This can be achieved using the MBeanServerConnection API and the correct node and attribute names within the JBoss configuration:

// get an javax.management.MBeanServerConnection object
MBeanServerConnection mbServerConn = ManagementFactory.getPlatformMBeanServer();
// drill down to the 'http' node in the standard-socket-binding-group
ObjectName nameSocket = new ObjectName("jboss.as:socket-binding-group=standard-sockets,socket-binding=http");
// use the getAttribute()-method of the MBeanServerConnection object
String serverBase = "http://" + mbServerConn.getAttribute(nameSocket, "bound-address") + ":" + mbServerConn.getAttribute(nameSocket, "port")+ "/";

How to find out about the nodes and attribute names? The easiest way to achieve this, is to use the JBoss command line interface jboss-cli.sh. After you connected to the JBoss instance in question, use the ls command to find out about the nodes. You can use tab for autocompletion. Once you have found a leave, use the parameter -l for listing the attribute names:


ls socket-binding-group=standard-sockets
ls socket-binding-group=standard-sockets/socket-binding=http
ls - socket-binding-group=standard-sockets/socket-binding=http

The last command results in

ATTRIBUTE VALUE TYPE
bound true BOOLEAN
bound-address XX.YY.ZZ.WW STRING
bound-port 8080 INT
client-mappings undefined LIST
fixed-port false BOOLEAN
interface undefined STRING
multicast-address undefined STRING
multicast-port undefined INT
name http STRING
port 8080 INT

mod_jk configuration does not work anymore after upgrading to Ubuntu 10.04 LTS

I recently upgraded a well working Ubuntu server instance from 8.04 LTS to 10.04 LTS as the support for 8.04 LTS will end this April 2013. This server system uses a JBoss(+Tomcat)/Apache2.2 stack for delivering its services. The connection between Apache and JBoss has been realised using the mod_jk plugin for Apache which uses the AJP approach. In 8.04, this worked fine. After having performed the do-release-upgrade, this connection did not work anymore. Increasing the logging level of the mod_jk plugin to debug, the logfile contained "missing uri map for <servername>:<path>" messages.

Looking around for possible causes, I found this email conversation, suggesting to use the JkMountCopy All directive in the global Apache2 configuration file. This solved the problem.

Another result suggested even not using mod_jk anymore but using the newer mod_proxy_ajp.

Creating a USB Flash Drive to boot VMWare VMvisor 5.0 installer

There are lot of guidelines available on how to create a bootable USB stick with VMware installer. Some of them use manual copying e.g http://www.ivobeerens.nl or http://www.techhead.co.uk or use the UNetbootin tool in Windows to create a bootable USB stick: https://community.emc.com.
But one major thing is to be obeyed: The USB stick must be formatted in FAT32 file system and the partition must be made active. Formatting the USB stick in Windows, all of this is done more or less automatically, but if you have only Linux at hand, you must follow a few steps that are also described in the vSphere 5 Documentation Center:

2 Create a partition table on the USB flash device.
/sbin/fdisk /dev/sdb
a Type d to delete partitions until they are all deleted.
b Type n to create primary partition 1 that extends over the entire disk.
c Type t to set the type to an appropriate setting for the FAT32 file system, such as c.
d Type a to set the active flag on partition 1.
e Type p to print the partition table.

The result should be similar to the following text:
Disk /dev/sdb: 2004 MB, 2004877312 bytes
255 heads, 63 sectors/track, 243 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 * 1 243 1951866 c W95 FAT32 (LBA)

f Type w to write the partition table and quit.

3 Format the USB flash drive with the Fat32 file system.
/sbin/mkfs.vfat -F 32 -n USB /dev/sdb1

SVN Statistics using StatSVN

While testing a few tools to quickly get useful statistics on SVN repository, I stumbled on StatSVN, which I liked the most. As it is written in Java, it should run on most systems. Just follow the instructions of the Quick Start section in the Wiki to create comprehensive statistics of your projects.

As StatSVN works on the svn log file you have to create for your checked out modules, it might make sense to create the logs only for a specific period of time:

svn log -v --xml -r {2011-06-30}:{2011-01-01} > log.xml

It is also possible to create joined statistics for several SVN modules. If you have a bunch of modules with branches you may want to make sure that you only check out the trunk portion of the modules and not all branches. For this, check out all the modules you have in the SVN repository but only the first level:

svn checkout --set-depth immediates;

This should give you the wanted directory structure. Now ”cd” into each module directory and check out the ”trunk” part of each module:

cd MODULE_DIR
svn update --set-depth immediates;
cd trunk;
svn update --set-depth infinity;

After creating the appropriate svn log file you can generate the statistics by invoking StatSVN, e.g.

java -jar /path/to/statsvn.jar /path/to/module/logfile.log /path/to/module

Re-scanning SCSI bus for new devices

It might be useful to re-scan the SCSI bus when a new device has been plugged in, e.g. via VMWare ESXi configuration changes, that has not been automatically identified by the OS. In Debian/Ubuntu you can do the following:
sudo apt-get install scsitools
sudo rescan-scsi-bus.sh

You can check the /sys/bus/scsi/devices/ directory before and after the re-scan and via dmesg to which device node in /dev directory e.g. the new harddisk has been added.

Don’t forget about the environment when you use cron

The commands that are executed via cron are started in a different environment than the one you have while you test the command in a shell. The blog entry of Mike Tremell explains the reasons and gives solutions to the problem. The easiest one is to set up the correct environment in a script that you call in a crontab:

. /etc/profile
. /home/user/.profile

%-sign in crontabs

The %-sign has a special meaning in crontab.

man 5 crontab:

“…Percent-signs (%) in the  command,  unless escaped with backslash (\), will be changed into newline characters, and all data after the first % will be sent

to the command as standard input. ”

So don’t forget to escape the %-signs with a backslash if you use them for example to format the date output:

`date +'\%Y\%m\%d'`

Passing Hudson BUILD_NUMBER to Sonar Plugin

Sonar is a great source code analysis tool that integrates through its plugin neatly into the Hudson continuous integration server. One major feature of Sonar is the module called “Time machine” where you can review the progress of the quality metrics for your project over time. However, to see this progress, it is necessary to tag the version of the Sonar run dynamically, otherwise, the new run “overwrites” the previous results. Sonar does not really discard the old results, but to fetch the older outcomes in the Time machine view is quite tedious.

So, what you need to do is really simple:

  1. Pass the BUILD_NUMBER to the Maven call in the field “Additional properties”
  2. Have the Sonar Plugin insert a variable for replacement in the generated POM file

In my example the replacement variable is called “version” but you can name it as you want.

Pass a variable Version Number to Hudson Sonar Plugin

Pass a variable Version Number to Hudson Sonar Plugin

With the shown configuration Sonar will store the results under “hudson-##” where ## is the current iteration of the Hudson build cycle. Of course it is also possible to pass other information, like the BUILD_ID or JOB_NAME. A listing of available Hudson environment variables can be found here.

Andreas

Save a Sharepoint Password in Firefox

Using a Sharepoint site is for Firefox users can very annoying as the Website, for what reason whatsoever, does not allow the Web browser to store the password. Some other sites also disallow for security reasons to store the password in the browser.

Sometimes, the trouble even doubles as the user can not change his/her Sharepoint password and always has to lookup the cryptic generated password.

However, there is a solution for allowing Firefox storing the password available:

  1. Go to the Login page of the Sharepoint Website
  2. Enter you username and password
  3. Copy the JavaScript code below into the Firefox Location bar
  4. Hit Enter and click on sign in
  5. Voilà, Firefox asks you if you like to save the password.

Copy this one into the location bar as it is written without any newlines:

javascript:(function(){var ca,cea,cs,df,dfe,i,j,x,y;function n(i,what){return i+" "+what+((i==1)?"":"s")}ca=cea=cs=0;df=document.forms;for(i=0;i<df.length;++i){x=df[i];dfe=x.elements;if(x.onsubmit){x.onsubmit="";++cs;}if(x.attributes["autocomplete"]){x.attributes["autocomplete"].value="on";++ca;}for(j=0;j<dfe.length;++j){y=dfe[j];if(y.attributes["autocomplete"]){y.attributes["autocomplete"].value="on";++cea;}}}alert("Removed autocomplete=off from "+n(ca,"form")+" and from "+n(cea,"form element")+", and removed onsubmit from "+n(cs,"form")+". After you type your password and submit the form, the browser will offer to remember your password.")})();

A more readable form of the JavaScript:

javascript:(
  function()
  {
    var ca,cea,cs,df,dfe,i,j,x,y;
    function n(i,what)
    {
      return i+" "+what+((i==1)"":"s")
    }
 
    ca=cea=cs=0;
    df=document.forms;
    for(i=0;i<df.length;++i)
    {
      x=df[i];
      dfe=x.elements;
      if(x.onsubmit)
      {
        x.onsubmit="";++cs;
      }
      if(x.attributes["autocomplete"])
      {
        x.attributes["autocomplete"].value="on";
        ++ca;
      }
 
      for(j=0;j<dfe.length;++j)
      {
        y=dfe[j];
        if(y.attributes["autocomplete"])
        {
           y.attributes["autocomplete"].value="on";++cea;
        }
      }
    }
    alert("Removed autocomplete=off from "+n(ca,"form")+" and from "+n(cea,"form element")+", and removed onsubmit from "+n(cs,"form")+". 
    After you type your password and submit the form, the browser will offer to remember your password.")
  })();

The script simply removes the HTML attribute “autocomplete=off” and has to be executed only once. You can check now if your password has been stored in the Firefox settings.

One minor issue remains: As the next time you visit the login page, the autocomplete attributes are active again, you must enter your complete login name, but this is quite easy to remember and the password will be automatically inserted by Firefox.

Reference: Force Firefox to Offer to Save …

HTH,

Andreas