Mail: Can't read mailbox (spinner running continuously) when foreign characters are in Sender's address

[zpintar]

Expected behavior

When foreign characters are in e-mail sender's e-mails , mails are shown in the mailbox's lists.

Actual behavior

When this particular e-mail sender's e-mails are in mail header, app hang and can not show mailbox content (spinner running all the time). Examples (bold characters): struè[email protected], voð[email protected], grað.[email protected]...

error log:
Caused by OCA\Mail\Exception\ServiceException: Sync failed for 26:INBOX: An exception occurred while executing 'INSERT INTO "oc_mail_recipients" ("message_id", "type", "label", "email") VALUES(?, ?, ?, ?)' with params [634589, 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"]: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xe8 0x6e 0x6f

Mail app

Mail app version: 1.33

Mailserver or service: Postfix

Server configuration

Operating system: Debian 9.12

Web server: Apache2 - 2.4.25

Database: PostgreSQL 9.6.17 on x86_64-pc-linux-gnu

PHP version: 7.3.17

Nextcloud Version: 18.0.3

Client configuration

Browser: Google Chrome 81.x

Operating system: Windows 10

[ChristophWurst]

foreign characters

To be precise this seems to be about encoding. When we read this data it should be valid utf-8, yet the database won't let us insert it.

[zpintar]

foreign characters

To be precise this seems to be about encoding. When we read this data it should be valid utf-8, yet the database won't let us insert it.

Hm, but why? What can be wrong with Postgre? (Is MariaDB has the same insertion problem?) And this insertion problem isn't exist on old v.1.1.x version of Mail app.
How v.1.1.x was successfully inserting these characters in dbase?

I tested this characters in Subject and body of an e-mail and in this case all working well. So, the problem persist only if we have this characters in Sender part of E-mail header.

[ChristophWurst]

Hm, but why? What can be wrong with Postgre?

It's not necessarily postgres. Just at some point the encoding is not the expected utf-8 as I assume.

And this insertion problem isn't exist on old v.1.1.x version of Mail app.
How v.1.1.x was successfully inserting these characters in dbase?

Again, the app code changed. There was no such insert in the old version. New version, new code. Just because one version works does not mean the next does.

I tested this characters in Subject and body of an e-mail and in this case all working well. So, the problem persist only if we have this characters in Sender part of E-mail header.

That is interesting. Thanks for testing that.

[zpintar]

If I can help with some other testings, please tell me.

[ChristophWurst]

From the PHP console. It can't display it, but it says this is valid utf-8.

php > echo "\x73\x74\x72\x75\xe8\x6e\x6f\x67\x40\x69\x70\x7a\x2e\x68\x72";
stru�[email protected]
php > echo mb_detect_encoding("\x73\x74\x72\x75\xe8\x6e\x6f\x67\x40\x69\x70\x7a\x2e\x68\x72");
UTF-8

[ChristophWurst]

Uh-uh. If I follow https://www.php.net/manual/en/function.mb-detect-encoding.php#102510 and use the strict mode, then it actually says that it can't detect the encoding.

[zpintar]

BTW, these characters come from Latin-2 set (Croatian (HR) set)
https://www.periodni.com/hr/unicode_utf-8_kodiranje.html#croatian_special_characters

[zpintar]

For example, Thunderbird or Roundcube Webmail 1.0.0 see these characters like this:
RW: stru�nog
Thunderbird: struènog
But this is actually character: stručnog = č

[zpintar]

What is difference when Mail app inserting this string in Subject filed on dbase (where working) and in Sender field (where not working)? Are these two dbase fields have the same string format?

[ChristophWurst]

Just to be sure. Your postgres db is set to utf8 encoding, right? https://www.postgresql.org/docs/9.1/multibyte.html

[ChristophWurst]

Please get the me output of psql -l as seen in those docs for the nextcloud database

[zpintar]

Just to be sure. Your postgres db is set to utf8 encoding, right? https://www.postgresql.org/docs/9.1/multibyte.html

Please get the me output of psql -l as seen in those docs for the nextcloud database

I must check this with our server admins

[hbogner]

Few corrections to initial issue by @zpintar

Operating system: Debian 7.9
Web server: Nginx

Operating system: Debian 9.12
Web server: Apache2 - 2.4.25

Nextcloud installation was 16.0.4 when it was the latest release, upgraded to current 18.0.3 and mail was working normally before.

Now for the new stuff which @ChristophWurst asked:

Database was created as UTF8 database:
createdb -E UTF8 -O database_user database;

This is also visible with "psql -l":
database | database_user | UTF8 | en_US.UTF-8 | en_US.UTF-8

Caching was configured according to https://docs.nextcloud.com/server/16/admin_manual/installation/source_installation.html

  'memcache.local' => '\\OC\\Memcache\\APCu',
  'memcache.distributed' => '\\OC\\Memcache\\Redis',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => 'localhost',
    'port' => 6379,
  )

