Not logging mail to SQL #1198

ricain59 · 2020-11-06T13:42:00Z

Issue summary

Hi,

I have a fresh install on centos 8.2.2004 with this versions:

I have installed the package required:
perl-DBD-MySQL-4.046-3.module_el8.1.0+203+e45423dc.x86_64
perl-DBI-1.641-3.module_el8.1.0+199+8f0a6bbd.x86_64

Steps to reproduce

I have start mailscanner with this command: MailScanner --debug and return this error:
`[root@mail ~]# MailScanner --debug

In Debugging mode, not forking...
Trying to setlogsock(unix)
internal error, python traceback seen in response
Building a message batch to scan...
Have a batch of 4 messages.
internal error, python traceback seen in response
internal error, python traceback seen in response
internal error, python traceback seen in response
internal error, python traceback seen in response
Stopping now as you are debugging me.
Could not use Custom Function code MailScanner::CustomConfig::InitMailWatchLogging, it could not be "eval"ed. Make sure the module is correct with perl -wc (Error: DBD::mysql::st execute failed: Incorrect string value: '\xE3o spa...' for column mailscanner.maillog.spamreport at row 1 at /usr/share/MailScanner/perl/custom/MailWatch.pm line 226, line 30.
) at /usr/share/MailScanner/perl/MailScanner/Config.pm line 1053.
`

Actual result

I have verify and database on utf8mb4_unicode_ci
I have search but unsuccesfully resolve my problem.

Can you help me on this problem?
Thank you

Installation

Version and method

MailWatch Version: 1.2.15
Install type: zip
Updated from an older MailWatch or fresh install: Fresh install

Server configuration

Operation System: CentOS
PHP version: 7.2.24
Database: MariaDB
Mail Server: Postfix
Used web server: Apache

The text was updated successfully, but these errors were encountered:

endelwar · 2020-11-06T16:44:50Z

Hi, which encoding and collation are set on mailscanner.maillog.spamreport? They should read utf8mb4 and utf8mb4_unicode_ci.

DBD::mysql::st execute failed: Incorrect string value: '\xE3o spa...' for column mailscanner.maillog.spamreport at row 1 usually indicates that table field can't accept utf8 strings which contain 4 bytes chars (in MySQL utf8mb4).

ricain59 · 2020-11-09T10:19:48Z

Hi,
Thank you for answer.

i hope your question refers to the given base on table maillog:

I do changes this?

Skywalker-11 · 2020-11-09T11:19:49Z

@ricain59 did you run the upgrade.php after install?

ricain59 · 2020-11-09T11:26:10Z

@Skywalker-11 Yes, but i run again after your suggestion:

MailWatch for MailScanner Database Upgrade to 1.2.15

Have you done a full backup of your database? Type 'yes' to continue: yes

Testing connectivity to the database ................................. OK

Updating database schema:

 - Convert database to UTF-8.......................................... ALREADY DONE

 - Fix security issues in database.................................... No known security issues

 - Drop `geoip_country` table......................................... ALREADY DROPPED
 - Drop `spamscores` table............................................ ALREADY DROPPED
 - Add autorelease table to `mailscanner` database.................... ALREADY EXIST
 - Add mtalog_ids table to `mailscanner` database..................... ALREADY EXIST
 - Add resetid, resetexpire and lastreset fields in `users` table..... ALREADY EXIST
 - Add login_expiry and login_timeout fields in `users` table......... ALREADY EXIST
 - Add id field in `users` table...................................... ALREADY EXIST
 - Fix schema for type field in `users` table......................... OK

 - Fix schema for username field in `audit_log` table................. ALREADY DONE
 - Fix schema for id field in `blacklist` table....................... ALREADY DONE
 - Fix schema for id field in `whitelist` table....................... ALREADY DONE
 - Fix schema for username field in `users` table..................... ALREADY DONE
 - Fix schema for username field in `user_filters` table.............. ALREADY DONE
 - Fix schema for spamscore field in `users` table.................... ALREADY DONE
 - Fix schema for highspamscore field in `users` table................ ALREADY DONE
 - Fix schema for timestamp field in `maillog` table.................. ALREADY DONE
 - Fix schema for nameinfected in `maillog` table..................... ALREADY DONE
 - Fix schema for password field in `users` table..................... ALREADY DONE
 - Fix schema for fullname field in `users` table..................... ALREADY DONE
 - Fix schema for rule_desc field in `mcp_rules` table................ ALREADY DONE

 - Cleanup orphaned user_filters...................................... OK
 - Add id field and primary key to `audit_log` table.................. ALREADY DONE
 - Add inq_id field and primary key to `inq` table.................... ALREADY DONE
 - Add maillog_id field and primary key to `maillog` table............ ALREADY DONE
 - Add rblspamreport field to `maillog` table......................... ALREADY DONE
 - Add token field to `maillog` table................................. ALREADY DONE
 - Add released field to `maillog` table.............................. ALREADY DONE
 - Add last_update field to `maillog` table........................... ALREADY EXIST
 - Add salearn field to `maillog` table............................... ALREADY DONE
 - Add messageid field to `maillog` table............................. ALREADY DONE
 - Check for missing tokens in `maillog` table........................ NOTHING FOUND
 - Add mtalog_id field and primary key to `mtalog` table.............. ALREADY DONE
 - Add mtalog_id field and primary key to `outq` table................ ALREADY DONE
 - Add id field and primary key to `saved_filters` table.............. ALREADY DONE
 - Add mtalog_id field and primary key to `user_filters` table........ ALREADY DONE

 - Dropping too big index `maillog_from_idx` on table `maillog`....... ALREADY DONE
 - Dropping too big index `maillog_to_idx` on table `maillog`......... ALREADY DONE

 - Convert database to utf8mb4........................................ ALREADY DONE

 - Convert table `audit_log` to utf8mb4............................... ALREADY CONVERTED
 - Convert table `autorelease` to utf8mb4............................. ALREADY CONVERTED
 - Convert table `blacklist` to utf8mb4............................... ALREADY CONVERTED
 - Convert table `inq` to utf8mb4..................................... ALREADY CONVERTED
 - Convert table `maillog` to utf8mb4................................. ALREADY CONVERTED
 - Convert table `mcp_rules` to utf8mb4............................... ALREADY CONVERTED
 - Convert table `mtalog` to utf8mb4.................................. ALREADY CONVERTED
 - Convert table `mtalog_ids` to utf8mb4.............................. ALREADY CONVERTED
 - Convert table `outq` to utf8mb4.................................... ALREADY CONVERTED
 - Convert table `saved_filters` to utf8mb4........................... ALREADY CONVERTED
 - Convert table `sa_rules` to utf8mb4................................ ALREADY CONVERTED
 - Convert table `users` to utf8mb4................................... ALREADY CONVERTED
 - Convert table `user_filters` to utf8mb4............................ ALREADY CONVERTED
 - Convert table `whitelist` to utf8mb4............................... ALREADY CONVERTED

 - Convert table `audit_log` to InnoDB................................ ALREADY CONVERTED
 - Convert table `autorelease` to InnoDB.............................. ALREADY CONVERTED
 - Convert table `blacklist` to InnoDB................................ ALREADY CONVERTED
 - Convert table `inq` to InnoDB...................................... ALREADY CONVERTED
 - Convert table `maillog` to InnoDB.................................. ALREADY CONVERTED
 - Convert table `mcp_rules` to InnoDB................................ ALREADY CONVERTED
 - Convert table `mtalog` to InnoDB................................... ALREADY CONVERTED
 - Convert table `mtalog_ids` to InnoDB............................... ALREADY CONVERTED
 - Convert table `outq` to InnoDB..................................... ALREADY CONVERTED
 - Convert table `saved_filters` to InnoDB............................ ALREADY CONVERTED
 - Convert table `sa_rules` to InnoDB................................. ALREADY CONVERTED
 - Convert table `users` to InnoDB.................................... ALREADY CONVERTED
 - Convert table `user_filters` to InnoDB............................. ALREADY CONVERTED
 - Convert table `whitelist` to InnoDB................................ ALREADY CONVERTED

 - Search for missing indexes on table `maillog`...................... DONE

Checking for obsolete files:

 - Cache dir already removed.......................................... OK

Checking MailScanner.conf settings:

 - QuarantineWholeMessage ............................................ OK
 - QuarantineWholeMessagesAsQueueFiles ............................... OK
 - DetailedSpamReport ................................................ OK
 - IncludeScoresInSpamAssassinReport ................................. OK
 - SpamActions ....................................................... OK
 - HighScoringSpamActions ............................................ OK
 - AlwaysLookedUpLast ................................................ OK

Checking conf.php configuration entry:

 - All mandatory entries are present.................................. OK
 - All obsolete entries are already removed........................... OK
 - optional RPC_PORT ................................................. INFO
 - optional RPC_SSL .................................................. INFO
 - optional RPC_REMOTE_SERVER ........................................ INFO
 - optional VIRUS_REGEX .............................................. INFO
 - optional LDAP_BIND_PREFIX ......................................... INFO
 - optional LDAP_BIND_SUFFIX ......................................... INFO
 - optional EXIM_QUEUE_IN ............................................ INFO
 - optional EXIM_QUEUE_OUT ........................................... INFO
 - optional PWD_RESET_FROM_NAME ...................................... INFO
 - optional PWD_RESET_FROM_ADDRESS ................................... INFO
 - optional MAIL_SENDER .............................................. INFO
 - optional SESSION_NAME ............................................. INFO
 - optional SENDMAIL_QUEUE_IN ........................................ INFO
 - optional SENDMAIL_QUEUE_OUT ....................................... INFO
 - optional MAILWATCH_SMTP_HOSTNAME .................................. INFO
 - optional ALLOW_NO_USER_DOMAIN ..................................... INFO
 - optional ENABLE_SUPER_DOMAIN_ADMINS ............................... INFO


 - Checking minimal PHP version >= 5.4................................ PHP version OK

*** ERROR/WARNING SUMMARY ***
conf.php: optional configuration entry "RPC_PORT" is missing, needed if RPC_ONLY mode is enabled
conf.php: optional configuration entry "RPC_SSL" is missing, needed if RPC_ONLY mode is enabled
conf.php: optional configuration entry "RPC_REMOTE_SERVER" is missing, needed to show number of mails in postfix queues on remote server (RPC)
conf.php: optional configuration entry "VIRUS_REGEX" is missing, needed in distributed setup
conf.php: optional configuration entry "LDAP_BIND_PREFIX" is missing, needed when using LDAP authentication
conf.php: optional configuration entry "LDAP_BIND_SUFFIX" is missing, needed when using LDAP authentication
conf.php: optional configuration entry "EXIM_QUEUE_IN" is missing, needed only if using Exim as MTA
conf.php: optional configuration entry "EXIM_QUEUE_OUT" is missing, needed only if using Exim as MTA
conf.php: optional configuration entry "PWD_RESET_FROM_NAME" is missing, needed if Password Reset feature is enabled
conf.php: optional configuration entry "PWD_RESET_FROM_ADDRESS" is missing, needed if Password Reset feature is enabled
conf.php: optional configuration entry "MAIL_SENDER" is missing, needed if you use Exim or Sendmail Queue
conf.php: optional configuration entry "SESSION_NAME" is missing, needed if experiencing session conflicts
conf.php: optional configuration entry "SENDMAIL_QUEUE_IN" is missing, needed only if using Sendmail as MTA
conf.php: optional configuration entry "SENDMAIL_QUEUE_OUT" is missing, needed only if using Sendmail as MTA
conf.php: optional configuration entry "MAILWATCH_SMTP_HOSTNAME" is missing, needed only if you use a remote SMTP server to send MailWatch emails
conf.php: optional configuration entry "ALLOW_NO_USER_DOMAIN" is missing, allow usernames not in mail format for domain admins and regular users
conf.php: optional configuration entry "ENABLE_SUPER_DOMAIN_ADMINS" is missing, allows domain admins to change domain admins from the same domain

ricain59 · 2020-11-11T10:51:54Z

Hi,

Any other idea for help?

Thank you.

Skywalker-11 · 2020-11-11T11:56:34Z

Do you have Digest::SHA installed? Since 1.2.5 it is required instead of Digest::SHA1)

ricain59 · 2020-11-11T13:12:09Z

@Skywalker-11 yes, this is installed

Digest::SHA is up to date (6.02).

