Redis backend: failed while db.Prepare(INSERT...) ... Unknown column 'return_path'...

[truedays]

Hello,

I'm attempting to use the guerrilla-db-redis backend. I've populated my mysql DB with the SQL provided in README.md but I still get the following error.

[root@Meteor go-guerrilla]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 53
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use gmail
Database changed
MariaDB [gmail]> CREATE TABLE IF NOT EXISTS `new_mail` (   `mail_id` int(11) NOT NULL auto_increment,   `date` datetime NOT NULL,   `from` varchar(128) character set latin1 NOT NULL,   `to` varchar(128) character set latin1 NOT NULL,   `subject` varchar(255) NOT NULL,   `body` text NOT NULL,   `charset` varchar(32) character set latin1 NOT NULL,   `mail` longblob NOT NULL,   `spam_score` float NOT NULL,   `hash` char(32) character set latin1 NOT NULL,   `content_type` varchar(64) character set latin1 NOT NULL,   `recipient` varchar(128) character set latin1 NOT NULL,   `has_attach` int(11) NOT NULL,   `ip_addr` varchar(15) NOT NULL,   `delivered` bit(1) NOT NULL default b'0',   `attach_info` text NOT NULL,   `dkim_valid` tinyint(4) default NULL,   PRIMARY KEY  (`mail_id`),   KEY `to` (`to`),   KEY `hash` (`hash`),   KEY `date` (`date`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

MariaDB [gmail]> Bye
[root@Meteor go-guerrilla]# ./guerrillad  -v serve
INFO[0000] guerrillad 1.5.1-30-g44d8a9d                 
DEBU[0000] Build Time: 2017-01-20_01:28:15_-0500        
DEBU[0000] Commit:     44d8a9dc97cd149d7cbce80a293dda9ff248ea97 
DEBU[0000] making servers                               
INFO[0000] Listening on TCP 0.0.0.0:25                  
DEBU[0000] [0.0.0.0:25] Waiting for a new client. Next Client ID: 1 
INFO[0000] pid_file (/var/run/go-guerrilla.pid) written with pid:6849 
FATA[0000] failed while db.Prepare(INSERT...)            error="Received #1054 error from MySQL server: \"Unknown column 'return_path' in 'field list'\""

Shouldn't the schema be 1:1 not 15 of 17? (sorry for the poor illustration!):

[root@Meteor go-guerrilla]# mysql gmail -Ne 'desc new_mail' | nl
     1  mail_id int(11) NO      PRI     NULL    auto_increment
     2  date    datetime        NO      MUL     NULL
     3  from    varchar(128)    NO              NULL
     4  to      varchar(128)    NO      MUL     NULL
     5  subject varchar(255)    NO              NULL
     6  body    text    NO              NULL
     7  charset varchar(32)     NO              NULL
     8  mail    longblob        NO              NULL
     9  spam_score      float   NO              NULL
    10  hash    char(32)        NO      MUL     NULL
    11  content_type    varchar(64)     NO              NULL
    12  recipient       varchar(128)    NO              NULL
    13  has_attach      int(11) NO              NULL
    14  ip_addr varchar(15)     NO              NULL
    15  delivered       bit(1)  NO              b'0'
    16  attach_info     text    NO              NULL
    17  dkim_valid      tinyint(4)      YES             NULL
[root@Meteor go-guerrilla]# git grep return_path | tr , '\n' | nl
     1  backends/guerrilla_db_redis.go: sql += "(`date`
     2   `to`
     3   `from`
     4   `subject`
     5   `body`
     6   `charset`
     7   `mail`
     8   `spam_score`
     9   `hash`
    10   `content_type`
    11   `recipient`
    12   `has_attach`
    13   `ip_addr`
    14   `return_path`
    15   `is_tls`)"

[flashmob]

Hi, Looks the example schema given in Readme is wrong. Can you please try this schema and see if it works?

CREATE TABLE IF NOT EXISTS `new_mail` (
  `mail_id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `from` varchar(128) character set latin1 NOT NULL,
  `to` varchar(128) character set latin1 NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `charset` varchar(32) character set latin1 NOT NULL,
  `mail` longblob NOT NULL,
  `spam_score` float NOT NULL,
  `hash` char(32) character set latin1 NOT NULL,
  `content_type` varchar(64) character set latin1 NOT NULL,
  `recipient` varchar(128) character set latin1 NOT NULL,
  `has_attach` int(11) NOT NULL,
  `ip_addr` varchar(15) NOT NULL,
  `return_path` VARCHAR(255) NOT NULL,
  `is_tls` BIT(1) DEFAULT b'0' NOT NULL,
  PRIMARY KEY  (`mail_id`),
  KEY `to` (`to`),
  KEY `hash` (`hash`),
  KEY `date` (`date`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

[truedays]

Hi @flashmob ,

With that schema I get: FATA[0000] failed while db.Prepare(UPDATE...) error="Received #1146 error from MySQL server: \"Table 'gmail.gm2_setting' doesn't exist\""

[flashmob]

You'll need to create one more table:

CREATE TABLE gm2_setting
(
    setting_name VARCHAR(250) NOT NULL,
    setting_value TEXT,
    site_id INT(11) DEFAULT '1' NOT NULL
);
CREATE INDEX setting_name ON gm2_setting (setting_name);

and then:

insert into gm2_setting (setting_name), ('received_emails')

[truedays]

The SQL CREATE Adding the _gm2setting table fixed my issue. :+1:

The subsequent SQL INSERT didn't work, but everything seems functional even with no data in that table. :thinking:

[flashmob]

Looking into it more, it looks like the SQL code in this file is outdated and different to what we have in prod. We no longer use the gm2_setting table to store the tally, we count the tallies in a different system and we only need to issue one query to update the tally every few seconds. Doing it for every email received is very inefficient. Also, the setting_value type is TEXT which is very inefficient to inclement, and will only work if you have turned off strict mode in MySQL https://support.kayako.com/article/472-how-do-i-disable-mysql-strict-mode-on-the-server. In conclusion, the guerrilla_db_redis.go is only an example, you would need to customize the query / saving to fit your own system.

Anyhow, thanks for pointing out these problems. An improved guerrilla_db_redis.go will be pushed soon that also has a query batching optimization.

[truedays]

Thank you @flashmob !