php.ini was setup according to https://docs.nextcloud.com/server/16/admin_manual/installation/server_tuning.html

opcache.enable=1
opcache.enable_cli=1
opcache.interned_strings_buffer=8
opcache.max_accelerated_files=10000
opcache.memory_consumption=128
opcache.save_comments=1
opcache.revalidate_freq=1

Any other information regarding server side I can provide?

[ChristophWurst]

Thanks. That looks fine.

Any other information regarding server side I can provide?

None so far but I'll let you know :v:

[zpintar]

Few corrections to initial issue by @zpintar

Operating system: Debian 7.9
Web server: Nginx

Operating system: Debian 9.12
Web server: Apache2 - 2.4.25

Nextcloud installation was 16.0.4 when it was the latest release, upgraded to current 18.0.3 and mail was working normally before.

Corrected. ;)

[zpintar]

@ChristophWurst any news here?

[ChristophWurst]

No

[vpluar]

Hello,
Is there any news about foreign characters?

[ChristophWurst]

Rule of thumb: if nobody updated the ticket, there was is no news.

[Zibc]

I am seeing the same exception in my log files. It is for the subject field instead though but I do not think that matters.
if I use that website to convert the hex: https://www.rapidtables.com/convert/number/hex-to-ascii.html
Then UTF8 does give an error, but iso-8859-1 not, which is the encoding declared in the e-mail header.
So would trying to convert from another encoding to UTF8 before inserting in the db a way around this issue ?

[ChristophWurst]

Then UTF8 does give an error, but iso-8859-1 not, which is the encoding declared in the e-mail header.
So would trying to convert from another encoding to UTF8 before inserting in the db a way around this issue ?

Yeah, that is the idea. However, the Horde lib already claims that the strings are UTF-8. So I don't know where to look for the incorrect handling.

Which attribute other than subject is it in your case?

[Zibc]

I meant, in my case, the e-mail subject causes the issue, not the sender address like this issue reports.

[ChristophWurst]

@Zibc which database is that?

[Zibc]

I see mail_messages in the logs

An exception occurred while executing 'INSERT INTO `oc_mail_messages` (`uid`, `message_id`, `mailbox_id`, `subject`, `sent_at`, `flag_answered`, `flag_deleted`, `flag_draft`, `flag_flagged`, `flag_seen`, `flag_forwarded`, `flag_junk`, `flag_notjunk`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ... : SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\x80 5,- ...' for column `cloud`.`oc_mail_messages`.`subject` at row 1

[ChristophWurst]

@Zibc which database is that? MySQL?

[Zibc]

Oops, apologies for the stupid previous answer

I'm running MariaDB 10.3.22

[ChristophWurst]

I'm running MariaDB 10.3.22

Then see https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/mysql_4byte_support.html

[Zibc]

That's already enabled unfortunately.

[ChristophWurst]

This is strange. Everyone else who had insert issues on mysql/mariadb fixed their setups that way. Please double and triple check.

[hbogner]

Maybe it's not the database issue? Mine is pgsql utf8 and the app breaks.

[ChristophWurst]

Yeah, the mysql users can fix their instances with the 4byte guide. For Postgres I still don't know why these errors occur.

[Zibc]

I already followed the guide to switch my instance to utf84byte

MariaDB [(none)]> SELECT default_character_set_name FROM information_schema.SCHEMATA  WHERE schema_name = "cloud";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4                    |
+----------------------------+

