Re: One inst has 39 columns- the other 40
- Original Message - From: Charles Brown cbr...@bmi.com I have two mysql instances in my mysql cluster. I noticed that their MysSQL.user tables do not have the same number of columns. Their USER table attributes are not the same. One instance has 39 columns while the other has 42 columns. Can someone please help me here. Could it be because I'm running mysql cluster? Can someone help me. I take it you are indeed talking about NDB cluster ? The MySQL instances in your NDB cluster are really just SQL-to-NDB translator, so running NDB has nothing to do with the layout of your mysql.* table structure. The most likely cause is simply different versions of MySQL on the nodes, followed by not having run the mysql_upgrade script after you upgraded the software. It's not a /major/ problem, really, but it is good to have the correct layout so you can assing all the available privileges. See man mysql_upgrade for more information. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Group_Concat help...
Hello! I am trying to do something, but I can't seem to figure out how... My query is as follows: SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips', COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; This query works, it gives me results like: user_id login_ipsconnections --- --- - 1 192.168.0.200,192.168.0.201 5 But what I am looking to add, is the number of connections per IP, so it would look kinda something like this: user_id login_ipsconnections --- --- - 1 192.168.0.200 (1),192.168.0.201 (3) 5 Is this possible to do with just 1 query? If so, how would I go about doing it?? And the table schema: CREATE TABLE `mysql_test` ( `id` int(11) unsigned NOT NULL auto_increment, `user_id` int(11) unsigned NOT NULL, `login_datetime` timestamp NOT NULL default CURRENT_TIMESTAMP, `login_ip` varchar(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; here is the data in my table: id user_id login_datetime login_ip -- --- --- --- 11 2012-03-19 11:57:38 192.168.0.200 21 2012-03-19 11:57:40 192.168.0.201 31 2012-03-19 11:57:42 192.168.0.200 41 2012-03-19 11:57:43 192.168.0.200 51 2012-03-19 11:57:45 192.168.0.201 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Group_Concat help...
Try this SELECT `user_id`, `login_ip`, COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id`, `login_ip` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; On Mar 19, 2012, at 12:06 PM, Steven Staples wrote: SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips', COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Group_Concat help...
-Original Message- From: Mihail Manolov [mailto:mihail.mano...@liquidation.com] Sent: March 19, 2012 12:44 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Group_Concat help... Try this SELECT `user_id`, `login_ip`, COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id`, `login_ip` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; On Mar 19, 2012, at 12:06 PM, Steven Staples wrote: SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips', COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; Thanks for the reply Mihail. I was initially doing it that way, but if you look at the example of what I wanted as a reply, it doesn't work. I was hoping for a result that I could just plop into the PHP code, and I wouldn't have to manipulate it at all, but so far, it looks like I am going to have to do that... unless anyone else here has another idea... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Group_Concat help...
; 2012/03/19 12:06 -0400, Steven Staples SELECT `user_id`, GROUP_CONCAT(DISTINCT `login_ip`) AS 'login_ips', COUNT(`id`) AS 'connections' FROM `mysql_test` WHERE `login_datetime` BETWEEN '2012-03-19 00:00:00' AND '2012-03-19 23:59:59' GROUP BY `user_id` HAVING COUNT(`id`) 2 ORDER BY COUNT(`id`) DESC LIMIT 0, 15; user_id login_ipsconnections --- --- - 1 192.168.0.200 (1),192.168.0.201 (3) 5 Is this possible to do with just 1 query? If so, how would I go about doing it?? Yes, but with two levels: the inner level (a virtual table) groups by both user_id and login_ip, with COUNT; the outer as above, except from the inner level, not right from table mysql_test, and with not COUNT but SUM of the inner COUNTs. By the way, I would do this: WHERE DATE(`login_datetime`) = '2012-03-19' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
One inst has 39 columns- the other 40
I have two mysql instances in my mysql cluster. I noticed that their MysSQL.user tables do not have the same number of columns. Their USER table attributes are not the same. One instance has 39 columns while the other has 42 columns. Can someone please help me here. Could it be because I'm running mysql cluster? Can someone help me. Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
