Anonymous_User Absent Member.
Absent Member.
556 views

JDBC EventSource not fetching data frm Mysql general_log tbl


Greetings!! I have installed Sentinel 7 and initialized event source
for mysql to get data from general_log table. JDBC connector is unable
to read the logging table general_log. When I analyzed, I found that
sentinel's JDBC event source trying to run to get log from table with
following query (as mentioned in general_log):
+---------------------+---------------------------------------+-----------+-----------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time | user_host |
thread_id | server_id | command_type | argument


|
+---------------------+---------------------------------------+-----------+-----------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2012-12-27 17:09:22 | root[root] @ [192.168.1.109] | 1 |
0 | Query | SELECT event_time , user_host host , thread_id tID
, server_id sID , command_type commandType, argument argument FROM
general_log WHERE cast(event_time as datetime) >= 'null' order by
event_time limit 200 | | 2012-12-27 17:18:21 | sentinel[sentinel] @
[192.168.1.109] | 5 | 0 | Query | SELECT
event_time , user_host host , thread_id tID , server_id sID ,
command_type commandType, argument argument FROM general_log WHERE
cast(event_time as datetime) >= 'null' order by event_time limit 200 |
But when I run this command from console, I get Empty Set with 1 warning
as follows: mysql> SELECT event_time , user_host host , thread_id tID ,
server_id sID , command_type commandType, argument argument FROM
general_log WHERE cast(event_time as datetime) >= 'null' order by
event_time limit 200 ; Empty set, 1 warning (0.25 sec) When I looked at
warning, it shows: mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+ | Level
| Code | Message |
+---------+------+--------------------------------------------+ |
Warning | 1292 | Truncated incorrect datetime value: 'null' |
+---------+------+--------------------------------------------+ 1 row in
set (0.00 sec) I have tried two users, root & sentinel, but getting no
event into the sentinel. I suspect issue in the WHERE clause in
statement used by JDBC connector. Please suggest the solution.


--
rajeshemailto
------------------------------------------------------------------------
rajeshemailto's Profile: https://forums.netiq.com/member.php?userid=196
View this thread: https://forums.netiq.com/showthread.php?t=46449

0 Likes
5 Replies
Anonymous_User Absent Member.
Absent Member.

Re: JDBC EventSource not fetching data frm Mysql general_log tbl

Using the standard MySQL tools does the query work if you remove the
single-quotes from around the string 'null'? If so you just need to
modify the (I think) collector so that the SQL itself does not include
those quotes.

Out of curiosity, which collector is this, and for which version of which
application?

Good luck.
0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: JDBC EventSource not fetching data frm Mysql general_log tbl


AB, Finally, issue got resolved. The issue was with "sqlquery.base"
query. Earlier query was SELECT event_time , user_host host , thread_id
tID , server_id sID , command_type commandType, argument argument FROM
general_log WHERE cast(event_time as datetime) >= '%s' order by
event_time limit %d Which I changed to SELECT event_time , user_host
host , thread_id tID , server_id sID , command_type commandType,
argument argument FROM general_log WHERE cast(event_time as datetime) >=
'1970-01-01' order by event_time desc LIMIT %d; We are trying to
connect to in-house app which has mysql database. DB version is "Server
version: 5.5.28-enterprise-commercial-advanced-log MySQL Enterprise
Server - Advanced Edition (Commercial)" where as connector is "Database
(JDBC) 2011.1r2".


--
rajeshemailto
------------------------------------------------------------------------
rajeshemailto's Profile: https://forums.netiq.com/member.php?userid=196
View this thread: https://forums.netiq.com/showthread.php?t=46449

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: JDBC EventSource not fetching data frm Mysql general_log tbl

> Which I changed to SELECT event_time , user_host
> host , thread_id tID , server_id sID , command_type commandType,
> argument argument FROM general_log WHERE cast(event_time as datetime) >=
> '1970-01-01' order by event_time desc LIMIT %d;


Glad to hear it's working. Just to be sure on this logic, you're sorting
by some kind of time field descending, so the last events will come first,
right? That's typically not desirable in an event stream of data since it
means events are out of order when viewed by others. Also, I am guessing
(purely) that this could do bad things to Sentinel's ability to have some
kind of offset, so either you'll get all events over and over (assuming
you never clear them out) or if you clear them out manually somehow you'll
still get the events in the wrong order.

I believe the '%s' is in there so that you can send in an offset and then
the database server will only send back new events, in the order they
happened preferably, so that you are not getting and re-processing all
events indefinitely, a task which eventually becomes unmanageable. More
information on this may be available in the PlugIn SDK forum under the
developers section of the forums site.

Good luck.
0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: JDBC EventSource not fetching data frm Mysql general_log tbl


AB,Happy New Year to you & your family!!Thanks for pointing-out possible
issue by removing %s and %d and adding desc in query. I relooked at
setup and run some test and found that not only order of event is logged
incorrectly but also loss of event happening. I deliberately increased
the poll time for 5mins and used 250+ select statement on database
within 5 mins. On next polling, I found that only 200 events gets logged
and lost 50+ event.This time, to fix the issue, rather than changing
query, I changes the javascript "sqlquery.js". In the function, I
modified script for setting offset as
follows: if(this.offset==null) { newQuery=newQuery.replace(/%s/,String("1980-0-0
00:00:00")); } else { newQuery=newQuery.replace(/%s/,this.offset); }Also,
I restored the original version of sqlquery.base. Now, when I performed
the test, I saw that JDBC connector\collector is setting offset
correctly and no loss of event happening this time.Thanks for your
valuable input to really fix the issue.


--
rajeshemailto
------------------------------------------------------------------------
rajeshemailto's Profile: https://forums.netiq.com/member.php?userid=196
View this thread: https://forums.netiq.com/showthread.php?t=46449

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: JDBC EventSource not fetching data frm Mysql general_log tbl

In the function, I
> modified script for setting offset as
> follows: if(this.offset==null) { newQuery=newQuery.replace(/%s/,String("1980-0-0
> 00:00:00")); } else { newQuery=newQuery.replace(/%s/,this.offset); }Also,
> I restored the original version of sqlquery.base. Now, when I performed
> the test, I saw that JDBC connector\collector is setting offset
> correctly and no loss of event happening this time.Thanks for your
> valuable input to really fix the issue.


That's great to hear; thank-you for posting back your results as they will
certainly help others with this type of issue in the future.

Good luck.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.