MariaDB [cloud]> SHOW FULL COLUMNS FROM oc_mail_messages;
+--------------------+--------------+-------------+------+-----+---------+----------------+---------------------------------+---------+
| Field              | Type         | Collation   | Null | Key | Default | Extra          | Privileges                      | Comment |
+--------------------+--------------+-------------+------+-----+---------+----------------+---------------------------------+---------+
| id                 | int(11)      | NULL        | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| uid                | int(11)      | NULL        | NO   | MUL | NULL    |                | select,insert,update,references |         |
| message_id         | varchar(255) | utf8mb4_bin | YES  |     | NULL    |                | select,insert,update,references |         |
| mailbox_id         | varchar(4)   | utf8mb4_bin | NO   |     | NULL    |                | select,insert,update,references |         |
| subject            | varchar(255) | utf8mb4_bin | NO   |     |         |                | select,insert,update,references |         |
| sent_at            | int(11)      | NULL        | NO   | MUL | NULL    |                | select,insert,update,references |         |
| flag_answered      | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| flag_deleted       | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| flag_draft         | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| flag_flagged       | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| flag_seen          | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| flag_forwarded     | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| flag_junk          | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| flag_notjunk       | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| updated_at         | int(11)      | NULL        | YES  |     | NULL    |                | select,insert,update,references |         |
| structure_analyzed | tinyint(1)   | NULL        | NO   |     | 0       |                | select,insert,update,references |         |
| flag_attachments   | tinyint(1)   | NULL        | YES  |     | NULL    |                | select,insert,update,references |         |
| preview_text       | varchar(255) | utf8mb4_bin | YES  |     | NULL    |                | select,insert,update,references |         |
+--------------------+--------------+-------------+------+-----+---------+----------------+---------------------------------+---------+

Also re-run the maintenance:repair command but did not had anything to repair.
The issue is still that the character is not a UTF8 valid code, but an ISO one.
Maybe convert from ISO to UTF8 if the query fails ? That sounds easy but I guess there is just more to it on the back end.

[Benance]

hello
I've got nearly the same problem as @Zibc
Here are the infos about my nextcloud instance:

Nginx + Mariadb on a debian 9 server

utf8mb4 activated

Here are the logs from nextcloud:

[mail] Error: OCA\Mail\Exception\ServiceException: Sync failed for 1:INBOX: An exception occurred while executing 'INSERT INTO oc_mail_messages (uid, message_id, mailbox_id, subject, sent_at, flag_answered, flag_deleted, flag_draft, flag_flagged, flag_seen, flag_forwarded, flag_junk, flag_notjunk, flag_important) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [14388, "[e-mail]", 10, "Plus qu\u2019un jour !", 1593162037, false, false, false, false, false, false, false, false, true]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '14388-10' for key 'mail_msg_mb_uid_idx' at <>

1. /var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php line 114
   OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox {id: 10}, 42, null, false)
2. /var/www/nextcloud/apps/mail/lib/BackgroundJob/SyncJob.php line 92
   OCA\Mail\Service\Sync\ImapToDbSynchronizer->syncAccount(OCA\Mail\Account {})
3. /var/www/nextcloud/lib/public/BackgroundJob/Job.php line 79
   OCA\Mail\BackgroundJob\SyncJob->run({accountId: 1})
4. /var/www/nextcloud/lib/public/BackgroundJob/TimedJob.php line 61
   OCP\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})
5. /var/www/nextcloud/cron.php line 125
   OCP\BackgroundJob\TimedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

at 2020-06-26T09:01:44+00:00

The problem occured after receiving a mail with a foreign character

How to solve the problem?

[ChristophWurst]

Integrity constraint violation

Yeah so no, you have another issue. This has nothing to do with the original report, hence the distinctive error message.

[ChristophWurst]

Maybe convert from ISO to UTF8 if the query fails ?

That is a hack at best. We should fix the cause, not the symptoms. The API promises to return UTF8, so clearly there is a bug somewhere that is responsible for the incorrect encoding.

[Benance]

All right,
But what to do (except waiting for a patch), to make nextcloud mail app work again?
or what can I do to help you to patch this bug (i.e: giving you some useful logs)?

[ChristophWurst]

Something that I have not considered yet is the possibility that it's either a problem with specific setup of Nextcloud (php) or specific IMAP servers. Would you have the possibility to set up another Nextcloud somewhere else to try to reproduce?

Another thing to test is export the message (as original as possible), insert it into another IMAP account (with no modifications if possible) and configure the second account in your primary Nextcloud. Then we'd know if it's possibly the IMAP server.

[Benance]

My nextcloud setup is not specific.
It runs with php 7.3
My IMAP server is Dovecot and I have never changed its default config.
Unfortunately, I have destroyed the faulty message...

[ChristophWurst]

