Category: Roundcube

postgresqlRoundcube

Installing Roundcube 1.1.4 on Postgres 9.3 – invalid identities table

When installing roundcube 1.1.4 on postgres 9.3 the installer script showed that the identities table could not be upgraded correctly.

I looked more deeply by looking at the sql statement that creates the identities table and I see the following definition

CREATE TABLE `identities` (
 `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `user_id` int(10) UNSIGNED NOT NULL,
 `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
 `del` tinyint(1) NOT NULL DEFAULT '0',
 `standard` tinyint(1) NOT NULL DEFAULT '0',
 `name` varchar(128) NOT NULL,
 `organization` varchar(128) NOT NULL DEFAULT '',
 `email` varchar(128) NOT NULL,
 `reply-to` varchar(128) NOT NULL DEFAULT '',
 `bcc` varchar(128) NOT NULL DEFAULT '',
 `signature` text,
 `html_signature` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY(`identity_id`),
 CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
 REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 INDEX `user_identities_index` (`user_id`, `del`),
 INDEX `email_identities_index` (`email`, `del`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;

when I look at the database table that I am upgrading from I can see that their is no column for ‘changed’,  so when I try to add it\

postgres=# alter table identities add column changed datetime not null default '1000-01-01 00:00:00';
ERROR: type "datetime" does not exist
LINE 1: alter table identities add column changed datetime not null ...

It fails  so I try again and replace datetime with the timestamp column and it succeeds

postgres=# alter table identities add column changed timestamp not null default '1000-01-01 00:00:00';
RESULT OK