Question regards mysqldump and replication
Hi. I have a question regarding mysql replication and mysqldump. I have a master (A). All my clients insert/update/delete only to this master. Then I have a Slave (B). This slave only replicates the master. There are no other processes changing/inserting data into the Slave. The slave also logs binlog so I could replicate from that server as well. Now I want a chained Slave ( like A - B - C , C being the chained slave). So my idea is: stop replication on B so no changes during dump, dump its master status, mysqldump all databases. Then load the dump on C and configure slave on C according to the master status from B. I did that and end up in hundreds of duplicate key errors. How can that be? What should I do now? Do I need to wait for some settling after I have stop slave on B and before starting the actual mysqldump? Mysql Version is 5.0.51b on A and B, 5.0.77 on C, operating system is linux. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Totalling Counts done in Subqueries
Hi all, I'm hoping someone can help me with this please. Is there a way to total counts done in subqueries? So I want to do: -=-=-=-=-=-=-= SELECT `Notes`.`note_id`, `Notes`.`last_updated_datetime`, `Notes`.`event_date`, `Notes`.`subject`, `Notes`.`summary`, `Notes`.`content`, (SELECT COUNT(*) FROM `Notes__Issues` WHERE `Notes`.`note_id` = `Notes__Issues`.`note_id` ) AS 'linked_issues_count', (SELECT COUNT(*) FROM `Notes__People` WHERE `Notes`.`note_id` = `Notes__People`.`note_id` ) AS 'linked_people_count', (SELECT COUNT(*) FROM `Notes__Organisations` WHERE `Notes`.`note_id` = `Notes__Organisations`.`note_id` ) AS 'linked_organisations_count', (linked_issues_count + linked_people_count + linked_organisations_count) AS 'total' FROM `Notes` WHERE ( `added_user_id` = '6' ) ORDER BY last_updated_datetime DESC LIMIT 25 -=-=-=-=-=-=-=-=- Note the: (linked_issues_count + linked_people_count + linked_organisations_count) AS 'total' That's the bit that doesn't work. Is there a way? Thanks Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Totalling Counts done in Subqueries
Is there a way to total counts done in subqueries? Select expression aliases can't be referenced at the same level. You have to create another outer level ... SELECT note_id, last_updated_datetime,event_date,subject,summary,content, linked_issues_count,linked_people_count, linked_organisations_count, linked_issues_count + linked_people_count + linked_organisations_count AS total FROM ( SELECT note_id,last_updated_datetime,event_date,subject,summary,content, (SELECT COUNT(*) FROM Notes__Issues WHERE Notes.note_id = Notes__Issues.note_id ) AS 'linked_issues_count', (SELECT COUNT(*) FROM Notes__People WHERE Notes.note_id = Notes__People.note_id ) AS 'linked_people_count', (SELECT COUNT(*) FROM Notes__Organisations WHERE Notes.note_id = Notes__Organisations.note_id ) AS 'linked_organisations_count', FROM Notes WHERE added_user_id = 6 ORDER BY last_updated_datetime DESC LIMIT 25 ) AS tmp; PB http://www.artfulsoftware.com - Nigel Peck wrote: Hi all, I'm hoping someone can help me with this please. Is there a way to total counts done in subqueries? So I want to do: -=-=-=-=-=-=-= SELECT `Notes`.`note_id`, `Notes`.`last_updated_datetime`, `Notes`.`event_date`, `Notes`.`subject`, `Notes`.`summary`, `Notes`.`content`, (SELECT COUNT(*) FROM `Notes__Issues` WHERE `Notes`.`note_id` = `Notes__Issues`.`note_id` ) AS 'linked_issues_count', (SELECT COUNT(*) FROM `Notes__People` WHERE `Notes`.`note_id` = `Notes__People`.`note_id` ) AS 'linked_people_count', (SELECT COUNT(*) FROM `Notes__Organisations` WHERE `Notes`.`note_id` = `Notes__Organisations`.`note_id` ) AS 'linked_organisations_count', (linked_issues_count + linked_people_count + linked_organisations_count) AS 'total' FROM `Notes` WHERE ( `added_user_id` = '6' ) ORDER BY last_updated_datetime DESC LIMIT 25 -=-=-=-=-=-=-=-=- Note the: (linked_issues_count + linked_people_count + linked_organisations_count) AS 'total' That's the bit that doesn't work. Is there a way? Thanks Nigel No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.238 / Virus Database: 270.12.8/2086 - Release Date: 04/29/09 06:37:00
Re: Totalling Counts done in Subqueries
Peter Brawley wrote: Is there a way to total counts done in subqueries? Select expression aliases can't be referenced at the same level. You have to create another outer level ... alternatively use variables: mysql select @first := 1 as value1, @second := 2 as value2, @fir...@second as total; +++---+ | value1 | value2 | total | +++---+ | 1 | 2 | 3 | +++---+ 1 row in set (0.03 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Totalling Counts done in Subqueries
Thanks Nigel and Peter, I went for Nigel's solution below. Both very useful, learnt a lot, thank you. Cheers, Nigel nigel wood wrote: Is there a way to total counts done in subqueries? Never done this but my educated guess is: SELECT `Notes`.`note_id`, `Notes`.`last_updated_datetime`, `Notes`.`event_date`, `Notes`.`subject`, `Notes`.`summary`, `Notes`.`content`, @linked_issues_count := (SELECT COUNT(*) FROM `Notes__Issues` WHERE `Notes`.`note_id` = `Notes__Issues`.`note_id` ) AS 'linked_issues_count', @linked_people_count := (SELECT COUNT(*) FROM `Notes__People` WHERE `Notes`.`note_id` = `Notes__People`.`note_id` ) AS 'linked_people_count', @linked_organisations_count :=( SELECT COUNT(*) FROM `Notes__Organisations` WHERE `Notes`.`note_id` = `Notes__Organisations`.`note_id` ) AS 'linked_organisations_count', (@linked_issues_count + @linked_people_count + @linked_organisations_count) AS 'total' FROM `Notes` WHERE ( `added_user_id` = '6' ) ORDER BY last_updated_datetime DESC LIMIT 25 hope that helps Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is there a MySQL 5.4 Speed advantage for MyISAM tables?
I see MySQL 5.4 is out. http://www.mysql.com/news-and-events/generate-article.php?id=1602 Sun claims there are speed improvements for Innodb and ClusterDb tables, but is there any reason to upgrade if I'm only using MyISAM tables? Also I didn't see a Windows binary download. Does this mean I have to compile the source from one of the Linux distros? What compiler do I use? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Where the hell did 5.4 come from?
Have I been in a coma or something? WTF happened to 5.2 and 5.3? Hell, we're still on 5.0.51 and 5.1 just came out a month or two ago right? -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 30, 2009 7:40 AM To: mysql@lists.mysql.com Subject: Is there a MySQL 5.4 Speed advantage for MyISAM tables? I see MySQL 5.4 is out. http://www.mysql.com/news-and-events/generate-article.php?id=1602 Sun claims there are speed improvements for Innodb and ClusterDb tables, but is there any reason to upgrade if I'm only using MyISAM tables? Also I didn't see a Windows binary download. Does this mean I have to compile the source from one of the Linux distros? What compiler do I use? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SELECT of records that have a matching record in a many to many table
Can someone please help me with this one? I'm trying to SELECT from a table only those records that have a record, matching a search term, in a table related by a many to many relationship. The many to many relationship is in a mapping/junction table. Here's an example of what I have so far: -=-=-=-=-=-=-=-=-=-=-=-=- SELECT `Notes`.`note_id` FROM `Notes` INNER JOIN `Notes__Districts` ON `Notes__Districts`.`note_id` = `Notes`.`note_id` LEFT JOIN `Districts` ON `Districts`.`district_id` = `Notes__Districts`.`district_id` WHERE `Districts`.`name` REGEXP 'bradford'; -=-=-=-=-=-=-=-=-=-=-=-=- Hopefully someone can see what I'm trying to do here and point me in the right direction :) Maybe I need to use a subquery? I've got a feeling I can do this without that but can't get my head round how to set up the JOINs in this case with having to use three tables in the one query, I'm only used to two tables at once. I couldn't find any tutorials that cover this. Thanks in advance, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Where the hell did 5.4 come from?
My thoughts exactly! This article might help: http://dev.mysql.com/tech-resources/articles/mysql-54.html It worries me though that 5.1 went through a large number of alpha releases, then a set of beta releases before the GA release came out. It looks like they've thrown 5.4 straight out without anyone even being aware that it existed! Hell, 6.0 is on its tenth release and it's still in alpha. Like you say, it'd be interesting to see which blackhole 5.2 and 5.3 fell into...! Andy Daevid Vincent wrote: Have I been in a coma or something? WTF happened to 5.2 and 5.3? Hell, we're still on 5.0.51 and 5.1 just came out a month or two ago right? -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 30, 2009 7:40 AM To: mysql@lists.mysql.com Subject: Is there a MySQL 5.4 Speed advantage for MyISAM tables? I see MySQL 5.4 is out. http://www.mysql.com/news-and-events/generate-article.php?id=1602 Sun claims there are speed improvements for Innodb and ClusterDb tables, but is there any reason to upgrade if I'm only using MyISAM tables? Also I didn't see a Windows binary download. Does this mean I have to compile the source from one of the Linux distros? What compiler do I use? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Where the hell did 5.4 come from?
Oracle owns the mess now. I assume the next release will be 5.11, followed by 5.11i, and then finally dropping of the 5 to be in line with how they manager their os, leave it to just be 11i. To ensure it is smooth they will change the license and add $5k in suport costs. From: Andy Shellam [andy-li...@networkmail.eu] Sent: Thursday, April 30, 2009 2:19 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Where the hell did 5.4 come from? My thoughts exactly! This article might help: http://dev.mysql.com/tech-resources/articles/mysql-54.html It worries me though that 5.1 went through a large number of alpha releases, then a set of beta releases before the GA release came out. It looks like they've thrown 5.4 straight out without anyone even being aware that it existed! Hell, 6.0 is on its tenth release and it's still in alpha. Like you say, it'd be interesting to see which blackhole 5.2 and 5.3 fell into...! Andy Daevid Vincent wrote: Have I been in a coma or something? WTF happened to 5.2 and 5.3? Hell, we're still on 5.0.51 and 5.1 just came out a month or two ago right? -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 30, 2009 7:40 AM To: mysql@lists.mysql.com Subject: Is there a MySQL 5.4 Speed advantage for MyISAM tables? I see MySQL 5.4 is out. http://www.mysql.com/news-and-events/generate-article.php?id=1602 Sun claims there are speed improvements for Innodb and ClusterDb tables, but is there any reason to upgrade if I'm only using MyISAM tables? Also I didn't see a Windows binary download. Does this mean I have to compile the source from one of the Linux distros? What compiler do I use? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.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: Now() : SQL syntax error. But why?
Thanks, Scott. I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! Thanks, again. Have a great day. :) On Thu, Apr 30, 2009 at 12:52 PM, Scott Haneda talkli...@newgeo.com wrote: On Apr 29, 2009, at 11:29 AM, Antonio PHP wrote: This is MySQL data structure. - I underlined where it causes the error message. (datetime) `id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL `Revenue` mediumint(6) NOT NULL, `Company_Size` mediumint(6) NOT NULL, `Ownership` tinyint(1) NOT NULL, `Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, *`Created` datetime NOT NULL, *PRIMARY KEY (`id_Company`), KEY `Ownership` (`Ownership`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE CASCADE ON UPDATE CASCADE; Next time can you include unmodified SQL so it is a copy and paste for me, rather than debugging what changes you made that are causing error. Here is php script - $sql = INSERT INTO company SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source' *Created=NOW() // if I remove this line it works fine. *; mysql_query ($sql) or die (mysql_error()); Same here, as I am not sure your edits are just edits, or the lack of a comma after the job source variable is the issue. This works on my end: $Name = 'Tom'; $Revenue = '100'; $Company_Size = '500'; $Ownership= 'partner'; $Homepage = 'example.com'; $Job_Source = 'friend'; $sql = INSERT INTO mailing SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source', Created=NOW(); echo $sql; mysql_query ($sql) or die (mysql_error()); -- Scott * If you contact me off list replace talklists@ with scott@ *
Re: Now() : SQL syntax error. But why?
Always echo out your SQL string, it will make it a lot more obvious. You want to see the result. I php concatenated string can be confusing at times. Also, you are not escaping your data, so if you had a word of 'stops, here' that would break it as well. So in your case, you very well may break it by changing the data you put in. You could also do something like stuffing drop database foo; into your data, and be in for real fun. Pass every string to http://us2.php.net/mysql_real_escape_string On Apr 30, 2009, at 9:27 PM, Antonio PHP wrote: I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
