[prev in list] [next in list] [prev in thread] [next in thread]
List: syslog-ng
Subject: Re: [syslog-ng] Syslog-NG mysql log with auto increment not working
From: JAHANZAIB SYED <aacable () hotmail ! com>
Date: 2020-01-08 6:02:13
Message-ID: PU1PR06MB2216B1CE9D6EB915C4628DE3A63E0 () PU1PR06MB2216 ! apcprd06 ! prod ! outlook ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<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>
[Attachment #4 (unknown)]
______________________________________________________________________________
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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic