Re: Fw: Column order seems to play a role after migration from 8.1 to 9.1 if sequences are used

Andreas,

Thanks for getting back to us!

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 26 April 2016 at 05:22, Andreas Arens <andreas(dot)arens(at)list(dot)lu> wrote:

> To sum up, it turned out that Hibernate version 4.0.1 had a flaw that
> appears to be fixed in the latest version. Have a look into the Hibernate
> forum topic for details:
> https://forum.hibernate.org/viewtopic.php?t=1043212
>
> Andreas
>
> ----- Forwarded by Andreas Arens/LIST on 26/04/2016 11:20 -----
>
> From: Andreas Arens/LIST
> To: pgsql-jdbc(at)postgresql(dot)org
> Date: 25/04/2016 13:11
>
> Subject: Re: [JDBC] Column order seems to play a role after migration
> from 8.1 to 9.1 if sequences are used
> ------------------------------
>
>
> Rob,
>
> Thanks for pointing the different things out. Luckily, we don't have have
> to specify the tables through *hbm files in Hibernate anymore. I forwarded
> the issue to the Hibernate folks
> (https://forum.hibernate.org/viewtopic.php?f=1&t=1043212
> <https://forum.hibernate.org/viewtopic.php?f=1&t=1043212>). I will post
> updates in this list when more is known.
>
> Andreas
>
> [image: Inactive hide details for rob stone ---21/04/2016 21:02:03---On
> Thu, 2016-04-21 at 09:30 -0400, Dave Cramer wrote: > This is a]rob stone
> ---21/04/2016 21:02:03---On Thu, 2016-04-21 at 09:30 -0400, Dave Cramer
> wrote: > This is a bug in their code. They are relyin
>
> From: rob stone <floriparob(at)gmail(dot)com>
> To: Dave Cramer <davecramer(at)gmail(dot)com>, Andreas Arens <
> andreas(dot)arens(at)list(dot)lu>
> Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> Date: 21/04/2016 21:02
> Subject: Re: [JDBC] Column order seems to play a role after migration
> from 8.1 to 9.1 if sequences are used
> ------------------------------
>
>
>
> On Thu, 2016-04-21 at 09:30 -0400, Dave Cramer wrote:
> > This is a bug in their code. They are relying on the presumption that
> > id is the first column
> > As for pg versions. Use the projects distros we patch them ahead of
> > anyone.
>
> Hello,
>
> I haven't used Hibernate for yonks but does the column order in the
> phrases.hbm file match the order used in the CREATE TABLE definition?
> I have a vague recollection that was important. We used to generate the
> hbm files from the SQL create script.
>
> As an aside, if you define column id as:-
>
> id BIGSERIAL NOT NULL PRIMARY KEY USING INDEX TABLESPACE blah,
>
> saves you having to define a sequence and the index as well as making
> it pretty obvious exactly what column id does.
> Also, an ISO language code of, say, 'en-GB', isn't going to fit in a
> VARCHAR(3) sized column.
>
>
> Cheers,
> Rob
>
>
> > On Apr 21, 2016 8:45 AM, "Andreas Arens" <andreas(dot)arens(at)list(dot)lu>
> > wrote:
> > > Dave,
> > >
> > > Well, it seems that the PostgreSQL server itself does not log an
> > > error, but I get it from the JDBC driver:
> > >
> > > Caused by: org.postgresql.util.PSQLException: Bad value for type
> > > int : lkjlkjlkj ljlékjlékj lék lkjlék àékj éà àék
> > > at
> > > org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2Resu
> > > ltSet.java:2955)
> > > at
> > > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2Res
> > > ultSet.java:2138)
> > > at
> > > org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSe
> > > t.java:1052)
> > > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > > [rt.jar:1.7.0_67]
> > > at
> > > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImp
> > > l.java:57) [rt.jar:1.7.0_67]
> > > at
> > > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcc
> > > essorImpl.java:43) [rt.jar:1.7.0_67]
> > > at java.lang.reflect.Method.invoke(Method.java:606)
> > > [rt.jar:1.7.0_67]
> > > at
> > > org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHand
> > > ler.continueInvocation(AbstractResultSetProxyHandler.java:104)
> > > [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
> > >
> > > which might be the result from a wrong assumption within the
> > > Hibernate layer while evaluating the result set. This clearly
> > > supports your view. Weird though that it only appears with 9.1 and
> > > not 8.1.
> > >
> > > I am upgrading to 9.1 as it is the latest version supported by
> > > Ubuntu 12.04 LTS. Once this is done, the system will be migrated to
> > > 16.04 LTS and then the database to PostgreSQL to 9.5.
> > >
> > > Andreas
> > >
> > >
> > > Dave Cramer ---21/04/2016 14:24:47---Andreas, So did the server
> > > have an error after that ? It does not appear to. In
> > >
> > > From: Dave Cramer <pg(at)fastcrypt(dot)com>
> > > To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > > Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> > > Date: 21/04/2016 14:24
> > > Subject: Re: [JDBC] Column order seems to play a role after
> > > migration from 8.1 to 9.1 if sequences are used
> > > Sent by: pgsql-jdbc-owner(at)postgresql(dot)org
> > >
> > >
> > >
> > > Andreas,
> > >
> > > So did the server have an error after that ? It does not appear to.
> > > In which case it is still some problem with hibernate. At this
> > > point I suspect hibernate thinks the first value returned is the id
> > > which it subsequently tries to put into the id column?
> > >
> > > PS. Why did you not upgrade to 9.5 ? FYI, 9.2 has significant
> > > performance benefits over 9.1
> > >
> > >
> > >
> > > Dave Cramer
> > >
> > > davec(at)postgresintl(dot)com
> > > www.postgresintl.com
> > >
> > > On 21 April 2016 at 08:19, Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > > wrote:
> > > Dave,
> > >
> > > Thanks a lot for your quick reply.
> > >
> > > Unfortunately, I don't think your guess is right as the generated
> > > statement does not contain the id. Here's what the PostgreSQL
> > > server logs:
> > >
> > > 2016-04-21 14:11:21 CEST LOG: execute <unnamed>: insert into
> > > phrases (frequency, language, phrase) values ($1, $2, $3) RETURNING
> > > *
> > > 2016-04-21 14:11:21 CEST DETAIL: parameters: $1 = '4', $2 = 'ger',
> > > $3 = 'lkjlkjlkj ljlékjlékj lék'
> > >
> > > Another point that speaks against this theory is that the same JDBC
> > > driver jar works fine with PostgreSQL 8.1 .
> > >
> > > Andreas
> > >
> > > Dave Cramer ---21/04/2016 12:15:24---Andreas, My guess is that
> > > hibernate is adding the id parameter into the statement
> > >
> > > From: Dave Cramer <pg(at)fastcrypt(dot)com>
> > > To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > > Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> > > Date: 21/04/2016 12:15
> > > Subject: Re: [JDBC] Column order seems to play a role after
> > > migration from 8.1 to 9.1 if sequences are used
> > > Sent by: davecramer(at)gmail(dot)com
> > >
> > >
> > >
> > >
> > > Andreas,
> > >
> > > My guess is that hibernate is adding the id parameter into the
> > > statement and expects it to be the first column.
> > >
> > > This could be confirmed by looking at the server logs.
> > >
> > > P.S. Please subscribe to the pgjdbc list
> > >
> > > Dave Cramer
> > >
> > > davec(at)postgresintl(dot)com
> > > www.postgresintl.com
> > >
> > > On 21 April 2016 at 04:59, Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > > wrote:
> > > Hello,
> > >
> > > I don't know if the root cause of my observation is effectively
> > > JDBC-related, but I thought it might be the best starting point.
> > >
> > > 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 (h
> > > ttp://stackoverflow.com/questions/25977903/hibernate-columns-
> > > mismatch-on-persistance), 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 with the community and ask the question, if
> > > you have any ideas what the actual root cause is. Please point me
> > > to any resources, if that is a known and justified behaviour of the
> > > database. Otherwise, I hope this might help others in similar
> > > situations.
> > >
> > > Cheers,
> > > Andreas
> > >
> > >
>
>
>
>