ricain59 · 2020-11-13T09:56:41Z

Hi,
Any other idea ?
Thank you for help.

Skywalker-11 · 2020-11-13T14:44:40Z

Can you run this as the user MailScanner is running at to verify that it is using the correct versions?

perl -MDBD::mysql -e 'print "$DBD::mysql::VERSION \n" '
perl -MDBD::mysql -e 'print "$DBI::VERSION \n" '

ricain59 · 2020-11-13T15:38:41Z

@Skywalker-11 Of course, this is result:

MailScanner.conf:Run As User = postfix
[root@mail MailScanner]# sudo -u postfix perl -MDBD::mysql -e 'print "$DBD::mysql::VERSION \n" '
4.046
[root@mail MailScanner]# sudo -u postfix perl -MDBD::mysql -e 'print "$DBI::VERSION \n" '
1.641

shawniverson · 2020-11-14T16:32:17Z

This is very bizarre to me considering that MailScanner is not python.

internal error, python traceback seen in response

ricain59 · 2020-11-16T10:04:16Z

@shawniverson
on my system i have python but this error is strange:

[root@mail ~]# python --version
Python 3.6.8

On received mail i have this on log:

....
MailWatch: Logging message D97811A433A0.A4DD8 to SQL
...

But never logged to SQL.

Thank you for your help, but i don't find anyway to resolve this, on log any warning or error appear less this message:

