Syslog-NG mysql log with auto increment not working
I have Syslog-ng (ver 3.25.1) on Ubuntu 18.4 server. I am using following code to dynamically create date wise table and insert records accordingly. ************************ destination d_mysql { sql(type(mysql) host("localhost") username("root") password("XXXXXXXX") database("syslog") table("${R_YEAR}_${R_MONTH}_${R_DAY}") columns( "id int(20) unsigned not null auto_increment primary key", "host varchar(40) not null", "facility varchar(20)", "priority varchar(10)", "level varchar(10)", "program text", "date date not null", "time time not null", "message text not null") values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG",) indexes("host","priority")); }; ************************ Above code auto creates table like this ************************ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | host | varchar(40) | NO | MUL | NULL | | | facility | varchar(20) | YES | | NULL | | | priority | varchar(10) | YES | MUL | NULL | | | level | varchar(10) | YES | | NULL | | | program | text | YES | | NULL | | | date | date | NO | | NULL | | | time | time | NO | | NULL | | | message | text | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 9 rows in set (0.01 sec) ************************ Problem is that its not inserting record in the table. I enabled mysql log and I can see only one entry ************************ 2020-01-08T05:26:29.016553Z 167 Query INSERT INTO 2020_01_08 (id, host, facility, priority, level, program, date, time, message) VALUES ('NULL', '101.11.11.252', 'user', 'notice', 'notice', 'script,warning', '2020-01-08', '10:23:30', '10:23:30 jan/08/2020') ************************ but this entry is not recorded in the mysql table, When I remove the ID columns/values from the syslog code, DROP the table, & restart syslog-ng service, then table again recates, and all records inserts fine without problem. When I insert record using phpmyadmin, the record enter fine with this code INSERT INTO `2020_01_08` (`id`, `host`, `facility`, `priority`, `level`, `program`, `date`, `time`, `message`) VALUES (NULL, '2', '2', '2', '2', '2', '2020-01-01', '42:00:00', '2'); I tried to add NULL in the values for ID in the syslog-ng code , but no use, as soon as i remove ID DROP the table, & restart syslog-ng service, all goes fine. How can I configure the auto increment column ? Regards, SYED JAHANZAIB Web : http://aacable.wordpress.com LinkedIn: http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407 <http:///>
Your id column is an int so try switching values("") to values("0") On Wed, Jan 8, 2020 at 12:33 AM JAHANZAIB SYED <aacable@hotmail.com> wrote:
I have Syslog-ng (ver 3.25.1) on Ubuntu 18.4 server. I am using following code to dynamically create date wise table and insert records accordingly.
************************ destination d_mysql { sql(type(mysql) host("localhost") username("root") password("XXXXXXXX") database("syslog") table("${R_YEAR}_${R_MONTH}_${R_DAY}") columns( "id int(20) unsigned not null auto_increment primary key", "host varchar(40) not null", "facility varchar(20)", "priority varchar(10)", "level varchar(10)", "program text", "date date not null", "time time not null", "message text not null") values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG",) indexes("host","priority")); }; ************************
Above code auto creates table like this
************************ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | host | varchar(40) | NO | MUL | NULL | | | facility | varchar(20) | YES | | NULL | | | priority | varchar(10) | YES | MUL | NULL | | | level | varchar(10) | YES | | NULL | | | program | text | YES | | NULL | | | date | date | NO | | NULL | | | time | time | NO | | NULL | | | message | text | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 9 rows in set (0.01 sec) ************************
Problem is that its not inserting record in the table. I enabled mysql log and I can see only one entry
************************ 2020-01-08T05:26:29.016553Z 167 Query INSERT INTO 2020_01_08 (id, host, facility, priority, level, program, date, time, message) VALUES ('NULL', '101.11.11.252', 'user', 'notice', 'notice', 'script,warning', '2020-01-08', '10:23:30', '10:23:30 jan/08/2020') ************************
but this entry is not recorded in the mysql table,
When I remove the *ID *columns/values from the syslog code, DROP the table, & restart syslog-ng service, then table again recates, and all records inserts fine without problem.
When I insert record using phpmyadmin, the record enter fine with this code
INSERT INTO `2020_01_08` (`id`, `host`, `facility`, `priority`, `level`, `program`, `date`, `time`, `message`) VALUES (NULL, '2', '2', '2', '2', '2', '2020-01-01', '42:00:00', '2');
I tried to add NULL in the values for ID in the syslog-ng code , but no use, as soon as i remove ID DROP the table, & restart syslog-ng service, all goes fine.
How can I configure the auto increment column ?
*Regards, * *SYED JAHANZAIB*
*Web : http://aacable.wordpress.com <http://aacable.wordpress.com> LinkedIn: http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407 <http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407>*
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.balabit.com/wiki/syslog-ng-faq
SOLVED adding "0" solve the problem. Thank you for quick support :) Regards, SYED JAHANZAIB <http:///> ________________________________ From: syslog-ng <syslog-ng-bounces@lists.balabit.hu> on behalf of Nik Ambrosch <nik@ambrosch.com> Sent: Wednesday, January 8, 2020 10:51 AM To: Syslog-ng users' and developers' mailing list <syslog-ng@lists.balabit.hu> Subject: Re: [syslog-ng] Syslog-NG mysql log with auto increment not working Your id column is an int so try switching values("") to values("0") On Wed, Jan 8, 2020 at 12:33 AM JAHANZAIB SYED <aacable@hotmail.com<mailto:aacable@hotmail.com>> wrote: I have Syslog-ng (ver 3.25.1) on Ubuntu 18.4 server. I am using following code to dynamically create date wise table and insert records accordingly. ************************ destination d_mysql { sql(type(mysql) host("localhost") username("root") password("XXXXXXXX") database("syslog") table("${R_YEAR}_${R_MONTH}_${R_DAY}") columns( "id int(20) unsigned not null auto_increment primary key", "host varchar(40) not null", "facility varchar(20)", "priority varchar(10)", "level varchar(10)", "program text", "date date not null", "time time not null", "message text not null") values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG",) indexes("host","priority")); }; ************************ Above code auto creates table like this ************************ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | host | varchar(40) | NO | MUL | NULL | | | facility | varchar(20) | YES | | NULL | | | priority | varchar(10) | YES | MUL | NULL | | | level | varchar(10) | YES | | NULL | | | program | text | YES | | NULL | | | date | date | NO | | NULL | | | time | time | NO | | NULL | | | message | text | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 9 rows in set (0.01 sec) ************************ Problem is that its not inserting record in the table. I enabled mysql log and I can see only one entry ************************ 2020-01-08T05:26:29.016553Z 167 Query INSERT INTO 2020_01_08 (id, host, facility, priority, level, program, date, time, message) VALUES ('NULL', '101.11.11.252', 'user', 'notice', 'notice', 'script,warning', '2020-01-08', '10:23:30', '10:23:30 jan/08/2020') ************************ but this entry is not recorded in the mysql table, When I remove the ID columns/values from the syslog code, DROP the table, & restart syslog-ng service, then table again recates, and all records inserts fine without problem. When I insert record using phpmyadmin, the record enter fine with this code INSERT INTO `2020_01_08` (`id`, `host`, `facility`, `priority`, `level`, `program`, `date`, `time`, `message`) VALUES (NULL, '2', '2', '2', '2', '2', '2020-01-01', '42:00:00', '2'); I tried to add NULL in the values for ID in the syslog-ng code , but no use, as soon as i remove ID DROP the table, & restart syslog-ng service, all goes fine. How can I configure the auto increment column ? Regards, SYED JAHANZAIB Web : http://aacable.wordpress.com LinkedIn: http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407 <http:///> ______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.balabit.com/wiki/syslog-ng-faq
participants (2)
-
JAHANZAIB SYED
-
Nik Ambrosch