Mail: Cannot sync mailbox due to "SQLSTATE[22007]: Invalid datetime format"

[pylorak]

Expected behavior

sudo -u www-data php occ mail:account:sync <account-id> should succeed.

Actual behavior

CLI-command throws an exception and account sync fails. If mail app is visited in the browser, the inbox never finishes loading.
CLI-log follows (some info redacted):

root@xxxxx xxxxx# sudo -u www-data php occ mail:account:sync 1
[debug] account is up to date, skipping mailbox sync
[debug] Skipping mailbox sync for Archive
[debug] Skipping mailbox sync for Aurox
[debug] Skipping mailbox sync for Drafts
[debug] Initial sync 1:INBOX - fetch all messages from IMAP took 1s

In ImapToDbSynchronizer.php line 235:

  Sync failed for 1:INBOX: 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`, `flag_important`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [4644, "<[email protected]
  >", "[]", null, "<[email protected]>", 7, "\x44\x69\x67\x69\x74\x61\x6c\x20\x4d\x75\x6c\x74\x69\x6d\x65\x74\x65\x72\x20\x34\x30\x30\x30\x20\x43\x6f\x75\x6e\x74\x73\x20\x73\x74\x61\x74\x74\x20\x80\x20\x33\x3
  9\x2c\x39\x35\x20\x6e\x75\x72\x20\x80\x20\x32\x39\x2c\x39\x35", 1513950517, false, false, false, false, true, false, false, false, false]:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\x80 39,9...' for column `nextcloud`.`oc_mail_messages`.`subject` at row 1


In AbstractMySQLDriver.php line 106:

  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_flag
  ged`, `flag_seen`, `flag_forwarded`, `flag_junk`, `flag_notjunk`, `flag_important`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [4644, "<[email protected]>", "[]", null, "<E1eSNge
  [email protected]>", 7, "\x44\x69\x67\x69\x74\x61\x6c\x20\x4d\x75\x6c\x74\x69\x6d\x65\x74\x65\x72\x20\x34\x30\x30\x30\x20\x43\x6f\x75\x6e\x74\x73\x20\x73\x74\x61\x74\x74\x20\x80\x20\x33\x39\x2c\x39\x35\x20\x6e\x75
  \x72\x20\x80\x20\x32\x39\x2c\x39\x35", 1513950517, false, false, false, false, true, false, false, false, false]:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\x80 39,9...' for column `nextcloud`.`oc_mail_messages`.`subject` at row 1


In PDOStatement.php line 129:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\x80 39,9...' for column `nextcloud`.`oc_mail_messages`.`subject` at row 1


In PDOStatement.php line 127:

  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\x80 39,9...' for column `nextcloud`.`oc_mail_messages`.`subject` at row 1


mail:account:sync [-f|--force] [--] <account-id>

Mail app

Mail app version: 1.7.2

Mailserver or service: Exim 4.92 (from Debian)

Server configuration

Operating system: Debian 10.7

Web server: Apache/2.4.38 (from Debian)

Database: MariaDB 10.3.27 (from Debian)

PHP version: PHP 7.3.19 (from Debian)

Nextcloud Version: 20.0.4

Client configuration

Browser: Firefox 84.0.1

Operating system: Windows 10 20H2 (on browser), Debian 10.7 (on CLI)

[tarator]

Same problem here:

Sync failed for 4:INBOX: 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`, `flag_important`) 

VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' 

with params

 [5573, "<xxxxxxx@MOBILE>", "[]", null, "<xxxxxxxx@MOBILE>", 59, "\x4a\x65\x74\x7a\x74\x20\x6b\x75\x72\x7a\x65\x20\x5a\x65\x69\x74\x20\x62\x69\x73\x20\x7a\x75\x20\x32\x36\x30\x20\x80\x20\x52\x61\x62\x61\x74\x74\x20\x62\x65\x69\x20\x42\x75\x63\x68\x75\x6e\x67\x20\x65\x75\x72\x65\x72\x20\x45\x53\x4c\x2d\x53\x70\x72\x61\x63\x68\x72\x65\x69\x73\x65\x20\x7c\x20\x53\x6b\x79\x70\x65\x2d\x47\x75\x74\x68\x61\x62\x65\x6e\x20\x75\x6d\x20\x6d\x69\x6e\x64\x65\x73\x74\x65\x6e\x73\x20\x33\x30\x25\x20\x62\x69\x6c\x6c\x69\x67\x65\x72\x20\x7c\x20\x42\x65\x72\x67\x68\x6f\x63\x68\x32\x20\x62\x72\x69\x6e\x67\x74\x20\x4b\x72\x61\x66\x74\x20\x75\x6e\x64\x20\x46\x72\x65\x75\x64\x65\x20\x61\x6d\x20\x54\x75\x6e", 1326567613, false, false, false, false, true, false, false, false, false]: 

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\x80 Raba...' for column `nextcloud`.`oc_mail_messages`.`subject` at row 1

and here another:

OCA\Mail\Exception\ServiceException: Sync failed for 11:INBOX: 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`, `flag_important`) 

VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' 

