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

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