[syslog-ng] Syslog-NG mysql log with auto increment not working

[JAHANZAIB SYED]

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:///>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.balabit.hu/pipermail/syslog-ng/attachments/20200108/19d58375/attachment-0001.html>