RE: Can this be done with a single query?

2010-10-12 Thread Daevid Vincent
Absolutely was just going to suggest this Travis. Another option and this is untested, but is to use HAVING and an alias. Something to this effect... SELECT INET_NTOA(e.src_ip) AS source_ip WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' HAVING source_ip BETWEEN '10.0.0.

Re: InnoDB Crash

2010-10-12 Thread Willy Mularto
I got the solution. I stop the ib* files in MySQL datadir and start the server. Now everything is back to normal. Thanks. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ On Oct 12, 2010, at 11:00 PM, Suresh Kuna wrote: > Hey Willy - Install the new binaries and start mysq

RE: Can this query be done w/o adding another column?

2010-10-12 Thread Travis Ard
Sorry, try changing the column mappings.ip to use the table aliases (m.ip and m2.ip). -Travis From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 11:37 AM To: Travis Ard Cc: mysql@lists.mysql.com Subject: Re: Can this query be done w/o adding another column

Re: MySQL sock file is missing: server doesn't start

2010-10-12 Thread spacemarc
2010/10/11 Sabika M : > are the permissions on that directory correct? The .sock file is created at > start up by mysql mysqld doesn't start because are missing /var/run/mysqld dir and /var/run/mysqld/mysqld.sock file. Dir and file are created on mysqld boot, i presume. It's cyclic! 2010/10/12 par

Re: Can this query be done w/o adding another column?

2010-10-12 Thread Paul Halliday
On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard wrote: > You could join your mappings table twice, once on src_ip and again on > dst_ip: > > SELECT COUNT(signature) AS count, >MAX(timestamp) AS maxTime, > INET_NTOA(src_ip), >m.cc as src_cc, > INET_NTOA(dst_ip), > m2.

RE: Can this query be done w/o adding another column?

2010-10-12 Thread Travis Ard
You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_prot

RE: Can this be done with a single query?

2010-10-12 Thread Travis Ard
You may get better performance from your query, and be able to make better use of indexes if you use integer comparisons for your IP address expressions instead of converting to strings with pattern matching. You might consider something like the following: SELECT DISTINCT(e.src_ip) FROM event

Can this query be done w/o adding another column?

2010-10-12 Thread Paul Halliday
Geez, really taking advantage of the list today :). This one is a little more complicated, well, in my head anyway. Same tables as before, event and mappings. Mappings is just IP to Country info. I want to be able to join both a src and dst but the problem is the mappings table just has one ip col

Backing up the InnoDB tables

2010-10-12 Thread Tompkins Neil
Hi On a shared MySQL server with access just to my own database, what is the recommend backup methods and strategies for the InnoDB tables ? Cheers Neil

Re: Can this be done with a single query?

2010-10-12 Thread Paul Halliday
On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers wrote: > I would try: > > SELECT DISTINCT(e.src_ip) > FROM event AS e > LEFT JOIN mappings AS m ON e.src_ip=m.src_ip > WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' > AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%' > AND INE

Re: InnoDB Crash

2010-10-12 Thread Suresh Kuna
Hey Willy - Install the new binaries and start mysql with new binary as basedir and see whether innodb has enabled or not. Check the error log why the innodb is getting disabled, make a copy of it here too. On Tue, Oct 12, 2010 at 2:57 PM, Willy Mularto wrote: > Hi List, > Last night accidentall

Re: Can this be done with a single query?

2010-10-12 Thread Johnny Withers
I would try: SELECT DISTINCT(e.src_ip) FROM event AS e LEFT JOIN mappings AS m ON e.src_ip=m.src_ip WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%' AND INET_NTOA(e.src_ip) NOT LIKE '172.16.%.%' AND INET_NTOA(e.src_ip) NOT LIKE '

Re: Can this be done with a single query?

2010-10-12 Thread Nathan Sullivan
Paul, I think you could accomplish this by adding a subquery to your where clause, like: AND NOT EXISTS (SELECT * FROM mappings m where m.src_ip=src_ip) Hope that helps. Nathan On Tue, Oct 12, 2010 at 03:19:36AM -0700, Paul Halliday wrote: > I have 2 tables: events and mappings. > > what

Can this be done with a single query?

2010-10-12 Thread Paul Halliday
I have 2 tables: events and mappings. what I want to do is something like: SELECT DISTINCT(src_ip) FROM event WHERE timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' AND INET_NTOA(src_ip) NOT LIKE '10.%.%.%' AND INET_NTOA(src_ip) NOT LIKE '172.16.%.%' AND INET_NTOA(src_ip) NOT LIK

InnoDB Crash

2010-10-12 Thread Willy Mularto
Hi List, Last night accidentally one of my InnoDB table crash. And cause client can not connect to MySQL, it always said cannot connect to socket, even the daemon is launched. I tried to set innodb_force_recovery from 0 to 6 and only number 3 bring back the connection. After that I dump the data