My nextcloud setup is not specific.

Yes it is. It's specific to you.

So if anyone is willing to collaborate and help us test this with the help of more than one setup, please let us know. A generic does not work on my setup does not help at all as we can't find a common denominator based on that. This is still not reproducible with my dev setup, so I can't debug.

[Benance]

In case it could help to solve this bug, here is the full log generated just after the problem appeared:
OCA\Mail\Exception\ServiceException: Sync failed for 1:INBOX: An exception occurred while executing 'INSERT INTO oc_mail_messages (uid, message_id, mailbox_id, subject, sent_at, flag_answered, flag_deleted, flag_draft, flag_flagged, flag_seen, flag_forwarded, flag_junk, flag_notjunk, flag_important) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [14388, "[e-mail]", 10, "Plus qu\u2019un jour !", 1593162037, false, false, false, false, false, false, false, false, true]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '14388-10' for key 'mail_msg_mb_uid_idx'

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 114:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox { id: 10}, 42, null, false)

/var/www/nextcloud/apps/mail/lib/BackgroundJob/SyncJob.php - line 92:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->syncAccount(OCA\Mail\Account {})

/var/www/nextcloud/lib/public/BackgroundJob/Job.php - line 79:

OCA\Mail\BackgroundJob\SyncJob->run({ accountId: 1})

/var/www/nextcloud/lib/public/BackgroundJob/TimedJob.php - line 61:

OCP\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})

/var/www/nextcloud/cron.php - line 125:

OCP\BackgroundJob\TimedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

Causé parDoctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO oc_mail_messages (uid, message_id, mailbox_id, subject, sent_at, flag_answered, flag_deleted, flag_draft, flag_flagged, flag_seen, flag_forwarded, flag_junk, flag_notjunk, flag_important) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [14388, "[e-mail]", 10, "Plus qu\u2019un jour !", 1593162037, false, false, false, false, false, false, false, false, true]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '14388-10' for key 'mail_msg_mb_uid_idx'

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 169:

Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exceptio ... '", Doctrine\DBA ... ]})

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 145:

Doctrine\DBAL\DBALException::wrapException(Doctrine\DBA ... {}, Doctrine\DBA ... ]}, "An exceptio ... '")

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1063:

Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBA ... {}, Doctrine\DBA ... ]}, "INSERT INTO ... )", { 1: 14388,2 ... e})

/var/www/nextcloud/lib/private/DB/Connection.php - line 220:

Doctrine\DBAL\Connection->executeUpdate("INSERT INTO ... )", [ 14388,"<W_ ... e], [ 1,2,1,2,1,5,5,5,5,5,5,5,5,5])

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 203:

OC\DB\Connection->executeUpdate("INSERT INTO ... )", { uid: 14388 ... e}, { uid: 1,mes ... 5})

/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 215:

Doctrine\DBAL\Query\QueryBuilder->execute()

/var/www/nextcloud/apps/mail/lib/Db/MessageMapper.php - line 132:

OC\DB\QueryBuilder\QueryBuilder->execute()

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 312:

OCA\Mail\Db\MessageMapper->insertBulk(OCA\Mail\Db\Message { id: null})

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 202:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->runPartialSync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox { id: 10}, 42, null)

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 114:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox { id: 10}, 42, null, false)

/var/www/nextcloud/apps/mail/lib/BackgroundJob/SyncJob.php - line 92:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->syncAccount(OCA\Mail\Account {})

/var/www/nextcloud/lib/public/BackgroundJob/Job.php - line 79:

OCA\Mail\BackgroundJob\SyncJob->run({ accountId: 1})

/var/www/nextcloud/lib/public/BackgroundJob/TimedJob.php - line 61:

OCP\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})

/var/www/nextcloud/cron.php - line 125:

OCP\BackgroundJob\TimedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

Causé parDoctrine\DBAL\Driver\PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '14388-10' for key 'mail_msg_mb_uid_idx'

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1054:

Doctrine\DBAL\Driver\PDOStatement->execute()

/var/www/nextcloud/lib/private/DB/Connection.php - line 220:

