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

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

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

JBoss 4.0.5: javax.naming.NamingException: Could not dereference object

If you experience such an error it is very well based on having the JBoss server and/or the clients started using JDK 6.0. Some more explanations can be found in JBoss Forum topics here and here.

Sometimes I run in this problem as my production systems still use JBoss 4.0.5 GA. Beginning with JBoss 4.2.0GA this error should not appear anymore. Sometime in future I will update my servers. Until then, I will stick with JDK 1.5.0.

Optional ant tasks in separate build.xml file invoked with Maven AntRun plugin

In a legacy development project, ant build.xml files are used for compilation and packaging. To integrate new features more easily, I decided to use Maven and the AntRun plugin. The original targets can simply be invoked as it is explained in the examples.

However, if you want to use taks not included in the default jar, e.g. the optional ant tasks, you have to correctly add the dependencies to the AntRun plugin. Unfortunately, I moved the dependencies to the global section of my pom.xml. This mistake took me a quite some time to fix, as I simply oversaw it. 🙁

Just make sure, it looks like this:

<plugin>
  <artifactId>maven-antrun-plugin</artifactId>
  <dependencies>
    <dependency>
      <groupId>org.apache.ant</groupId>
      <artifactId>ant-nodeps</artifactId>
      <version>1.7.0</version>
    </dependency>
  </dependencies>
  <executions>
    <execution>
    ...
    </execution>
  </executions>
</plugin>