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

[Nik Ambrosch]

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