Mail: Mailbox refresh hang forever after migration from 1.1.4 to 1.3.2

[doc75]

Expected behavior

Mailbox opened without any issue and list of mail appears after a couple of seconds

Actual behavior

list of message never appear, the refresh circle stays forever

Here is the error log (mail address have been removed and replaced by <text explaining the content remove and approximate length>:

error_clean.log

Seems related to mail with a lot of recipients.

Mail app

Mail app version: 1.3.2 (right after migration from a 1.1.4 which was working like a charm)

Mailserver or service: Dovecot (personal installation, the same one used with 1.1.4)

Server configuration

Operating system: Alpine Linux (official Nextcloud docker image)

Web server: Nginx

Database: PostgreSQL

PHP version: 7.3.16

Nextcloud Version: 17.0.25 (official docker image)

Client configuration

Browser: Firefox 75.0

Operating system: Ubuntu 18.04

[ChristophWurst]

Seems related to mail with a lot of recipients.

Do you know the specific message that causes this? Mind sending an exported .mbox (via Thunderbird or Evolution) to my email? I would like to inspect that header. I think it's malformed.

The app inserts one entry per recipient. But somehow in your case many of them are crammed into a single item. Thus the error

"Message": "Sync failed for 1:INBOX: An exception occurred while executing 'INSERT INTO \"oc_mail_recipients\" (\"message_id\", \"type\", \"label\", \"email\") VALUES(?, ?, ?, ?)' with params [310, 1, <list of mail address separated by 2 commas and the last one surrounded by double quotes (number of char in the string is about 1900)>]:\n\nSQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(255)",

[FleischKarussel]

I can confirm the hanging of Mail 1.3.2 with mailbox.org as IMAP provider (with a lot of emails in Inbox and multiple folders), when it was working with an older release, don't know the previous release.
GMX IMAP with just one email in the Inbox, works fine. ( I know, not a good comparison.)
I am a bit clueless what's the error log OP attached.
Is this a stripped nextcloud.log?

Using Docker Nextcloud 18.0.3 and Mail 1.3.2 with Docker PGSQL 11.x

[ChristophWurst]

confirm this

what exactly are you confirming?

[doc75]

Do you know the specific message that causes this? Mind sending an exported .mbox (via Thunderbird or Evolution) to my email? I would like to inspect that header. I think it's malformed.

Mail sent with information.
Thanks.

[zpintar]

@ChristophWurst This is exactly the same error what we are getting in our log.
Our admins try to check dbase's configs, but without success.
Not all mailboxes are affected. Only these with many e-mails in inbox.
We are using Nextcloud 18.03.

[ChristophWurst]

Another suggestion: enable query logs on the database. Once the error returns inspect the SQLs issued before the aborted transaction.

[ChristophWurst]

Ah I possibly mixed a few tickets. @zpintar are you also affected by the insert failing due to a very long recipient?

[zpintar]

Ah I possibly mixed a few tickets. @zpintar are you also affected by the insert failing due to a very long recipient?

I'm not sure that I understood this? What is "very long recipient? e-mail with long name or e-mail with long message(s), or many e-mail in receive inbox?

[ChristophWurst]

is String data, right truncated: 7 ERROR: value too long for type character varying(255) the error you're experiencing?

I'll see if I can reproduce the problem locally with @doc75's email

[ChristophWurst]

Okay, I can actually not. The email imports just fine. The recipients are correctly treated as multiple, hence no error when inserting.

[zpintar]

I noticed our server admins to this thread, so they will communicate with you about this problem. I'm only power user. ;)

[zpintar]

Hmm, maybe this is something useful?
"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":117,"Previous":{"Exception":"OCA\\Mail\\Exception\\ServiceException","Message":"Sync failed for 25:INBOX: An exception occurred while executing 'INSERT INTO \"oc_mail_recipients\" (\"message_id\", \"type\", \"label\", \"email\") VALUES(?, ?, ?, ?)' with params [581675, 0, \"\\x73\\x74\\x72\\x75\\xe8\\x6e\\x6f\\x67\\x40\\x69\\x70\\x7a\\x2e\\x68\\x72\", \"\\x73\\x74\\x72\\x75\\xe8\\x6e\\x6f\\x67\\x40\\x69\\x70\\x7a\\x2e\\x68\\x72\"]:\n\nSQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xe8 0x6e
Something with UTF8 encoding??

[ChristophWurst]

Yeah, I'm almost confident that this is the root of all these errors.

0xe8 0x6e

Is that the full sequence? Because if so, that isn't valid utf8 according to https://onlineutf8tools.com/convert-hexadecimal-to-utf8

It would mean that the problem actually is the same as in https://github.com/nextcloud/mail/issues/2925. But just with a different database type.

[ChristophWurst]

So in https://github.com/nextcloud/mail/issues/2940 we actually have the same problem.

[ChristophWurst]

Possibly related issues

• https://github.com/nextcloud/server/issues/7876

And there was another app with a somewhat related problem: https://github.com/nextcloud/bookmarks/pull/246. I just don't know if/how we can improve that. I'll dig into that.

@zpintar if you have an account on that mail server where the problem occurs and you could give me the credentials, I could perhaps debug the problem more easily. Would that be an option? Of course don't share anything personal or sensitive. A new account with just one of the messages causing trouble is sufficient :v:

[zpintar]

Huh, this is my business account and it is confidential, so I can not give you access in it. Sorry. Maybe I can isolate some problematic messages and create another account for investigation.
I'll have to see with our server admins.

[ChristophWurst]

I checked the code. And both the value for the email address and the label should already be UTF-8. I don't know where the invalid sequence is coming from.

[Urs-O]

This affects me also. Could I help with delivering additional information?

[ChristophWurst]

See https://github.com/nextcloud/mail/issues/2930#issuecomment-616423921. A test account where this is reproducible would be highly appreciated :v:

[vpluar]

Hello,
We have detect that the problem is with foreign characters in the email's header. It is send error when trying to insert them in the postgre DB.
2020-04-19 19:52:45.595 CEST [19209] ncadmin@nextcloud_db ERROR: invalid byte sequence for encoding "UTF8": 0xe1 0x72 0x64
2020-04-19 19:52:45.595 CEST [19209] ncadmin@nextcloud_db STATEMENT: INSERT INTO "oc_mail_recipients" ("message_id", "type", "label", "email") VALUES($1, $2, $3, $4)
2020-04-19 19:52:45.625 CEST [19209] ncadmin@nextcloud_db ERROR: current transaction is aborted, commands ignored until end of transaction block
2020-04-19 19:52:45.625 CEST [19209] ncadmin@nextcloud_db STATEMENT: UPDATE "oc_mail_mailboxes" SET "sync_new_lock" = $1, "sync_changed_lock" = $2, "sync_vanished_lock" = $3 WHERE "id" = $4
2020-04-19 19:52:45.634 CEST [19209] ncadmin@nextcloud_db ERROR: current transaction is aborted, commands ignored until end of transaction block
2020-04-19 19:52:45.634 CEST [19209] ncadmin@nextcloud_db STATEMENT: UPDATE "oc_jobs" SET "execution_duration" = $1 WHERE "id" = $2
2020-04-19 19:52:45.635 CEST [19209] ncadmin@nextcloud_db ERROR: current transaction is aborted, commands ignored until end of transaction block
2020-04-19 19:52:45.635 CEST [19209] ncadmin@nextcloud_db STATEMENT: UPDATE "oc_jobs" SET "reserved_at" = '0' WHERE "id" = $1

For example we have find a received email where there is aditional addresses malformed, for example begoñ[email protected]

[doc75]

See #2930 (comment). A test account where this is reproducible would be highly appreciated v

@ChristophWurst I am on the way to create a test account and check that I can reproduce the issue with a single (or small bunch of mail). When done, I'll email you the credentials to help debugging.

[ChristophWurst]

We have detect that the problem is with foreign characters in the email's header. It is send error when trying to insert them in the postgre DB.

That is correct. The open question is why the encoding is invalid when inserted. It should be in UTF-8 when we get it from the IMAP lib and UTF-8 should be fine with the database.

[doc75]

@ChristophWurst mail sent with information to connect to a test account (2 different mails having the issue decribed in my log)

[ChristophWurst]

@doc75 thanks a lot. I can connect to the account and get an error during the initial sync as well with 1406 Data too long for column 'label' at row 1" on mysql. Yay.

[ChristophWurst]

Something is up with that one specific message. Also Gnome's evolution chokes on it. This is the message header:

So, I think the person who sent the email messed up the recipient list, thus most of them are now included in the label of the third most last recipient. Then there are two more recipients that look OK.

It looks like there is a " quote for the first recipient, then you only see ,, and some „. Then there is the other " and it makes sense that the two applications detect all of this as on recipient with a very long label. I wouldn't be surprised if only a few of the listed people got the email.

For this app the best we can do is to just cap the label after x characters.

[ChristophWurst]

For this app the best we can do is to just cap the label after x characters.

Et voila: https://github.com/nextcloud/mail/pull/2955

[zpintar]

@ChristophWurst Just tried v 1.3.3. Still does not work in my case. Throws the same error as earlier. Invalid byte sequence in UTF-8. I'll try to do some isolated e-mails and forward to you.

[ChristophWurst]

Yes there were no changes related to this problem.

I'll try to do some isolated e-mails and forward to you.

Awesome. Thanks a lot!

[doc75]

Issue fixed in v1.3.3.

@ChristophWurst do you want to keep it open for @zpintar issue or can we close it ?

[campill0]

Hello,
I have upgraded to 1.3.3 and the error keeps happening.
The mail application hangs if there is any mail with a recipient with foreign characters such as españ[email protected]

[zpintar]

@ChristophWurst Yes. I can confirm that problem lies on foreign characters in Senders:
For example, this particular e-mail sender's e-mails hang the app (bold characters make trouble): struè[email protected], voð[email protected],
grað.[email protected]

Maybe is the best to split this issue as new one?

[ChristophWurst]

Yes, let's split it. The problem @doc75 originally reported has been resolved.

[doc75]

Fixed with v1.3.3

[zpintar]

Yes, let's split it. The problem @doc75 originally reported has been resolved.

I created new issue for problem with foreign characters #2968