Nextcloud Talk broken after upgrade to 20.0.0 (MySQL `Invalid datetime format: 1366 Incorrect string value`)

doobry · October 14, 2020, 11:33pm

After upgrading to 20.0.0, the Talk app doesn’t show my rooms/conversations anymore and Nextcloud keeps logging database errors:

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\\\\xF0\\\\x9F\\\\x98\\\\x8D' for column `nextcloud`.`comments`.`message` at row 1

For reference, here’s the full log line with complete backtrace:

Oct 15 01:09:43 cloud1 NextCloud[9218]: {"reqId":"7wxTe7JAfy0YnvsWRcDN","level":3,"time":"2020-10-14 23:09:43.108400","remoteAddr":"0.0.0.0","user":"doobry","app":"no app in context","method":"GET","url":"/ocs/v2.php/apps/spreed/api/v2/room","message":"{\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO `comments` (`parent_id`, `topmost_parent_id`, `children_count`, `actor_type`, `actor_id`, `message`, `verb`, `creation_timestamp`, `latest_child_timestamp`, `object_type`, `object_id`, `reference_id`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [\\\"0\\\", \\\"0\\\", 0, \\\"guests\\\", \\\"changelog\\\", \\\"- Spice up your messages with emojis from the emoji picker \\\\ud83d\\\\ude0d\\\", \\\"comment\\\", \\\"2020-10-14 23:09:43\\\", null, \\\"chat\\\", \\\"380\\\", null]:\\n\\nSQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\\\\xF0\\\\x9F\\\\x98\\\\x8D' for column `nextcloud`.`comments`.`message` at row 1\",\"Code\":0,\"Trace\":[{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php\",\"line\":169,\"function\":\"convertException\",\"class\":\"Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver\",\"type\":\"->\",\"args\":[\"An exception occurred while executing 'INSERT INTO `comments` (`parent_id`, `topmost_parent_id`, `children_count`, `actor_type`, `actor_id`, `message`, `verb`, `creation_timestamp`, `latest_child_timestamp`, `object_type`, `object_id`, `reference_id`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [\\\"0\\\", \\\"0\\\", 0, \\\"guests\\\", \\\"changelog\\\", \\\"- Spice up your messages with emojis from the emoji picker \\\\ud83d\\\\ude0d\\\", \\\"comment\\\", \\\"2020-10-14 23:09:43\\\", null, \\\"chat\\\", \\\"380\\\", null]:\\n\\nSQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\\\\xF0\\\\x9F\\\\x98\\\\x8D' for column `nextcloud`.`comments`.`message` at row 1\",{\"errorInfo\":[\"22007\",1366,\"Incorrect string value: '\\\\xF0\\\\x9F\\\\x98\\\\x8D' for column `nextcloud`.`comments`.`message` at row 1\"],\"__class__\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDOException\"}]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php\",\"line\":149,\"function\":\"wrapException\",\"class\":\"Doctrine\\\\DBAL\\\\DBALException\",\"type\":\"0:0:0:0:0:0:0:0\",\"args\":[{\"__class__\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver\"},{\"errorInfo\":[\"22007\",1366,\"Incorrect string value: '\\\\xF0\\\\x9F\\\\x98\\\\x8D' for column `nextcloud`.`comments`.`message` at row 1\"],\"__class__\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDOException\"},\"An exception occurred while executing 'INSERT INTO `comments` (`parent_id`, `topmost_parent_id`, `children_count`, `actor_type`, `actor_id`, `message`, `verb`, `creation_timestamp`, `latest_child_timestamp`, `object_type`, `object_id`, `reference_id`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [\\\"0\\\", \\\"0\\\", 0, \\\"guests\\\", \\\"changelog\\\", \\\"- Spice up your messages with emojis from the emoji picker \\\\ud83d\\\\ude0d\\\", \\\"comment\\\", \\\"2020-10-14 23:09:43\\\", null, \\\"chat\\\", \\\"380\\\", null]:\\n\\nSQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\\\\xF0\\\\x9F\\\\x98\\\\x8D' for column `nextcloud`.`comments`.`message` at row 1\"]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php\",\"line\":1071,\"function\":\"driverExceptionDuringQuery\",\"class\":\"Doctrine\\\\DBAL\\\\DBALException\",\"type\":\"0:0:0:0:0:0:0:0\",\"args\":[{\"__class__\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver\"},{\"errorInfo\":[\"22007\",1366,\"Incorrect string value: '\\\\xF0\\\\x9F\\\\x98\\\\x8D' for column `nextcloud`.`comments`.`message` at row 1\"],\"__class__\":\"Doctrine\\\\DBAL\\\\Driver\\\\PDOException\"},\"INSERT INTO `comments` (`parent_id`, `topmost_parent_id`, `children_count`, `actor_type`, `actor_id`, `message`, `verb`, `creation_timestamp`, `latest_child_timestamp`, `object_type`, `object_id`, `reference_id`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\",{\"1\":\"0\",\"2\":\"0\",\"3\":0,\"4\":\"guests\",\"5\":\"changelog\",\"6\":\"- Spice up your messages with emojis from the emoji picker \\ud83d\\ude0d\",\"7\":\"comment\",\"8\":\"2020-10-14 23:09:43\",\"9\":null,\"10\":\"chat\",\"11\":\"380\",\"12\":null}]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/lib/private/DB/Connection.php\",\"line\":214,\"function\":\"executeUpdate\",\"class\":\"Doctrine\\\\DBAL\\\\Connection\",\"type\":\"->\",\"args\":[\"INSERT INTO `comments` (`parent_id`, `topmost_parent_id`, `children_count`, `actor_type`, `actor_id`, `message`, `verb`, `creation_timestamp`, `latest_child_timestamp`, `object_type`, `object_id`, `reference_id`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\",[\"0\",\"0\",0,\"guests\",\"changelog\",\"- Spice up your messages with emojis from the emoji picker \\ud83d\\ude0d\",\"comment\",{\"date\":\"2020-10-14 23:09:43.106097\",\"timezone_type\":3,\"timezone\":\"UTC\",\"__class__\":\"DateTime\"},null,\"chat\",\"380\",null],[2,2,2,2,2,2,2,\"datetime\",\"datetime\",2,2,2]]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php\",\"line\":209,\"function\":\"executeUpdate\",\"class\":\"OC\\\\DB\\\\Connection\",\"type\":\"->\",\"args\":[\"INSERT INTO `comments` (`parent_id`, `topmost_parent_id`, `children_count`, `actor_type`, `actor_id`, `message`, `verb`, `creation_timestamp`, `latest_child_timestamp`, `object_type`, `object_id`, `reference_id`) VALUES(:dcValue1, :dcValue2, :dcValue3, :dcValue4, :dcValue5, :dcValue6, :dcValue7, :dcValue8, :dcValue9, :dcValue10, :dcValue11, :dcValue12)\",{\"dcValue1\":\"0\",\"dcValue2\":\"0\",\"dcValue3\":0,\"dcValue4\":\"guests\",\"dcValue5\":\"changelog\",\"dcValue6\":\"- Spice up your messages with emojis from the emoji picker \\ud83d\\ude0d\",\"dcValue7\":\"comment\",\"dcValue8\":{\"date\":\"2020-10-14 23:09:43.106097\",\"timezone_type\":3,\"timezone\":\"UTC\",\"__class__\":\"DateTime\"},\"dcValue9\":null,\"dcValue10\":\"chat\",\"dcValue11\":\"380\",\"dcValue12\":null},{\"dcValue1\":2,\"dcValue2\":2,\"dcValue3\":2,\"dcValue4\":2,\"dcValue5\":2,\"dcValue6\":2,\"dcValue7\":2,\"dcValue8\":\"datetime\",\"dcValue9\":\"datetime\",\"dcValue10\":2,\"dcValue11\":2,\"dcValue12\":2}]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"line\":217,\"function\":\"execute\",\"class\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryBuilder\",\"type\":\"->\",\"args\":[]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/lib/private/Comments/Manager.php\",\"line\":786,\"function\":\"execute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\",\"args\":[]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/lib/private/Comments/Manager.php\",\"line\":749,\"function\":\"insertQuery\",\"class\":\"OC\\\\Comments\\\\Manager\",\"type\":\"->\",\"args\":[{\"__class__\":\"OC\\\\Comments\\\\Comment\"},true]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/lib/private/Comments/Manager.php\",\"line\":725,\"function\":\"insert\",\"class\":\"OC\\\\Comments\\\\Manager\",\"type\":\"->\",\"args\":[{\"__class__\":\"OC\\\\Comments\\\\Comment\"}]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/apps/spreed/lib/Chat/ChatManager.php\",\"line\":151,\"function\":\"save\",\"class\":\"OC\\\\Comments\\\\Manager\",\"type\":\"->\",\"args\":[{\"__class__\":\"OC\\\\Comments\\\\Comment\"}]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/apps/spreed/lib/Chat/Changelog/Manager.php\",\"line\":80,\"function\":\"addChangelogMessage\",\"class\":\"OCA\\\\Talk\\\\Chat\\\\ChatManager\",\"type\":\"->\",\"args\":[{\"__class__\":\"OCA\\\\Talk\\\\Room\"},\"- Spice up your messages with emojis from the emoji picker \\ud83d\\ude0d\"]},{\"file\":\"/var/www/cloud.example.org/nextcloud-20.0.0/apps/spreed/

Is anybody else hit by this issue? First, I thought that it was due to missing utf8mb4 encoding in my database, but I thoroughly checked https://docs.nextcloud.com/server/20/admin_manual/configuration_database/mysql_4byte_support.html and verified that it’s enabled on our Nextcloud instance. We have MariaDB 10.3 as database backend and the table collation is utf8mb4_bin for all Nextcloud DB tables:

MariaDB [nextcloud]> SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema='nextcloud';
+--------------+----------------------------------------+-----------------+
| table_schema | table_name                             | table_collation |
+--------------+----------------------------------------+-----------------+
| nextcloud    | comments                               | utf8mb4_bin     |
| nextcloud    | filecache                              | utf8mb4_bin     |
| nextcloud    | users                                  | utf8mb4_bin     |
| nextcloud    | groups                                 | utf8mb4_bin     |
| nextcloud    | preferences                            | utf8mb4_bin     |
| nextcloud    | properties                             | utf8mb4_bin     |
| nextcloud    | talk_bridges                           | utf8mb4_bin     |
| nextcloud    | talk_guests                            | utf8mb4_bin     |
| nextcloud    | talk_participants                      | utf8mb4_bin     |
| nextcloud    | talk_signaling                         | utf8mb4_bin     |
| nextcloud    | talk_rooms                             | utf8mb4_bin     |
| nextcloud    | talk_commands                          | utf8mb4_bin     |
[...]
+--------------+----------------------------------------+-----------------+

Any idea how to fix this?

nickvergessen · October 15, 2020, 6:44am

Can you show the comments table structure?

doobry · October 15, 2020, 7:40am

Sure @nickvergessen:

MariaDB [nextcloud]> DESCRIBE comments;
+------------------------+---------------------+------+-----+---------+----------------+
| Field                  | Type                | Null | Key | Default | Extra          |
+------------------------+---------------------+------+-----+---------+----------------+
| id                     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| parent_id              | bigint(20) unsigned | NO   | MUL | 0       |                |
| topmost_parent_id      | bigint(20) unsigned | NO   | MUL | 0       |                |
| children_count         | int(10) unsigned    | NO   |     | 0       |                |
| actor_type             | varchar(64)         | NO   | MUL |         |                |
| actor_id               | varchar(64)         | NO   |     |         |                |
| message                | longtext            | YES  |     | NULL    |                |
| verb                   | varchar(64)         | YES  |     | NULL    |                |
| creation_timestamp     | datetime            | YES  |     | NULL    |                |
| latest_child_timestamp | datetime            | YES  |     | NULL    |                |
| object_type            | varchar(64)         | NO   | MUL |         |                |
| object_id              | varchar(64)         | NO   |     |         |                |
| reference_id           | varchar(64)         | YES  |     | NULL    |                |
+------------------------+---------------------+------+-----+---------+----------------+
13 rows in set (0.003 sec)

nickvergessen · October 15, 2020, 10:27am

Can you try running occ db:convert-mysql-charset again?

doobry · October 15, 2020, 10:40am

Sure, but seems like everything is correct already:

$ php occ db:convert-mysql-charset
All tables already have the correct collation -> nothing to do

nickvergessen · October 15, 2020, 10:46am

Can you manually post in your chats or use it in a file comment, or user status, or file name?

doobry · October 15, 2020, 10:54am

Nope, this also gives an error:

Incorrect string value: '\\\\xF0\\\\x9F\\\\x98\\\\x8D' for column `nextcloud`.`comments`.`message` at row 1

(I tried to add 🙂 as comment to a file)

doobry · October 15, 2020, 11:01am

So apparently something is wrong with the database/table charset/collation, but still Nextcloud detects them as being correct?

doobry · October 15, 2020, 2:23pm

@nickvergessen: Do you have ideas how to further debug this? I even hit this problem on two separate setups (both Nextcloud installations on up-to-date Debian Buster with MariaDB 10.3.23).

Travel_Geek · October 15, 2020, 5:43pm

@doobry I think the best way to fix this problem would to make a backup with no encryption then reimage the server and put the files back on. I would understand if this solution will not work for you.

doobry · October 15, 2020, 5:52pm

@Travel_Geek: sounds like a pretty expensive solution. I wouldn’t mind to track down the real problem instead

nickvergessen · October 16, 2020, 8:25am

Can you check if all steps of https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/mysql_4byte_support.html have been executed correctly?

doobry · October 16, 2020, 1:18pm

@nickvergessen: Yes, I thoroughly checked.

Only thing is, that MariaDB 10.3 no longer has the innodb_file_format variable. See MariaDB 10.3 Changes & Improvements.

ROW_FORMAT in information_schema.INNODB shows Compressed for all Nextcloud tables.

doobry · October 22, 2020, 4:17pm

Could it be that the problem is not MariaDB but in PHP or the Doctrine library? I just did some testing with inserting multibyte characters into the table in question and that worked like a charm:

INSERT INTO `comments` (`parent_id`, `topmost_parent_id`, `children_count`, `actor_type`, `actor_id`, `message`, `verb`, `creation_timestamp`, `latest_child_timestamp`, `object_type`, `object_id`, `reference_id`) VALUES(0, 0, 0, "guests", "changelog", "a string with multibyte character emoji: ❤️", "comment", NOW(), NULL, "chat", 195, NULL);

radan · November 13, 2020, 8:33pm

Did you solve the problem. I think, I have the same problem with mail_recipients-table:

SQLSTATE[22007]: Invalid datetime format: 1366 
Incorrect string value: '\xF6he@st...' for column `nextclouddb`.`oc_mail_recipients`.`email` at row 1

any ideas?

xppx99 · December 10, 2020, 1:22pm

Hello,

I was having a similar issue in Calendar and Contact componentes, and searched a lot.
Eventually I’ve come up with issue 20883 and finally the solution sugested by a4bits worked for me:

set your mariadb mysql.cnf
from
init-connect='SET NAMES utf8'
to
init-connect='SET NAMES utf8mb

I hope this helps others that are facing simular issues…

NeurohrByteS · October 2, 2023, 3:24pm

Just registered an account to say thank you!

Update for MariaDB 10.3.

[mysqld]
innodb_file_per_table=1
init_connect = "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"

Finally I can activate emojis (since many newer Apps just assume that they can put them into the database).