Doctrine\DBAL\Connection->executeUpdate("INSERT INTO ... )", [ 14388,"<W_ ... e], [ 1,2,1,2,1,5,5,5,5,5,5,5,5,5])

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 203:

OC\DB\Connection->executeUpdate("INSERT INTO ... )", { uid: 14388 ... e}, { uid: 1,mes ... 5})

/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 215:

Doctrine\DBAL\Query\QueryBuilder->execute()

/var/www/nextcloud/apps/mail/lib/Db/MessageMapper.php - line 132:

OC\DB\QueryBuilder\QueryBuilder->execute()

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 312:

OCA\Mail\Db\MessageMapper->insertBulk(OCA\Mail\Db\Message { id: null})

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 202:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->runPartialSync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox { id: 10}, 42, null)

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 114:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox { id: 10}, 42, null, false)

/var/www/nextcloud/apps/mail/lib/BackgroundJob/SyncJob.php - line 92:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->syncAccount(OCA\Mail\Account {})

/var/www/nextcloud/lib/public/BackgroundJob/Job.php - line 79:

OCA\Mail\BackgroundJob\SyncJob->run({ accountId: 1})

/var/www/nextcloud/lib/public/BackgroundJob/TimedJob.php - line 61:

OCP\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})

/var/www/nextcloud/cron.php - line 125:

OCP\BackgroundJob\TimedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

Causé parPDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '14388-10' for key 'mail_msg_mb_uid_idx'

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php - line 117:

PDOStatement->execute(null)

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1054:

Doctrine\DBAL\Driver\PDOStatement->execute()

/var/www/nextcloud/lib/private/DB/Connection.php - line 220:

Doctrine\DBAL\Connection->executeUpdate("INSERT INTO ... )", [ 14388,"<W_ ... e], [ 1,2,1,2,1,5,5,5,5,5,5,5,5,5])

/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 203:

OC\DB\Connection->executeUpdate("INSERT INTO ... )", { uid: 14388 ... e}, { uid: 1,mes ... 5})

/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php - line 215:

Doctrine\DBAL\Query\QueryBuilder->execute()

/var/www/nextcloud/apps/mail/lib/Db/MessageMapper.php - line 132:

OC\DB\QueryBuilder\QueryBuilder->execute()

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 312:

OCA\Mail\Db\MessageMapper->insertBulk(OCA\Mail\Db\Message { id: null})

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 202:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->runPartialSync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox { id: 10}, 42, null)

/var/www/nextcloud/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 114:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox { id: 10}, 42, null, false)

/var/www/nextcloud/apps/mail/lib/BackgroundJob/SyncJob.php - line 92:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->syncAccount(OCA\Mail\Account {})

/var/www/nextcloud/lib/public/BackgroundJob/Job.php - line 79:

OCA\Mail\BackgroundJob\SyncJob->run({ accountId: 1})

/var/www/nextcloud/lib/public/BackgroundJob/TimedJob.php - line 61:

OCP\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})

/var/www/nextcloud/cron.php - line 125:

OCP\BackgroundJob\TimedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

[Benance]

Incredible!
After a reboot of my server, I have tried to connect to nextcloud mail app again and it works!
I don't understand what happened...

[ChristophWurst]

It's most likely a concurrency issue. Because our locking mechanism on mailboxes should prevent more than one process to try and insert a new entry. But in your case it was attempted to be inserted twice. So the error is not the issue. The issue is that this code was run more than once. I have no idea why this is happening tbh, you database configuration might be incorrect, like an invalid isolation level, but it's unrelated. If you run into this again or would like to help improve this app, please open a new ticket. This is still unrelated to the original problem reported here.

[GrillGuth]

This may be a similar to the problem I encountered in #3246?