with params 

[1634, "<[email protected]>", "[]", null, "<[email protected]>", 238, "\x54\x6f\x70\x20\x53\x6d\x61\x72\x74\x70\x68\x6f\x6e\x65\x73\x20\x61\x62\x20\x80\x30\x2c\x2d", 1474257540, false, false, false, false, true, false, false, false, false]: 
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\x800,-' for column `nextcloud`.`oc_mail_messages`.`subject` at row 1

[lajonss]

In my case (docker-compose with mariadb) enabling utf8mb4 resolved "invalid format in subject" errors.
https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/mysql_4byte_support.html

[tarator]

@lajonss I already have converted my tables to utf8mb4 before installing and enabling the Nextcloud-Mail-App. But I get this errors anyway.

MariaDB [nextcloud]>

SELECT t.table_name, c.character_set_name 
FROM information_schema.`TABLES` t,
  information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` c 
WHERE c.collation_name = t.table_collation 
  AND t.table_schema = "nextcloud" 
  AND t.table_name like "oc_mail%";

+------------------------+--------------------+
| table_name             | character_set_name |
+------------------------+--------------------+
| oc_mail_mailboxes      | utf8mb4            |
| oc_mail_attachments    | utf8mb4            |
| oc_mail_classifiers    | utf8mb4            |
| oc_mail_messages       | utf8mb4            |
| oc_mail_coll_addresses | utf8mb4            |
| oc_mail_accounts       | utf8mb4            |
| oc_mail_aliases        | utf8mb4            |
| oc_mail_recipients     | utf8mb4            |
+------------------------+--------------------+
8 rows in set (0.004 sec)

[ChristophWurst]

@tarator :-1:'ing @lajonss's constructive help just because it didn't help with your problem is kinda meh.

[tarator]

@tarator 👎'ing @lajonss's constructive help just because it didn't help with your problem is kinda meh.

Nothing to take personal. Just an indicator, that it didn’t help in my case.

[ChristophWurst]

It's disrespectful, even if you read the reaction differently. Not everyone will understand this.

[pylorak]

Hello, original reporter here, I also made sure my database is already configured correctly, by once again following the linked guide above. I've gone through all steps but every option was already set (Mariadb conf, nextcloud config var, database collation), and after executing php occ maintenance:repair, it even told me that:

 - Repair MySQL collation
     - All tables already have the correct collation -> nothing to do
...

So I can confirm that database 4-byte support is already enabled and is not the problem here.

[erama]

Hi, I have similar problem here:

OCA\Mail\Exception\ServiceException: Sync failed for 10:INBOX: An exception occurred while executing 'INSERT INTOoc_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,flag_important) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [175, "<f7a46b2[e-mail]>", "[]", null, "<f7a46b2[e-mail]>", 44, "\ud83c\udd95\u00a0Nueva plataforma educativa de Mercado Libre Publicidad | Certificate en Product Ads", 1598457824, false, false, false, false, true, false, false, false, false]: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x86\x95\xC2\xA0...' for columnc1clouderamacom.oc_mail_messages.subject` at row 1
/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/SyncService.php - line 121:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync()

/var/www/clients/client1/web9/web/apps/mail/lib/Controller/MailboxesController.php - line 161:

OCA\Mail\Service\Sync\SyncService->syncMailbox()

/var/www/clients/client1/web9/web/lib/private/AppFramework/Http/Dispatcher.php - line 169:

OCA\Mail\Controller\MailboxesController->sync()

/var/www/clients/client1/web9/web/lib/private/AppFramework/Http/Dispatcher.php - line 100:

OC\AppFramework\Http\Dispatcher->executeController()

/var/www/clients/client1/web9/web/lib/private/AppFramework/App.php - line 152:

OC\AppFramework\Http\Dispatcher->dispatch()

/var/www/clients/client1/web9/web/lib/private/Route/Router.php - line 309:

OC\AppFramework\App::main()

/var/www/clients/client1/web9/web/lib/base.php - line 1008:

OC\Route\Router->match()

/var/www/clients/client1/web9/web/index.php - line 37:

OC::handleRequest()

Causado porDoctrine\DBAL\Exception\DriverException: 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, flag_important) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [175, "f7a46b2[e-mail]", "[]", null, "f7a46b2[e-mail]", 44, "\ud83c\udd95\u00a0Nueva plataforma educativa de Mercado Libre Publicidad | Certificate en Product Ads", 1598457824, false, false, false, false, true, false, false, false, false]: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: 'xF0x9Fx86x95xC2xA0...' for column c1clouderamacom.oc_mail_messages.subject at row 1
/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 169:

Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException()

/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 149:

Doctrine\DBAL\DBALException::wrapException()

/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1071:

Doctrine\DBAL\DBALException::driverExceptionDuringQuery()

/var/www/clients/client1/web9/web/lib/private/DB/Connection.php - line 222:

Doctrine\DBAL\Connection->executeUpdate()

/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 209:

OC\DB\Connection->executeUpdate()

/var/www/clients/client1/web9/web/lib/private/DB/QueryBuilder/QueryBuilder.php - line 217:

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

