replication
All, I have a situation whereby I have two servers each running mysql in a Master slave replicated setup. Currently if the master server goes down we have to manually failover the database servers, taking a snaphost etc.. Is this still the optimal way to do this, or has the 'Change Master' command, and various other things superceded this method now, and thus is there a better and more automated way to do db failover? Regards Marty p.s. If this is better implemented in Version 4, I would be happy to receive advice on this, as it would form ammunition to push for an upgrade. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order By question
I have a query:- SELECT recTran.TransactionID,tr.* FROM Transaction tr LEFT OUTER JOIN ReconciledTransactions recTran ON recTran.TransactionID = tr.TransactionID WHERE tr.ReconciliationID = '8' HAVING recTran.TransactionID IS NULL ORDER BY 'tr.Amount' DESC; The problem I have is that the Order By doesn't seem to be returning the results in the order I expect. Can anybody shed any light on this? I am expecting to see the data returned ordered overall by the order_by clause, however it doesn't do this. Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order By question - solved
sorry, I had some extraneous quotes in my perl code:-) zzz - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 15, 2003 11:17 PM Subject: Order By question I have a query:- SELECT recTran.TransactionID,tr.* FROM Transaction tr LEFT OUTER JOIN ReconciledTransactions recTran ON recTran.TransactionID = tr.TransactionID WHERE tr.ReconciliationID = '8' HAVING recTran.TransactionID IS NULL ORDER BY 'tr.Amount' DESC; The problem I have is that the Order By doesn't seem to be returning the results in the order I expect. Can anybody shed any light on this? I am expecting to see the data returned ordered overall by the order_by clause, however it doesn't do this. Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select from one table where ID not in another table
All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select from one table where ID not in another table
All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
I'm not sure if I've described the exact results I want very well, but thanks to everyone for your help so far, hope you can bear with me a little longer. Below are the table Descriptions Table1:- +---+---+--+-+-+ + | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+ + | TransactionID | int(11) | | PRI | NULL| auto_increment | | ReconciliationD| int(11) | YES | | 0 | | x| AccountID | int(11) | | MUL | 0 | | y| AccountNumber | varchar(255) | | | 0 | | z| Created | datetime | | | -00-00 00:00:00 | | u| Updated | timestamp(14) | YES | | NULL| | v| Value | decimal(20,2) | | | 0.00| | w| Date | datetime | | | -00-00 00:00:00 | | t| DatabaseID| int(11) | | | 0 | | +---+---+--+-+-+ + Table1 Data 1,8,x,y,z,u,v,w,t(x,y,z,u,v,w,t are irrelavant fields to the join) 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t 6,9,x,y,z,u,v,w,t 7,9,x,y,z,u,v,w,t 8,9,x,y,z,u,v,w,t 9,9,x,y,z,u,v,w,t 10,9,x,y,z,u,v,w,t Table2:- +-+---+--+-+ -+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+ -+---+ | ReconciledTransactionID | int(11) | | PRI | 0 | | | TransactionID | int(11) | | UNI | 0 | | | ReconciliationID| int(11) | | PRI | 0 | | +-+---+--+-+ -+---+ Table2 Data 1,1,8 2,2,8 So far I have this query:- SELECT tr.* FROM Table1 tr LEFT JOIN Table2 recTran ON recTran.ReconciliationID = tr.ReconciliationID WHERE tr.Rec onciliationID = '8' AND tr.TransactionID recTran.TransactionID; So Expected results should be:- 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t This Works ok, HOWEVER, if Table2 Has no Data, the query returns NO results. What I want it to return is:- 1,8,x,y,z,u,v,w,t 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t any Takers? Regards Marty - Original Message - From: Jason Ramsey [EMAIL PROTECTED] To: Martin Moss [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:27 PM Subject: RE: Select from one table where ID not in another table Well, in order for that to work, you will need to do an explicit JOIN somewhere or else the IS NULL or NOT NULL won't work. You might try... SELECT table1.*, table2.id FROM table1 LEFT JOIN table2 ON table1.ortherkeyid = table2.otherkeyid WHERE table1.otherkeyid = '7236523' AND (table2.otherkeyid IS NULL or table2.otherkeyid IS NOT NULL) -Original Message- From: Martin Moss [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:01 PM To: Martin Moss; [EMAIL PROTECTED] Subject: Re: Select from one table where ID not in another table Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General
Re: Select from one table where ID not in another table - Solved
Finally worked it out, here's the query I'm using SELECT table2.TransactionID,table1.* FROM Table1 table1 LEFT OUTER JOIN Table2 table2 ON table2.TransactionID = table1.Transa ctionID WHERE table1.ReconciliationID = '8' HAVING table2.TransactionID IS NULL; Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:47 PM Subject: Re: Select from one table where ID not in another table I'm not sure if I've described the exact results I want very well, but thanks to everyone for your help so far, hope you can bear with me a little longer. Below are the table Descriptions Table1:- +---+---+--+-+-+ + | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+ + | TransactionID | int(11) | | PRI | NULL| auto_increment | | ReconciliationD| int(11) | YES | | 0 | | x| AccountID | int(11) | | MUL | 0 | | y| AccountNumber | varchar(255) | | | 0 | | z| Created | datetime | | | -00-00 00:00:00 | | u| Updated | timestamp(14) | YES | | NULL| | v| Value | decimal(20,2) | | | 0.00| | w| Date | datetime | | | -00-00 00:00:00 | | t| DatabaseID| int(11) | | | 0 | | +---+---+--+-+-+ + Table1 Data 1,8,x,y,z,u,v,w,t(x,y,z,u,v,w,t are irrelavant fields to the join) 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t 6,9,x,y,z,u,v,w,t 7,9,x,y,z,u,v,w,t 8,9,x,y,z,u,v,w,t 9,9,x,y,z,u,v,w,t 10,9,x,y,z,u,v,w,t Table2:- +-+---+--+-+ -+---+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+ -+---+ | ReconciledTransactionID | int(11) | | PRI | 0 | | | TransactionID | int(11) | | UNI | 0 | | | ReconciliationID| int(11) | | PRI | 0 | | +-+---+--+-+ -+---+ Table2 Data 1,1,8 2,2,8 So far I have this query:- SELECT tr.* FROM Table1 tr LEFT JOIN Table2 recTran ON recTran.ReconciliationID = tr.ReconciliationID WHERE tr.Rec onciliationID = '8' AND tr.TransactionID recTran.TransactionID; So Expected results should be:- 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t This Works ok, HOWEVER, if Table2 Has no Data, the query returns NO results. What I want it to return is:- 1,8,x,y,z,u,v,w,t 2,8,x,y,z,u,v,w,t 3,8,x,y,z,u,v,w,t 4,8,x,y,z,u,v,w,t 5,8,x,y,z,u,v,w,t any Takers? Regards Marty - Original Message - From: Jason Ramsey [EMAIL PROTECTED] To: Martin Moss [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:27 PM Subject: RE: Select from one table where ID not in another table Well, in order for that to work, you will need to do an explicit JOIN somewhere or else the IS NULL or NOT NULL won't work. You might try... SELECT table1.*, table2.id FROM table1 LEFT JOIN table2 ON table1.ortherkeyid = table2.otherkeyid WHERE table1.otherkeyid = '7236523' AND (table2.otherkeyid IS NULL or table2.otherkeyid IS NOT NULL) -Original Message- From: Martin Moss [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:01 PM To: Martin Moss; [EMAIL PROTECTED] Subject: Re: Select from one table where ID not in another table Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List
Re: Querying a Linux machine
Is it giving you access denied, or is it failing to find the host machine? if it's access denied you will need to add the username and hostname (of whichever machine you are connecting from) and grant that user access. use the 'mysql_setpermission' script to do this. If you can't actually see the machine, then it's a linux issue. Marty - Original Message - From: Michael Piko [EMAIL PROTECTED] To: Victoria Reznichenko [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 29, 2003 11:39 AM Subject: Re: Querying a Linux machine Its a standard RedHat 8.0 install. MySQL version is 3.23.52-3 installed from the standard RPMs - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 29, 2003 7:56 PM Subject: Re: Querying a Linux machine Michael Piko [EMAIL PROTECTED] wrote: I have mysql on a Linux machine and can connect to through 'localhost' and its real IP/hostname. I cannot connect to it from other machines. Is there anything I need to do to make this happen? Please, provide some more info 1. Version of MySQL 2. Do you use binary or sorce distribution? 3. What exactly Linux do you use (SuSe, Red Hat etc.)? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New Group By, order by question
All, I have a question about grouping numbers. Lets say I have 10 records each containing a numeric value:- 1 2 3 5 10 -1 -2 -3 -4 -5 What I wish to do is to select the records from the database but group them like this :- e.g. by the highest value (ASC or DESC) regarldess of whether the value is positive or negative? 10 5 -5 -4 3 -3 2 -2 1 -1 Can I do this in one query, or do I need to make two queries Regards Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
So simple, yet wonderful:-) Re: New Group By, order by question
Thanks to everyone who Helped, Regards Marty - Original Message - From: Joseph Bueno [EMAIL PROTECTED] To: Martin Moss [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 24, 2003 3:56 PM Subject: Re: New Group By, order by question select order by abs(field) desc; Hope this helps, Joseph Bueno Martin Moss wrote: All, I have a question about grouping numbers. Lets say I have 10 records each containing a numeric value:- 1 2 3 5 10 -1 -2 -3 -4 -5 What I wish to do is to select the records from the database but group them like this :- e.g. by the highest value (ASC or DESC) regarldess of whether the value is positive or negative? 10 5 -5 -4 3 -3 2 -2 1 -1 Can I do this in one query, or do I need to make two queries Regards Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql Mirroring
Hi, Just a quick high level design question? Is mirroring only a one way action? i.e. one in use database and one backup database? Or is it possible to use both databases and they'll both update each other? Regards Marty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
