Bug 394839 - pimitemtable.remoteid Column Too Small For Exchange Calendar Via davmail
Summary: pimitemtable.remoteid Column Too Small For Exchange Calendar Via davmail
Status: RESOLVED FIXED
Alias: None
Product: Akonadi
Classification: Frameworks and Libraries
Component: DAV Resource (show other bugs)
Version: 5.7.2
Platform: Other Linux
: NOR normal (vote)
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
: 406106 408042 (view as bug list)
Depends on:
Blocks:
 
Reported: 2018-05-29 21:02 UTC by Garry Williams
Modified: 2019-06-10 14:41 UTC (History)
4 users (show)

See Also:
Latest Commit:
Version Fixed In:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Garry Williams 2018-05-29 21:02:46 UTC
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.
Comment 1 Erik Quaeghebeur 2018-12-11 14:21:39 UTC
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?
Comment 2 Garry Williams 2018-12-11 16:58:34 UTC
(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.
Comment 3 Erik Quaeghebeur 2018-12-11 17:55:53 UTC
(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'
Comment 4 Garry Williams 2018-12-11 21:34:37 UTC
(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.
Comment 5 Erik Quaeghebeur 2018-12-11 21:49:48 UTC
(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).
Comment 6 Erik Quaeghebeur 2019-04-02 06:42:20 UTC
*** Bug 406106 has been marked as a duplicate of this bug. ***
Comment 7 Christophe Giboudeaux 2019-06-01 14:44:40 UTC
*** Bug 408042 has been marked as a duplicate of this bug. ***
Comment 8 Daniel Vrátil 2019-06-10 09:21:42 UTC
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
Comment 9 Garry Williams 2019-06-10 14:41:59 UTC
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.