search

trusteddomainproject / OpenDMARC

This is the Trusted Domain Project's impementation of the DMARC protocol libary and mail filter, called OpenDMARC. A "milter" connects to unix-based mailers (originally, sendmail, but now many) and provides a standard filtering API.
Other
98 stars 52 forks source link

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

Open deeztek opened 2 years ago

deeztek commented 2 years ago

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 commented 2 years ago

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.