nextcloud / nextcloud/mail

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

Open
#4,299 30 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

1. to develop bug
Dominant language
JavaScript
Stars
1k
Forks
348
Avg merge
12h 28m
Merged PRs (30d)
91

Description

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, "<E1eSNge-0004vA-Pf@www.neuhold-elektronik.at
  >", "[]", null, "<E1eSNge-0004vA-Pf@www.neuhold-elektronik.at>", 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, "<E1eSNge-0004vA-Pf@www.neuhold-elektronik.at>", "[]", null, "<E1eSNge
  -0004vA-Pf@www.neuhold-elektronik.at>", 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)

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with ImapToDbSynchronizer.php around line 235 and the reported INSERT failure in AbstractMySQLDriver.php. Reproduce sudo -u www-data php occ mail:account:sync <account-id> using the affected mailbox and inspect how the subject containing euro symbols reaches MariaDB. Done means the account sync completes and the inbox loads without the invalid-string error.

Written by the indexing model from the issue text.

Assessment

Tech stack
mariadb, php
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.