Re: Why does a group_concat on a join change aggregate values?

Should have showed the whole thing. Take a look here (click image to see full output): http://www.pintumbler.org/tmp On Thu, May 7, 2015 at 4:11 PM, shawn l.green shawn.l.gr...@oracle.com wrote: Hi Paul, On 5/7/2015 10:17 AM, Paul Halliday wrote: Fighting a bit with this one... If I do

Why does a group_concat on a join change aggregate values?

. What is happening behind the scenes? Thanks! -- Paul Halliday http://www.pintumbler.org/

Help with REGEXP

I am trying to pick out a range of IP addresses using REGEXP but failing miserably :) The pattern I want to match is: 10.%.224-239.%.% The regex I have looks like this: AND INET_NTOA(src_ip) REGEXP '\d{1,3}\\.\d{1,3}\.(22[4-9]|23[0-9])\\.\d{1,3}' but, go fish. Thoughts? Thanks! -- Paul

Re: Help with REGEXP

[.]\d{1,3}[.](224|225|226|227|228|229|23\d))[.]\d{1.3} On Thu, Mar 19, 2015 at 9:39 AM, Paul Halliday paul.halli...@gmail.com wrote: I am trying to pick out a range of IP addresses using REGEXP but failing miserably :) The pattern I want to match is: 10.%.224-239.%.% The regex I have looks

InnoDB error 5

error 105. 2013-11-21 08:47:26 802808c00 InnoDB: Cannot continue operation. I followed that link but it doesn't tell me anything outside of what is above. Can I fix this? Thanks. -- Paul Halliday http://www.pintumbler.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com

Re: InnoDB error 5

at 9:46 AM, Manuel Arostegui man...@tuenti.com wrote: 2013/11/21 Reindl Harald h.rei...@thelounge.net Am 21.11.2013 13:51, schrieb Paul Halliday: Had a system crash this morning and I can't seem to get mysql back up and running. This is the error: InnoDB: Progress in percent: 1 2 3 4 5

RE: How do I select all rows of table that have some rows in another table (AND, not OR)

I am so, so glad that someone finally said what I think each time I see a message from you Mr. James.  Original message From: Rick James rja...@yahoo-inc.com Date: 06-12-2013 8:45 PM (GMT-04:00) To: Daevid Vincent dae...@daevid.com,mysql@lists.mysql.com Subject: RE: How do

Re: query help

substring work? SELECT SUBSTRING(recid, 13, column size) AS numbers FROM table ORDER BY numbers DESC -- Paul Halliday http://www.pintumbler.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: Group expansion as part of the result

On Tue, Mar 27, 2012 at 3:43 PM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Mar 27), Paul Halliday said: Say I have: SELECT COUNT(name), name, COUNT(DISTINCT(status)) FROM table GROUP BY name and it returns: 20 paul 5 19 john 2 75 mark 3 is there a way to return

Delete from another table on update.

Is it possible to wrap a DELETE statement in an ON DUPLICATE KEY UPDATE? Something like: ON DUPLICATE KEY UPDATE host=b1 (DELETE FROM another_table WHERE host=b1) ? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com

Inconsistent query result.

' is not shown, on another system (linux 5.1.54), 'US' appears in the results. Is there a better way to write this? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub

Re: Inconsistent query result.

On Tue, Oct 11, 2011 at 10:37 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 10/11/2011 8:11 AM, Paul Halliday wrote: I have the following query: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc

Substring confusion.

| +---+-+ Why is this? Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Design question.

|list9|list20 and then just breaking it out in the code. The entire table would of course need to be scanned each day to check whether or not an address had been taken off a list. (efficiency?) Any comments/suggestions would be greatly appreciated. Thanks. -- Paul Halliday http://www.pintumbler.org

Help with query.

curious if I can pull it off with a single query. Thanks! -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Lowest non-zero number

On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodge m...@good-stuff.co.uk wrote: Given a table containing a range of INT values, is there any easy way to select from it the lowest non-zero number? SELECT number FROM table WHERE number 0 ORDER BY number ASC LIMIT 1; ? -- Paul Halliday Ideation

Re: Missing results.

Presumably those records were absorbed into your 'group by' clause, since there was an entry, from a later time, which had the same values for all the group by fields. -- Simcha Younger sim...@syounger.com Geez, how obvious. I was thinking on a completely different plane. I feel pretty

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

). -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? On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard travis_...@hotmail.com wrote: You

Can this be done with a single query?

) NOT LIKE '192.168.%.%'; but, within that somewhere also check to see if src_ip exists in mappings. If it does, do not return it. Is this possible? Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org

Re: Can this be done with a single query?

On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers joh...@pixelated.netwrote: 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

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

duplicating the ip column but I have a nagging feeling that that probably isn't necessary. Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org

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

On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard travis_...@hotmail.com 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,

Is conversion required?

to a partition (i386) and reinstalled. Can I just copy this back or does some magic need to happen first? Thanks! -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org

Re: Join on a where clause.

On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi everybody! Neil Aggarwal wrote: Paul: SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' AND

Join on a where clause.

I have 2 tables: 1) Event Data 2) Mappings The query should return something like this: Hits IP Country Code 20213.136.52.29 SE I am trying this: SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip), mappings.cc FROM event, mappings WHERE

Table advice.

I have a database that I am (will) be using to track URL's. The table structure looks like this: CREATE TABLE event ( eid INT UNSIGNED NOT NULL AUTO_INCREMENT, timestamp INT(10) UNSIGNED NOT NULL DEFAULT 0, ipINT(10) UNSIGNED NOT NULL DEFAULT 0, fqdn

open_files_limit problem.

I am trying to change this value and it doesn't seem to work. Looking at the processes I have: mysql 21752 0.0 0.1 1652 1092 p3 I 3:50PM 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql

Re: Strange query.

2 - 0 3 - 6 Is this possible? On 10 Jan 2007 19:55:09 -, Felix Geerinckx [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Paul Halliday) wrote in news:[EMAIL PROTECTED]: I am trying to deal with empty values so that I can graph data over a 24 hour period without gaps. Have a look here: http

Strange query.

Hi, I am trying to deal with empty values so that I can graph data over a 24 hour period without gaps. I created a table called HOURS which simply has 0-23 and I am trying to do a join on this to produce the desired results. I think that the DATE_FORMAT in the query is screwing things up. The

Re: Strange query.

GROUP BY hour - Original Message - From: Paul Halliday [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 10, 2007 8:39 AM Subject: Strange query. Hi, I am trying to deal with empty values so that I can graph data over a 24 hour period without gaps. I created a table

Re: If exists query.

On 4/23/06, John Hicks [EMAIL PROTECTED] wrote: Paul Halliday wrote: I am doing queries that produce a table that looks something like this: Count | IP Address| First Seen| Last Seen | Days 5000 10.0.0.1 2005-12-102006-04-15 50* 6500 10.0.0.2

Re: If exists query.

(*) between 2005-12-10 and 2006-04-14 the count was distributed within 50 distinct days. **This time the count is still high and it occured within 3 days between 2006-04-01 and 2006-04-06. I just cant figure out how to come up with the days part :). On 4/23/06, John Hicks [EMAIL PROTECTED] wrote: Paul

If exists query.

I am trying to formulate a query that will increment if a condition is true. For example, if I do a select (just let me know if there was data on this day, if so increment count by 1 and check the next day) where timestamp between jan and feb. Thanks. -- MySQL General Mailing List For list

Re: Can't search words of three letters?

Why not try regex? http://dev.mysql.com/doc/refman/4.1/en/regexp.html On 4/12/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi to all, I have to build a search of products on web catalog site. It has to search a whole words to avoid when somebody search for a pin and get *pin*k shirt. I

Insuring select returns the last record for a given day.

Hi, I have a table that looks somthing like this: ID timestampcampusIDS ePOinbound outbound statinfo 2289411143430287MA0 0424526713 284590944 0 NULL 2289401143430002ST 2 0

Merge tables.

Hi, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about

Re: Merge tables.

On 3/14/06, Martijn Tonies [EMAIL PROTECTED] wrote: Hello Paul, I suggest you reply to the mailinglist :-) ... The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or

problem with excluded days in date range query.

I have the following query: select count(*) as cnt, date(timestamp) as day from table where date_sub(curdate(),interval 14 day) = timestamp group by day; If there are no data for a particular day, it is not included. Is there a way to include all days even if the result is 0? I saw an example

Conditional insert or update.

, I would also like to roll the values too. For example if the MAC changes again, put mac_change into mac_current... Thanks. -- Paul Halliday Diplomacy is the art of saying Nice doggie! till you can find a rock. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql

Re: Conditional insert or update.

. Paul Halliday [EMAIL PROTECTED] wrote: Hi,=20 I have a table that looks something like this: ip | mac_current | mac_change now if I have an entry that looks like this 10.0.0.1 | 11:11:11:11:11:11:11 | NULL If the next time the script runs and the mac has

Solution to slow queries (Resolved, kinda)

populates the db was running at the same time too. Is there a way to force the table drop? (Without adding checks to the shell script) Thanks. _ Paul Halliday http://dp.penix.org Diplomacy is the art of saying Nice doggie! till you can find a rock. -- MySQL General Mailing

Solution to slow queries

(this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? Any thoughts, or a pointer in the right direction would be greatly appreciated. Thanks. -- _ Paul Halliday http://dp.penix.org Diplomacy is the art

Re: Solution to slow queries

On 5/10/05, Roger Baklund [EMAIL PROTECTED] wrote: Paul Halliday wrote: srcaddr VARCHAR(15), dstaddr VARCHAR(15), Are these ip-adresses? If they are, consider using UNSIGNED INT columns and the INET_NTOA() and INET_ATON() funtions. It will save you a lot of space, thus