opendmarc-import: failed to create table ID: Field 'repuri' doesn't have a default value

[deeztek]

Hi,

Having an issue running Dmarc reports. Script reports the following:

opendmarc-import: failed to create table ID: Field 'repuri' doesn't have a default value

According to the mysql schema the default value of '' doesn't seem to be appropriate. What should the default value be instead of ''?

-- A table for logging reporting requests
CREATE TABLE IF NOT EXISTS requests (
	id INT NOT NULL AUTO_INCREMENT,
	domain INT NOT NULL,
	repuri VARCHAR(255) NOT NULL DEFAULT '',
	adkim TINYINT NOT NULL DEFAULT '0',
	aspf TINYINT NOT NULL DEFAULT '0',
	policy TINYINT NOT NULL DEFAULT '0',
	spolicy TINYINT NOT NULL DEFAULT '0',
	pct TINYINT NOT NULL DEFAULT '0',
	locked TINYINT NOT NULL DEFAULT '0',
	firstseen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	lastsent TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00',

	PRIMARY KEY(id),
	KEY(lastsent),
	UNIQUE KEY(domain)
);

[endoflevelboss]

Is mariadb used as the database backend? In mariadb the particular value '1970-01-01 00:00:00' seems to have a special meaning (#0 epoch time) that's different to mysql.

I succeeded by removing "DEFAULT '1970-01-01 00:00:00'" from the lastsent line and then importing the scheme manually by the mysql command line client. I guess opendmarc-import will do the import on first execution, so probably making the change in /usr/share/opendmarc/db/schema.mysql will get applied as well.

With the default removed, the default value will be set to "0000-00-00 00:00:00". Worked for me and I did not dig deeper.