search

userjack6880 / Open-DMARC-Analyzer

Open DMARC Analyzer is an Open Source DMARC Report Analyzer to be used with DMARC reports that have been parsed by John Levine's rrdmarc script or techsneeze's dmarcts-report-parser.
GNU General Public License v3.0
217 stars 23 forks source link

[Bug]: Table 'dmarc.report' doesn't exist #104

Open surad123 opened 5 months ago

surad123 commented 5 months ago

Describe the bug A clear and concise description of what the bug is.

To Reproduce Steps to reproduce the behavior:

  1. Go to install.php
  2. See error: connecting to database... success opening file... success → CREATE VIEW IF NOT EXISTS report_stats AS ( → SELECT → report.serial, domain, rcount, disposition, reason, → policy_p, policy_pct, dkimdomain, dkimresult, dkim_align, → spfdomain, spfresult, spf_align, mindate, maxdate → FROM report RIGHT JOIN rptrecord → ON report.serial=rptrecord.serial → ); performing query... failed: Table 'dmarc.report' doesn't exist

Expected behavior sql will not execute

Software Version [x] Version 1

Desktop (please complete the following information):

Server (please complete the following information):

userjack6880 commented 4 months ago

Have you already installed and ran Open Report Parser (or a similar project) before attempting install of this? Per the Readme:

robbrandt commented 4 months ago

I just ran into this too. Ubuntu. Open Report Parser installed. The issue is that CREATE VIEW IF NOT EXISTS doesn't seem to be a thing by looking at the MySQL documentation. I changed it to CREATE VIEW (because I knew it didn't exist) and ran install again, and everything worked.

userjack6880 commented 3 months ago

I just ran into this too. Ubuntu. Open Report Parser installed. The issue is that CREATE VIEW IF NOT EXISTS doesn't seem to be a thing by looking at the MySQL documentation. I changed it to CREATE VIEW (because I knew it didn't exist) and ran install again, and everything worked.

Thanks for pointing this out - this will be reviewed.

MatthewHana commented 3 months ago

I just ran into this too. Ubuntu. Open Report Parser installed. The issue is that CREATE VIEW IF NOT EXISTS doesn't seem to be a thing by looking at the MySQL documentation. I changed it to CREATE VIEW (because I knew it didn't exist) and ran install again, and everything worked.

It looks like your issue is different to OPs. OP is running MariaDB 10.3 which has support for 'IF NOT EXISTS' in 'CREATE VIEW' statements since MariaDB 10.1.3 released in 2015. See https://mariadb.com/kb/en/create-view/ and https://mariadb.com/kb/en/mariadb-10-1-3-release-notes for more info.

The MySQL v8.0 (and 5.7) documentation doesn't show support for 'IF NOT EXISTS' in 'CREATE VIEW'. See https://dev.mysql.com/doc/refman/8.0/en/create-view.html and https://dev.mysql.com/doc/refman/5.7/en/create-view.html.

There is shared support for 'OR REPLACE' in 'CREATE VIEW' statements in both MySQL and MariaDB. It could be used as an alternative to 'IF NOT EXISTS' to prevent SQL errors. This will obviously have the effect of overriding the view if it exists, but it should ensure consistency between both variants.