Could not use Custom Function code MailScanner::CustomConfig::InitMailWatchLogging, it could not be "eval"ed. Make sure the module is correct with perl -wc (Error: DBD::mysql::st execute failed: Incorrect string value: '\xE3o spa...' for column `mailscanner`.`maillog`.`spamreport` at row 1 at /usr/share/MailScanner/perl/custom/MailWatch.pm line 226, <CLIENT> line 52.

endelwar · 2020-11-18T13:41:49Z

I'm not using MariaDB on my server, but I see that changelog of the last release of DBD::MySQL (4.050) reports a fix for 10.3 compatibility.

I don't know if an upgrade will fix your issue, which seems related to UTF8 handling on MariaDB server side.

You can also try to force utf8mb4 on server side by editing my.cnf configuration file like this:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server = utf8mb4

forcing any clients, like MailWatch.pm, to use utf8mb4 encoding.

ricain59 · 2020-11-18T15:00:40Z

@endelwar Thank you for your suggestion.

I have edit this file:
/etc/my.cnf.d/client.cnf

[client]
default-character-set = utf8mb4

[client-mariadb]
default-character-set = utf8mb4

/etc/my.cnf.d/mariadb-server.cnf

...
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server = utf8mb4
....

/etc/my.cnf.d/mysql-clients.cnf

[mysql]
default-character-set = utf8mb4

Reboot and problem persist :(

[root@mail ~]# MailScanner --debug


In Debugging mode, not forking...
Trying to setlogsock(unix)
internal error, python traceback seen in response
Building a message batch to scan...
Have a batch of 1 message.
internal error, python traceback seen in response
Stopping now as you are debugging me.
Could not use Custom Function code MailScanner::CustomConfig::InitMailWatchLogging, it could not be "eval"ed. Make sure the module is correct with perl -wc (Error: DBD::mysql::st execute failed: Incorrect string value: '\xE3o spa...' for column `mailscanner`.`maillog`.`spamreport` at row 1 at /usr/share/MailScanner/perl/custom/MailWatch.pm line 226, <CLIENT> line 34.
) at /usr/share/MailScanner/perl/MailScanner/Config.pm line 1053.

Probably change mariadb to mysql? Other suggestion?

Thank you for your help.

shawniverson · 2020-11-19T12:51:49Z

Got another report on this issue from eFa Project side so also digging into this. I have no reports from CentOS 7 instances. This could be a clue so I'm going to compare component versions.

https://forum.efa-project.org/viewtopic.php?f=19&p=16962

ricain59 · 2020-11-23T16:17:43Z

Thank you, i'm waiting for feedback.

b416 · 2021-01-15T13:03:21Z

I have the same issue, but very strange, it's not logging some of the messages only...

# grep "MailWatch: Logging message" /var/log/mail.log | wc -l
1035
# grep "Logged to MailWatch SQL" /var/log/mail.log | wc -l
814

My site is a web forum sending mostly outgoing notifications to users.

And in the logs there is no difference between logged and not logged messages.

I forgot to specify, I am using the latest Persona 8 MySQL on Ubuntu 20.10.
Latest MailScanner, latest MailWatch, I like to keep my systems up to date.

shawniverson · 2021-01-17T16:21:26Z

To all having this issue, try the latest MailWatch 1.2.16, there were some updates in it that may help alleviate this issue.

ricain59 · 2021-01-28T17:16:58Z

Hi,

I've updated to 1.2.16 but i get same error.

Thank you.

ricain59 · 2021-02-11T11:38:27Z

Hi,

On file /usr/share/MailScanner/perl/custom/MailWatch.pm on line 369 i've changed:
#$msg{spamreport} = $spamreport;
$msg{spamreport} = fix_latin($spamreport);

And works, but it's ok this ?

Thank you

shawniverson · 2021-03-07T03:27:51Z

This seems to be the best approach to the problem.

Fixes #1198

shawniverson added the version:1.2 label Nov 14, 2020

shawniverson added the feedback-needed label Nov 19, 2020

shawniverson mentioned this issue Mar 7, 2021

Handle latin encoded chars found in reports and messageid #1208

Merged

shawniverson linked a pull request Mar 7, 2021 that will close this issue

Handle latin encoded chars found in reports and messageid #1208

Merged

endelwar closed this as completed in #1208 Mar 28, 2021

endelwar pushed a commit that referenced this issue Mar 28, 2021

Handle latin encoded chars found in reports and messageid (#1208)

ea3a7a9

Fixes #1198

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Not logging mail to SQL #1198

Not logging mail to SQL #1198

ricain59 commented Nov 6, 2020

endelwar commented Nov 6, 2020

ricain59 commented Nov 9, 2020 •

edited

Loading

Skywalker-11 commented Nov 9, 2020

ricain59 commented Nov 9, 2020 •

edited

Loading

ricain59 commented Nov 11, 2020

Skywalker-11 commented Nov 11, 2020 •

edited

Loading

ricain59 commented Nov 11, 2020

ricain59 commented Nov 13, 2020

Skywalker-11 commented Nov 13, 2020

ricain59 commented Nov 13, 2020

shawniverson commented Nov 14, 2020

ricain59 commented Nov 16, 2020

endelwar commented Nov 18, 2020

ricain59 commented Nov 18, 2020

shawniverson commented Nov 19, 2020

ricain59 commented Nov 23, 2020

b416 commented Jan 15, 2021 •

edited

Loading

shawniverson commented Jan 17, 2021

ricain59 commented Jan 28, 2021

ricain59 commented Feb 11, 2021

shawniverson commented Mar 7, 2021

Not logging mail to SQL #1198

Not logging mail to SQL #1198

Comments

ricain59 commented Nov 6, 2020

Issue summary

Steps to reproduce

Actual result

Installation

Version and method

Server configuration

endelwar commented Nov 6, 2020

ricain59 commented Nov 9, 2020 • edited Loading

Skywalker-11 commented Nov 9, 2020

ricain59 commented Nov 9, 2020 • edited Loading

ricain59 commented Nov 11, 2020

Skywalker-11 commented Nov 11, 2020 • edited Loading

ricain59 commented Nov 11, 2020

ricain59 commented Nov 13, 2020

Skywalker-11 commented Nov 13, 2020

ricain59 commented Nov 13, 2020

shawniverson commented Nov 14, 2020

ricain59 commented Nov 16, 2020

endelwar commented Nov 18, 2020

ricain59 commented Nov 18, 2020

shawniverson commented Nov 19, 2020

ricain59 commented Nov 23, 2020

b416 commented Jan 15, 2021 • edited Loading

shawniverson commented Jan 17, 2021

ricain59 commented Jan 28, 2021

ricain59 commented Feb 11, 2021

shawniverson commented Mar 7, 2021

ricain59 commented Nov 9, 2020 •

edited

Loading

ricain59 commented Nov 9, 2020 •

edited

Loading

Skywalker-11 commented Nov 11, 2020 •

edited

Loading

b416 commented Jan 15, 2021 •

edited

Loading