IntAct and PostgreSQL problems (was: digest message)

[Bruno Aranda]

Hi,

Rainer is having the same issues, and I think it is related to a bug with the integration of Hibernate and PostgreSQL. Basically, the pattern I am seeing here is that for some fields that we expect to be "text" or Lobs, hibernate is complaining that type Int is not valid.

We will investigate this next week then... if you are testing, you could use H2 or something while we find the issue...

Cheers,

Bruno

On 28 October 2011 16:41, dominik...@biologie.uni-freiburg.de <dominik...@biologie.uni-freiburg.de> wrote:

Hi guys,

i got a new error when trying to export the sample data
("intact_2006-07-19.xml") by using ExportToPsiXml.java, using one of
these publicationID´s:
IntactEntry intactEntry =
IntactEntryFactory.createIntactEntry(intactContext).addPublicationId("16469704");
IntactEntry intactEntry =
IntactEntryFactory.createIntactEntry(intactContext).addPublicationId("16469705");


Exception in thread "main"
org.springframework.dao.DataIntegrityViolationException: could not
execute query; SQL [select this_.ac as ac10_7.....
.....
Caused by: org.postgresql.util.PSQLException: Bad value for type int:
MNTDLAAGKMASAACSMDPIDSFELLDLLFDRQDGILRHVELGEGWGHVKDQVLPNPDSDDFLSSILGSGDSLPSSPLWSPEGSDSGISEDLPSDPQDTPPRSGPATSPAGCHPAQPGKGPCLSYHPGNSCSTTTPGPVIQVPEASVTIDLEMWSPGGRICAEKPADPVDLSPRCNLTVKDLLLSGSSGDLQQHHLGASYLLRPGAGHCQELVLTEDEKKLLAKEGITLPTQLPLTKYEERVLKKIRRKIRNKQSAQESRKKKKEYIDGLETRMSACTAQNQELQRKVLHLEKQNLSLLEQLKKLQAIVVQSTSKSAQTGT.
       at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:
2638)
       at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:
1968)
       at org.postgresql.jdbc3.Jdbc3ResultSet.getClob(Jdbc3ResultSet.java:
44)
       at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getClob(AbstractJdbc2ResultSet.java:
373)
       at org.hibernate.type.descriptor.sql.ClobTypeDescriptor
$2.doExtract(ClobTypeDescriptor.java:70)
       at
org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:
64)
       at
org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:
254)
       at
org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:
250)
       at
org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:
230)
       at
org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:
331)
       at
org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:
2265)
       at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1527)
       at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1455)
       at org.hibernate.loader.Loader.getRow(Loader.java:1355)
       at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:611)
       at org.hibernate.loader.Loader.doQuery(Loader.java:829)
       at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:
274)
       at org.hibernate.loader.Loader.doList(Loader.java:2533)


It´s a matter of the featureRange list as far as i know. If the ranges
are set to 0, the error disappears. When trying to importand export
other sample data, i do not get these error.
I don´t think i had this problem with the previous intact-version. I
set the intact-core to 2.4.1, but the rest is up to date.

This is not an urgent issue for me, just wanted to let you know what
is working/not working. Maybe its just due to the different version of
the intact-core.


Cheers,
Dominik


On Oct 27, 7:05 pm, "dominik.mert...@biologie.uni-freiburg.de"

