incorrect datetime value

[sraghavarao]

rsyslogd: ommysql: db error (1292): Incorrect datetime value: 'Jan 17 17:06:09' for column 'logtime' at row 1 [v8.32.0].

rsyslog sends datetime value in the above format and the column logtime is defined as "0000:00:00:00:00" . tried all formats available in rsyslog still it sends the same format with is different from the field definition in mysql TABLE.

[TheWitness]

What version of syslog plugin are you running? Also, run the following commands and post the output:

SHOW CREATE TABLE syslog_incoming;
SHOW CREATE TABLE syslog;

cat /etc/rsyslog.d/cacti.conf

[sraghavarao]

SHOW CREATE TABLE syslog_incomingsyslog_incoming Create Table: CREATE TABLE syslog_incoming ( facility_id int(10) unsigned DEFAULT NULL, priority_id int(10) unsigned DEFAULT NULL, program varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL, logtime timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', host varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, message varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', seq bigint(20) unsigned NOT NULL AUTO_INCREMENT, status tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (seq), KEY program (program), KEY status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

SHOW CREATE TABLE syslog  syslog Create Table: CREATE TABLE syslog ( facility_id int(10) unsigned DEFAULT NULL, priority_id int(10) unsigned DEFAULT NULL, program_id int(10) unsigned DEFAULT NULL, host_id int(10) unsigned DEFAULT NULL, logtime timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', message varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', seq bigint(20) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (seq), KEY logtime (logtime), KEY program_id (program_id), KEY host_id (host_id), KEY priority_id (priority_id), KEY facility_id (facility_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci  

cat /etc/rsyslog.d/cacti.conf

$ModLoad imudp $UDPServerRun 514 $ModLoad ommysql

$template cacti_syslog,"INSERT INTO syslog_incoming(facility_id, priority_id, program, logtime, host, message) \   values (%syslogfacility%, %syslogpriority%, '%programname%', '%timegenerated%', '%HOSTNAME%', '%msg%')", SQL

. >localhost,cacti,cactiuser,colhyd4065;cacti_syslog

using rsyslog v8.32 , ubuntu 18.04.

                    From: South Park Neo <notifications@github.com>Sent: Fri, 17 Jan 2020 18:18:57 GMT+0530To: Cacti/plugin_syslog <plugin_syslog@noreply.github.com>Cc: Raghavarao <srrao@cityonlines.com>, Author <author@noreply.github.com>Subject: Re: [Cacti/plugin_syslog] incorrect datetime value (#106) What version of syslog plugin are you running? Also, run the following commands and post the output:

SHOW CREATE TABLE syslog_incoming; SHOW CREATE TABLE syslog; cat /etc/rsyslog.d/cacti.conf

[TheWitness]

Try this in place of simply %timegenerated%, do '%timegenerated:::date-mysql% and see if that clears it up.

[sraghavarao]

Great the job is done . working fine after the change

[TheWitness]

Okay, let me update the documentation then. Thanks for catching and reporting.