Re: two issues trying current james which seem related to openjpa

One last comment from testing current james from sources with postgresql 
11. The property standard_conforming_strings in postgresql.conf should 
be kept as the default (standard_conforming_strings = on). No error 
shows when running james this way.

The other error regarding JAMES_SIEVE_SCRIPT.ACTIVATION_DATE_TIME 
incompatibility with the same column in the given schema definition, 
seems related to openjpa and its postgresql integration. The error does 
not show with Derby.

Pablo Pita

On 24.11.20 09:32, Pablo Pita Leira wrote:
> Hello,
>
> there is a hint on the issue below by reading in James Server Wide 
> Configuration (https://james.apache.org/server/config-system.html):
>
> Note for postgresql databases: Add standard_conforming_strings=off to 
> your postgresql.xml, otherwise you will get ""Invalid escape string 
> Hint: Escape string must be empty or one character. {prepstmnt 
> 174928937 SELECT t0.mailbox_id, t0.mailbox_highest_modseq, 
> t0.mailbox_last_uid, t0.mailbox_name, t0.mailbox_namespace, 
> t0.mailbox_uid_validity, t0.user_name FROM public.james_mailbox t0 
> WHERE (t0.mailbox_name LIKE ? ESCAPE '\\' AND t0.user_name = ? AND 
> t0.mailbox_namespace = ?) [params=?, ?, ?]} [code=0, state=22025]"
>
> The postgresql.conf has standard_conforming_strings = off, and so it 
> is configured in the postgresql instance I am using. As the 
> documentation mentions postgresql.xml, is that a typo or is it 
> pointing to some other configuration somewhere else ? Any feedback here ?
>
> Pablo
>
> On 21.11.20 11:27, Pablo Pita Leira wrote:
>> Hello,
>>
>> I have compiled the current james-project from linagora's github 
>> repository. Then built the jpa docker server. I took the files that 
>> would get installed in the docker image to used them as installation 
>> in a debian server with PostgreSQL as database.
>>
>> When testing this installation, I have encounter two issues which 
>> seem related to openjpa.
>>
>> The first, is regarding the Sieve mailet, and a problem mapping a 
>> column of an entity:
>>
>> 9507  Global  INFO   [main] openjpa.Runtime - Starting OpenJPA 3.1.2
>> 9744  Global  INFO   [main] openjpa.jdbc.JDBC - Using dictionary 
>> class "org.apache.openjpa.jdbc.sql.PostgresDictionary".
>> 18173  Global  INFO   [main] openjpa.jdbc.JDBC - Connected to 
>> PostgreSQL version 11.9 using JDBC driver PostgreSQL JDBC Driver 
>> version 42.2.18.
>> 24017  Global  WARN   [main] openjpa.jdbc.Schema - Existing column 
>> "activation_date_time" on table "public.JAMES_SIEVE_SCRIPT" is 
>> incompatible with the same column in the given schema definition. 
>> Existing column:
>> Full Name: JAMES_SIEVE_SCRIPT.activation_date_time
>> Type: timestamp
>> Size: 35
>> Default: null
>> Not Null: false
>> Given column:
>> Full Name: JAMES_SIEVE_SCRIPT.ACTIVATION_DATE_TIME
>> Type: unknown(2014)
>> Size: 0
>> Default: null
>> Not Null: false
>>
>>
>> The second, is that the imap server throws a persistence exception. 
>> It seems that the sql to find the mailbox has a ESCAPE '\' part that 
>> is not OK.
>>
>> Wrapped by: org.apache.openjpa.persistence.PersistenceException: 
>> Unterminated string literal started at position 213 in SQL SELECT 
>> t0.mailbox_id, t0.mailbox_highest_modseq, t0.mailbox_last_uid, 
>> t0.mailbox_name, t0.mailbox_namespace, t0.mailbox_uid_validity, 
>> t0.user_name FROM public.JAMES_MAILBOX t0 WHERE (t0.mailbox_name LIKE 
>> ? ESCAPE '\' AND t0.user_name = ? AND t0.mailbox_namespace = ?). 
>> Expected  char {SELECT t0.mailbox_id, t0.mailbox_highest_modseq, 
>> t0.mailbox_last_uid, t0.mailbox_name, t0.mailbox_namespace, 
>> t0.mailbox_uid_validity, t0.user_name FROM public.JAMES_MAILBOX t0 
>> WHERE (t0.mailbox_name LIKE ? ESCAPE '\' AND t0.user_name = ? AND 
>> t0.mailbox_namespace = ?)} [code=0, state=42601]
>>     at 
>> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:5278)
>>     at 
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:5238)
>>     at 
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:134)
>>     at 
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:115)
>>     at 
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:67)
>>     at 
>> org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:162)
>>     at 
>> org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:42)
>>     at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1311)
>>     at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1062)
>>     at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:912)
>>     at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:843)
>>     at 
>> org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:601)
>>     at 
>> org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:297)
>>     at 
>> org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:314)
>>     at 
>> reactor.core.publisher.MonoFlattenIterable.subscribeOrReturn(MonoFlattenIterable.java:87)
>>     ... 45 common frames omitted
>> Wrapped by: org.apache.james.mailbox.exception.MailboxException: 
>> Search of mailbox UserBound{namespace=Optional[#private], 
>> user=Optional[Username{localPart=pablo.pita, 
>> domainPart=Optional[Domain : pitagoral.com]}], 
>> mailboxNameExpression=org.apache.james.mailbox.model.search.PrefixedRegex@9365af5b} 
>> failed
>>     at 
>> org.apache.james.mailbox.jpa.mail.JPAMailboxMapper.lambda$findMailboxWithPathLike$13(JPAMailboxMapper.java:175)
>>     at reactor.core.publisher.Flux.lambda$onErrorMap$29(Flux.java:6479)
>>
>>
>> Anyway, the emails somehow arrived at the destination, and I could 
>> also read emails from thunderbird.
>>
>> The upgrade to OpenJPA 3.1.2 from version 3.1.0, particularly because 
>> OpenJPA 3.1.1 claims to solve some issues related to date/time 
>> mappings 
>> (http://openjpa.apache.org/openjpa-3.1.x.html#changes-in-openjpa-311), 
>> did not solve the issues.
>>
>> Any feedback for the problems here ?
>>
>> Pablo
>>
>>