<dominik.mert...@biologie.uni-freiburg.de> wrote:
> Hi Bruno,
>
> thanks a lot, it seems to work fine now. I just changed the version of
> the intact-core to 2.4.1 and this solved the issue. However, the
> number of controlled vocabularies is now over 1600, so many people
> seem to work on this, good to know!
> I will keep working on integrating the intact software in mine and
> hope to get it running soon. If i am reaching deadlock again i will
> let you know ;-)
>
> Thanks for the quick replies and the fast modifications on the
> project!
>
> Best regards,
> Dominik
>
> On Oct 26, 11:36 am, Bruno Aranda <brunoara...@gmail.com> wrote:
>
>
>
>
>
>
>
> > Hi,
>
> > I guess me updating the intact-kickstart to the latest intact-core 2.5.0 is
> > causing more problems that I thought. Basically, it expects some new tables
> > related to curation flows or application info.
>
> > I have created a class CreateDatabaseDDL in intact-kickstart, which can be
> > used to generate a fresh DDL for IntAct. I understand you are using a
> > database with existing data? You have two options if this is the case:
>
> > 1) Use intact-core 2.4.1 - update the intact-kickstart POM file with that
> > version.
> > 2) It is possible to generate manually a DDL containing the update between
> > two intact-core versions. To do that, speficy the version of the intact-core
> > you want to use (in this case 2.5.0 as seen in the POM file. I have added a
> > comment in the CreateDatabaseDDL class where I explain how you can use the
> > method SchemaUtils.generateUpdateSchemaDDLForPostgresSQL(...). You could try
> > to run the generated SQL statements in your existing database to modify the
> > schema manually.
>
> > Let us know if this works?
>
> > Cheers!
>
> > Bruno
>
> > On 25 October 2011 17:43, dominik.mert...@biologie.uni-freiburg.de <
>
> > dominik.mert...@biologie.uni-freiburg.de> wrote:
> > > Hi Bruno,
>
> > > thanks again for the quick reply, now i can start intact-kickstart in
> > > eclipse.
>
> > > However, when i tried to create the database (postgres) again and
> > > tried to import th controlled vocabularies, i realized that only 38
> > > rows in ia_controlledvocab, about 900 were expected... Therfore, when
> > > trying to run the ImportPsiData file it crashes.
> > > I tested all possibilities, creating the database:
>
> > > postgres=# CREATE DATABASE "intact-db" with owner = intact encoding =
> > > 'SQL_ASCII
> > > 'tablespace = pg_default template=template0;
>
> > > and without the SCL_ASCII encoding (pgadmin complained about it...)
> > > I tried to create the schema with the intact-kickstart running
> > > CreateDatabase, ImportControlledVocabularies and ImportPsiData, but i
> > > got several error messages.
>
> > > The most useful for you should be this one:
> > > CreateDatabase with Eclipse: (52 tables, 2 sequences: cv_local_seq,
> > > intact_ac)
> > > 2011-10-25 18:01:07,006 [main] INFO  (AbstractApplicationContext,456)
> > > - Refreshing
> > > org.springframework.context.support.ClassPathXmlApplicationContext@5dccce3c
> > > :
> > > startup date [Tue Oct 25 18:01:06 CEST 2011]; root of context
> > > hierarchy
> > > 2011-10-25 18:01:07,123 [main] INFO  (XmlBeanDefinitionReader,315) -
> > > Loading XML bean definitions from URL [jar:file:/C:/Users/
> > > dominator/.m2/repository/uk/ac/ebi/intact/core/intact-core/2.5.0/
> > > intact-core-2.5.0.jar!/META-INF/intact.spring.xml]
> > > 2011-10-25 18:01:09,679 [main] INFO  (XmlBeanDefinitionReader,315) -
> > > Loading XML bean definitions from URL [jar:file:/C:/Users/
> > > dominator/.m2/repository/uk/ac/ebi/intact/dataexchange/psimi/intact-
> > > psixml-exchange/2.1.3/intact-psixml-exchange-2.1.3.jar!/META-INF/
> > > intact.spring.xml]
> > > 2011-10-25 18:01:09,732 [main] INFO  (XmlBeanDefinitionReader,315) -
> > > Loading XML bean definitions from URL [jar:file:/C:/Users/
> > > dominator/.m2/repository/uk/ac/ebi/intact/dataexchange/intact-enricher/
> > > 2.1.3/intact-enricher-2.1.3.jar!/META-INF/intact.spring.xml]
> > > 2011-10-25 18:01:09,804 [main] INFO  (XmlBeanDefinitionReader,315) -
> > > Loading XML bean definitions from URL [jar:file:/C:/Users/
> > > dominator/.m2/repository/uk/ac/ebi/intact/dataexchange/intact-cvutils/
> > > 2.1.3/intact-cvutils-2.1.3.jar!/META-INF/intact.spring.xml]
> > > 2011-10-25 18:01:09,915 [main] INFO  (XmlBeanDefinitionReader,315) -
> > > Loading XML bean definitions from class path resource [META-INF/
> > > postgres-create.spring.xml]
> > > 2011-10-25 18:01:09,955 [main] INFO  (XmlBeanDefinitionReader,315) -
> > > Loading XML bean definitions from class path resource [META-INF/
> > > base.spring.xml]
> > > 2011-10-25 18:01:10,099 [main] INFO  (DefaultListableBeanFactory,612)
> > > - Overriding bean definition for bean 'intactConfig': replacing
> > > [Generic bean: class
> > > [uk.ac.ebi.intact.core.config.IntactConfiguration]; scope=;
> > > abstract=false; lazyInit=false; autowireMode=0; dependencyCheck=0;
> > > autowireCandidate=true; primary=false; factoryBeanName=null;
> > > factoryMethodName=null; initMethodName=null; destroyMethodName=null;
> > > defined in URL [jar:file:/C:/Users/dominator/.m2/repository/uk/ac/ebi/
> > > intact/core/intact-core/2.5.0/intact-core-2.5.0.jar!/META-INF/
> > > intact.spring.xml]] with [Generic bean: class
> > > [uk.ac.ebi.intact.core.config.IntactConfiguration]; scope=;
> > > abstract=false; lazyInit=false; autowireMode=0; dependencyCheck=0;
> > > autowireCandidate=true; primary=false; factoryBeanName=null;
> > > factoryMethodName=null; initMethodName=null; destroyMethodName=null;
> > > defined in class path resource [META-INF/base.spring.xml]]
> > > 2011-10-25 18:01:10,101 [main] INFO  (DefaultListableBeanFactory,612)
> > > - Overriding bean definition for bean 'userContext': replacing
> > > [Generic bean: class [uk.ac.ebi.intact.core.context.UserContext];
> > > scope=; abstract=false; lazyInit=false; autowireMode=0;
> > > dependencyCheck=0; autowireCandidate=true; primary=false;
> > > factoryBeanName=null; factoryMethodName=null; initMethodName=null;
> > > destroyMethodName=null; defined in URL [jar:file:/C:/Users/
> > > dominator/.m2/repository/uk/ac/ebi/intact/core/intact-core/2.5.0/
> > > intact-core-2.5.0.jar!/META-INF/intact.spring.xml]] with [Generic
> > > bean: class [uk.ac.ebi.intact.core.context.UserContext]; scope=;
> > > abstract=false; lazyInit=false; autowireMode=0; dependencyCheck=0;
> > > autowireCandidate=true; primary=false; factoryBeanName=null;
> > > factoryMethodName=null; initMethodName=null; destroyMethodName=null;
> > > defined in class path resource [META-INF/base.spring.xml]]
> > > 2011-10-25 18:01:10,883 [main] INFO  (DriverManagerDataSource,153) -
> > > Loaded JDBC driver: org.postgresql.Driver
> > > 2011-10-25 18:01:10,884 [main] INFO  (AbstractApplicationContext
> > > $BeanPostProcessorChecker,1332) - Bean 'kickstartDataSource' is not
> > > eligible for getting processed by all BeanPostProcessors (for example:
> > > not eligible for auto-proxying)
> > > 2011-10-25 18:01:10,920 [main] INFO  (AbstractApplicationContext
> > > $BeanPostProcessorChecker,1332) - Bean
> > > 'org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter#c970c0e'
> > > is not eligible for getting processed by all BeanPostProcessors (for
> > > example: not eligible for auto-proxying)
> > > 2011-10-25 18:01:10,946 [main] INFO
> > > (LocalContainerEntityManagerFactoryBean,221) - Building JPA container
> > > EntityManagerFactory for persistence unit 'intact-core-default'
> > > SLF4J: Class path contains multiple SLF4J bindings.
> > > SLF4J: Found binding in [jar:file:/C:/Users/dominator/.m2/repository/
> > > org/slf4j/slf4j-log4j12/1.6.1/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/
> > > StaticLoggerBinder.class]
> > > SLF4J: Found binding in [jar:file:/C:/Users/dominator/.m2/repository/
> > > org/slf4j/slf4j-jdk14/1.5.8/slf4j-jdk14-1.5.8.jar!/org/slf4j/impl/
> > > StaticLoggerBinder.class]
> > > SLF4J: Seehttp://www.slf4j.org/codes.html#multiple_bindingsforan
> > > explanation.
> > > 2011-10-25 18:01:17,447 [main] INFO  (JdbcSupportLoader,79) -
> > > Disabling contextual LOB creation as JDBC driver reported JDBC version
> > > [3] less than 4
> > > 2011-10-25 18:01:17,450 [main] INFO  (TransactionFactoryFactory,62) -
> > > Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory
> > > 2011-10-25 18:01:17,452 [main] INFO  (TransactionManagerLookupFactory,
> > > 80) - No TransactionManagerLookup configured (in JTA environment, use
> > > of read-write or transactional second-level cache is not recommended)
> > > 2011-10-25 18:01:17,498 [main] INFO  (SessionFactoryImpl,200) -
> > > building session factory
> > > 2011-10-25 18:01:19,957 [main] INFO  (SessionFactoryObjectFactory,105)
> > > - Not binding factory to JNDI, no JNDI name configured
> > > 2011-10-25 18:01:24,585 [main] ERROR (SchemaExport,386) -
> > > Unsuccessful: create table ia_lifecycle_event (ac varchar(30) not
> > > null, created timestamp not null, created_user varchar(30) not null,
> > > updated timestamp not null, userstamp varchar(30) not null, deprecated
> > > bool not null, note text, when timestamp, event_ac varchar(30) not
> > > null, publication_ac varchar(30) not null, user_ac varchar(30) not
> > > null, primary key (ac))
> > > 2011-10-25 18:01:24,595 [main] ERROR (SchemaExport,387) - ERROR:
> > > syntax error at or near "when"
> > > 2011-10-25 18:01:26,496 [main] ERROR (SchemaExport,386) -
> > > Unsuccessful: create index idx_event_who on ia_lifecycle_event
> > > (user_ac)
> > > 2011-10-25 18:01:26,506 [main] ERROR (SchemaExport,387) - ERROR:
> > > relation "ia_lifecycle_event" does not exist
> > > 2011-10-25 18:01:26,506 [main] ERROR (SchemaExport,386) -
> > > Unsuccessful: create index idx_event_event on ia_lifecycle_event
> > > (event_ac)
> > > 2011-10-25 18:01:26,506 [main] ERROR (SchemaExport,387) - ERROR:
> > > relation "ia_lifecycle_event" does not exist
> > > 2011-10-25 18:01:26,506 [main] ERROR (SchemaExport,386) -
> > > Unsuccessful: alter table ia_lifecycle_event add constraint
> > > FK_LIFECYCLE_EVENT_EVENT foreign key (event_ac) references
> > > ia_controlledvocab
> > > 2011-10-25 18:01:26,516
>

> ...
>
> read more »

[Bruno Aranda]

Hi,

I have found a workaround, which hilights we have a bug somewhere, but at least it can make IntAct work fine in PostgreSQL. Basically, you need to override the default versions of hibernate, by adding this to the POM file:

<dependency>

            <groupId>org.hibernate</groupId>

            <artifactId>hibernate-core</artifactId>

            <version>3.6.0.Final</version>

        </dependency>

        <dependency>

            <groupId>org.hibernate</groupId>

            <artifactId>hibernate-entitymanager</artifactId>

            <version>3.6.0.Final</version>

        </dependency>

And, to fix a problem with the automatic database creation in PostgreSQL a bug was fixed, so using this is recommended until we release the core with the fix soon.

<dependency>

            <groupId>uk.ac.ebi.intact.core</groupId>

            <artifactId>intact-core</artifactId>

            <version>2.5.1-SNAPSHOT</version>

        </dependency>

Cheers,

Bruno