LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 09-21-2010, 02:10 AM   #1
hattori.hanzo
Member
 
Registered: Aug 2006
Posts: 168

Rep: Reputation: 15
Adding DATE_SUB to a mysql query


I have the following mysql query which works great.

Code:
SELECT msg FROM `logs` WHERE ((`host` = 'aaa.bbb.ccc.ddd') OR (`host` = 'aaa.bbb.ccc.ddd')) AND `msg` REGEXP CONVERT( _utf8 'physical addresses' USING latin1 ) COLLATE latin1_swedish_ci
I need to refine this query so that I can query the last # hours using the `datetime` field in the database. I have researched the DATE_SUB function and can query for example the last 1 hour like this:

Code:
SELECT msg FROM `logs` WHERE (`datetime` >= DATE_SUB(NOW(), INTERVAL 1 HOUR))
How can I incorporate this into my original query. I have tried the following but there are no results:

Code:
SELECT datetime, msg FROM `logs` WHERE datetime >= DATE_SUB(NOW(), INTERVAL 1 HOUR)  AND ((`host` = 'aaa.bbb.ccc.ddd') OR (`host` = 'aaa.bbb.ccc.ddd')) AND `msg` REGEXP CONVERT( _utf8 'physical addresses' USING latin1 ) COLLATE latin1_swedish_ci
What am I missing?

Thanks & regards
 
Old 09-21-2010, 11:23 PM   #2
dkm999
Member
 
Registered: Nov 2006
Location: Seattle, WA
Distribution: Fedora
Posts: 407

Rep: Reputation: 35
I cannot see anything immediately wrong with your last query, but my approach to debugging MySQL statements is to take the one that doesn't work and remove one condition at a time until it does what it should, and then add the conditions back in, checking each time that I get the expected results. Maybe you've already done this, but if not, I recommend the technique.
 
Old 09-22-2010, 12:24 AM   #3
hattori.hanzo
Member
 
Registered: Aug 2006
Posts: 168

Original Poster
Rep: Reputation: 15
Thanks. I did try and remove each condition but once I add the AND statements, the results become null.

Last edited by hattori.hanzo; 09-22-2010 at 01:50 AM.
 
Old 09-22-2010, 01:03 AM   #4
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
Have you considered the possibility that there's actually
no records that match all conditions?

Also I'm not familiar with MySQLs whacky join syntax. Could
it be that you need to wrap the time-stamp and the host
statements in yet another set of parenthesis?
I assume that "FROM `logs' ... AND `msg`" represents a
join of some sort?

Cheers,
Tink

Last edited by Tinkster; 09-22-2010 at 01:05 AM.
 
Old 09-22-2010, 04:31 AM   #5
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
Tinkster, I do not see a join. Both datetime and msg come from the logs table.

I think that your first point is indeed a very valid one (and often overlooked).

At OP, can you post the results of the first query (e.g. limited to 10 rows)?
 
Old 09-22-2010, 01:23 PM   #6
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
Quote:
Originally Posted by Wim Sturkenboom View Post
Tinkster, I do not see a join. Both datetime and msg come from the logs table.

I think that your first point is indeed a very valid one (and often overlooked).

At OP, can you post the results of the first query (e.g. limited to 10 rows)?
As I said, I don't DO MySQL, but I'd like to respectfully disagree. :}
Code:
SELECT datetime, msg FROM `logs` WHERE datetime >= DATE_SUB(NOW(), INTERVAL 1 HOUR)  AND ((`host` = 'aaa.bbb.ccc.ddd') OR (`host` = 'aaa.bbb.ccc.ddd')) AND `msg` REGEXP CONVERT( _utf8 'physical addresses' USING latin1 ) COLLATE latin1_swedish_ci
If you look at a) the similar syntax of the RED and the GREEN
part, and consider that b) the GREEN part has no resemblence
to anything one could use as part of a WHERE clause (there's
not comparison, just some sort of function call) I think this
really is some whacky join syntax.



Cheers,
Tink
 
Old 09-22-2010, 10:16 PM   #7
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
Actually, MySQL isn't particularly different from any other RDBMS eg Oracle ie it uses normal SQL, but like each RDBMS it has std & 'extra' built-in fns avail.
http://dev.mysql.com/doc/refman/5.0/...mmary-ref.html
In the Green code, it's part of the WHERE clause alright; reading R->L he's CONVERTing a value, then REGEXing it, but he's forgotten to actually compare the result (ie cvt+rgex'd value) to another value.
In fact
Quote:
Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL
http://dev.mysql.com/doc/refman/5.0/...perator_regexp
 
