topic Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors in Alfresco Archive

Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

trialot — Mon, 02 Nov 2009 14:02:17 GMT

Alfresco owners who use PostgreSQL must be aware that some errors can occur due to maldefined SqlMaps. NOTE: In most cases, a previous version of PostgreSQL will exhibit strange behavior in the sense that it is accepting the maldefined SqlMaps. That does not mean the problem is not there. ADVISE: do

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

trialot — Mon, 02 Nov 2009 14:05:31 GMT

If your logs do have the above mentioned error, just patch the file:

../tomcat/webapps/alfresco/WEB-INF/classes/alfresco/ibatis/org.hibernate.dialect.Dialect/propval-common-SqlMap.xml

and remove all "||" operators. There are three of them.

Replace them with "or" operator.

NOTE: patch is not confirmed by Alfresco. Changing is at own risk, but works like a charm for me.

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

trialot — Mon, 02 Nov 2009 14:08:55 GMT

If your logs do have the above mentioned error, just patch the file:

../tomcat/webapps/alfresco/WEB-INF/classes/alfresco/ibatis/org.hibernate.dialect.PostgreSQLDialect/audit-insert-SqlMap.xml

and change alf_audit_entry to alf_audit_entry_seq. You should have:

<insert id="insert_AuditEntry" parameterClass="AuditEntry" >

        <selectKey resultClass="long" keyProperty="id">
            select nextVal('alf_audit_entry_seq')
        </selectKey>

        insert into alf_audit_entry (id, audit_app_id, audit_user_id, audit_time, audit_values_id)
        values (#id#, #auditApplicationId#, #auditUserId#, #auditTime#, #auditValuesId#)

    </insert>‍‍‍‍‍‍‍‍‍‍

NOTE: patch is not confirmed by Alfresco. Changing is at own risk, but works like a charm for me.

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

johnhelen — Wed, 04 Nov 2009 00:47:42 GMT

It works great for me, thanks

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

nowhere — Mon, 23 Nov 2009 09:55:04 GMT

Hi,
I downloaded alfresco-community-war-3.2r2.zip and I'm getting following error:

ERROR [web.context.ContextLoader] Context initialization failed
org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL
 grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLExcep
tion:
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-insert-SqlMap.x
ml.
— The error occurred while applying a parameter map.
— Check the alfresco.avm.insert_AVMStoreProperty-SelectKey-InlineParameterMap.

— Check the statement (query failed).
— Cause: org.postgresql.util.PSQLException: ERROR: relation "dual" does not ex
ist
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-insert-SqlMap.x
ml.‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Do I need postgresql AMP? What Is solution if any?
Thanks in advence for suggestions!

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

trialot — Mon, 23 Nov 2009 13:28:19 GMT

@Nowhere,

Your question is somewhat without context. Do you use PostgreSql or MySql as a database?

If PostgreSql: yes, then you need a postgresql-3.2r2.amp file.

This is not yet available for download (to my best knowledge).

It seems that you are use a PostgreSql database that involves some errors in the specific Sqlmap.

I did some earlier checks with 3.2r and made an amp file that patches errors in the postgresql-3.2r.amp file.

I will do a similar thing for the 3.2r2 variant and if necessary, include your error in my analysis and/or patch.

Some patience is needed therefore.

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

nowhere — Mon, 23 Nov 2009 14:03:29 GMT

Sorry Trialot,
reading topic title I was thinking it was referred to postgresql question. My superficiality, sorry.

Anyway I'm using postgresSql 8.3 and alfresco-community-war-3.2r2.zip. I downloaded postgresql-3.2-community.amp (not 3.2r2 that isn't available), but it didn't solve the issue. So I changed in avm-insert-SqlMap.xml

<insert id="insert_AVMStoreProperty" parameterMap="parameter_IdStorePropertyPatternMap">
    
        <selectKey resultClass="long" keyProperty="id" type="pre">
            select avm_store_properties_seq.nextval as value from dual
        </selectKey>
        
        insert into avm_store_properties (id, actual_type_n, persisted_type_n, multi_valued, boolean_value, long_value, float_value, double_value, string_value, serializable_value, avm_store_id, qname_id)
        values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        
    </insert>‍‍‍‍‍‍‍‍‍‍

to:

<insert id="insert_AVMStoreProperty" parameterMap="parameter_IdStorePropertyPatternMap">
    
        <selectKey resultClass="long" keyProperty="id" type="pre">
            select nextVal('avm_store_properties_seq')
        </selectKey>
        
        insert into avm_store_properties (id, actual_type_n, persisted_type_n, multi_valued, boolean_value, long_value, float_value, double_value, string_value, serializable_value, avm_store_id, qname_id)
        values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        
    </insert>‍‍‍‍‍‍‍‍‍‍

and it works, even if I don't know if it's correct what I did. After I solved some others problems in Sqlmaps, but they were very intuitive.

Thanks for your reply.

Greetings,
Nowhere

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

trialot — Mon, 23 Nov 2009 15:39:10 GMT

@Nowhere,

You indeed gave the information I needed 😉

Problem is the following: the error you reported should not be present at all, when using the postgresql-3.2r.amp file.

I noticed that you use another postgresql amp file and it is very likely to be the cause of errors.

My advice: install the postgresql-3.2r.amp file! That certainly is preventing some issues in the future.

Furthermore, note that the core sql maps of alfresco also have to be changed: see the posts above and verify whether it is complete now.

Glad, by the way, that you found your solution, always a good sign

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

nowhere — Mon, 23 Nov 2009 15:44:33 GMT

I had error before installing the postgresql-3.2.amp file, so I tried it because I didn't find the postgresql-3.2r.amp file. Is it available? If so I'll look for it better…thanks.

Still a question…what do you mean with

Furthermore, note that the core sql maps of alfresco also have to be changed: see the posts above and verify whether it is complete now.

?
What should I check? Sorry for silly questions, I'm newbie

Still…what are the informations you needed?

You indeed gave the information I needed

Thanks for your patience!

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

trialot — Mon, 23 Nov 2009 20:36:36 GMT

In the alfresco package, other typos are present in the SqlMaps.

It is not rocket science to change them, the posts above: the solutions and the sqlmaps are given.

Furthermore, before doing that, you should install the amp file, you can get it at:

http://process.alfresco.com/ccdl/?file=release/community/build-2384/postgresql-3.2r.amp

Install as a normal amp, restart and check the logs.

Then, changing the sqlmaps audit-insert-SqlMap.xml and propval-common-SqlMap.xml as indicated in the first posts of this thread.

Should do the trick!

PS a new postgresql-3.2r2.amp will be probably available soon…you can wait, but that it is not wise.

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

mikeh — Mon, 23 Nov 2009 20:43:29 GMT

In the alfresco package, other typos are present in the SqlMaps.

If you know there are problems, please raise them in JIRA and we'll get them fixed! Not everyone finds these forums.

Thanks,
Mike

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

pim — Mon, 25 Jan 2010 16:57:04 GMT

It looks like for 3.2r2, the problem is just this section in avm-insert-SqlMap.xml:

    <insert id="insert_AVMStoreProperty" parameterMap="parameter_IdStorePropertyPatternMap">
      …
    </insert>

Try

    <insert id="insert_AVMStoreProperty" parameterClass="AVMStoreProperty">
        <selectKey resultClass="long" keyProperty="id">
            select nextVal('avm_store_properties_seq')
        </selectKey>
        insert into avm_store_properties (id, avm_store_id, qname_id, actual_type_n, persisted_type_n, multi_valued, boolean_value, long_value, float_value, double_value, string_value, serializable_value)
        values (#id#, #avmStoreId#, #qnameId#, #actualType#, #persistedType#, #isMultiValued#, #booleanValue#, #longValue#, #floatValue#, #doubleValue#, #stringValue#, #serializableValue#)
    </insert>

Re: Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

jturmelle — Wed, 10 Feb 2010 00:13:25 GMT

It looks like for 3.2r2, the problem is just this section in avm-insert-SqlMap.xml:

<insert id="insert_AVMStoreProperty" parameterMap="parameter_IdStorePropertyPatternMap">
…
</insert>‍‍‍

Try

<insert id="insert_AVMStoreProperty" parameterClass="AVMStoreProperty">
<selectKey resultClass="long" keyProperty="id">
select nextVal('avm_store_properties_seq')
</selectKey>
insert into avm_store_properties (id, avm_store_id, qname_id, actual_type_n, persisted_type_n, multi_valued, boolean_value, long_value, float_value, double_value, string_value, serializable_value)
values (#id#, #avmStoreId#, #qnameId#, #actualType#, #persistedType#, #isMultiValued#, #booleanValue#, #longValue#, #floatValue#, #doubleValue#, #stringValue#, #serializableValue#)
</insert>‍‍‍‍‍‍‍

Do you mean replace part 1 with part 2, or simply add part 2?

Thanks,

Jeff