I did setup a new Nextcloud instance (running on Ubuntu 20.04 and utilizing the Nextcloud snap. It is based on MySQL 5.7.30 and utf8mb4 is preconfigured. I added my mailaccount that did not load and the error persisted.

Sadly I am not capable of exporting the problematic message as I do not know how. Simply forwarding it (utilizing the mail service provider web-interface) to another new mail account did not reproduce the problem. The new mail account loaded normaly and I could open the mail. This is however probably the case because the mail service provider webinterface replaced the problematic character during the forwarding process.

The error log entry on the new instance:

[mail] Error: OCA\Mail\Exception\ServiceException: Sync failed for 2:INBOX: An exception occurred while executing 'INSERT INTO `oc_mail_messages` (`uid`, `message_id`, `mailbox_id`, `subject`, `sent_at`, `flag_answered`, `flag_deleted`, `flag_draft`, `flag_flagged`, `flag_seen`, `flag_forwarded`, `flag_junk`, `flag_notjunk`, `flag_important`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [79372, "<[email protected]>", 2, "\x54\x41\x4b\x45\x20\x32\x30\x30\x25\x20\x55\x50\x20\x54\x4f\x20\x80\x32\x30\x30\x30\x20\x4f\x4e\x20\x59\x4f\x55\x52\x20\x46\x49\x52\x53\x54\x20\x44\x45\x50\x4f\x53\x49\x54", 1484438176, false, false, false, false, true, true, false, false, false]:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\x802000 ...' for column 'subject' at row 1 at <<closure>>

0. /var/snap/nextcloud/21796/nextcloud/extra-apps/mail/lib/Service/Sync/SyncService.php line 126
   OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync(OCA\Mail\Account {}, OCA\Mail\Db\Mailbox {id: 2}, 42, [], true)
1. /var/snap/nextcloud/21796/nextcloud/extra-apps/mail/lib/Controller/FoldersController.php line 124
   OCA\Mail\Service\Sync\SyncService->syncMailbox(OCA\Mail\Account {}, "INBOX", 42, [], false, "is:starred not:important")
2. /snap/nextcloud/21796/htdocs/lib/private/AppFramework/Http/Dispatcher.php line 170
   OCA\Mail\Controller\FoldersController->sync(2, "SU5CT1g=", [], true, "is:starred not:important")
3. /snap/nextcloud/21796/htdocs/lib/private/AppFramework/Http/Dispatcher.php line 100
   OC\AppFramework\Http\Dispatcher->executeController(OCA\Mail\Controller\FoldersController {}, "sync")
4. /snap/nextcloud/21796/htdocs/lib/private/AppFramework/App.php line 137
   OC\AppFramework\Http\Dispatcher->dispatch(OCA\Mail\Controller\FoldersController {}, "sync")
5. /snap/nextcloud/21796/htdocs/lib/private/AppFramework/Routing/RouteActionHandler.php line 47
   OC\AppFramework\App::main("OCA\\Mail\\Cont ... r", "sync", OC\AppFramework\ ... {}, {accountId: "2", ... "})
6. <<closure>>
   OC\AppFramework\Routing\RouteActionHandler->__invoke({accountId: "2", ... "})
7. /snap/nextcloud/21796/htdocs/lib/private/Route/Router.php line 297
   call_user_func(OC\AppFramework\ ... {}, {accountId: "2", ... "})
8. /snap/nextcloud/21796/htdocs/lib/base.php line 1007
   OC\Route\Router->match("/apps/mail/api/ ... c")
9. /snap/nextcloud/21796/htdocs/index.php line 37
   OC::handleRequest()

POST /index.php/apps/mail/api/accounts/2/folders/SU5CT1g%3D/sync

[ChristophWurst]

Sadly I am not capable of exporting the problematic message as I do not know how. Simply forwarding it (utilizing the mail service provider web-interface) to another new mail account did not reproduce the problem. The new mail account loaded normaly and I could open the mail. This is however probably the case because the mail service provider webinterface replaced the problematic character during the forwarding process.

Yep, sounds like the other client handled the email better. Some of the clients (mostly native on the desktop) offer an option to export as .mbox or similar. This might preserve the special encoding. Does that web-based client have this feature?

[GrillGuth]

The web-based client does not support this. However, I installed Thunderbird and I think I successfully extracted the mail but I am not sure how to check if the special encoding is preserved.
I also noticed that when viewing the messages "source code" Content-Type: text/html; charset="us-ascii" is shown. However the subject "TAKE 200% UP TO €2000 ON YOUR FIRST DEPOSIT" already contains the "€" symbol. This symbol is probably the error source as thunderbird displays it also as "�".

If somebody is interested I can share the .mbox file in private.

[ChristophWurst]

If somebody is interested I can share the .mbox file in private.

You can send it to firstname at nextcloud.com

[oldnomad]

If I'm not mistaken, RFCs say that all parts of address are allowed to be US ASCII, or, if server supports SMTPUTF8 extension, UTF-8. All other charsets are not "officially" allowed (since there's no way to specify which one you're using), but many mail clients use them anyway. In this particular case, address is in ISO 8859-2 (a.k.a. "Latin-2"):

$ echo -e "\x73\x74\x72\x75\xe8\x6e\x6f\x67\x40\x69\x70\x7a\x2e\x68\x72"
stru�[email protected]
$ echo -e "\x73\x74\x72\x75\xe8\x6e\x6f\x67\x40\x69\x70\x7a\x2e\x68\x72" | iconv -f iso-8859-2
struč[email protected]

I'm not sure what can be done here, except sanitizing addresses for UTF-8.

[ChristophWurst]

I also noticed that when viewing the messages "source code" Content-Type: text/html; charset="us-ascii" is shown. However the subject "TAKE 200% UP TO €2000 ON YOUR FIRST DEPOSIT" already contains the "€" symbol. This symbol is probably the error source as thunderbird displays it also as "�".

Gnome Evolution also can't display it. So yeah, I guess we just have to ignore those characters.

[ChristophWurst]

In https://github.com/nextcloud/mail/issues/3830 @vasyugan found some more logs of a subject with supposedly invalid characters https://gist.github.com/vasyugan/390db5899f7a49a2e55686df91a43f13

[vasyugan]

In #3830 @vasyugan found some more logs of a subject with supposedly invalid characters https://gist.github.com/vasyugan/390db5899f7a49a2e55686df91a43f13

Should I send you the original messages? That' nothing private, just the regular newsletter of novayagazeta.ru

[vasyugan]

a side effect seems to be a memory leak, because over the last couple of days, the nextcloud cronjob was occasionally killed by the kernel, and after I set a memory limit for the php cli, php regularly killed the cron.php script and reported the following error:

PHP Fatal error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 262144 bytes) in /var/www/chrooted/*/htdocs/cloud.**/apps/mail/lib/IMAP/Threading/Container.php on line 111

[ChristophWurst]

Should I send you the original messages? That' nothing private, just the regular newsletter of novayagazeta.ru

That would be really helpful, thanks!

[vasyugan]

Should I send you the original messages? That' nothing private, just the regular newsletter of novayagazeta.ru

That would be really helpful, thanks!

sent mail to your first name at nextcloud dot com. Correct?

[vasyugan]

Should I send you the original messages? That' nothing private, just the regular newsletter of novayagazeta.ru

That would be really helpful, thanks!

Here is the mail, with the email address and recipient server name redacted.

https://gist.github.com/vasyugan/60238be007bb45acac5c842e3ecd3720

[ChristophWurst]

https://github.com/nextcloud/mail/pull/3855 should fix the insert problems with emails that contain non-utf8 characters.

[StCyr]

Hello,

Could someone confirm the fix from @ChristophWurst (https://github.com/nextcloud/mail/pull/3855) solves the issue?

[bluszcz]

I have installed today "Mail" app and I am getting following:

In AbstractPostgreSQLDriver.php line 79:

  An exception occurred while executing 'INSERT INTO "oc_mail_messages" ("uid", "message_id", "references", "in_reply_to", "thread_root_id", "mailbox_id"
  , "subject", "sent_at", "flag_answered", "flag_deleted", "flag_draft", "flag_flagged", "flag_seen", "flag_forwarded", "flag_junk", "flag_notjunk", "fla
  g_important") VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [304590, "<75smmuo1807567086964.329755255682996zCw2BK6356.41021IXA
  I73duamu[e-mail]>", "[]", null, "<75smmuo1807567086964.329755255682996zCw2
  BK6356.41021IXAI73duamu[e-mail]>", 71, "\x57\x65\x20\x73\x68\x6f\x77\x2d\x
  61\x73\x20\x77\x65\x20\x6d\x61\x6b\x65\x20\x80\x32\x30\x30\x20\x6f\x66\x20\x80\x31\x30", 1335188377, false, false, false, false, true, false, false, fa
  lse, false]:

  SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0x80


In PDOStatement.php line 129:

  SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0x80


In PDOStatement.php line 127:

  SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0x80

Using postgres 10 (it used to be owncloud instance migrated in the meanwhile to nextcloud, ages ago):

 owncloud7               | owncloud7     | UTF8     | C.UTF-8 | C.UTF-8 |

[ChristophWurst]

3855 should fix the insert problems with emails that contain non-utf8 characters.

That was for the sender email. `INSERT INTO "oc_mail_messages"`` is about the message, specifically the message subject in this case @bluszcz.

[bluszcz]

Thanks for getting back @ChristophWurst , but I am not sure if I understand your comment, should I post my comment in other place or? Cheers.

P.S.

I am using 1.7.2, which is marked "production ready".