<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<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);">
<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 !important">************************</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 !important">************************</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 !important">************************</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 !important">************************</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 !important">************************</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 !important">
<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="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>
<strong></strong>
<div align="left"> <br>
<b><font color="#0c0c0c" style="">Web : </font><font color="#0c0c0c" style=""><a href="http://aacable.wordpress.com" target="_blank">http://aacable.wordpress.com</a></font><font color="#0c0c0c" style=""><br>
</font><font color="#0c0c0c" style="">LinkedIn: </font><font color="#0c0c0c" style=""><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 face="Verdana" size="2"></font></div>
</div>
</div>
</body>
</html>