Old 09-22-2010, 11:05 PM   #8
dkm999
Member
 
Registered: Nov 2006
Location: Seattle, WA
Distribution: Fedora
Posts: 407

Rep: Reputation: 35
Actually, a careful reading of the MySQL manual would show that this IS correct SQL syntax: the REGEXP operator takes two arguments, one before it to indicate the string, and one after to provide the regular expression. In this case, the regular expression is the output of the CONVERT function call. This is just one more example of why it is hard to read SQL: the distinction between operators and function calls amounts to whether there are parentheses or not, and parentheses are used for expression enclosure as well as function argument enclosure.

And we're still no closer to helping hattori.hanzo with his problem. I vote for the suggestion about checking whether NULL is actually the right answer to the query.
 
Old 09-23-2010, 02:54 AM   #9
hattori.hanzo
Member
 
Registered: Aug 2006
Posts: 168

Original Poster
Rep: Reputation: 15
Thanks all for the comments.

Here are the 10 rows of output as requested.

Quote:
"11555 09/23/2010 00:02:18 tEvtLgMgr 0 : Security [12] Session: IPSEC[user1]:437087 physical addresses: remote 132.39.12.18 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:02:18 tEvtLgMgr 0 : Security [12] Session: IPSEC[user1]:437087 physical addresses: remote 132.39.12.18 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:02:19 tEvtLgMgr 0 : Security [12] Session: IPSEC[user1]:437087 physical addresses: remote 132.39.12.18 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:03:50 tEvtLgMgr 0 : Security [12] Session: IPSEC[user2]:437089 physical addresses: remote 60.41.124.153 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:03:50 tEvtLgMgr 0 : Security [12] Session: IPSEC[user2]:437089 physical addresses: remote 60.41.124.153 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:03:50 tEvtLgMgr 0 : Security [12] Session: IPSEC[user2]:437089 physical addresses: remote 60.41.124.153 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:08:15 tEvtLgMgr 0 : Security [12] Session: IPSEC[user3]:437091 physical addresses: remote 58.19.16.30 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:08:15 tEvtLgMgr 0 : Security [12] Session: IPSEC[user3]:437091 physical addresses: remote 58.19.16.30 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:08:15 tEvtLgMgr 0 : Security [12] Session: IPSEC[user3]:437091 physical addresses: remote 58.19.16.30 local aaa.bbb.ccc.ddd"
"11555 09/23/2010 00:10:21 tEvtLgMgr 0 : Security [12] Session: IPSEC[user4]:437093 physical addresses: remote 218.22.15.183 local aaa.bbb.ccc.ddd"
I need time to think about what chrism01 is suggesting. Newbie here.. :-)

Last edited by hattori.hanzo; 09-23-2010 at 02:55 AM.
 
Old 09-23-2010, 10:16 PM   #10
hattori.hanzo
Member
 
Registered: Aug 2006
Posts: 168

Original Poster
Rep: Reputation: 15
Finally got this working.

Quote:
SELECT SQL_CALC_FOUND_ROWS `msg` FROM `logs` WHERE `host` IN ('aaa.bbb.ccc.ddd','aaa.bbb.ccc.ddd') AND `datetime` >= DATE_SUB(NOW(), INTERVAL 1 HOUR) AND msg LIKE '%physical addresses%'
Hope this helps someone.

Last edited by hattori.hanzo; 09-24-2010 at 01:39 AM.
 
Old 09-24-2010, 01:43 AM   #11
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
One way to debug this is to create a small static table with a few rows in it ie one that is not being updated all the time eg
Code:
create table mytest
as select *
from real_table
limit 10
Then you can easily check the WHERE clause of your code one part at a time ie just against the timestamps, then against the host ids.
In your current code both have to match at the same time, and that may not be true at the time you test on the real table.
BTW, unless host or logs are MYSQL keywords, you don't need quote marks around those names.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
interesting MySQL query/view query :s mjh Programming 3 03-25-2008 08:30 AM
mysql use output of one query in another query secretlydead Programming 2 11-19-2007 02:25 AM
help with mysql query: return nth rows in query hawarden Programming 2 07-31-2006 07:36 PM
adding an entry to /etc/fstab file and another query Tinku Linux - General 3 08-11-2004 09:35 PM
Mysql Error: Lost Connection to Mysql during query ramnath Programming 5 11-18-2003 01:27 PM


All times are GMT -5. The time now is 10:41 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration