Error: Invalid default value for 'tstamp'

[mo-mueller]

I have installed CoolURI on my TYPO3 8.7.7 site. The URLs are changed now, but if I click on a link I'm getting "Page not found". So I have checked the database with the install tool. There are two differences:

CREATE TABLE `link_cache` (`id` INT UNSIGNED AUTO_INCREMENT NOT NULL, `params` BLOB DEFAULT NULL, `url` VARCHAR(255) DEFAULT NULL, `tstamp` DATETIME DEFAULT CURRENT_TIMESTAMP, `crdatetime` DATETIME DEFAULT NULL, `sticky` SMALLINT UNSIGNED DEFAULT 0, INDEX `url` (url(255)), INDEX `params` (params(255)), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

CREATE TABLE `link_oldlinks` (`id` INT UNSIGNED AUTO_INCREMENT NOT NULL, `link_id` INT UNSIGNED DEFAULT 0 NOT NULL, `url` VARCHAR(255) DEFAULT NULL, `tstamp` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, `sticky` SMALLINT UNSIGNED DEFAULT 0, UNIQUE INDEX `id` (id), INDEX `url` (url(255)), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

If I try to execute it, I'm getting Database update failed Error: Invalid default value for 'tstamp'

I have read here, that CURRENT_TIMESTAMP is only acceptable on TIMESTAMP fields and not on DATETIME.

Is that a thing of my TYPO3 Installation or of my server configuration, or why it seems like I'm the first one who have this problem?

EDIT:

I have exuted the above statements now manually on phpmyadmin without the "DEFAULT CURRENT_TIMESTAMP" and now the "Page not found" problem is gone. But nevertheless you should fix that.

[bednee]

You're not getting 404 because of this error. It's a known issue. Unfortunately CoolUri relies on the timestamp, so it's not easy to fix in.
Anyway, is that 404 produced by CoolUri or Apache? If it's Apache, then it could be missing .htaccess rules for example. If it's CoolUri, check the cache that the links are generated correctly.

[mo-mueller]

@bednee It was because of that. Because the tables 'link_cache' and 'link_oldlinks' were missing because of that error and thats why I couldn't even have a link cache.

I have seen, that you have in the ext_tables.sql the following:

CREATE TABLE link_cache (
	id int(10) unsigned NOT NULL auto_increment,
	params blob,
	url char(255),
	tstamp TIMESTAMP default CURRENT_TIMESTAMP,
	crdatetime datetime default NULL,
	sticky tinyint(1) unsigned default 0,

	PRIMARY KEY (id),
	KEY url (url(255)),
	KEY params (params(255))
);

And tstamp TIMESTAMP default CURRENT_TIMESTAMP is correct. So I dont know from where he got the sql statements above, where DATETIME is the wrong type. Do you have an idea?

[bednee]

I think TYPO3 is doing it. It doesn't support TIMESTAMP naturally so it probably tries to remap it to DATETIME.

[mo-mueller]

Ah yes, that's possible. But is the default CURRENT_TIMESTAMP important? Maybe you can remove that. I did the same when I executed the statements manually. And right now it seems like it's working.

[bednee]

Well, it's needed at the moment. I believe it's possible to remove it, but I don't have capacity for it at the moment. I'll keep this issue open and hopefully I'll find some time to work on it (or approve merge request ;) )