Error at Preview in Template

[eleaese]

We are using Alfresco 1.4 Community with DB2 UDB 9.1 on Windows 2003 .

After installing Alfresco, everything went fine, except for the following. When we choose to view details of a document, for example and html one, select action Preview in template, choose any template from the combo, we got the attached error from Hibernate:

Hibernate operation: could not load an entity: [org.alfresco.repo.domain.hibernate.NodeStatusImpl#component[protocol,identifier,guid]{protocol=workspace, guid=b08db99b-a354-11db-9288-afa9868bb29e_en_US, identifier=SpacesStore}]; SQL [select nodestatus0_.protocol as

Full text is at the end of the message.

SQLSTATE 22001 means:

Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small.

Do anybody get the same issue and have a way to overcome the problem?

Thanks in advance.


The Hibernate exception

Hibernate operation: could not load an entity: [org.alfresco.repo.domain.hibernate.NodeStatusImpl#component[protocol,identifier,guid]{protocol=workspace, guid=b08db99b-a354-11db-9288-afa9868bb29e_en_US, identifier=SpacesStore}]; SQL [select nodestatus0_.protocol as protocol3_3_, nodestatus0_.identifier as identifier3_3_, nodestatus0_.guid as guid3_3_, nodestatus0_.transaction_id as transact4_3_3_, nodestatus0_.node_id as node5_3_3_, nodeimpl1_.id as id0_0_, nodeimpl1_.protocol as protocol0_0_, nodeimpl1_.identifier as identifier0_0_, nodeimpl1_.uuid as uuid0_0_, nodeimpl1_.type_qname as type5_0_0_, nodeimpl1_.acl_id as acl6_0_0_, storeimpl2_.protocol as protocol6_1_, storeimpl2_.identifier as identifier6_1_, storeimpl2_.root_node_id as root3_6_1_, aspects3_.node_id as node1_5_, aspects3_.qname as qname5_, parentasso4_.child_node_id as child3_6_, parentasso4_.id as id6_, parentasso4_.id as id4_2_, parentasso4_.parent_node_id as parent2_4_2_, parentasso4_.child_node_id as child3_4_2_, parentasso4_.type_qname as type4_4_2_, parentasso4_.qname as qname4_2_, parentasso4_.child_node_name as child6_4_2_, parentasso4_.child_node_name_crc as child7_4_2_, parentasso4_.is_primary as is8_4_2_, parentasso4_.assoc_index as assoc9_4_2_ from alf_node_status nodestatus0_ left outer join alf_node nodeimpl1_ on nodestatus0_.node_id=nodeimpl1_.id left outer join alf_store storeimpl2_ on nodeimpl1_.protocol=storeimpl2_.protocol and nodeimpl1_.identifier=storeimpl2_.identifier left outer join alf_node_aspects aspects3_ on nodeimpl1_.id=aspects3_.node_id left outer join alf_child_assoc parentasso4_ on nodeimpl1_.id=parentasso4_.child_node_id where nodestatus0_.protocol=? and nodestatus0_.identifier=? and nodestatus0_.guid=?]; DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null; nested exception is com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null

[derek]

Hi,

I've raised the issue: http://issues.alfresco.com/browse/AR-1170.  Helping us identify the issue would speed things up significantly.

Firstly, take copies of the schema script files that were generated during system startup - you should see the file names listed in the log file from the startup.
Take a look at the schema scripts to see if there are any obvious issues.  Then attempt to find the column(s) that are causing the issue.  You can execute the table creation scripts manually.
If you know which columns are the issue, then the Hibernate dialect can be modified.  But identifying the problem would be the biggest help.

Regards

[eleaese]

Hi,

Thank your for your reply.

After I configured db access for DB2 UDB, Alfresco tried to create schema with Hibernate. I found an issue with table alf_child_assoc

create table alf_child_assoc (
id bigint generated by default as identity,
   parent_node_id bigint,
   child_node_id bigint,
type_qname varchar(255) not null,
qname varchar(255) not null,
child_node_name varchar(50) not null,
child_node_name_crc bigint not null,
is_primary smallint,
assoc_index integer,
primary key (id),
unique (parent_node_id, type_qname, child_node_name, child_node_name_crc)
);

because unique indexes couldn't include null columns.

I checked and found the hibernate mapping file at org/alfresco/repo/domain/hibernate/node.hbm.xml, in file WEB-INF/lib/repository.jar

The mapping for class org.alfresco.repo.domain.hibernate.ChildAssocImpl was:

…
<class
         name="org.alfresco.repo.domain.hibernate.ChildAssocImpl"
         proxy="org.alfresco.repo.domain.ChildAssoc"
         dynamic-insert="false"
         dynamic-update="false"
         lazy="true"
         optimistic-lock="version"
         table="alf_child_assoc" >
      <!– auto-generated ID –>
      <id name="id" column="id" type="long" >
         <generator class="native" />
      </id>
      <!– forward assoc to parent node –>
      <many-to-one
            name="parent"
            class="org.alfresco.repo.domain.hibernate.NodeImpl"
            lazy="proxy"
            fetch="select"
            optimistic-lock="true"
            not-null="true"
            unique-key="UIDX_CHILD_NAME" >
         <column name="parent_node_id" />
      </many-to-one>
      <!– forward assoc to child node –>
      <many-to-one
            name="child"
            lazy="proxy"
            fetch="select"
            class="org.alfresco.repo.domain.hibernate.NodeImpl"
            optimistic-lock="true"
            not-null="true" >
         <column name="child_node_id" />
      </many-to-one>
      <property name="typeQName" column="type_qname" type="QName" length="255" not-null="true" unique-key="UIDX_CHILD_NAME" />
      <property name="qname" column="qname" type="QName" length="255" not-null="true" />
      <property name="childNodeName" column="child_node_name" type="string" length="50" not-null="true" unique-key="UIDX_CHILD_NAME" />
      <property name="childNodeNameCrc" column="child_node_name_crc" type="long" not-null="true" unique-key="UIDX_CHILD_NAME" />
      <property name="isPrimary" column="is_primary" />
      <property name="index" column="assoc_index" />
   </class>
…

  

I changed the line with  "Changed ->"

<!– forward assoc to parent node –>
      <many-to-one
            name="parent"
            class="org.alfresco.repo.domain.hibernate.NodeImpl"
            lazy="proxy"
            fetch="select"
            optimistic-lock="true"
            not-null="true"
            unique-key="UIDX_CHILD_NAME" >
Changed ->         <column name="parent_node_id" not-null="true" />
      </many-to-one>

to instruct Hibernate to declare the column as Not Null. The SQL script was

…
create table alf_child_assoc (
id bigint generated by default as identity,
parent_node_id bigint not null,
child_node_id bigint,
type_qname varchar(255) not null,
qname varchar(255) not null,
child_node_name varchar(50) not null,
child_node_name_crc bigint not null,
is_primary smallint,
assoc_index integer,
primary key (id),
unique (parent_node_id, type_qname, child_node_name, child_node_name_crc)
);
…

Everything went fine after the change, Alfresco started OK. The problem arised when we tried to apply templates.

A minor comment. I issued the select statement at the DB2 Command Line Processor, without the where …,  and it worked. The sentence was:

select nodestatus0_.protocol as protocol3_3_, nodestatus0_.identifier as identifier3_3_, nodestatus0_.guid as guid3_3_, nodestatus0_.transaction_id as transact4_3_3_, nodestatus0_.node_id as node5_3_3_, nodeimpl1_.id as id0_0_, nodeimpl1_.protocol as protocol0_0_, nodeimpl1_.identifier as identifier0_0_, nodeimpl1_.uuid as uuid0_0_, nodeimpl1_.type_qname as type5_0_0_, nodeimpl1_.acl_id as acl6_0_0_, storeimpl2_.protocol as protocol6_1_, storeimpl2_.identifier as identifier6_1_, storeimpl2_.root_node_id as root3_6_1_, aspects3_.node_id as node1_5_, aspects3_.qname as qname5_, parentasso4_.child_node_id as child3_6_, parentasso4_.id as id6_, parentasso4_.id as id4_2_, parentasso4_.parent_node_id as parent2_4_2_, parentasso4_.child_node_id as child3_4_2_, parentasso4_.type_qname as type4_4_2_, parentasso4_.qname as qname4_2_, parentasso4_.child_node_name as child6_4_2_, parentasso4_.child_node_name_crc as child7_4_2_, parentasso4_.is_primary as is8_4_2_, parentasso4_.assoc_index as assoc9_4_2_ from alf_node_status nodestatus0_ left outer join alf_node nodeimpl1_ on nodestatus0_.node_id=nodeimpl1_.id left outer join alf_store storeimpl2_ on nodeimpl1_.protocol=storeimpl2_.protocol and nodeimpl1_.identifier=storeimpl2_.identifier left outer join alf_node_aspects aspects3_ on nodeimpl1_.id=aspects3_.node_id left outer join alf_child_assoc parentasso4_ on nodeimpl1_.id=parentasso4_.child_node_id

I also activated ShowSQL=true for Hibernate settings, but it didn't show the value of parameters. I suspect (just an idea) the problem is related with the where parameters.

[derek]

Hi,

Thanks for the suggestion regarding the indexes.  We'll fix that.

Could you double check that you are using the correct JDBC driver for your database version.  We have come across this type of problem when the JDBC driver was incorrect, although not on DB2.  What is the exact version of your JDBC driver?

When you do put in some data into the where clause manually, does the query blow up?

Regards

[eleaese]

Hi,

Thank you for your answer.

Re: jdbc driver version.
I'm using IBM DB2 UDB 9.1 Type 4 JDBC Driver
I also try with IBM DB2 UDB 8.2.4 Type 4 JDBC Driver, with the same result.

Re: SQL with parameters
It worked OK.
I tested with some combinations, everything went fine at CLP. For example:

where nodestatus0_.protocol='archive' and
nodestatus0_.identifier='SpacesStore' and
nodestatus0_.guid='aecb7b27-a355-11db-9288-afa9868bb29e'

Re: Hibernate logging
I changed Hibernate logging to get SQL and parameters. The log entries are:

14:20:27,401 DEBUG [org.hibernate.type.StringType] binding 'workspace' to parameter: 1
14:20:27,401 DEBUG [org.hibernate.type.StringType] binding 'SpacesStore' to parameter: 2
14:20:27,417 DEBUG [org.hibernate.type.StringType] binding 'b08db99b-a354-11db-9288-afa9868bb29e_en_US' to parameter: 3

Testing the SQL statement with those parameters at the command line worked OK, resulting in an empty recordset, no rows.

[derek]

What's with the _en_US?  I've not seen that before.  It would certainly cause the truncation as the column is only allowed to be 40 characters.  In any case, the query would return no results - it's not a valid GUID.

Since that data cannot be contained in the database, it would have to be held or generated somewhere else.  Does the stack trace give any clues?  My initial guess is that the client code (template code) is generating a NodeRef with an invalid GUID.  Check the current HEAD for the same problem.  It might be that it was fixed during subsequent bug fixing.

Regards

[eleaese]

Hi,

Thank you for your explanation. Please let me dig a little bit more. I checked the code at the page, it shows the right ID (I mean, without the suffix en_US) related with the combo item in order to select the template to apply.

Could you explain me a little bit more how I can fix it ?

Besides that, my main purpose was to validate DB2 UDB as a valid DBMS for repository services. By far this small issue is negligible, I testes the whole document flow, actions, aspects and didn't find any trouble.

Thank you again.

[derek]

Hi,

You'll have to take a look at the full stack trace of the exception and follow the call right from the client down to the NodeService.  The aim is to find the code that generates the invalid NodeRef instance.

If you don't mind, could you attach the full stack trace as a file to the JIRA bug?

Regards

[warren]

Derek,

I have the same issue and I have checked the Jira bug to upload a stack trace but it has been marked as a duplicate of another bug (AR-1002) and closed.

This is not correct. This bug is quite distinct from the bug AR-1002 which relates to the failed creation of a table (alf_child_assoc) and indexes. Which is now working in the 2.0 release.

This error AR-1170 is still present in Community 2.0 release.

If this issue can be re-opened I can supply as much info as you require.

Regards

[derek]

You're right.  It's open again.