Cannot sync mailbox due to "SQLSTATE[22007]: Invalid datetime format"
Nobody has claimed this yet.
- 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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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