nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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.

Find more posts tagged with

Comments

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.

mikeh

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

pim

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>

jturmelle

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