Solutions: Alfresco 3.2r - PostgreSQL and Ibatis errors

[trialot]

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 use a PostgreSQL version higher than 8.3 and use patches for the maldefined SqlMaps. That also prevents future surprises.

A patch in the postgresql-3.2r.amp is not yet present, to my best knowledge.

In this thread, I am just giving some problems I encountered and their solutions.

[trialot]

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.

[trialot]

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.

[johnhelen]

It works great for me, thanks

[nowhere]

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!

[trialot]

@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.

[nowhere]

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

[trialot]

@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

[nowhere]

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!

[trialot]

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.