Re: DB help needed to solve issue with pimitemtable

[ Comments below, in-line ]


On Tue, 11 Dec 2018 19:01:27 +0100, Erik Quaeghebeur wrote:

> Dear list,
> 
> 
> I get an error that a remoteId is too long for pimitemtable.
> This is a reported bug:
> 
> https://bugs.kde.org/show_bug.cgi?id=394839
> 
> I tried the workaround suggested there, i.e., to increase the length of 
> the remoteId column, but get an error about atime (another pimitemtable 
> column) having an invalid default value.
> 
> I would be grateful if someone with more DB expertise than me could have 
> a look at this. Response either here or at the bug report is fine.
> 

Hey Erik,

When are you getting the error?

In MySQL, you can show the table's DDL:

     show create table pimitemtable;

In my copy of the DDL, I see 'atime' has a default of current_timestamp():

CREATE TABLE `pimitemtable` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `rev` int(11) NOT NULL DEFAULT 0,
   `remoteId` varbinary(255) DEFAULT NULL,
   `remoteRevision` varbinary(255) DEFAULT NULL,
   `gid` varbinary(255) DEFAULT NULL,
   `collectionId` bigint(20) DEFAULT NULL,
   `mimeTypeId` bigint(20) DEFAULT NULL,
   `datetime` timestamp NOT NULL DEFAULT current_timestamp(),
   `atime` timestamp NOT NULL DEFAULT current_timestamp(),
   `dirty` tinyint(1) DEFAULT NULL,
   `size` bigint(20) NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`),
   KEY `mimeTypeId` (`mimeTypeId`),
   KEY `PimItemTable_collectionIndex` (`collectionId`),
   KEY `PimItemTable_gidIndex` (`gid`),
   KEY `PimItemTable_ridIndex` (`remoteId`),
   KEY `PimItemTable_idSortIndex` (`id`),
   CONSTRAINT `pimitemtable_ibfk_1` FOREIGN KEY (`collectionId`) 
REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `pimitemtable_ibfk_2` FOREIGN KEY (`mimeTypeId`) 
REFERENCES `mimetypetable` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;



--
Pablo Sanchez - Blueoak Database Engineering, Inc
Ph:    819.459.1926        iNum:  883.5100.0990.1054