<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
<b>SOLVED</b></div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
adding "0" solve the problem.</div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
Thank you for quick support :)</div>
<div style="font-family: "Courier New", monospace; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="Signature">
<div id="divtagdefaultwrapper" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<div><br>
</div>
<div><br>
<font color="#0066ff"><strong><font color="#000000">Regards,</font> </strong></font><br>
<font color="#0066ff"></font></div>
<div align="left"><strong><font color="#0033ff">SYED JAHANZAIB</font></strong></div>
<div align="center"><a href="http:///" target="_blank"><b style="font-family:'times new roman','new york',times,serif; color:black; font-size:12pt"><span dir="rtl" lang="AR-SA" style="color:green; font-size:20pt"></span></b></a><font face="Verdana" size="2"></font></div>
</div>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> syslog-ng <syslog-ng-bounces@lists.balabit.hu> on behalf of Nik Ambrosch <nik@ambrosch.com><br>
<b>Sent:</b> Wednesday, January 8, 2020 10:51 AM<br>
<b>To:</b> Syslog-ng users' and developers' mailing list <syslog-ng@lists.balabit.hu><br>
<b>Subject:</b> Re: [syslog-ng] Syslog-NG mysql log with auto increment not working</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div>Your id column is an int so try switching values("") to values("0")</div>
<div><br>
</div>
<div><br>
</div>
<div> </div>
</div>
<br>
<div class="x_gmail_quote">
<div dir="ltr" class="x_gmail_attr">On Wed, Jan 8, 2020 at 12:33 AM JAHANZAIB SYED <<a href="mailto:aacable@hotmail.com">aacable@hotmail.com</a>> wrote:<br>
</div>
<blockquote class="x_gmail_quote" style="margin:0px 0px 0px 0.8ex; border-left:1px solid rgb(204,204,204); padding-left:1ex">
<div dir="ltr">
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
<span>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.<br>
</span>
<div><br>
</div>
<div><span style="font-family:"Courier New",monospace; background-color:rgb(255,255,255); display:inline">************************</span></div>
<div>destination d_mysql {<br>
</div>
<div>sql(type(mysql)<br>
</div>
<div>host("localhost")<br>
</div>
<div>username("root")<br>
</div>
<div>password("XXXXXXXX")<br>
</div>
<div>database("syslog")<br>
</div>
<div>table("${R_YEAR}_${R_MONTH}_${R_DAY}")<br>
</div>
<div>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")<br>
</div>
<div> values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG",)<br>
</div>
<div> indexes("host","priority"));<br>
</div>
<div>};<br>
</div>
<div>************************</div>
<div><br>
</div>
<div>Above code auto creates table like this<br>
</div>
<div><br>
</div>
<div><span style="font-family:"Courier New",monospace; background-color:rgb(255,255,255); display:inline">************************</span></div>
<div>| Field | Type | Null | Key | Default | Extra |<br>
</div>
<div>+----------+------------------+------+-----+---------+----------------+<br>
</div>
<div>| id | int(11) unsigned | NO | PRI | NULL | auto_increment |<br>
</div>
<div>| host | varchar(40) | NO | MUL | NULL | |<br>
</div>
<div>| facility | varchar(20) | YES | | NULL | |<br>
</div>
<div>| priority | varchar(10) | YES | MUL | NULL | |<br>
</div>
<div>| level | varchar(10) | YES | | NULL | |<br>
</div>
<div>| program | text | YES | | NULL | |<br>
</div>
<div>| date | date | NO | | NULL | |<br>
</div>
<div>| time | time | NO | | NULL | |<br>
</div>
<div>| message | text | NO | | NULL | |<br>
</div>
<div>+----------+------------------+------+-----+---------+----------------+<br>
</div>
<div>9 rows in set (0.01 sec)<br>
</div>
<div><span style="font-family:"Courier New",monospace; background-color:rgb(255,255,255); display:inline">************************</span><br>
</div>
<div><br>
</div>
<div>Problem is that its not inserting record in the table. I enabled mysql log and I can see only one entry<br>
</div>
<div><br>
</div>
<div><span style="font-family:"Courier New",monospace; background-color:rgb(255,255,255); display:inline">************************</span><br>
</div>
<div>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')<br>
</div>
<div><span style="font-family:"Courier New",monospace; background-color:rgb(255,255,255); display:inline">************************</span><br>
</div>
<div><br>
</div>
<div>but this entry is not recorded in the mysql table, <br>
</div>
<div><br>
</div>
<div>When I remove the <span style="font-family:"Courier New",monospace; background-color:rgb(255,255,255); display:inline">
<b>ID </b></span>columns/values from the syslog code, DROP the table, & restart syslog-ng service, then table again recates, and all records inserts fine without problem.<br>
</div>
<div><br>
</div>
<div>When I insert record using phpmyadmin, the record enter fine with this code</div>
<div><br>
</div>
<div>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');<br>
</div>
<div><br>
</div>
<div>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.<br>
</div>
<div><br>
</div>
<div>How can I configure the auto increment column ?<br>
</div>
<div><br>
</div>
<span></span><br>
</div>
<div style="font-family:"Courier New",monospace; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div id="x_gmail-m_8313864630692289546Signature">
<div id="x_gmail-m_8313864630692289546divtagdefaultwrapper" style="font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255); font-family:Calibri,Arial,Helvetica,sans-serif">
<div><br>
</div>
<div><br>
<font color="#0066ff"><strong><font color="#000000">Regards,</font> </strong></font><br>
<font color="#0066ff"></font></div>
<div align="left"><strong><font color="#0033ff">SYED JAHANZAIB</font></strong></div>
<strong></strong>
<div align="left"> <br>
<b><font color="#0c0c0c">Web : </font><font color="#0c0c0c"><a href="http://aacable.wordpress.com" target="_blank">http://aacable.wordpress.com</a></font><font color="#0c0c0c"><br>
</font><font color="#0c0c0c">LinkedIn: </font><font color="#0c0c0c"><a href="http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407" target="_blank">http://pk.linkedin.com/pub/syed-jahanzaib/24/3b/407</a></font></b></div>
<div align="center"><a href="http:///" target="_blank"><b style="font-family:"times new roman","new york",times,serif; color:black; font-size:12pt"><span dir="rtl" lang="AR-SA" style="color:green; font-size:20pt"></span></b></a><font size="2" face="Verdana"></font></div>
</div>
</div>
</div>
______________________________________________________________________________<br>
Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" rel="noreferrer" target="_blank">
https://lists.balabit.hu/mailman/listinfo/syslog-ng</a><br>
Documentation: <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" rel="noreferrer" target="_blank">
http://www.balabit.com/support/documentation/?product=syslog-ng</a><br>
FAQ: <a href="http://www.balabit.com/wiki/syslog-ng-faq" rel="noreferrer" target="_blank">
http://www.balabit.com/wiki/syslog-ng-faq</a><br>
<br>
</blockquote>
</div>
</div>
</body>
</html>