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

Поиск
Список
Период
Сортировка
От Andreas Arens
Тема Re: Column order seems to play a role after migration from 8.1 to 9.1 if sequences are used
Дата
Msg-id OFC788ADED.CE93CC43-ONC1257F9C.0044F3C4-C1257F9C.0045F84B@list.lu
обсуждение исходный текст
Ответ на Re: Column order seems to play a role after migration from 8.1 to 9.1 if sequences are used  (Dave Cramer <pg@fastcrypt.com>)
Ответы Re: Column order seems to play a role after migration from 8.1 to 9.1 if sequences are used  (Dave Cramer <davecramer@gmail.com>)
Re: Column order seems to play a role after migration from 8.1to 9.1 if sequences are used  (Dave Cramer <davecramer@gmail.com>)
Список pgsql-jdbc

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(AbstractJdbc2ResultSet.java:2955)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2138)
at org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:1052)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.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.AbstractResultSetProxyHandler.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
 

Inactive hide details for Dave Cramer ---21/04/2016 14:24:47---Andreas, So did the server have an error after that ? It does noDave 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@fastcrypt.com>
To: Andreas Arens <andreas.arens@list.lu>
Cc: List <pgsql-jdbc@postgresql.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@postgresql.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@postgresintl.com
www.postgresintl.com

On 21 April 2016 at 08:19, Andreas Arens <andreas.arens@list.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@fastcrypt.com>
    To:
    Andreas Arens <andreas.arens@list.lu>
    Cc:
    List <pgsql-jdbc@postgresql.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@gmail.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@postgresintl.com
    www.postgresintl.com

    On 21 April 2016 at 04:59, Andreas Arens <
    andreas.arens@list.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 (
      http://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

Вложения

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Dave Cramer
Дата:
Сообщение: Re: Column order seems to play a role after migration from 8.1 to 9.1 if sequences are used
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: Column order seems to play a role after migration from 8.1to 9.1 if sequences are used