I was unable to configure an Exchange calendar via the davmail proxy because the item ID was longer than the column length for the pimitemtable.remoteid column (255). The error resulted in log messages like this: DATABASE ERROR: Error code: "1406" DB error: "Data too long for column 'remoteId' at row 1" Error text: "Data too long for column 'remoteId' at row 1 QMYSQL3: Unable to execute statement" Query: "INSERT INTO PimItemTable (rev, remoteId, remoteRevision, gid, collectionId, mimeTypeId, datetime, atime, dirty, size) VALUES (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9)" I turned on general_log in mysql to catch the error. The fix was: alter table pimitemtable modify column remoteId varchar(1024); (I imagine that 1024 is larger than what is strictly needed, but it costs nothing to specify it.) The problem data looks like this: http://localhost:1080/users/garry_williams2@comcast.com/calendar/AAMkADE0ZTY4YjY0LTM3ZmUtNGQ3Yy1hMzdjLTdlNTZlYjgzMTNkZgBGAAAAAABv2lgKJBYZRoOfK2MzkdgNBwCAS5PvJdbcTKxWQLEnWOAHAAAAAAENAACAS5PvJdbcTKxWQLEnWOAHAAAaQl7dAAA%3D.EML#040000008200E00074C5B7101A82E00800000000A059FC7E8054D20100000000000000001000000067BF664FED94F14D8772488D96A9E4E92017-01-17T19:30:00+00:00 My pimitemtable now has this: MariaDB [akonadi]> select length(remoteid) from pimitemtable where length(remoteid) > 255; +------------------+ | length(remoteid) | +------------------+ | 361 | | 361 | | 361 | | 361 | | 361 | | 361 | | 361 | | 363 | | 363 | +------------------+ 9 rows in set (0.01 sec) MariaDB [akonadi]> And the calendar is finally working.
I think I'm hitting the same issue. When I try to apply the alter statement in akonadiconsole's DB console, I get > Invalid default value for 'atime' QMYSQL: Unable to execute query @Garry I see that you are accessing the database directly. How do I go about doing that. Running 'mysql' gives the (not unexpected) error message > ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) I guess I need to indicate to mysql where the akonadi database is, but how?
(In reply to Erik Quaeghebeur from comment #1) > @Garry I see that you are accessing the database directly. How do I go about > doing that. If you do a ps -ef|grep mysql command, you will see the --socket= parameter that you need to use when you issue the mysql command. If you simply do mysql --socket=/tmp/akonadi-blah-blah then, at the mysql prompt, do use akonadi you will be all set to alter the table as mentioned above. It's disappointing that the developers have not seen fit to even acknowledge this bug report. At least you managed to find it.
(In reply to Garry Williams from comment #2) > If you do a > > ps -ef|grep mysql > > command, you will see the --socket= parameter that you need to use when you > issue the mysql command. If you simply do > > mysql --socket=/tmp/akonadi-blah-blah > > then, at the mysql prompt, do > > use akonadi > > you will be all set to alter the table as mentioned above. That worked, but I get the same error as within the DB console of akonadiconsole: > mysql> alter table pimitemtable modify column remoteId varchar(1024); > ERROR 1067 (42000): Invalid default value for 'atime'
(In reply to Erik Quaeghebeur from comment #3) > That worked, but I get the same error as within the DB console of > akonadiconsole: > > > mysql> alter table pimitemtable modify column remoteId varchar(1024); > > ERROR 1067 (42000): Invalid default value for 'atime' I don't know what to tell you: MariaDB [akonadi]> alter table pimitemtable modify column remoteId varchar(1024); Query OK, 0 rows affected (0.001 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [akonadi]> You might want to read https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at and similar references.
(In reply to Garry Williams from comment #4) > (In reply to Erik Quaeghebeur from comment #3) > > That worked, but I get the same error as within the DB console of > > akonadiconsole: > > > > > mysql> alter table pimitemtable modify column remoteId varchar(1024); > > > ERROR 1067 (42000): Invalid default value for 'atime' > > I don't know what to tell you: > > MariaDB [akonadi]> alter table pimitemtable modify column remoteId > varchar(1024); > Query OK, 0 rows affected (0.001 sec) > Records: 0 Duplicates: 0 Warnings: 0 > > MariaDB [akonadi]> > > You might want to read > > > https://stackoverflow.com/questions/36882149/error-1067-42000-invalid- > default-value-for-created-at > > and similar references. Thanks. Not much luck trying the suggestions there, though. In any case, I'm being helped on the kdepim-users mailing list: https://marc.info/?l=kdepim-users&m=154455132016232&w=2 BTW: it appears this bug report is on the radar of the developers. It's just a question of time (which may be a long time).
*** Bug 406106 has been marked as a duplicate of this bug. ***
*** Bug 408042 has been marked as a duplicate of this bug. ***
Git commit c059d054251282c3bc8d946447fb156d4442afa3 by Daniel Vrátil. Committed on 10/06/2019 at 09:21. Pushed by dvratil into branch 'master'. Raise limit for PimItem.remoteId to 1024 characters Summary: Some DAV servers produce exteremely long RIDs that do not fit into the current 255 character limit. This is only problem on MySQL, on other DBs we use TEXT, which has no length limit. On MySQL, just increasing the column length to 1024 is not enough, there's a limit on maximum length of VARBINARY/VARCHAR index, which is 756 bytes (255 characters). To be able to have an index on a VARBINARY column with up to 1024 characters we need to enable innodb_large_prefix and change how the extra characters are stored on the disk (ROW_FORMAT). Test Plan: Modified a unit-test to attempt creating an Item with 1024 characters long RID Reviewers: #kde_pim, vkrause Reviewed By: #kde_pim, vkrause Subscribers: kde-pim Tags: #kde_pim Differential Revision: https://phabricator.kde.org/D21714 M +2 -0 autotests/libs/itemappendtest.cpp M +2 -2 src/server/storage/akonadidb.xml M +6 -1 src/server/storage/dbinitializer_p.cpp M +6 -0 src/server/storage/dbupdate.xml M +3 -0 src/server/storage/mysql-global-mobile.conf https://commits.kde.org/akonadi/c059d054251282c3bc8d946447fb156d4442afa3
You might check that innodb_large_prefix was deprecated in MySQL 5.7.7 and is now removed from MySQL as of 8.0.0. (My version happens to be 10.3.12-MariaDB on Fedora 30.) I tried your patch on my mysql.conf file and got a fatal error on start-up because of this.