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

[Juri Haberland]

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.

   Juri