/var/www/clients/client1/web9/web/apps/mail/lib/Db/MessageMapper.php - line 282:

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

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 287:

OCA\Mail\Db\MessageMapper->insertBulk()

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 215:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->runInitialSync()

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/SyncService.php - line 121:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync()

/var/www/clients/client1/web9/web/apps/mail/lib/Controller/MailboxesController.php - line 161:

OCA\Mail\Service\Sync\SyncService->syncMailbox()

/var/www/clients/client1/web9/web/lib/private/AppFramework/Http/Dispatcher.php - line 169:

OCA\Mail\Controller\MailboxesController->sync()

/var/www/clients/client1/web9/web/lib/private/AppFramework/Http/Dispatcher.php - line 100:

OC\AppFramework\Http\Dispatcher->executeController()

/var/www/clients/client1/web9/web/lib/private/AppFramework/App.php - line 152:

OC\AppFramework\Http\Dispatcher->dispatch()

/var/www/clients/client1/web9/web/lib/private/Route/Router.php - line 309:

OC\AppFramework\App::main()

/var/www/clients/client1/web9/web/lib/base.php - line 1008:

OC\Route\Router->match()

/var/www/clients/client1/web9/web/index.php - line 37:

OC::handleRequest()

Causado porDoctrine\DBAL\Driver\PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: 'xF0x9Fx86x95xC2xA0...' for column c1clouderamacom.oc_mail_messages.subject at row 1
/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1062:

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

/var/www/clients/client1/web9/web/lib/private/DB/Connection.php - line 222:

Doctrine\DBAL\Connection->executeUpdate()

/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 209:

OC\DB\Connection->executeUpdate()

/var/www/clients/client1/web9/web/lib/private/DB/QueryBuilder/QueryBuilder.php - line 217:

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

/var/www/clients/client1/web9/web/apps/mail/lib/Db/MessageMapper.php - line 282:

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

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 287:

OCA\Mail\Db\MessageMapper->insertBulk()

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 215:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->runInitialSync()

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/SyncService.php - line 121:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync()

/var/www/clients/client1/web9/web/apps/mail/lib/Controller/MailboxesController.php - line 161:

OCA\Mail\Service\Sync\SyncService->syncMailbox()

/var/www/clients/client1/web9/web/lib/private/AppFramework/Http/Dispatcher.php - line 169:

OCA\Mail\Controller\MailboxesController->sync()

/var/www/clients/client1/web9/web/lib/private/AppFramework/Http/Dispatcher.php - line 100:

OC\AppFramework\Http\Dispatcher->executeController()

/var/www/clients/client1/web9/web/lib/private/AppFramework/App.php - line 152:

OC\AppFramework\Http\Dispatcher->dispatch()

/var/www/clients/client1/web9/web/lib/private/Route/Router.php - line 309:

OC\AppFramework\App::main()

/var/www/clients/client1/web9/web/lib/base.php - line 1008:

OC\Route\Router->match()

/var/www/clients/client1/web9/web/index.php - line 37:

OC::handleRequest()

Causado porPDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: 'xF0x9Fx86x95xC2xA0...' for column c1clouderamacom.oc_mail_messages.subject at row 1
/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php - line 127:

PDOStatement->execute()

/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1062:

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

/var/www/clients/client1/web9/web/lib/private/DB/Connection.php - line 222:

Doctrine\DBAL\Connection->executeUpdate()

/var/www/clients/client1/web9/web/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php - line 209:

OC\DB\Connection->executeUpdate()

/var/www/clients/client1/web9/web/lib/private/DB/QueryBuilder/QueryBuilder.php - line 217:

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

/var/www/clients/client1/web9/web/apps/mail/lib/Db/MessageMapper.php - line 282:

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

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 287:

OCA\Mail\Db\MessageMapper->insertBulk()

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/ImapToDbSynchronizer.php - line 215:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->runInitialSync()

/var/www/clients/client1/web9/web/apps/mail/lib/Service/Sync/SyncService.php - line 121:

OCA\Mail\Service\Sync\ImapToDbSynchronizer->sync()

/var/www/clients/client1/web9/web/apps/mail/lib/Controller/MailboxesController.php - line 161:

OCA\Mail\Service\Sync\SyncService->syncMailbox()

/var/www/clients/client1/web9/web/lib/private/AppFramework/Http/Dispatcher.php - line 169:

OCA\Mail\Controller\MailboxesController->sync()

/var/www/clients/client1/web9/web/lib/private/AppFramework/Http/Dispatcher.php - line 100:

OC\AppFramework\Http\Dispatcher->executeController()

/var/www/clients/client1/web9/web/lib/private/AppFramework/App.php - line 152:

OC\AppFramework\Http\Dispatcher->dispatch()

/var/www/clients/client1/web9/web/lib/private/Route/Router.php - line 309:

OC\AppFramework\App::main()

/var/www/clients/client1/web9/web/lib/base.php - line 1008:

OC\Route\Router->match()

/var/www/clients/client1/web9/web/index.php - line 37:

OC::handleRequest()`