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

[Juri Haberland]

Juri Haberland wrote:
> On 2016-10-27 09:31, Niklaas Baudet von Gersdorff wrote:
>> OpenDMARC, as distributed by FreeBSD, comes with two files for
>> creating a database for reporting: mkdb.mysql and schema.mysql.
>> Which one should I use?
>>
>> I think about using the former because it seems to be newer
>> (copyright is from 2013 vs. 2010), but I'm wondering why the
>> older one is packaged too.
>
> 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.

For a patch see ticket 197: https://sourceforge.net/p/opendmarc/tickets/197/

  Juri