[opendmarc-users] mkdb.mysql or schema.mysql?

[Niklaas Baudet von Gersdorff]

Juri Haberland [2016-10-27 15:17 +0200] :

> Looking at the git log db/schema.mysql was first and quite some changes
> where made to it. Then, suddenly, everyone forgot about db/schema.mysql and
> added report/mkdb.mysql, which received one important fix (details later).
> 
> The main differences between db/schema.mysql and report/mkdb.mysql are:
> 1) in table 'ipaddr': addr VARCHAR(64) NOT NULL  vs.  addr VARCHAR(64)
> DEFAULT NULL
> 2) in table 'messages': spf TINYINT UNSIGNED NOT NULL  vs.  spf TINYINT NOT
> NULL (this one is important)
> 3) in table 'requests': locked TINYINT(4) NOT NULL  vs.  locked TINYINT(4)
> NOT NULL DEFAULT '0'
> 
> #2 is important, because 'spf' can be '-1', so we want it to be signed!
> Regarding #1 IMHO it makes more sense for 'ipaddr' to be never NULL, because
> we need the IP address for the reports (it doesn't make any sense to send a
> report with saying where the message came from).
> For #3 having a default might be more sensible.
> 
> But there is more: The old db/schema.mysql has some additional keys defined,
> which should make the queries faster.
> 
> Looking at my database, it seems I used the old db/schema.mysql. I had no
> problems so far, but I altered the spf column in table messages to signed,
> now that I came across it.
> 
> I will open a ticket regarding this and prepare a patch that drops
> report/mkdb.mysql and adds #2 and #3 to db/schema.mysql.

Juri Haberland [2016-10-28 10:19 +0200] :
> 
> For a patch see ticket 197: https://sourceforge.net/p/opendmarc/tickets/197/

Juri, thanks a lot for investigating this and providing a patch!

On my systems, I'll initialise the database with the patched
schema.mysql.

    Niklaas