Bug report: PDOStatement: Field 'updatetime' default value

by flare, Wednesday, October 08, 2014, 22:36 (3488 days ago)

Hello, guys.

Case:
nginx+phpfpm
Percona mysql 5.6.19-67 as db provider instead of SQLite.

On any update (cron curl/manual via web ui), I'm recieving such error:

FastCGI sent in stderr: "PHP message: PDOStatement: Field 'updatetime' doesn't have a default value" while reading response header from upstream

I've found that selfoss fills database by itself with wrong data type:

> SHOW CREATE TABLE items;

| items | CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`title` text NOT NULL,
`content` longtext NOT NULL,
`thumbnail` text,
`icon` text,
`unread` tinyint(1) NOT NULL,
`starred` tinyint(1) NOT NULL,
`source` int(11) NOT NULL,
`uid` varchar(255) NOT NULL,
`link` text NOT NULL,
`updatetime` datetime NOT NULL,
`author` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `source` (`source`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

Mysql type `datetime` can NOT have default value like NOW(), TODAY(), CURRENT_TIMESTAMP etc. Bug described here:
http://bugs.mysql.com/bug.php?id=27645
There are some posts at stackoverflow about this issue, like this one:
http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column

Solution:

Change the column type to `TIMESTAMP`:
ALTER TABLE items MODIFY updatetime timestamp;

This also set neccessary defaults on update/insert, see below:

> SHOW CREATE TABLE items;

items | CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` text NOT NULL,
`content` longtext NOT NULL,
`thumbnail` text,
`icon` text,
`unread` tinyint(1) NOT NULL,
`starred` tinyint(1) NOT NULL,
`source` int(11) NOT NULL,
`uid` varchar(255) NOT NULL,
`link` text NOT NULL,
`updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`author` varchar(255) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `source` (`source`)
) ENGINE=MyISAM AUTO_INCREMENT=65 DEFAULT CHARSET=utf8 |

After ALTER procedure everything works like a charm.
May the force be with you.