<?xml version="1.0" encoding="utf-8"?><rss version="2.0" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:dc="http://purl.org/dc/elements/1.1/">
<channel>
<title>selfoss support forum - Bug report: PDOStatement: Field 'updatetime' default value</title>
<link>https://forum.selfoss.aditu.de/</link>
<description>selfoss support forum</description>
<language>en</language>
<item>
<title>Bug report: PDOStatement: Field 'updatetime' default value</title>
<content:encoded><![CDATA[<p>Hello, guys.</p>
<p><strong>Case:</strong><br />
nginx+phpfpm<br />
Percona <strong>mysql</strong> 5.6.19-67 as db provider instead of SQLite.</p>
<p>On any update (cron curl/manual via web ui), I'm recieving such error:</p>
<p><em>FastCGI sent in stderr: &quot;PHP message: PDOStatement: Field 'updatetime' doesn't have a default value&quot; while reading response header from upstream</em></p>
<p>I've found that selfoss fills database by itself with wrong data type:</p>
<p><em>&gt; SHOW CREATE TABLE items;</em></p>
<p><em>| items | CREATE TABLE `items` (<br />
  `id` int(11) NOT NULL AUTO_INCREMENT,<br />
  `datetime` datetime NOT NULL,<br />
  `title` text NOT NULL,<br />
  `content` longtext NOT NULL,<br />
  `thumbnail` text,<br />
  `icon` text,<br />
  `unread` tinyint(1) NOT NULL,<br />
  `starred` tinyint(1) NOT NULL,<br />
  `source` int(11) NOT NULL,<br />
  `uid` varchar(255) NOT NULL,<br />
  `link` text NOT NULL,</em><br />
<strong>  `updatetime` datetime NOT NULL,</strong><br />
<em>  `author` varchar(255) DEFAULT NULL,<br />
  PRIMARY KEY (`id`),<br />
  KEY `source` (`source`)<br />
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |</em></p>
<p>Mysql type `datetime` can NOT have default value like NOW(), TODAY(), CURRENT_TIMESTAMP etc. Bug described here:<br />
<a href="http://bugs.mysql.com/bug.php?id=27645">http://bugs.mysql.com/bug.php?id=27645</a><br />
There are some posts at stackoverflow about this issue, like this one:<br />
<a href="http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column">http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column</a></p>
<p><strong>Solution:</strong></p>
<p>Change the column type to `TIMESTAMP`:<br />
ALTER TABLE items MODIFY updatetime timestamp;</p>
<p>This also set neccessary defaults on update/insert, see below:</p>
<p><em>&gt; SHOW CREATE TABLE items;<br />
</em></p>
<p><em>items | CREATE TABLE `items` (<br />
  `id` int(11) NOT NULL AUTO_INCREMENT,<br />
  `title` text NOT NULL,<br />
  `content` longtext NOT NULL,<br />
  `thumbnail` text,<br />
  `icon` text,<br />
  `unread` tinyint(1) NOT NULL,<br />
  `starred` tinyint(1) NOT NULL,<br />
  `source` int(11) NOT NULL,<br />
  `uid` varchar(255) NOT NULL,<br />
  `link` text NOT NULL,</em><br />
  <strong>`updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,</strong><br />
<em>  `author` varchar(255) DEFAULT NULL,<br />
  `datetime` datetime DEFAULT NULL,<br />
  PRIMARY KEY (`id`),<br />
  KEY `source` (`source`)<br />
) ENGINE=MyISAM AUTO_INCREMENT=65 DEFAULT CHARSET=utf8 |<br />
</em></p>
<p>After ALTER procedure everything works like a charm.<br />
May the force be with you.</p>
]]></content:encoded>
<link>https://forum.selfoss.aditu.de/index.php?id=939</link>
<guid>https://forum.selfoss.aditu.de/index.php?id=939</guid>
<pubDate>Wed, 08 Oct 2014 20:36:33 +0000</pubDate>
<dc:creator>flare</dc:creator>
</item>
</channel>
</rss>
