Query help
Hi I've the following basic table login_id email_address ip_address I want to extract all records from this table in which a user has used the same IP address but different email address to login ? Thanks, Neil
Re: Query help
Hi Neil, select login_id, ip_address from basic_table group by login_id,ip_address having count(login_id,ip_address)1 this should work in case you want to see also the list of emails add: group_concat(email_address,',') as list_of_used_emails to the select fields. Claudio 2011/3/2 Tompkins Neil neil.tompk...@googlemail.com Hi I've the following basic table login_id email_address ip_address I want to extract all records from this table in which a user has used the same IP address but different email address to login ? Thanks, Neil -- Claudio
Two Identical Values on Primary Key Column
Hi all, I have just experienced a strange problem with mysql production database. The table faqsessions have a primary key on column `Code` and the above select return 2 rows! mysql mysql mysql show create table faqsessions; +-+--+ | Table | Create Table | +-+--+ | faqsessions | CREATE TABLE `faqsessions` ( `Code` int(11) unsigned NOT NULL auto_increment, `sid` int(11) NOT NULL, `ip` text NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=1567573 DEFAULT CHARSET=latin1 | +-+--+ 1 row in set (0.00 sec) mysql mysql mysql select * from faqsessions where time in (1268650281, 1268650260); +++--++ | Code | sid | ip | time | +++--++ | 611179 | 312713 | 66.249.68.87 | 1268650281 | | 611179 | 312713 | 66.249.68.89 | 1268650260 | +++--++ 2 rows in set (1.49 sec) mysql mysql mysql select * from faqsessions where Code = 611179; +++--++ | Code | sid | ip | time | +++--++ | 611179 | 312713 | 66.249.68.87 | 1268650281 | +++--++ 1 row in set (0.00 sec) Any idea? Rodrigo Ferreira CMDBA, CMDEV
Re: Two Identical Values on Primary Key Column
Is it possible that someone did an alter table disable keys at some point, maybe for a bulk load, and forgot to re-enable them ? - Original Message - From: Rodrigo Ferreira rodrigof_si...@yahoo.com To: mysql@lists.mysql.com Sent: Wednesday, 2 March, 2011 3:04:31 PM Subject: Two Identical Values on Primary Key Column Hi all, I have just experienced a strange problem with mysql production database. The table faqsessions have a primary key on column `Code` and the above select return 2 rows! mysql mysql mysql show create table faqsessions; +-+--+ +-+--+ `Code` int(11) unsigned NOT NULL auto_increment, `sid` int(11) NOT NULL, `ip` text NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=1567573 DEFAULT CHARSET=latin1 | +-+--+ 1 row in set (0.00 sec) mysql mysql mysql select * from faqsessions where time in (1268650281, 1268650260); +++--++ +++--++ +++--++ 2 rows in set (1.49 sec) mysql mysql mysql select * from faqsessions where Code = 611179; +++--++ +++--++ +++--++ 1 row in set (0.00 sec) Any idea? Rodrigo Ferreira CMDBA, CMDEV -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: Query help
-Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, March 02, 2011 6:00 AM To: [MySQL] Subject: Query help Hi I've the following basic table login_id email_address ip_address I want to extract all records from this table in which a user has used the same IP address but different email address to login ? Thanks, Neil [JS] I haven't looked at my code lately, but I'm pretty sure that SELECT ip_address FROM basic_table GROUP BY ip_address HAVING COUNT(*) 1; is what you want. You don't need to group on login_id. And, as Claudio said, SELECT ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids will give you the IP addresses as well. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query help
Thanks for the response. This is what I was after. Although, I am looking to find out the email addresses used to login from the same IP ? On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, March 02, 2011 6:00 AM To: [MySQL] Subject: Query help Hi I've the following basic table login_id email_address ip_address I want to extract all records from this table in which a user has used the same IP address but different email address to login ? Thanks, Neil [JS] I haven't looked at my code lately, but I'm pretty sure that SELECT ip_address FROM basic_table GROUP BY ip_address HAVING COUNT(*) 1; is what you want. You don't need to group on login_id. And, as Claudio said, SELECT ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids will give you the IP addresses as well. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com
Re: Two Identical Values on Primary Key Column
Hi Johan,
It seems InnoDB doesn't support disable/enable keys.
mysql alter table faqsessions enable keys;
Query OK, 0 rows affected, 1 warning (0.14 sec)
mysql show warnings;
+---+--+-+
| Level | Code |
Message |
+---+--+-+
| Note | 1031 | Table storage engine for 'faqsessions' doesn't have this
option |
+---+--+-+
1 row in set (0.00 sec)
mysql alter table faqsessions disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql show warnings;
+---+--+-+
| Level | Code |
Message |
+---+--+-+
| Note | 1031 | Table storage engine for 'faqsessions' doesn't have this
option |
+---+--+-+
1 row in set (0.00 sec)
mysql
--- On Wed, 3/2/11, Johan De Meersman vegiv...@tuxera.be wrote:
From: Johan De Meersman vegiv...@tuxera.be
Subject: Re: Two Identical Values on Primary Key Column
To: Rodrigo Ferreira rodrigof_si...@yahoo.com
Cc: mysql@lists.mysql.com
Date: Wednesday, March 2, 2011, 11:21 AM
#yiv704254679 p {margin:0;}Is it possible that someone did an alter table
disable keys at some point, maybe for a bulk load, and forgot to re-enable them
?
From: Rodrigo Ferreira rodrigof_si...@yahoo.com
To: mysql@lists.mysql.com
Sent: Wednesday, 2 March, 2011 3:04:31 PM
Subject: Two Identical Values on Primary Key Column
Hi all,
I have just experienced a strange problem with mysql production database. The
table faqsessions have a primary key on column `Code` and the above select
return 2 rows!
mysql
mysql
mysql show create table faqsessions;
+-+---
---+
+-+--+
`Code` int(11) unsigned NOT NULL auto_increment,
`sid` int(11) NOT NULL,
`ip` text NOT NULL,
`time` int(11) NOT NULL,
PRIMARY KEY (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=1567573 DEFAULT CHARSET=latin1 |
+-+--+
1 row in set (0.00 sec)
mysql
mysqlg
t;
mysql select * from faqsessions where time in (1268650281, 1268650260);
+++--++
+++--++
+++--++
2 rows in set (1.49 sec)
mysql
mysql
mysql select * from faqsessions where Code = 611179;
+++--++
+++--++
+++--++
1 row in set (0.00 sec)
Any idea?
Rodrigo Ferreira
CMDBA, CMDEV
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
RE: Query help
If you want one row for each combination, you'll need either a temporary table or a sub-query. Try this: SELECT ip_address, login_id FROM basic_table JOIN (SELECT ip_address FROM basic_table GROUP BY ip_address HAVING COUNT(*) 1) AS x ON basic_table.ip_address = x.ip_address; Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, March 02, 2011 10:12 AM To: Jerry Schwartz Cc: [MySQL] Subject: Re: Query help Thanks for the response. This is what I was after. Although, I am looking to find out the email addresses used to login from the same IP ? On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, March 02, 2011 6:00 AM To: [MySQL] Subject: Query help Hi I've the following basic table login_id email_address ip_address I want to extract all records from this table in which a user has used the same IP address but different email address to login ? Thanks, Neil [JS] I haven't looked at my code lately, but I'm pretty sure that SELECT ip_address FROM basic_table GROUP BY ip_address HAVING COUNT(*) 1; is what you want. You don't need to group on login_id. And, as Claudio said, SELECT ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids will give you the IP addresses as well. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Get MySQL server IP address in SQL
Anyone knows how to get the server* IP address* thru SQL? -- Claudio *mysql select * from GLOBAL_variables where variable_name like 'hostname';* *+---++* *| VARIABLE_NAME | VARIABLE_VALUE |* *+---++* *| HOSTNAME | haarlemeer |* *+---++* *1 row in set (0.01 sec**)* *mysql select * from GLOBAL_variables where variable_name like '%ip%';* *+++* *| VARIABLE_NAME | VARIABLE_VALUE |* *+++* *| SSL_CIPHER ||* *| SLAVE_SKIP_ERRORS | OFF|* *| SKIP_SHOW_DATABASE | OFF|* *| SKIP_NAME_RESOLVE | OFF|* *| NAMED_PIPE | OFF|* *| SKIP_NETWORKING| OFF|* *| SQL_SLAVE_SKIP_COUNTER ||* *| SKIP_EXTERNAL_LOCKING | ON |* *+++* *8 rows in set (0.00 sec)*
Re: Get MySQL server IP address in SQL
Am 03.03.2011 00:31, schrieb Claudio Nanni: Anyone knows how to get the server* IP address* thru SQL? no, because it is nonsense and has nothing to do with a db-server if you connect via tcp you know the ip and even if not - what should give you mysqld if the host has more than one ip? signature.asc Description: OpenPGP digital signature
