Help with subqueries...
vmware reviewit # mysql --version mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.1 Given two tables: CREATE TABLE `logs` ( `id` bigint(20) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `body` text, PRIMARY KEY (`id`), ) ENGINE=InnoDB; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(30) NOT NULL, `firstname` varchar(255) NOT NULL, `lastname` varchar(255) NOT NULL, `login_date` datetime default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), ) ENGINE=InnoDB; I want a query that will show me the username and the single most current log body and date for every user. I think I need to use a sub query, but I can't even get the very basic example one to work... http://dev.mysql.com/doc/refman/5.0/en/subqueries.html mysql SELECT * FROM logs WHERE user_id = (SELECT id FROM users); ERROR 1242 (21000): Subquery returns more than 1 row mysql SELECT * FROM logs WHERE users.id = (SELECT id FROM users); ERROR 1054 (42S22): Unknown column 'users.id' in 'where clause' mysql SELECT logs.*, users.username - FROM logs JOIN users ON users.id = logs.id - WHERE user_id = (SELECT id FROM users ORDER BY username); ERROR 1242 (21000): Subquery returns more than 1 row Duh. Isn't that the point? Of course I want the subquery to return more than one row?! I don't get it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with subqueries...
On 4/28/06, Daevid Vincent [EMAIL PROTECTED] wrote: vmware reviewit # mysql --version mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.1 Given two tables: CREATE TABLE `logs` ( `id` bigint(20) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `body` text, PRIMARY KEY (`id`), ) ENGINE=InnoDB; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(30) NOT NULL, `firstname` varchar(255) NOT NULL, `lastname` varchar(255) NOT NULL, `login_date` datetime default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), ) ENGINE=InnoDB; I want a query that will show me the username and the single most current log body and date for every user. I think I need to use a sub query, but I can't even get the very basic example one to work... http://dev.mysql.com/doc/refman/5.0/en/subqueries.html mysql SELECT * FROM logs WHERE user_id = (SELECT id FROM users); ERROR 1242 (21000): Subquery returns more than 1 row try SELECT * FROM logs WHERE user_id in (SELECT id FROM users); mysql SELECT * FROM logs WHERE users.id = (SELECT id FROM users); ERROR 1054 (42S22): Unknown column 'users.id' in 'where clause' the table logs has only the column user_id . mysql SELECT logs.*, users.username - FROM logs JOIN users ON users.id = logs.id - WHERE user_id = (SELECT id FROM users ORDER BY username); ERROR 1242 (21000): Subquery returns more than 1 row again replace = by in. Hope this helps. Thanx Alex
RE: Help with subqueries... MAX() and GROUP BY
-Original Message- From: Alex Arul [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 11:28 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Help with subqueries... On 4/28/06, Daevid Vincent [EMAIL PROTECTED] wrote: vmware reviewit # mysql --version mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.1 Given two tables: CREATE TABLE `logs` ( `id` bigint(20) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned default '0', `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `body` text, PRIMARY KEY (`id`), ) ENGINE=InnoDB; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(30) NOT NULL, `firstname` varchar(255) NOT NULL, `lastname` varchar(255) NOT NULL, `login_date` datetime default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), ) ENGINE=InnoDB; I want a query that will show me the username and the single most current log body and date for every user. I think I need to use a sub query, but I can't even get the very basic example one to work... http://dev.mysql.com/doc/refman/5.0/en/subqueries.html mysql SELECT * FROM logs WHERE user_id = (SELECT id FROM users); ERROR 1242 (21000): Subquery returns more than 1 row try SELECT * FROM logs WHERE user_id in (SELECT id FROM users); mysql SELECT logs.*, users.username - FROM logs JOIN users ON users.id = logs.id - WHERE user_id = (SELECT id FROM users ORDER BY username); ERROR 1242 (21000): Subquery returns more than 1 row again replace = by in. Hope this helps. Thanks Alex, that got me started. I don't understand why I had to use IN when the example uses = but at least it kinda works... The problem is now that I can't get the right data. mysql select max(created_on), user_id, id from logs group by user_id; +-+-++ | max(created_on) | user_id | id | +-+-++ | 2006-04-25 20:10:59 | NULL| 4 | | 2006-04-27 23:48:27 | 1 | 50 | -- 456 | 2006-04-27 22:18:35 | 2 | 16 | -- 431 +-+-++ The max date is correct but that isn't the correspoinding action id, they should be 456 and 431 instead... Which I really don't understand this: mysql select max(id), user_id, id from logs group by user_id; +-+-++ | max(id) | user_id | id | +-+-++ | 183 | NULL| 4 | | 456 | 1 | 50 | | 431 | 2 | 16 | +-+-++ 3 rows in set (0.00 sec) So that is making this query wrong too of course. mysql SELECT max(`logs`.created_on), `logs`.*, users.username FROM `logs` LEFT JOIN users ON `logs`.user_id = users.id WHERE user_id IN (SELECT id FROM users ORDER BY username) GROUP BY user_id ORDER BY `logs`.created_on\G *** 1. row *** max(`logs`.created_on): 2006-04-27 22:18:35 id: 16 user_id: 2 created_on: 2006-03-14 22:40:16 body: Logged in. username: joe *** 2. row *** max(`logs`.created_on): 2006-04-27 23:48:27 id: 50 user_id: 1 created_on: 2006-03-31 16:15:16 body: Created Account username: bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with subqueries... MAX() and GROUP BY
Thanks Alex, that got me started. I don't understand why I had to use IN when the example uses = but at least it kinda works... The problem is now that I can't get the right data. mysql select max(created_on), user_id, id from logs group by user_id; +-+-++ | max(created_on) | user_id | id | +-+-++ | 2006-04-25 20:10:59 | NULL| 4 | | 2006-04-27 23:48:27 | 1 | 50 | -- 456 | 2006-04-27 22:18:35 | 2 | 16 | -- 431 +-+-++ The max date is correct but that isn't the correspoinding action id, they should be 456 and 431 instead... Which I really don't understand this: mysql select max(id), user_id, id from logs group by user_id; +-+-++ | max(id) | user_id | id | +-+-++ | 183 | NULL| 4 | | 456 | 1 | 50 | | 431 | 2 | 16 | +-+-++ 3 rows in set (0.00 sec) So that is making this query wrong too of course. mysql SELECT max(`logs`.created_on), `logs`.*, users.username FROM `logs` LEFT JOIN users ON `logs`.user_id = users.id WHERE user_id IN (SELECT id FROM users ORDER BY username) GROUP BY user_id ORDER BY `logs`.created_on\G *** 1. row *** max(`logs`.created_on): 2006-04-27 22:18:35 id: 16 user_id: 2 created_on: 2006-03-14 22:40:16 body: Logged in. username: joe *** 2. row *** max(`logs`.created_on): 2006-04-27 23:48:27 id: 50 user_id: 1 created_on: 2006-03-31 16:15:16 body: Created Account username: bob I just noticed something kinda sorta unrelated... Maybe I don't even need a subquery at all (not that this example here solves the data being wrong issue)... SELECT max(`logs`.created_on), `logs`.*, users.username FROM `logs` LEFT JOIN users ON `logs`.user_id = users.id GROUP BY user_id ORDER BY `logs`.created_on; *** 1. row *** max(`logs`.created_on): 2006-04-25 20:10:59 id: 4 user_id: NULL created_on: 2006-03-13 18:40:39 body: TEST username: NULL *** 2. row *** max(`logs`.created_on): 2006-04-27 22:18:35 id: 16 user_id: 2 created_on: 2006-03-14 22:40:16 body: Logged in. username: joe *** 3. row *** max(`logs`.created_on): 2006-04-27 23:48:27 id: 50 user_id: 1 created_on: 2006-03-31 16:15:16 body: Created Account username: bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP --- Slow SP
CREATE TABLE `his_msisdn_imei_activ_hist` ( `MSISDN` varchar(23) NOT NULL, `ACTIV_IMEI` varchar(20) NOT NULL, `ACTIV_PHONE_TYPE` varchar(100) NOT NULL, `PREV_IMEI` varchar(20) default NULL, `PREV_PHONE_TYPE` varchar(100) default NULL, `ACTIV_TIME` datetime NOT NULL, PRIMARY KEY (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; This primary key is a bad idea. A VERY VERY bad idea. For starters, a primary key should have ONE field, not THREE. While it is allowed, it's not going to help performance at all. Next is that the primary key Care for a fight over this one? :-) A primary key should be the primary key. If this is 3 columns, or 1 varchar column, it's all fine. I agree with your point of the ACTIV_TIME being a bad candidate for being part of a PK though. Oh, and having multiple columns in a PK does not mean you cannot create additional indices as/if required. All in all, your statement about multiple columns in a PK is a very very bad statement ;-) should be a numeric field. You've got varchars and datetimes! Yuck! If you want to enforce a rule such as restricting duplicate values, then start by creating yourself a sane primary key ( an unsigned int, for example ), and *THEN* put an index ( with your don't allow duplicates rule ) across your (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`) fields. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
~Mysql performance~
Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? Thanks, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, The following is the hard disk specs for both the servers: server one: ( whose performance is good ) --- class: HD bus: IDE detached: 0 device: hda driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 - class: HD bus: IDE detached: 0 device: hdb driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 This has 2 SATA hard disks of 200 GB capacity. There is no RAID configured. server two: ( whose performance is poor ) -- class: HD bus: SCSI detached: 0 device: sda driver: ignore desc: Seagate ST3146707LC host: 0 id: 0 channel: 0 lun: 0 generic: sg0 - class: HD bus: SCSI detached: 0 device: sdb driver: ignore desc: Seagate ST3146707LC host: 0 id: 1 channel: 0 lun: 0 generic: sg1 This has 2 SCSI hard disks of capacity 146 GB each. There is no RAID configured. Thanks, Abdul. On Fri, 2006-04-28 at 12:52 +0200, living liquid | Christian Meisinger wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, I would like to make one correction. The server one has 2 IDE hard disks and not SATA hard disks. Thanks, Abdul. On Fri, 2006-04-28 at 16:33 +0530, Mohammed Abdul Azeem wrote: Hi, The following is the hard disk specs for both the servers: server one: ( whose performance is good ) --- class: HD bus: IDE detached: 0 device: hda driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 - class: HD bus: IDE detached: 0 device: hdb driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 This has 2 SATA hard disks of 200 GB capacity. There is no RAID configured. server two: ( whose performance is poor ) -- class: HD bus: SCSI detached: 0 device: sda driver: ignore desc: Seagate ST3146707LC host: 0 id: 0 channel: 0 lun: 0 generic: sg0 - class: HD bus: SCSI detached: 0 device: sdb driver: ignore desc: Seagate ST3146707LC host: 0 id: 1 channel: 0 lun: 0 generic: sg1 This has 2 SCSI hard disks of capacity 146 GB each. There is no RAID configured. Thanks, Abdul. On Fri, 2006-04-28 at 12:52 +0200, living liquid | Christian Meisinger wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? This email has been Scanned for Viruses! www.newbreak.com This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Invitation to find out about Freestateprojects.org and join our mailing list
Hi. Came across your work and just thought I'd send you this intro email about the Free State Projects as we are looking to make connections! Daryl --- This email is to inform you about http://freestateprojects.org which is the information page for the Free State Projects, South Africa. The Free State Projects revolve around community development, education and upliftment; self-sufficiency, permaculture, co-ops, direct democracy, true cost economics, poverty alleviation, music festivals and other zany, wacky ideas like that. So why not take a look and see what its all about, just so you know in the back of your mind that there's always a place to emergency escape to. Set in the mountains that straddle two countries, its a beautiful part of the world, just look at the scenery section if you don't believe me! Here a suite of community development projects is taking place, uplifting the lives of those who choose to take part in them and we would appreciate any assistance you could give. Certainly if you are visiting South Africa at any point drop us a line and we can ensure you are looked after if you pay us a visit but be assured even if you never visit us there are still opportunities for cooperation. Also if you join the announcement list you will receive INFREQUENT mail about any interesting developments either relating to the website or to the development of the community. You can join the mailing list by visiting the Contact Us page: http://freestateprojects.org/connections.html There is no general user posting to this list so you will hardly be receiving any messages. If, however, you feel you really don't want to know about this stuff, you're not interested in helping worthy communities in the third world, you really like your job and would never consider visiting a stunning area with sun, mountains and snow please accept my apologies for disturbing you and ignore this message. Though I hope you remain with us and help a beautiful thing grow and take shape. So visit us at http://freestateprojects.org which will try to have photos, multimedia presentations, etc that I would not send out via email. There is also a wiki server which means you can contribute to any of the topics in real time! Just look for the link to the wiki which can be found on most pages. Also, if you have any friends that may be interested in this type of thing why not forward this email on to them? So have a brilliant week and remember to stretch often when working at a computer. Sincerely yours, Daryl Fuchs / Cyclotron Development coordinator - http://freestateprojects.org If you have any queries please reach us on [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the last entry per item
--- Peter Brawley [EMAIL PROTECTED] wrote: Brian Hi, I hope this is the right list for this question. If not, I'm happy to get help on where to post this question. Apologies in advance if this is an old question. We are designing a simple a tracking database with a table of entries showing the current location of each item in the system. Something simple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. An item is in the most recent location for that item_id, right? Then ... SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; PB Thanks -Brian Peter, I am surprised at you ;-) You should know that the query you sent won't work like you said. Here's your query suggestion: SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; The item_id will be unique (thanks to the GROUP BY item_id) and the MAX(timestamp) will also be correct (again because of the GROUP BY) but the middle column, `location`, will not necessarily be the location code of the record with the MAX(timestamp) :-( Because that column is neither part of the GROUP BY clause or covered by an aggregate function, the engine should throw an error. However, MySQL tries to be nicer than that so it just picks a random value from all of the rows where the item_id's are the same. The only way to get to the groupwize maximum (in this case the record with the latest date from a group of records sharing the same ID) is by using one of the techniques listed here (temp table, subquery, concat hack): http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Shawn Green Database Administrator __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import from unknown format (.bdd .ind .mor .ped)
http://perso.easynet.fr/cibderf/tout.html HTH Pedro mpa wrote: Greetings. I'm building an application and I need to import data to mysql from a db format I don't know. Unfortunately the person in charge of the data won't be reachable for the next 2 weeks and I want to continue my work. I was wondering if anyone knows the format extensions like: .bdd .ind .mor .ped Thanks in advance. Apologies for my bad English. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting all records inserted last 1 minutes?
Nothing' wrong- I just didn't know :) Yup! It works just fine! Thanks Paul for your help! -afan At 22:29 -0500 4/27/06, Afan Pasalic wrote: No, not exactly. More as there is a solution... What would be the best way to do? -afan What's wrong with WHERE last_access = NOW() - INTERVAL 1 HOUR ? You don't want CURTIME(), I think, because that has only time, not the date. Paul DuBois wrote: At 20:52 +0200 4/27/06, [EMAIL PROTECTED] wrote: Hi, I was trying to list all registeed users they are online last 15 minutes. I can do it by using timestamp: ?php $current_time = time(); $start_time = ($current_time - 15*60); SELECT * FROM members WHERE last_access = '$start_time' ? But, I know there is something like: SELECT * FROM members WHERE last_access BETWEEN(CURTIME(), INTERVAL 15 MIN) ?!? Thanks for any help. If you mean what is the syntax of BETWEEN?, it's in this section of the manual: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Killing MySQL connections on a specific interface from a specific host
Hi I'd like to know if there is anything to kill connections from a specific client ip that came to the server on a specific interface. I do not want to block them on layer 2 (which could easily be done with netfilter), I would like to be able to kill active connections. For example: I would like to kill connections from 192.168.50.3 that came in on interface eth0. Connections from that client IP to another interface should not be affected. Of course one could script something using lsof -i or netstat and the mysql processlist, but that would end in some nasty shellscript and I don't know how to only kill connections for one interface as the mysql processlist only shows the client ip, not the ip, the client connected to. Thanks for any ideas Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Killing MySQL connections on a specific interface from a specific host
On 4/28/06, Dominik Klein [EMAIL PROTECTED] wrote: Hi I'd like to know if there is anything to kill connections from a specific client ip that came to the server on a specific interface. I do not want to block them on layer 2 (which could easily be done with netfilter), I would like to be able to kill active connections. For example: I would like to kill connections from 192.168.50.3 that came in on interface eth0. Connections from that client IP to another interface should not be affected. Of course one could script something using lsof -i or netstat and the mysql processlist, but that would end in some nasty shellscript and I don't know how to only kill connections for one interface as the mysql processlist only shows the client ip, not the ip, the client connected to. Thanks for any ideas Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I am not really sure why you would need this , but I am just throwing in a possible solution.. First I would say since you need to kill connections on a specific interface (eth0), It would be fair to assume that you have more than one interface, if thats the case and you don't have the --bind-address option set in my.cnf, then your mysqld daemon would listen on all the available IP's on all interfaces, then it is vey difficult to know on what IP did mysql serve a specific connection, unless you would do a netstat, then correspond that IP to the clients IP in show processlist, etc etc , so pretty cumbersume... But unless there is a real need , you can just have the deamon to listen only on one specific IP residing on eth0, like this --bind-address= xxx.xxx.xx.x ( this IP resides on eth0) If this is feasible in your setup, then killing threads from a specific IP should be easy, if you need to kill threads manually then use a toll like mytop (http://jeremy.zawodny.com/mysql/mytop/), or if you want it automated then you could easily write a perl script which would parse the output of show full processlist, get all the connections from a specific client IP, and KILL them ... Hope this helps Kishore Jalleda http://kjalleda.googlepages.com/projects
Best column type for latitude / longitude?
Just curious the majority use. I've been using decimal(18,14), but that appears bigger than necessary... Maybe varchar(21) for latitude, and varchar(22) for longitude? ...Rene
Quick Replication Question
When you have master-slave replication enabled, and something goes wrong with one of the tables on the master, and you have to run mysqlcheck -r to fix it, does the fix get written to the binlog and replicated to the slave? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
On 4/28/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? Thanks, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Is the server2 a slave of server1, and is the same insert on server2 run directly on it or is itpart of a replicated query from server1. This came to my mind as u mentioned the two servers are geographically seperated .. Kishore Jalleda http://kjalleda.googlepages.com/projects
Re: Quick Replication Question
no. - Original Message - From: Robinson, Eric [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 28, 2006 8:51 AM Subject: Quick Replication Question When you have master-slave replication enabled, and something goes wrong with one of the tables on the master, and you have to run mysqlcheck -r to fix it, does the fix get written to the binlog and replicated to the slave? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching Different Tables' Columns and Rows
Hi, I am having the toughest time accomplishing this. I am asking for any examples or links to info pertaining to this as I want to make an effort before posting any code. After matching the specific rows by an identifying column between two tables.. I can manipulate some data albeit on a minor scale.. instead of finding only specifc rows that match, I'm receiving ALL rows between the tables, so it may be more logic than querying the db. However, my initial query to the db never seems to be correct and this may also be the problem. Any tips, suggestions or code examples is appreciated. Thanks -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the last entry per item
Shawn, Yep that's the theory, but where (i) the aggregate result is a column value, rather than a sum or average for example, and (ii) id is unique, I have not been able to get MySQL to give a wrong second_col value with that approach, eg try the following with the northwind database (it ought to be doable in one query, but this machine's version of the MySQL server crashed on that): -- 'wrong' max, omitting nulls DROP TABLE IF EXISTS o1; CREATE TABLE o1 SELECT orderid,shipcity,MAX(shippeddate) AS latest FROM orders WHERE shippeddate IS NOT NULL GROUP BY orderid; -- correct max, again omitting nulls DROP TABLE IF EXISTS o2; CREATE TABLE o2 SELECT orderid, shipcity, (SELECT MAX(shippeddate) AS latest FROM orders o2 WHERE o2.orderid=o1.orderid) AS latest FROM orders o1 GROUP BY orderid HAVING latest IS NOT NULL; -- report o1 and o2 rows which do not match: SELECT MIN(TableName) as TableName, orderid, shipcity, latest FROM ( SELECT 'o1' AS TableName,orderid,shipcity,latest FROM o1 UNION ALL SELECT 'o2' as TableName,orderid,shipcity,latest FROM o2 ) AS tmp GROUP BY orderid, shipcity,latest HAVING COUNT(*) = 1; Empty set (0.01 sec) PB - Shawn Green wrote: --- Peter Brawley [EMAIL PROTECTED] wrote: Brian Hi, I hope this is the right list for this question. If not, I'm happy to get help on where to post this question. Apologies in advance if this is an old question. We are designing a simple a tracking database with a table of entries showing the current location of each item in the system. Something simple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. An item is in the most recent location for that item_id, right? Then ... SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; PB Thanks -Brian Peter, I am surprised at you ;-) You should know that the query you sent won't work like you said. Here's your query suggestion: SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; The item_id will be unique (thanks to the GROUP BY item_id) and the MAX(timestamp) will also be correct (again because of the GROUP BY) but the middle column, `location`, will not necessarily be the location code of the record with the MAX(timestamp) :-( Because that column is neither part of the GROUP BY clause or covered by an aggregate function, the engine should throw an error. However, MySQL tries to be nicer than that so it just picks a random value from all of the rows where the item_id's are the same. The only way to get to the groupwize maximum (in this case the record with the latest date from a group of records sharing the same ID) is by using one of the techniques listed here (temp table, subquery, concat hack): http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Shawn Green Database Administrator __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select the last entry per item
LOL, three late nights in a row, lose that last post o' mine. PB - Shawn Green wrote: --- Peter Brawley [EMAIL PROTECTED] wrote: Brian Hi, I hope this is the right list for this question. If not, I'm happy to get help on where to post this question. Apologies in advance if this is an old question. We are designing a simple a tracking database with a table of entries showing the current location of each item in the system. Something simple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. An item is in the most recent location for that item_id, right? Then ... SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; PB Thanks -Brian Peter, I am surprised at you ;-) You should know that the query you sent won't work like you said. Here's your query suggestion: SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; The item_id will be unique (thanks to the GROUP BY item_id) and the MAX(timestamp) will also be correct (again because of the GROUP BY) but the middle column, `location`, will not necessarily be the location code of the record with the MAX(timestamp) :-( Because that column is neither part of the GROUP BY clause or covered by an aggregate function, the engine should throw an error. However, MySQL tries to be nicer than that so it just picks a random value from all of the rows where the item_id's are the same. The only way to get to the groupwize maximum (in this case the record with the latest date from a group of records sharing the same ID) is by using one of the techniques listed here (temp table, subquery, concat hack): http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Shawn Green Database Administrator __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching Different Tables' Columns and Rows
Basically, I want to match specific rows with ONLY a specific date.
Here's the code:
mysql_select_db($database_connBlog, $connBlog);
$query_FindArticleDates = SELECT * FROM blg_article_art, blg_topic_top
WHERE blg_article_art.idtop_art = blg_topic_top.id_top;
$findArticleDates = mysql_query($query_FindArticleDates, $connBlog) or
die(mysql_error());
$findRow_ArticleDates = mysql_fetch_assoc($findArticleDates);
$field_ArticleDates = mysql_fetch_array($findArticleDates);
$totalRows_FindArticleDates = mysql_num_rows($findArticleDates);
?php for ( $k = $totalRows_FindArticleDates; $k 0; $k-- ) {
if (mysql_num_rows($findArticleDates) 0){
mysql_data_seek($findArticleDates,0);
while($findRow_ArticleDates =
mysql_fetch_assoc($findArticleDates)){
$dates[] = $findRow_ArticleDates['FindArticleDate'];
}
} ?
a href = weblog/month.php??php echo m=$j#38;y=$year?
? echo $getMonth[$i++]. .$year; $j++; }?/a
It is supposed to print the month only IF it there is entries matching
the date. So if there were 3 entries made for one month, then all
entries for that month should be printed. Right now, this prints every
row in existence.
-Patrick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL PHP Connector
Could you give me MySQL PHP Connector, both mysql and mysqli connectors version 5.0.18? Thanks.
Re: How to select the last entry per item
Here are the queries which illustrate Shawn's point. Now to sleep. -- wrong DROP TABLE IF EXISTS o1; CREATE TABLE o1 SELECT customerid,shipcity,MAX(shippeddate) AS latest FROM orders GROUP BY customerid; -- right DROP TABLE IF EXISTS o2; CREATE TABLE o2 SELECT DISTINCT o1.customerid,o1.shipcity,o1.shippeddate AS latest FROM orders o1 LEFT JOIN orders o2 ON o1.customerid=o2.customerid AND o1.shippeddateo2.shippeddate WHERE o1.shippeddate IS NOT NULL AND o2.customerid IS NULL ORDER BY customerid; -- 3 of 89 rows differ SELECT MIN(TableName) as TableName, customerid, shipcity, latest FROM ( SELECT 'o1' AS TableName,customerid,shipcity,latest FROM o1 UNION ALL SELECT 'o2' as TableName,customerid,shipcity,latest FROM o2 ) AS tmp GROUP BY customerid, shipcity,latest HAVING COUNT(*) = 1; PB - Peter Brawley wrote: LOL, three late nights in a row, lose that last post o' mine. PB - Shawn Green wrote: --- Peter Brawley [EMAIL PROTECTED] wrote: Brian Hi, I hope this is the right list for this question. If not, I'm happy to get help on where to post this question. Apologies in advance if this is an old question. We are designing a simple a tracking database with a table of entries showing the current location of each item in the system. Something simple like this. Table_xyz item_id | location | status | time_stamp As the items move new time stamped entries are added to the database. How would you query to find the current location of all the items currently in the system. As you might expect we don't want to replace the entry for an item when a location update is made because we need to keep the history. We plan on removing items after a suitable delay when they reach their destination. An item is in the most recent location for that item_id, right? Then ... SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; PB Thanks -Brian Peter, I am surprised at you ;-) You should know that the query you sent won't work like you said. Here's your query suggestion: SELECT item_id, location,MAX(timestamp) FROM table_xyz GROUP BY item_id; The item_id will be unique (thanks to the GROUP BY item_id) and the MAX(timestamp) will also be correct (again because of the GROUP BY) but the middle column, `location`, will not necessarily be the location code of the record with the MAX(timestamp) :-( Because that column is neither part of the GROUP BY clause or covered by an aggregate function, the engine should throw an error. However, MySQL tries to be nicer than that so it just picks a random value from all of the rows where the item_id's are the same. The only way to get to the groupwize maximum (in this case the record with the latest date from a group of records sharing the same ID) is by using one of the techniques listed here (temp table, subquery, concat hack): http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Shawn Green Database Administrator __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.5.0/325 - Release Date: 4/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
On Fri, 28 Apr 2006, Mohammed Abdul Azeem wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. Are these queries run from localhost or from a remote location? It's very unusual for an IDE based system to outperform SCSI. Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Streaming LOB Data
It appears (for InnoDB at least) that while INSERTing a LOB that all LOB data must be loaded into memory before it is written to disk. Or is it just the size of the combined log files? Looking at Task Manager, it looks like it DOES try to load the whole thing into memory before streaming it to disk; which just seems wacky. The error I get is this: Out of memory (Needed xxx ... If the server crashes I get this: InnoDB: which exceeds the log group capacity 18870682. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. How can I make the size of LOBs I insert NOT be memory constrained? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with subqueries... MAX() and GROUP BY [sovled]
Well I think this is mostly working. I have a 'NULL' user ID which is 'system' that I need to get into here, but I think I'm mostly on track... There are lots of ways to accomplish this task it seems. ALL of which would be so much easier if mySQL would just return all the data from the same MAX() row it just pulled. I really don't see why this has to be so complicated. I appreciate in some ways that mySQL tries to be nice and give me some data, but what good is it if it's WRONG?! Either throw an error, so I make a proper query, or else give me what I wanted... *sigh* Is there EVER a time when someone wants the behaviour of mySQL now? I can't for the life of me think of a case. This page had some more info: http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Alex was close on his guess: mysql select max(logs.created_on), username , max(logs.id),body from logs, users where logs.user_id=users.id group by user_id; +--+--+--+-+ | max(logs.created_on) | username | max(logs.id) | body| +--+--+--+-+ | 2006-04-27 23:34:32 | joe | 473 | Created Account | | 2006-04-27 22:18:35 | bob | 431 | Logged in. | +--+--+--+-+ But notice that the body is now wrong. Grr... So, you have to MAX() that too... mysql select max(logs.created_on), username , max(logs.id), max(body) from logs, users where logs.user_id=users.id group by user_id; +--+--+--++ | max(logs.created_on) | username | max(logs.id) | max(body) | +--+--+--++ | 2006-04-27 23:34:32 | joe | 473 | Viewed Users Stats | | 2006-04-27 22:18:35 | bob | 431 | Viewed Users Stats | +--+--+--++ mysql select * from `logs` l1 where id = (select max(l2.id) from logs l2 where l1.user_id = l2.user_id); +-+-+-++ | id | user_id | created_on | body | +-+-+-++ | 431 | 2 | 2006-04-27 22:18:35 | Viewed Users Stats | | 473 | 1 | 2006-04-27 23:34:32 | Viewed Users Stats | +-+-+-++ mysql select l1.*, username from `logs` l1 join users on l1.user_id = users.id where l1.id = (select max(l2.id) from logs l2 where l1.user_id = l2.user_id); +-+-+-++--+ | id | user_id | created_on | body | username | +-+-+-++--+ | 473 | 1 | 2006-04-27 23:34:32 | Viewed Users Stats | joe | | 431 | 2 | 2006-04-27 22:18:35 | Viewed Users Stats | bob | +-+-+-++--+ From: Alex Arul [mailto:[EMAIL PROTECTED] Sent: Friday, April 28, 2006 2:18 AM To: Daevid Vincent Subject: Re: Help with subqueries... MAX() and GROUP BY from what i understand, i think this is what you are looking for select max(created_on), username , max(logs.id),body from logs, users where logs.user_id=users.id group by user_id; while using subqueries that feed into where clauses, you can use = only for cases where the subquery returns only one value else you have to use in. Thanx Alex On 4/28/06, Daevid Vincent [EMAIL PROTECTED] wrote: Thanks Alex, that got me started. I don't understand why I had to use IN when the example uses = but at least it kinda works... The problem is now that I can't get the right data. mysql select max(created_on), user_id, id from logs group by user_id; +-+-++ | max(created_on) | user_id | id | +-+-++ | 2006-04-25 20:10:59 | NULL| 4 | | 2006-04-27 23:48:27 | 1 | 50 | -- 456 | 2006-04-27 22:18:35 | 2 | 16 | -- 431 +-+-++ The max date is correct but that isn't the correspoinding action id, they should be 456 and 431 instead... Which I really don't understand this: mysql select max(id), user_id, id from logs group by user_id; +-+-++ | max(id) | user_id
Re: Can I select from remote tables in mysql 5.0.18-standard-log?
Thanks all, this looks exactly like what i'm looking for. However, when I create the federated table, it says it was successful but creates the table as Myisam. CREATE TABLE `petestdb.backup_pp_line_code` ( `catalog_id` int(10) unsigned NOT NULL, `line_code` char(3) NOT NULL, `product_typ_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`catalog_id`,`product_typ_id`), KEY `line_code_Index_2` (`line_code`) ) ENGINE=federated DEFAULT CHARSET=latin1 connection='mysql://[EMAIL PROTECTED]:3306/database2/backup_pp_line_code'; Show create table gives me : backup_pp_line_code | CREATE TABLE `backup_pp_line_code` ( `catalog_id` int(10) unsigned NOT NULL, `line_code` char(3) NOT NULL, `product_typ_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`catalog_id`,`product_typ_id`), KEY `line_code_Index_2` (`line_code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CONNECTION='mysql://[EMAIL PROTECTED]:3306/database2/backup_pp_line_code' | When I query the federated table, it says 0 rows although the target has over 12000 rows. Could the mysql-max distribution be a factor ? How do I verify if this is what i'm running ? Paul DuBois [EMAIL PROTECTED] wrote: At 14:38 -0700 4/27/06, P. Evans wrote: Hello Listers, Is it possible to run a query on one mysql server to another database on a different server ? eg creating an alias in database A on server A to table B on database B on server B ? Like a federated nickname on db2 udb or synonym on informix ? You can use FEDERATED to access tables on other MySQL servers. http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com - How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
RE: Help with subqueries... MAX() and GROUP BY
On Fri, 2006-04-28 at 00:13 -0700, Daevid Vincent wrote: The problem is now that I can't get the right data. mysql select max(created_on), user_id, id from logs group by user_id; +-+-++ | max(created_on) | user_id | id | +-+-++ | 2006-04-25 20:10:59 | NULL| 4 | | 2006-04-27 23:48:27 | 1 | 50 | -- 456 | 2006-04-27 22:18:35 | 2 | 16 | -- 431 +-+-++ The max date is correct but that isn't the correspoinding action id, they should be 456 and 431 instead... If you don't include id in either an aggregate function or the GROUP BY clause, MySQL 'helps' you by choosing a seemingly-random value to stick in the id field. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
Re: Can I select from remote tables in mysql 5.0.18-standard-log?
On 4/28/06, P. Evans [EMAIL PROTECTED] wrote: Thanks all, this looks exactly like what i'm looking for. However, when I create the federated table, it says it was successful but creates the table as Myisam. CREATE TABLE `petestdb.backup_pp_line_code` ( `catalog_id` int(10) unsigned NOT NULL, `line_code` char(3) NOT NULL, `product_typ_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`catalog_id`,`product_typ_id`), KEY `line_code_Index_2` (`line_code`) ) ENGINE=federated DEFAULT CHARSET=latin1 connection='mysql://[EMAIL PROTECTED]:3306/database2/backup_pp_line_code'; Show create table gives me : backup_pp_line_code | CREATE TABLE `backup_pp_line_code` ( `catalog_id` int(10) unsigned NOT NULL, `line_code` char(3) NOT NULL, `product_typ_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`catalog_id`,`product_typ_id`), KEY `line_code_Index_2` (`line_code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CONNECTION=' mysql://[EMAIL PROTECTED]:3306/database2/backup_pp_line_code' | When I query the federated table, it says 0 rows although the target has over 12000 rows. Could the mysql-max distribution be a factor ? How do I verify if this is what i'm running ? Paul DuBois [EMAIL PROTECTED] wrote: At 14:38 -0700 4/27/06, P. Evans wrote: Hello Listers, Is it possible to run a query on one mysql server to another database on a different server ? eg creating an alias in database A on server A to table B on database B on server B ? Like a federated nickname on db2 udb or synonym on informix ? You can use FEDERATED to access tables on other MySQL servers. http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com - How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. As I said earlier you will need the Mysql-Max Binary for the FEDERATED engine to work, you can get what version you are running by logging into mysql via the CLI, or with a SHOW VERSION(); Kishore Jalleda
RE: Quick Replication Question
Okay, so that is not too encouraging. Is there any way to find out what mysqkcheck did so it can be manually applied to the slave? Or is mysqlcheck not the best way to fix things when you are using replication? -Original Message- From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED] Sent: Friday, April 28, 2006 9:05 AM To: Robinson, Eric; mysql@lists.mysql.com Subject: Re: Quick Replication Question no. - Original Message - From: Robinson, Eric [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 28, 2006 8:51 AM Subject: Quick Replication Question When you have master-slave replication enabled, and something goes wrong with one of the tables on the master, and you have to run mysqlcheck -r to fix it, does the fix get written to the binlog and replicated to the slave? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching fields from two different tables
Folks, I could really use your assistance. Take a look here: http://pastebin.com/687889 How can I manipulate totalRows_numberComments so that I get the number of blg_comment_com.idart_com PER blg_article_art.id_art? Right now, it produces every blg_comment_com.idart_com in existence, definately not cool. Thanks -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] Possible?
René Fournier wrote: Simple problem: Many client apps need to send data to a server. By default each client will open a persistent TCP socket connection to a common IP address:port (10.10.10.10:1234) and write to it (which the server will save/log, etc.). My question is, what should be ready to listen at the IP:port? I was thinking of writing a PHP command-line socket server that listens at that port. But there will be a potentially huge number of clients connecting simultaneously (1000s), not transmitting a lot of data per se, but a lot of connections... Anyway, instead I thought writing a simple PHP script—say, listener.php—that gets executed by the web server and enters a socket_read loop until the client terminates the connection (set_time_limit(0)). Does this sound like a good way to do it? This way, Apache handles all the connections, and PHP is instantiated each time, but without having to fork processes, etc, and without having to do PHP CLI. Anyway, I've started looking at this, but I'm not quite sure if it's even possible. I mean, can something send a request to Apache, and continue to write data along that TCP socket? Normally, HTTP requests include GET or POST for such data, but this is not a a web browser that's opening the connection. Hope I'm somewhat clear. Just struggling through some options here Anyway, thanks in advance for any suggestions. ...Rene each client will open a persistent TCP socket connection to a common IP address:port (10.10.10.10:1234) (If you want simultaneous connections, each TCP connection will be to a different port.) But rather than write a client and a server application from scratch, why not just use HTTP POSTs? Keep It Simple, Stewart. --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timeseries
Hello, Is there an easy way to get the last timestamped record not later than a given date/time? I record data for a scientific application and I do not sample my data at a given, fixed frequency. Instead, whenever a physical value changes beyond a given threshold, I get a new timestamped value which is stored in the database. For data analysis however it is more convenient to have a regularly-spaced timeseries of that data. For instance, if I get temperature measurements at 7:56, 8:02, 8:13 and 8:27, and would like a timeseries 15-minutes apart (8:00, 8:15, 8:30), I would like MySQL to automatically figure out that I need the measurements from 7:56, 8:13 and 8:27. Any simple way to do that would be immensely appreciated. Thanks in advance, -- -- David Lindelöf Station 18 LESO-PB/EPFL 1015 Lausanne tel +41-21-693.5556 mob +41-79-415.6641 fax +41-21-693.2722 e-mail [EMAIL PROTECTED] url http://lesowww.epfl.ch/doctorants/lindelof/ weblog http://visnet.ch/~lindelof/smartbuildings/ -- We came. We saw. We kicked its ass. -- Bill Murray, _Ghostbusters_ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching fields from two different tables
-Patrick wrote: Folks, I could really use your assistance. Take a look here: http://pastebin.com/687889 How can I manipulate totalRows_numberComments so that I get the number of blg_comment_com.idart_com PER blg_article_art.id_art? Right now, it produces every blg_comment_com.idart_com in existence, definately not cool. Thanks -Patrick You had: SELECT COUNT(*) FROM blg_comment_com, blg_article_art WHERE blg_article_art.id_art=blg_comment_com.idart_com Try this: SELECT blg_article_art.id_art, COUNT(*) as NumberOfComments FROM blg_comment_com, blg_article_art WHERE blg_article_art.id_art = blg_comment_com.idart_com group by blg_article_art.id_art Try it in the mysql console before you try to plug it into php. (You can only learn so much at one time :) --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching fields from two different tables
John Hicks wrote: -Patrick wrote: Folks, I could really use your assistance. Take a look here: http://pastebin.com/687889 How can I manipulate totalRows_numberComments so that I get the number of blg_comment_com.idart_com PER blg_article_art.id_art? Right now, it produces every blg_comment_com.idart_com in existence, definately not cool. Thanks -Patrick You had: SELECT COUNT(*) FROM blg_comment_com, blg_article_art WHERE blg_article_art.id_art=blg_comment_com.idart_com Try this: SELECT blg_article_art.id_art, COUNT(*) as NumberOfComments FROM blg_comment_com, blg_article_art WHERE blg_article_art.id_art = blg_comment_com.idart_com group by blg_article_art.id_art Try it in the mysql console before you try to plug it into php. (You can only learn so much at one time :) --John That is fantastic John. Outputs the id along with respective count. However, it leads back to my original question... how do I coordinate the output from this query with php? I know php fairly well, but when mixes with mysql Im still new and I only want to call those particular values and have them respond appropriately. What do you suggest? Take this output and shove it into an array, and match up id_art with a ? echo arrayNum['id_art']; ? ? Say, if NumberOfComments 0, echo NumberOfComments? See, I can output the NumberOfComments, but they're either a total of the whole db and/or constant for every id_art.. even though the query output is correct? (also derived a similar output but used a different query... same problem resides -coordination). Any suggestions are appreciated, Thank you -Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timeseries
In the last episode (Apr 29), David Lindelf said:
Is there an easy way to get the last timestamped record not later
than a given date/time?
I record data for a scientific application and I do not sample my
data at a given, fixed frequency. Instead, whenever a physical value
changes beyond a given threshold, I get a new timestamped value which
is stored in the database.
For data analysis however it is more convenient to have a
regularly-spaced timeseries of that data. For instance, if I get
temperature measurements at 7:56, 8:02, 8:13 and 8:27, and would like
a timeseries 15-minutes apart (8:00, 8:15, 8:30), I would like MySQL
to automatically figure out that I need the measurements from 7:56,
8:13 and 8:27.
You could probably do it with a bunch of selects, each fetching the row
before each time period, all UNIONed together.
It might be better to just fetch the timestamps for all the records
within your time range of interest (which should be fast assuming
you've got an index on your time field), figure out which ones you want
in whatever language you're most familiar with, then fetch the records
with another single statement ... WHERE timestampfield IN ('time1',
'time2', 'time3'). That would also let you get a bit fancier: For
example, select the sample nearest to your 15-minute marks, rather than
the one less than the mark. Or maybe select the sample nearest to the
point 15 minutes after the previous sample, which will end up with you
drifting off the quarter of the hour marks but will get you better
spacing between samples.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching fields from two different tables
-Patrick wrote:
John Hicks wrote:
-Patrick wrote:
Folks, I could really use your assistance.
Take a look here: http://pastebin.com/687889
How can I manipulate totalRows_numberComments so that I get the number
of blg_comment_com.idart_com PER blg_article_art.id_art? Right now, it
produces every blg_comment_com.idart_com in existence, definately not
cool.
Thanks
-Patrick
You had:
SELECT COUNT(*) FROM blg_comment_com, blg_article_art WHERE
blg_article_art.id_art=blg_comment_com.idart_com
Try this:
SELECT blg_article_art.id_art, COUNT(*) as NumberOfComments
FROM blg_comment_com, blg_article_art
WHERE blg_article_art.id_art = blg_comment_com.idart_com
group by blg_article_art.id_art
Try it in the mysql console before you try to plug it into php.
(You can only learn so much at one time :)
--John
That is fantastic John. Outputs the id along with respective count.
However, it leads back to my original question... how do I coordinate
the output from this query with php?
I know php fairly well, but when
mixes with mysql
Im still new and I only want to call those particular
values and have them respond appropriately.
What do you suggest?
The wonderful thing about a computer is that you can make it do just
about anything that you want it to do.
But first you have to decide just what you want it to do.
You've seen the output from your SQL statement. Is there something you
would like to do with that? Do you want to print it out? Sort it? Select
from it?
No, no. Don't tell me. Let me see if I can read your mind. You are
writing your own blog software and you want to display the number of
comments following each post?
If that's the case you will want to select the post articles themselves
... and you might as well select the comment count at the same time.
So you can start with the same SQL statement and tweak it by just adding
a word or two. I'll let you figure that out.
But it looks like you have never done a query through PHP before, so
here is a quick template that should print out the above query results:
$Conn = mysql_pconnect(localhost, MyUserName, MyPassword)
or die(Unable to connect to database);
$Db = mysql_select_db(MyDatabaseName, $Conn)
or die(Unable to select database);
$Sql = SELECT blg_article_art.id_art, COUNT(*) as NumberOfComments
FROM blg_comment_com, blg_article_art
WHERE blg_article_art.id_art = blg_comment_com.idart_com
group by blg_article_art.id_art ;
$Result = mysql_query($Sql, $Conn)
or die(Query failed with error . mysql_error());
echo htmlhead/headbody\n;
echo tabletrthArticle ID/ththCount/th/tr\n;
while ($Record = mysql_fetch_object($Result)) {
echo tr
td$Record-id_art/td
td$Record-NumberOfComments/td
/tr
;
}
echo /table/body/html;
Here's another example, this one taken from the PHP manual page on mysql
functions (http://us3.php.net/manual/en/ref.mysql.php):
Example 1. MySQL extension overview example
?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');
// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo table\n;
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo \ttr\n;
foreach ($line as $col_value) {
echo \t\ttd$col_value/td\n;
}
echo \t/tr\n;
}
echo /table\n;
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
