Access MySQL Enterprise Monitor UI Remotely
Hi all, How can I access MySQL Enterprise Monitor webpage remotely. I don't want to access it through the localhost, but would like to access it like this: https://192.1.1.1:18443. Where can I change the settings so that the tomcat can listen for incoming connections from my network card, rather than the localhost address (127.0.0.1). TIA. Kind Regards Blessing Kamutande| SunDBA | ISS Infrastructure |DCO ~~ This e-mail is subject to the Telkom SA SOC Ltd electronic communication legal notice, available at : http://www.telkom.co.za/TelkomEMailLegalNotice.PDF http://www.telkom.co.za/TelkomEMailLegalNotice.PDF ~~
replication issue from ec2 to my network ..
Hi, I have configured replication from EC2 mysql instance to another mysql instance on our network via vpn in between. Sometimes it is showing no errors on the slave but the slave falls behind . When I issue the commands stop slave ; start slave ; it again catches up with the master. What is the reason for this ? how to resolve this ? Thanks, Umapathi. umapath...@gmail.com
Fwd: strange mysql update ..
Any update from anybody ? -- Forwarded message -- From: umapathi b umapath...@gmail.com Date: Thu, Sep 8, 2011 at 4:28 AM Subject: Re: strange mysql update .. To: Ananda Kumar anan...@gmail.com Cc: mysql@lists.mysql.com Here is the o/p after the update .. user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com - Umapathi On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar anan...@gmail.com wrote: Can you lets us know what is the output of select * from user_info where user_id=16078845; On Thu, Sep 8, 2011 at 1:02 PM, umapathi b umapath...@gmail.com wrote: I wanted to change the login_date of one user . The original data of that user is like this .. select * from user_info where user_id = 16078845 \G *** 1. row *** user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' | +-+--+-+ But I could not get any record in the table with the updated login_date .. mysql select * from user_info where login_date like '2011-08-05%' ; Empty set (0.67 sec) So my question is what happened exactly ? Why no records updated ? Help is highly appreciated in this regard .. - Umapathi umapath...@gmail.com
strange mysql update ..
I wanted to change the login_date of one user . The original data of that user is like this .. select * from user_info where user_id = 16078845 \G *** 1. row *** user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' | +-+--+-+ But I could not get any record in the table with the updated login_date .. mysql select * from user_info where login_date like '2011-08-05%' ; Empty set (0.67 sec) So my question is what happened exactly ? Why no records updated ? Help is highly appreciated in this regard .. - Umapathi umapath...@gmail.com
Re: strange mysql update ..
Here is the o/p after the update .. user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com - Umapathi On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar anan...@gmail.com wrote: Can you lets us know what is the output of select * from user_info where user_id=16078845; On Thu, Sep 8, 2011 at 1:02 PM, umapathi b umapath...@gmail.com wrote: I wanted to change the login_date of one user . The original data of that user is like this .. select * from user_info where user_id = 16078845 \G *** 1. row *** user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' | +-+--+-+ But I could not get any record in the table with the updated login_date .. mysql select * from user_info where login_date like '2011-08-05%' ; Empty set (0.67 sec) So my question is what happened exactly ? Why no records updated ? Help is highly appreciated in this regard .. - Umapathi umapath...@gmail.com
Efficient use of sub queries?
I was wondering if any one could point out potential problems with the following query or if there was a better alternative From a list of users I want to return all who don't have all the specified user_profile options or those who do not have at least one preference set to 1. The following query seems to work fine. SELECT DISTINCT(c.id) FROM user c WHERE c.id IN (901,907) AND ((SELECT COUNT(id) FROM user_profile WHERE option_id in (747,749) AND user_id=c.id) 2 OR (SELECT COUNT(id) FROM user_profile WHERE option_id in (747,749) AND user_id=c.id AND preference = 1) != 1 ) The table definition CREATE TABLE `user_profile` ( `id` int(11) NOT NULL AUTO_INCREMENT, `option_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `preference` decimal(10,2) DEFAULT '0.00', `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_profile_user_id` (`user_id`), KEY `user_profile_option_id` (`option_id`), CONSTRAINT `user_profile_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`), CONSTRAINT `user_profile_option_id` FOREIGN KEY (`option_id`) REFERENCES `option` (`id`), ) ENGINE=InnoDB; Thanks
Performing subtraction between fields
Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fwd: Re: Help Save MySQL
Lets register the domain www.keep-mysql-open.com, build a champain, and ask all our partners, clients and friends to support to it? And let's make some noise... Let's engage the society and big companies like Google, Facebook, twitter, 37Signals, Amazon, Percona, etc. And starting talking to the media like TechCrunch.com, BusinessWeek, ZD.Net, etc. Without society's engagement we won't acomplish anything! Regards, Bruno B. B. Magalhães Sócio-Diretor de Negócios e Tecnologia BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. Quoting mos mo...@fastmail.fm: Instead of forming a group hug and singing kumbuya to save MySQL, why don't we take some action to prevent Oracle from keeping (destroying/crippling) MySQL? Who are the regulatory bodies that will decide whether Oracle can keep MySQL? Both in Europe and North America? Asia? What are their email addresses? They may not realize how destructive Oracle can be to the future of MySQL and the number of MySQL database installations this will affect. I think we should start a writing campaign to the regulatory bodies to force (or encourage) them to have Oracle to sell or spin off MySQL to another party. What we need is ACTION. So if someone could provide us with the names of these regulatory parties and their email addresses, we can try to light a fire under them to put pressure on Oracle. A web page to SaveMySQL would also help get our message across. What do you think guys? Can someone provide us with the names and email addresses of the regulatory bodies, or would you rather I post the lyrics to kumbuya? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br - End forwarded message - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fwd: Re: Help Save MySQL
Peter and all, I will register and host it, without charging anyone anything, neither use it for any kind of advertising or anything besides to create a hub for this champain. I just want to give something back to the initiative and the community that helped me so much in the past... I suggest using Monty (if he aggrees) letter as base and start spreeding the word. Bruno B. B. Magalhães Sócio-Diretor de Negócios e Tecnologia BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. Quoting Peter Brawley peter.braw...@earthlink.net: Twenty bucks a year to hold the domain name. I'll do it if a couple of folks volunteer to help set up the page. PB - Bruno B. B. Magalhães wrote: Lets register the domain www.keep-mysql-open.com, build a champain, and ask all our partners, clients and friends to support to it? And let's make some noise... Let's engage the society and big companies like Google, Facebook, twitter, 37Signals, Amazon, Percona, etc. And starting talking to the media like TechCrunch.com, BusinessWeek, ZD.Net, etc. Without society's engagement we won't acomplish anything! Regards, Bruno B. B. Magalhães Sócio-Diretor de Negócios e Tecnologia BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. Quoting mos mo...@fastmail.fm: Instead of forming a group hug and singing kumbuya to save MySQL, why don't we take some action to prevent Oracle from keeping (destroying/crippling) MySQL? Who are the regulatory bodies that will decide whether Oracle can keep MySQL? Both in Europe and North America? Asia? What are their email addresses? They may not realize how destructive Oracle can be to the future of MySQL and the number of MySQL database installations this will affect. I think we should start a writing campaign to the regulatory bodies to force (or encourage) them to have Oracle to sell or spin off MySQL to another party. What we need is ACTION. So if someone could provide us with the names of these regulatory parties and their email addresses, we can try to light a fire under them to put pressure on Oracle. A web page to SaveMySQL would also help get our message across. What do you think guys? Can someone provide us with the names and email addresses of the regulatory bodies, or would you rather I post the lyrics to kumbuya? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br - End forwarded message - No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.427 / Virus Database: 270.14.113/2573 - Release Date: 12/18/09 07:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help Save MySQL
Hi Guys, Let's say that every mysql developer (here I am thinking only persons, not companies) that wants mysql to go forward would contribute from $500,00 to $1500,00, how much are we talking about? And we would have a 100% community owned and community driven open source initiative... Course, there are many others management problems and legal issues to solve, but if anybody would join me I would be the first one! And also would be a REMARKABLE adventure and maybe the next step for the open source initiatives around the world... Best Regards Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9695-2263 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Dec 17, 2009, at 2:16 PM, Neil Aggarwal wrote: If that’s what the price is going to be then perhaps I should offer 2€ or maybe MySQL users should get together submit a realistic offer. This sounds interesting... Get a community effort to accept donations and purchase MySQL. Then, put it under the GPL and make sure nobody owns it. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS virtual server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help Save MySQL
Maybe a three-years consul composed from and elected by the community (by community I mean the people that contributed with funds, not the user community) by voting? Regards, Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9695-2263 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Dec 17, 2009, at 3:03 PM, Martijn Tonies wrote: Hey, Let's say that every mysql developer (here I am thinking only persons, not companies) that wants mysql to go forward would contribute from $500,00 to $1500,00, how much are we talking about? And we would have a 100% community owned and community driven open source initiative... Course, there are many others management problems and legal issues to solve, but if anybody would join me I would be the first one! And also would be a REMARKABLE adventure and maybe the next step for the open source initiatives around the world... Brilliant... now, whose gonna run the project? ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database 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/mysql?unsub=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
Martjin, I really don't like to point fingers or anything like that, but the simple fact Oracle owns the MySQL copyrights is by it self very concerning, as all our investments (time and money) could be lost over night, if Oracles decides to close de source or change it's licensing policies. Many could say Oh, they will not do that, because they promised not to., as an old professor of mine said: What isn't written, does not count! Everything else is here say, and there is no legal or moral grounds. As a sailor I saw what Larry Ellisson did with the oldest and most prestigious match race in the sport of sailing, the America's Cup. He and Ernesto Bertarelli (a swiss billionaire) are fighting in the New York Supreme Court for over 3 years for power, and almost 160 years of history and sportsmanship are being destroyed. Personally, that's not Ellisson's fault because Bertarelli is the one who is trying to subvert the rules (the Deed of Gift written in 1852 and that drives the competition until today http://en.wikipedia.org/wiki/Deed_of_Gift), but this shows the kind of mentality that Ellisson works with: Until the last consequences. More info at: http://www.yachtingmagazine.com/article.jsp?ID=170610 Best Regards, Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9695-2263 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Dec 17, 2009, at 3:33 PM, Martijn Tonies wrote: John, Another read on the subject: http://blogs.the451group.com/opensource/2009/12/10/the-case-against-the-case-against-oracle-mysql/ Enjoy. With regards, Martijn Tonies Upscene Productions http://www.upscene.com I see some of your point Martin but I think the eu would look at that letter and see the author is stating 'MySQL has been used as a pricing lever'. That single factor should be enough for them to be very concerned by an acquisition as removing an effective pricing lever from the market by acquisition is anti-competitive and helps increase or maintain high prices. I don't believe you could use any other open source database as a pricing lever in the same way because none are as mature or offer the levels of support that MySQL does and no other open source system can boast the performance benefits (especially with ndbcluster) or the availability of suitably trained and certified people to support their products. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
right join troubles
I'm trying to select all members who have not registered for an event. I have tables 'members', 'events', and 'events_members', the latter a join table with event_id and member_id columns. The closest I've gotten is with this query: SELECT m.id, m.first_name, m.last_name FROM members AS m RIGHT JOIN events_members AS em ON (em.event_id = 10 AND m.id != em.member_id) ORDER BY m.last_name ASC; This returns an empty set IF there are no records at all in events_members with event_id = 10. But, in that case, I want to receive ALL members. However, if I add a single record with event_id = 10, I then get the expected list of all OTHER members. How can I modify this query so that, when there are 0 registered members for a particular event, I get back all members? Obviously, I could always first check for the existence of the event_id in the join table and, if not found, run the select on the members table. But I doubt that that's the best option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: right join troubles
On 09/19/2009 10:07 AM, Thomas Spahni wrote: On Sat, 19 Sep 2009, b wrote: I'm trying to select all members who have not registered for an event. I have tables 'members', 'events', and 'events_members', the latter a join table with event_id and member_id columns. The closest I've gotten is with this query: SELECT m.id, m.first_name, m.last_name FROM members AS m RIGHT JOIN events_members AS em ON (em.event_id = 10 AND m.id != em.member_id) ORDER BY m.last_name ASC; This returns an empty set IF there are no records at all in events_members with event_id = 10. But, in that case, I want to receive ALL members. However, if I add a single record with event_id = 10, I then get the expected list of all OTHER members. How can I modify this query so that, when there are 0 registered members for a particular event, I get back all members? Obviously, I could always first check for the existence of the event_id in the join table and, if not found, run the select on the members table. But I doubt that that's the best option. I think that you need two steps: CREATE TABLE events_members_tmp SELECT * FROM events_members WHERE event_id = 10; SELECT m.id, m.first_name, m.last_name FROM members AS m LEFT JOIN events_members_tmp AS em ON m.id = em.member_id WHERE em.member_id IS NULL ORDER BY m.last_name ASC; Having written this it appears that it could work in just one step as well: SELECT m.id, m.first_name, m.last_name FROM members AS m LEFT JOIN events_members AS em ON em.event_id = 10 AND m.id = em.member_id WHERE em.member_id IS NULL ORDER BY m.last_name ASC; That works great. I was sure that I needed a right, rather than left, join. But this makes perfect sense. Thanks a bunch! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
UNIQUE KEY vs UNIQUE INDEX
Are UNIQUE KEY UNIQUE INDEX two ways of specifying the same thing? If not, what are the differences? Feel free to tell me to RTFM but please post manual chapters. I've been looking but haven't been able to find anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: UNIQUE KEY vs UNIQUE INDEX
On 07/30/2009 02:23 PM, Joerg Bruehe wrote: Hi ! mos wrote: At 09:13 AM 7/30/2009, b wrote: Are UNIQUE KEY UNIQUE INDEX two ways of specifying the same thing? If not, what are the differences? Feel free to tell me to RTFM but please post manual chapters. I've been looking but haven't been able to find anything. They are the same thing. If you meant Primary Key and Unique Index then they too are basically the same except Primary Key is NOT NULL and the optimizer will infer the primary key when doing RI joins if an index is not specified. No, PK I understand. There may be other subtle differences that others can comment on. For all practical purposes, I agree. From the database (or SQL) theory point of view, a unique constraint is a logical concept (there cannot be any two cars having the same registration number), and any index is a physical means of implementation (to speed up access). I have never heard of any DBMS implementing a unique constraint in any way but using a unique index, but it could be done: at a huge performance cost (sequential search through the table). This is what I was thinking, also. For primary key, there is also the school that holds a primary key cannot change, it is a permanent attribute. If you share that idea, then there is a difference to the uniqueness concept. Yes, I treat a PK as being something that's over and above simply being unique. But this touches upon the reason for my question: I guess I was wondering if they might be subtly different, though merging the two would result in a PK :-) Take the example of a car: It has a manufacturer number which is permanent, but the registration will change when it is moved (or sold) to another area or country (depending on local law). So the manufacturer number could be used as the primary key (will never change), whereas the registration is guaranteed to be unique (at any time) but may vary over time. Thanks, both, for your replies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Windows version
russbucket wrote: I agree with you, but phpMyAdmin is not available for Windows that I'm aware of. The person I'm helping today is a windows user and except for a little Access Experience does not do databases well. I have the database set up in mysql so thats what I am hoping to get him using. Other choice is use Excel. So I hope one of these recommendations works. Thank for your response. As phpMyadmin is a web application run through a web server, there is no Linux or Windows version. They all work on any web server. You just un zip/rar/tar etc. the latest version to your web server folder as explained in the documentation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
COUNT from 2 tables
I'm trying to get a count for columns in 2 tables at once. I have sessions and downloads tables and would like to get some basic stats. mysql describe sessions; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | created | datetime | YES | | NULL|| | user_id | int(10) unsigned | NO | MUL | NULL|| +-+--+--+-+-++ 3 rows in set (0.01 sec) mysql describe downloads; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | created | datetime | YES | | NULL|| | user_id | int(10) unsigned | NO | MUL | NULL|| | item_file_id | int(10) unsigned | NO | MUL | NULL|| | session_id | int(10) unsigned | NO | | NULL| | | path | text | NO | | NULL|| +--+--+--+-+-++ 6 rows in set (0.01 sec) mysql SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM sessions GROUP BY month; +---++ | month | num_logins | +---++ | July | 6 | | June |214 | | May |150 | +---++ 3 rows in set (0.00 sec) mysql SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads FROM downloads GROUP BY month; +---+---+ | month | num_downloads | +---+---+ | June | 389 | | May | 220 | +---+---+ 2 rows in set (0.01 sec) In trying to get the count from both tables at once, the logins are no longer being summed as expected: mysql SELECT MONTHNAME(s.created) AS month, COUNT(s.id) AS num_logins, COUNT(d.id) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month; +---++---+ | month | num_logins | num_downloads | +---++---+ | July | 6 | 0 | | June |539 | 389 | | May |350 | 220 | +---++---+ 3 rows in set (0.31 sec) Is this possible to do without using a sub-query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: COUNT from 2 tables
On 07/08/2009 03:33 PM, Gary Smith wrote: Off the top of my head, try this. SELECT MONTHNAME(s.created) AS month, sum(if(ifnull(s.id,0) 0, 1, 0)) AS num_logins, sim(if(ifnull(d.id, 0) 0, 1, 0)) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d ON d.session_id = s.id GROUP BY month Nope, I'm still getting those same incorrect sums. Thanks, though. It seems to me that the problem is that I'm grouping by the month for one table but counting from both. I'd paste the output here but I just upgraded Fedora and the BETA (wtf?) version of Thunderbird crashes when I paste into an email (how the earlier paste worked I don't know). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: COUNT from 2 tables
On 07/08/2009 06:11 PM, Gary Smith wrote: Create a view or sub select, denormalizing the data and then group it. select month, sum(login) as num_logins, sum(download) as num_downloads from ( select monthname(s.created) as month_name , if(ifnull(s.id, 0) 0, 1, 0) as login , if(ifnull(d.id, 0) 0, 1, 0) as download from sessions s left join downloads d on s.id = d.session_id ) group by month name; which is the left table? downloads or logins? If logins shouldn't it be on the left side of the ON statement? (I'm not sure) My understanding is that is the ON statement that runs the left join, not which table is listed first (I could be wrong though -- chime in if you know the definitive answer please). Anyway, try this and see if it gets you closer. I had to change month_name to month and add AS foo just before the GROUP BY (Every derived table must have its own alias) but still no joy. I'm still getting those same incorrect numbers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Database Import from Oracle
Hi guys, the easiest is to use MySQL Migration Toolkit, I've sucessfuly used it with SQL Server (2000 and 2008) and Oracle 9i, all with simple structures and simple data, but worked VERY well... Regards, Bruno B. B. Magalhães Sócio-Diretor de Negócios e Tecnologia BLACKBEAN CONSULTORIA Rua Real Grandeza 193, Sala 210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9278-0082 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Mar 24, 2009, at 11:21 AM, mos wrote: At 06:28 AM 3/24/2009, you wrote: Hello @all. I`ve got the question how mysql will have to be set up, that it can handle round about 7.000.000 records most efficiently. What do you think about the the hard and software requirements in order to match the best combination? The data will come from oracle, so it would be interessting to, how I will have to import the data. Does this work once, or will I have to divide the data in several parts for import? Best Greetings, Frank Frank, The fastest way to import data from Oracle would be as a CSV file and then use Load Data Infile to import the data into MySQL. 7 million records should load in a couple of minutes. Make sure you confirm the row counts are equal after you have imported the data. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=brunomagalh...@blackbean.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What's wrong with user defined variable in this MySQL 5.1 example?
mos wrote: I'm using MySQL 5.1.30 (WinXP) and user defined variables don't seem to be working properly if the Select statement is sorted. Here is a simple example: CREATE TABLE `tmp` ( `Purch_Date` date DEFAULT NULL, `Product` char(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Here are the values that I entered in this order: Purch_DateProduct 2007-01-01A 2007-01-04B 2008-05-04B 2008-12-04B 2001-01-04D 2001-01-04C Now here is the Select statement that is failing: set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date OUTPUT: @num:=...@num+1Purch_Date 52001-01-04 12007-01-01 22007-01-04 32008-05-04 42008-12-04 The earliest date 2001-01-04 should have 1 and not 5 as the first column in the results. Why isn't the column @num:=...@num+1 sorted in ascending order since it was grouped by purch_date? Even if I explicitly tell it to sort by purch_date: set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date order by purch_date; I still get the same results. This has worked fine in MySQL 5.0.1 but is giving me these strange results in MySQL 5.1.30. Am I doing something wrong here? TIA Mike I suspect that what's happening is MySQL isn't selecting the rows in order but displaying them ordered by purch_date. That is, the other column value is being assigned to its row *before* the ordering occurs. That's just a guess though, really. I might be way off here. You could probably test this by selecting and ordering purch_date in a sub-query and assigning your other column to the results of that. (I think) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What's wrong with user defined variable in this MySQL 5.1 example?
mos wrote: At 03:14 AM 1/19/2009, b wrote: mos wrote: I'm using MySQL 5.1.30 (WinXP) and user defined variables don't seem to be working properly if the Select statement is sorted. Here is a simple example: CREATE TABLE `tmp` ( `Purch_Date` date DEFAULT NULL, `Product` char(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Here are the values that I entered in this order: Purch_DateProduct 2007-01-01A 2007-01-04B 2008-05-04B 2008-12-04B 2001-01-04D 2001-01-04C Now here is the Select statement that is failing: set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date OUTPUT: @num:=...@num+1Purch_Date 52001-01-04 12007-01-01 22007-01-04 32008-05-04 42008-12-04 The earliest date 2001-01-04 should have 1 and not 5 as the first column in the results. Why isn't the column @num:=...@num+1 sorted in ascending order since it was grouped by purch_date? Even if I explicitly tell it to sort by purch_date: set @num:=0;select @num:=...@num+1, Purch_Date from tmp group by purch_date order by purch_date; I still get the same results. This has worked fine in MySQL 5.0.1 but is giving me these strange results in MySQL 5.1.30. Am I doing something wrong here? TIA Mike I suspect that what's happening is MySQL isn't selecting the rows in order but displaying them ordered by purch_date. That is, the other column value is being assigned to its row *before* the ordering occurs. That's just a guess though, really. I might be way off here. You could probably test this by selecting and ordering purch_date in a sub-query and assigning your other column to the results of that. (I think) b, I replaced it with: set @num:=0;select * from (select @num:=...@num+1, purch_date from tmp order by purch_date) as t1; and that produces the correct sort order. @num:=...@num+1purch_date 1 2001-01-04 2 2001-01-04 3 2007-01-01 4 2007-01-04 5 2008-05-04 6 2008-12-04 This means anytime a user defined variable like @num := @num + 1 and is used in a query that is sorted, it is going to have to be changed to a subquery. For example, if someone was using it to display detail line item numbers on an invoice, it now has to be turned into a subselect. This breaks a lot of code. Yes your solution works (thanks for that), but I still think this is a bug. Mike Glad to help. The sub-query idea was only a suggestion for testing my idea, though. There's likely a better solution for production. Maybe you could create a view around the purch_date select, for instance. I'd think there'd be a few alternate methods for getting the results you want, some more efficient than others. As for whether it's a bug or not, I'm really not so sure I'd call it that. The behaviour seems to me to be logical because ordering always comes after row selection. It would take far too long to collect a set of results, otherwise. Like many things SQL, it might not be necessarily what most people would want but, in the end, something most of us can grudgingly agree is the best course. I know much more about PG than MysSQL and i'm pretty sure PG would do the same. I'd like to run some tests, actually. b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
help refactoring query
I'm having some difficulty getting my head around a particular query. I'd like to make this a view once I get something working. However, all I've been able to come up with uses a sub-query. So, no view on the horizon. I have 3 tables: users id, (etc. the usual) disciplines id, name (ie. film, photography, writing, etc.) disciplines_users discipline_id, user_id Each user may have one or more discipline. The view I'm looking for shows the total number of users who have a particular discipline. NOTE: the sum of the totals is greater than the total number of users, which is by design. SELECT name, COUNT(discipline.u_id) AS total FROM ( SELECT du.discipline_id, du.user_id as u_id, d.name FROM disciplines_users AS du LEFT JOIN disciplines AS d ON d.id = du.discipline_id ) AS discipline GROUP BY discipline.name ORDER BY discipline.name; +-+---+ | name| total | +-+---+ | Dance | 176 | | Film and Television | 376 | etc. I've a feeling that this could be done without that sub-query and using another join. If not, I might make the sub-query its own view and see what the performance is like. I'd appreciate any suggestions, especially any pointers on refactoring sub-queries into joins, in general. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Cache Hosts - Which are tempting max_connect_errors?
Hi, Is there a way in 4.X or 5.0 to tell which hosts are running up the counter towards max_connect_errors? We'd like to be able to monitor this and act accordingly? I've seen that they suggest to set it to 9 in 13.5.5.2 FLUSH Syntax to avoid it. I'm just wondering why they didn't say to just use --skip-cache-host . Thanks, Tuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SssS Generic modeling...
`TB1` ADD `CL14` VARCHAR(200) NOT NULL DEFAULT '' AFTER `CL14`; ALTER TABLE `TB1` ADD `CL15` VARCHAR(200) NOT NULL DEFAULT '' AFTER `CL15`; And the same would work for relations, validations, processes and workflows... And the business objects (for example Java, PHP or Python) would be or generated on instantiation or static build when needed. Anyone even implemented some thing like that? Is there any production experience? Seams that Microsoft Dynamics works like that (but with the metadata description stored in XML). Me and my friends were thingking about going futer with the development... Any one willing to help this little project? Best Regards, Bruno B. B. Magalhães Sócio-Diretor de Negócios e Tecnologia BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine Summer Issue Released
The next issue of MySQL Magazine is now available for download. Get it while it is hot! At forty-two great pages it is our biggest and best issue yet. This issue is anchored by the first annual MySQL Usage Survey results. Downloads at the MySQL Magazine homepage: http://www.mysqlzine.net Thanks to everyone who contributed. I couldn’t have done it without you all!! Keith Murphy -- Editor MySQL Magazine [EMAIL PROTECTED] http://www.mysqlzine.net (850) 637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: integer
I think you'll find that's BIGINT, max INT is 11 Moon's Father wrote: The maximum width is int(20). On Thu, May 22, 2008 at 5:15 PM, Saravanan [EMAIL PROTECTED] wrote: what kind of datatype it is int(50) parent_id| int(50) is that big int? Saravanan --- On Thu, 5/22/08, Norbert Tretkowski [EMAIL PROTECTED] wrote: From: Norbert Tretkowski [EMAIL PROTECTED] Subject: Re: integer To: mysql@lists.mysql.com Date: Thursday, May 22, 2008, 3:34 PM Am 22.05.2008, 14:04 +0530 schrieb Krishna Chandra Prajapati: I would to know the difference between the two int(10) and int (Used in create table ) MySQL has an excellent documentation, which of course also answers your question: http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html Norbert -- 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]
SELECT to return 0 instead of NULL?
Hi, I'm running a query : SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='hotspot'; But if there aren't any rows in radacct for the UserName of hotspot, it returns a NULL. Is there a way to change it to return 0 instead? (I can't change the application, but I can change the SQL) Thanks, Tuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql] Re: SELECT to return 0 instead of NULL?
On Fri, May 23, 2008 at 8:23 AM, Tuc at T-B-O-H.NET [EMAIL PROTECTED] wrote: Hi, I'm running a query : SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='hotspot'; But if there aren't any rows in radacct for the UserName of hotspot, it returns a NULL. Is there a way to change it to return 0 instead? (I can't change the application, but I can change the SQL) SELECT IFNULL( SUM(AcctSessionTime),0) FROM radacct WHERE UserName='hotspot'; Exactly what I need, thanks... I didn't see anything like that on the headers of : http://dev.mysql.com/doc/refman/5.0/en/select.html otherwise I wouldn't have asked. Thanks! Tuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Call for Articles for MySQL Magazine - Summer Issue
Hey everyone!! Just wanted you to know that it's that time. The summer issue of MySQL Magazine will be coming out July the 15th. That means that it is time to start gathering the articles. If you have interest in having your words read by thousands of people and having fame and fortune come to you (ok..maybe fame but probably not fortune) than you should submit an idea for an article. *Articles must be completed by June the 15th so don't delay*!! Have I got your interest? Send me an idea for an article to bmurphy AT paragon-cs.com. Anything relating to DBA work on MySQL or software development around MySQL will be considered. -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Practical hierarchies
', `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0', KEY `CatalogsCategoriesIndexA` (`CustomerID`,`CatalogID`,`CatalogCategoryID`,`CatalogCategoryActive`), KEY `CatalogsCategoriesIndexB` (` CatalogCategoryLevel1 ` ,` CatalogCategoryLevel2`,`CatalogCategoryLevel3`,`CatalogCategoryLevel4`), KEY `CatalogsCategoriesIndexC` (` CatalogCategoryCreatedBy `,`CatalogCategoryModifiedBy`,`CatalogCategoryRemovedBy`), KEY `CatalogsCategoriesIndexD` (` CatalogCategoryCreatedOn `,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Pros: SQL friendly so much less processing and queries required, very export / inport friendly Cons: Fixed deep and a little bit more difficult to implement the operating classes than parent/child relationships I would like very much to hear your opinions! Best regards to you all, Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical hierarchies
Just correcting my self, The hierarchical storage theory is nested set model, and not transverse. And also I know that oracle 10g has a hierarchical query, but I am more interested on pure SQL2 theories and solutions. And also, SHOULD (or could) MySQL have some type or recursive query? For example: SELECT RECURSIVE * FROM ProductsCategories USING ProductCategoryParentID = ProductCategoryID Is this syntax factive? Best regards, Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Apr 29, 2008, at 1:21 PM, Bruno B B Magalhães wrote: Hi everybody, I would like to discuss there hierarchical storage theory. Currently I have came across 2 types for storing hierarchical data (yes, I´ve read the article at mysql.com, and MANY others sites), but I would like to know your option about the day-to-day usage... I mean I´ve only used hierarchy for some small to medium projects, but I now I am planning a big account project (yes, I´ve researched many open- source solutions, but none was suitable for an enterprise level solution), and if someone has already walked this path... Well, I would like to share and discuss... How Oracle, SAP and Microsoft Dynamics did? Some one has some insight? ow they manage to store UNSPSC taxonomy data on their systems? Here is a table using parent and child relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryParentID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0', KEY `CatalogsCategoriesIndexA` (`CustomerID`,`CatalogID`,`CatalogCategoryParentID `,`CatalogCategoryID`,`CatalogCategoryActive`), KEY `CatalogsCategoriesIndexB` (` CatalogCategoryCreatedBy `,`CatalogCategoryModifiedBy`,`CatalogCategoryRemovedBy`), KEY `CatalogsCategoriesIndexC` (` CatalogCategoryCreatedOn `,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Pros: Easy to understand and implement the operating clas es, not export / inport friendly Cons: Recursive behavior, not suited for reports generation because of the recursivity Here is a table using transverse relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryLeftPosition` int(8) unsigned NOT NULL default '0', `CatalogCategoryRightPosition` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0', KEY `CatalogsCategoriesIndexA` (` CustomerID`,`CatalogID`,`CatalogCategoryID`,`CatalogCategoryActive`), KEY
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: I would like to move from 32-bit to 64-bit MySQL within the next year. Unfortunately, there is not a lot of documentation on migration or anything else regarding 64bit MySQL. My current setup consists of one master and two slaves (all using 32bit and MySQL 5.0). I am looking to add a 64bit slave to the mix. What is the difference between 32-bit and 64-bit? Is this a good idea? Can it be done? What would make this go wrong? I have made this migration on multiple servers. It has never been any trouble. Your biggest gain would probably be the ability to address more RAM. I would just dump the database from the 32-bit platform and import it into the 64-bit server. Keith -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? Seriously, 18 gb isn't too big to do a mysqldump. And I really wouldn't advise you trying to do a binary copy. You are just asking for trouble. Plan ahead and you can do this on a slave without any problem, import the data on the new server and sync it back up without any problems. -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance
Hi everybody, I am back to this list after a long period away due to work time restrictions... I have great news and a few interesting applications that I will release to the mysql community very soon, most probably as open source. But now I have a performance problem with a client of mine, that I was not able to solve... The problem is that I have a very large table in terms of data, about 7.000.000 financial transactions records, with the following table (translated from portuguese): CREATE TABLE `transactions` ( `client_id` int(5) unsigned zerofill NOT NULL default '0', `client_unit_id` int(4) unsigned zerofill NOT NULL default '', `client_property_id` int(6) unsigned zerofill NOT NULL default '00', `transaction_id` int(6) unsigned zerofill NOT NULL default '00', `transaction_account_id` int(3) unsigned zerofill NOT NULL default '000', `transaction_classification_id` int(3) unsigned NOT NULL default '0', `transaction_category_id` int(4) unsigned zerofill NOT NULL default '', `transaction_complement` varchar(200) NOT NULL, `transaction_date` date default NULL, `transaction_amount` decimal(16,2) NOT NULL, `transaction_parcel` varchar(8) NOT NULL, `transaction_nature` varchar(1) NOT NULL KEY `transactions_idx_1` (` client_id `,`client_unit_id`,`client_property_id`,`transaction_account_id`, ` transaction_classification_id ` ,` transaction_category_id `,`transaction_id`,`transaction_date`,`transaction_nature`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 And most the queries are similar to this one: SELECT UNIX_TIMESTAMP(transactions.transaction_date) AS date, transactions.transaction_complement AS complement, transactions.transaction_parcel AS parcel, transactions.transaction_amount AS amount, transactions.transaction_nature AS nature, transactions_categories.transaction_category_description AS category_description FROM transactions AS transactions LEFT JOIN transactions_categories AS transactions_categories ON transactions.transaction_category_id = transactions_categories.transaction_category_id WHERE transactions.client_id = :client AND transactions.client_unit_id = :unit AND transactions.transaction_date = :start_date AND transactions.transaction_date = :stop_date ORDER BY transactions.transaction_date, transactions.transaction_id ASC So the most important indexes are client_id , client_unit_id , client_property_id , transaction_account_id , transaction_classification_id , transaction_category_id , transaction_id , transaction_date , transaction_nature, and most of the time they are called together, I thing the most problematic part of those queries are the date range part, should I use a different index only for this column to maintain the index small? Most of the financials reports today takes about 8 to 12 seconds to be generated for one month (course that I have to sum previous months totals to give the balance). Thanks in advance... Regards, Bruno B B Magalh'aes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance
Hi Phill, Rob and Perrin, I forgot to attach the explain query from MySQL, course it's one of the most important things... Sorry!!! EXPLAIN SELECT UNIX_TIMESTAMP(transactions.transaction_date) AS date, transactions.transaction_complement AS complement, transactions.transaction_value AS value, transactions.transaction_nature AS nature, transactions_categories.transaction_category_description AS category_description, clients_units.client_unit_complement AS unit_complement FROM transactions AS transactions LEFT JOIN transactions_categories AS transactions_categories ON transactions.transaction_category_id = transactions_categories.transaction_category_id LEFT JOIN clients_units AS clients_units ON transactions.client_id = clients_units.client_id AND transactions.client_unit_id = clients_units.client_unit_id WHERE transactions.client_id = '00379' AND transactions.transaction_account_id = '001' AND transactions.transaction_classification_id = '101' AND transactions.transaction_date = '2008-03-01' AND transactions.transaction_date = '2008-03-31' ORDER BY transactions.transaction_date ASC 1 SIMPLE transactions ref transactions_idx_1 transactions_idx_1 4 const 9582 Using where; Using temporary; Using filesort 1 SIMPLE transactions_classificationsALL NULLNULLNULL NULL1660 1 SIMPLE clients_units ref clients_units_idx_1 clients_units_idx_1 8 bap_sat.transactions.client_id,bap_sat.transactions.client_unit_id 1 Seems that the transactions table is the sort of the problem, as it's using file sort and where... But my myisam sort cache is big, I thing it's about 80MB or so... Thank you everybody for your help!!! Best Regards, Bruno B B Magalhaes On Apr 22, 2008, at 3:21 PM, Perrin Harkins wrote: On Tue, Apr 22, 2008 at 11:41 AM, Bruno B. B. Magalhães [EMAIL PROTECTED] wrote: I thing the most problematic part of those queries are the date range part, should I use a different index only for this column to maintain the index small? My experience with doing data warehousing in MySQL was that when all you need is day granularity, you are much better off having a de-normalized 'days_since_epoch' column or a date dimension table with a column like that. Then your date math becomes simple integer comparisons which are much faster. - Perrin -- 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]
Re: How hard is it to move from on server to another?
A simple rsync should do the trick. How long will depend on how much data you have. I would just shut down the server, copy over the data directory and start the new server up. Should be a piece of cake. Keith David Ruggles wrote: I have a MySQL 5.x box and I am thinking about moving it to another more powerful server. I would be able to schedule some downtime so that's not an issue. How complicated a process would this be? I don't want to upgrade the software or anything, just move the existing tables, users and permissions to another physical server. It would even have the same IP address. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spring 2008 MySQL Magazine released!!
Hey everyone, I just posted the Spring issue of the MySQL Magazine. This issue contains a great group of articles that you will want to read. In addition, Sheeri Cabral did a fabulous job with a new layout for the magazine. It is as always FREE!!. Available for download from http://www.mysqlzine.net or http://www.paragon-cs.com/mag. thanks and enjoy!! Keith Murphy -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spring 2008 MySQL Magazine released!!
Daniel, Thanks for pointing that out. I haven't heard of this before so I really don't have a clue. We use Openoffice 2.4 to create the magazine and generate the pdf. It works fine in both OO and adobe acrobat (I am blessed with a Windows laptop). Keith Daniel Brown wrote: On Tue, Apr 15, 2008 at 2:20 PM, B. Keith Murphy [EMAIL PROTECTED] wrote: Hey everyone, I just posted the Spring issue of the MySQL Magazine. This issue contains a great group of articles that you will want to read. In addition, Sheeri Cabral did a fabulous job with a new layout for the magazine. It is as always FREE!!. Available for download from http://www.mysqlzine.net or http://www.paragon-cs.com/mag. thanks and enjoy!! Keith Murphy Looks good at a glance, Keith! Congrats! I'll print it and check it out when I get home tonight, but I wanted to respond to the thread to let anyone else who may be using KGhostView 0.20, like myself, that all pages appear blank. This is not a problem with the magazine, but appears to be a bug in KGhostView itself, so you will probably have to view the PDF with a different version or with an official Acrobat release. I'm going to submit a bug report with the project, but just wanted to put out a heads-up here on the list in case anyone else runs into the same problem and gives up before checking into it further. -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: procedure locks all statemens
When it selects values from the myisam table it is locking the entire table. Saravanan wrote: Hi Lists, i have created a procedure. It selects values from a myisam table and updates an innodb table one by one ( not as huge update it clearly use where clause). but whenever I run the procedure other statements waits for longtime until my procedure completes updates. any idea why it blocks. i tried start transaction and commint for every single update. but the result is same Saravanan Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL for Datamining
Sorry I can't recommend anything offhand, but this type of message does not need to go to the internals mailing list. Thanks, Keith Haitham Kaddoura wrote: Hi, does anyone use the or know a data mining package in MySQL? -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certified MySQL Associate (CMA) certification value
So I can relate a little personal knowledge about this. In July of last year I had someone approach me and ask how they could get a job as a DBA. After talking for a bit, it turned out that he had no database experience. He actually use to be a lawyer and decided to make a change. I told him to set up a database on a Linux computer and start playing around with it. The next month he said that he had done so. What next? Well, MySQL AB had just released the CMA exam. As Baron mentioned, it makes a great baseline to show your knowledge when you approach companies. I told him to start studying for the test using the MySQL Certification book (a subset of it covers the exam material). We met twice over the next few months to study a bit and then in December he took the test and passed it. Monday he actually started as our part time junior DBA. Is he making a lot of money? Nope. But, it is a good chance for him to get experience in a rapidly growing production environment and in another nine months or a year he *will *be able to get a decent job. So can you do it? Sure. Do you need a college degree? Not really. He certainly had one, but it had nothing to do with databases. What does it take? Desire, work and reasonable intelligence. That's all. I doubt you will make much at your first job, but learn from the experience and do everything you can to learn more. Once you get done with the CMA start working on the CMDBA exams. Unlike some other cert programs these aren't fluff. I am not sure of the statistics listed on MySQL's website, but there just aren't that many certified DBAs. And you won't find many jobs listed as jr dba. Don't sweat that. Approach companies that already have dbas and tell them you want a chance to learn. Show them through the CMA that you are serious. Working for peanuts. If it were me, and I was just starting out, I would pick what company I wanted to work at and if they wouldn't hire me for eight or ten dollars an hour I would offer to work for free. Just remember it is only temporary. Good luck. Keith Thufir wrote: On Wed, Feb 20, 2008 at 1:51 PM, Baron Schwartz [EMAIL PROTECTED] wrote: [...] In my opinion there is a tremendous shortage of qualified DBAs for MySQL. The certification may establish that you have at least a baseline of knowledge. If you're looking for a job, you could hardly go wrong with it, IMO. [...] Yeah, I think that I'm going for the certification, although I can't imagine what kind of job I could land -- junior DB admin? I don't have a college degree :( -Thufir -- B. Keith Murphy, CMA Paragon Consulting Services http://www.paragon-cs.com http://blog.paragon-cs.com (850) 637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: suggestion for disk configuration
Luca, In my testing I have had better throughput with RAID 10 (at least with XFS). I have not worried about the log files so much, so you might want to investigate that some. I would love to see benchmarks of your SAS array though. If you have a blog you should publish them if at all possible. I used iozone for raw throughput testing and then the querybench program (http://www.paragon-cs.com/queryprogs) to perform testing with production data against the server. Hope that helps. Keith Luca Ferrari wrote: Hi, I have to install a mysql server for managing several database, from small to quite big (tables with about 2M rows). I've got a SAS disk array and I was wondering what the best configuration could be: 1) raid 10 2) raid 5 3) a combination (e.g., raid10 for the data and raid 5 for the logs). Any suggestion or link? Thanks, Luca -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql replication
Naufal, You probably need to start with a clean slate. Do a complete resync and start the slave back up from a known stopping point. Keith Naufal Sheikh wrote: Hello, Just a small question. I had mysql replication configured on my master and slave server. Due to some issues ( when I was on vacations ), logging on master server was switched off and server restarted. Now my question is that in order to resysnc my master and slave correctly, do I need to copy the master database again to slave and then turn on logging and start slave again, or by just enabling the logging again, slave will resync from last known state. I believe that if the logging on master was switched off, it will have no record of the changes made to database and I will need top recopy the database, but just to confirm or if there is any simpler way. Thanks -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: turn a slave back into its own master
I believe you would have been ok with stop slave and reset master. Saravanan wrote: i am ok with this mysql change master to master_host=''; Query OK, 0 rows affected (0.01 sec) mysql show slave status\G Empty set (0.01 sec) Saravanan --- On Sat, 1/19/08, Paul Berry [EMAIL PROTECTED] wrote: From: Paul Berry [EMAIL PROTECTED] Subject: Re: turn a slave back into its own master To: [EMAIL PROTECTED] Cc: MySql mysql@lists.mysql.com Date: Saturday, January 19, 2008, 6:03 AM thanks - i think i'm ok because i get these results mysql show slave status; Empty set (0.00 sec) On 1/18/08, Saravanan [EMAIL PROTECTED] wrote: Hi, will removing the master.info help instead of executing change master? or stop slave; reset master; --- On Sat, 1/19/08, Paul Berry [EMAIL PROTECTED] wrote: From: Paul Berry [EMAIL PROTECTED] Subject: Re: turn a slave back into its own master To: MySql mysql@lists.mysql.com Date: Saturday, January 19, 2008, 5:31 AM i ended up doing this - it seems to have worked fine STOP SLAVE; SHOW SLAVE STATUS; CHANGE MASTER TO MASTER_HOST='', MASTER_PORT=0, MASTER_USER='', MASTER_PASSWORD=''; RESET MASTER; On 1/18/08, Paul Berry [EMAIL PROTECTED] wrote: hi guys, quick question how do i undo a CHANGE MASTER TO command and make it a master of itself again? thanks Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upcoming Mandatory Changes post-Sun Acquisition
Greetings! Bill Bilano here to chime in on what the Sun acquisition will mean to everyone here! Below you will find a list of changes that may impact how the MySQL Server and Client tools products will be utilized in the future. This list is in no way complete and I believe that additional changes will be announced as I see fit. Now that the big dogs are in the house, it's time to get a few things in line and get our ducks in a row. We need to make sure we don't rock the boat and everyone is carrying their own weight. We don't want any suprises! There are some big changes that are needed in order to compete in this rapidly growing market segment. 1. Now that Sun in large and in charge, you all need to learn up some new rules: HIPPA, SOX, PCI, and GLB. 2. The name of the product is changing to Product SuperNova; no more of this cowboy B.S.! 2. No more of that ISO ANSI SQL junks! Sun is not interestes in that! From now on ALL search queries must be submitsed in accoding with a forthcoming XML DTD! There wont be ANY exceptions! (YES THAT MEANS YOU, MONTY!) 2. The backend must be complete re-writted in 100% pure Java! This is going to be made into a case study in how Java usage can fix the mistakes that bad programmers made! MySLQ seems PERFECT for this! 3. All administor tools need to be redone in SWING! This is the only cross platform widget standard there is and you have to use it. Immediately. 4. Create and Optomize an NFS table storage mechanism! Find a way of doing this. 5. We need something to complete with SQLLite so GET 'ER DONE! LOLLL! 6. Full steam forward on MySQL-FS projet!! this needs to be merged into the primary source trunk... we know that ZFS is balls and Billy goat Gates tried to meet the brokeback demon with YUKON but he couldn't work it. HINT: This is the joker up our hole! 7. Zero tolreance for anybody who gives Theo allowed to access the source tree any more! For any reason! Even if he promises! 8. Be ready to do a presentation on why you need to keep your job with MyQSL after the merger. Have this ready for the next board meeting because they will be interviewing all employees! Some of you will be fired so have a plan for this as it is not MySLQ's problem! Now that I've spelledt it all our for you guys, you can thank me! Just as an FYI: _I do not work for MySQL or Sun_... I have some big Suns 6800s in my server room here at the bank and them is some fast, but these are the changes that I see as absolutely ESSENTIAL to the success of the product in the futuer!!! If SUN follows my lead, and I believe they will becacuse of my mile long list of credentials, then we can look forward to wide acceptence of this fantastic product in the futare! (i am IT director for a huge US bank and as such I demand respect, and i typially get it because of my great ideas and tireless devotuoion to the cfommunity!) Thanks for your TIME AND ATTENTION!!! p.S SELECT visit.my.bloglog ON internets AT www.bilano.biz WHEN PRIORITY = now; -- Mr. Billy B. Bilano, MSCE, CCNA, CISSP, and now QISP http://www.bilano.biz/ Expert Sysadmin Since 2003! 'C:\WINDOWS, C:\WINDOWS\GO, C:\PC\CRAWL' -- RMS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Computerworld reporter would like to hear from you ASAP today, on MySQL,acquisition by Sun...
Todd, I'd be happy to answer all of your questions. See my responses in-line below: What are your impressions of this deal? I think it is fantastic because it means that even more money will be made and this is what Sun's top priority is and represents what they care about, realistically. This is why I use commercial software instead of open-source. Is this good for the MySQL community? Absolutely, Todd. It will help get the errant programmers back on-track with sound software design and solid methodologies. Being able to rely on the sage judgment and wisdom of Sun's proven software developers and technological leadership will only make the community stronger and the products better. Good for MySQL users? Todd, I can't express how much better it will be for end-users! If you take a look at one of my other posts to this very mailing list, you'll see a broad strategy that I've outlined for the future of MySQL. I've put forth a number of proposals including a new MySQL-FS file system, new cross-platform administrative tools, and a renewed focus on the Java language and how it can be used to help MySQL along the road to success. Do you see a downside here? Not yet I don't! Overall, is this a good thing for MySQL and its users or a bad thing? As I've outlined, I think it's a fantastic first-step towards bringing MySQL out of the 20th century and into the 21st. This is nothing but the beginning of a bold new era in the annals of MySQL history. Together, Sun, MySQL, and the community can bring about the change that is needed. Please tell me your thoughts in as much detail as possible. Be sure, please to give me your FULL NAME, Job Title, PHONE NUMBER, e-mail address and what you do for a living -- whether programmer, consultant, etc. My name is Billy B. Bilano. I am the Director of Information Technology at a Major US Bank. If you need anything more, please contact me directly by emailing [EMAIL PROTECTED] and I'll get back to you in short order. Thanks so much for your quick reply! Thank you, Todd, for helping me share with your readers! -- Mr. Billy B. Bilano, MSCE, CCNA, CISSP, and now QISP http://www.bilano.biz/ Expert Sysadmin Since 2003! 'C:\WINDOWS, C:\WINDOWS\GO, C:\PC\CRAWL' -- RMS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine Spring Issue -- Call for Articles
Just four days ago I released the winter issue of MySQL Magazine (http://www.mysqlzine.net). However, I wanted to go ahead and get the jump on the spring issue as this quarter will be quite busy. Many of us are either speaking at the MySQL User's Conference or attending it in April. The spring issue of the magazine will be released on the first day of the User's Conference so I wanted to make sure that everyone had time to submit an article if they had interest. Any topic relating to MySQL development or administration is acceptable. In particular, I would love to have some submissions on php development with MySQL. Hope to hear from you soon! keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine - Winter Issue released
I just posted the latest edition of MySQL Magazine. It is available for free download from http://www.mysqlzine.net. Take a look! Keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Red Hat EL and Datbase Setup
Jason, You really are going to need to test this for yourself as it will somewhat depend on your application. Raid 5, 10 or the mentioned 1+0 might work for you best. keith [EMAIL PROTECTED] wrote: Try reading on RAID1+0, though it's a bit expensive in implementation but its great on READ WRITE.. Basing on the current stable version there is no built in table partitioning.. you can do it in an application level.. -Original Message- From: Jason Vinar [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 09, 2008 10:17 AM To: mysql@lists.mysql.com Subject: Red Hat EL and Datbase Setup Hi, I am looking for a little advice in setting up Red Hat and MySQL for a large database (at least I consider it to be large). My database will contain 2 large tables that are updated daily. The first table currenly has 19 million records and ~70 columns largely made up of varchar(20), char(5) and integers. It has a natural primary key and a composite index on 3 of the character columns. The second table currently has 400 million records and ~30 columns again made up of varchar(20), char(5) and integers. This table's primary key is defined using 2 columns and also has a composite index on the same 3 columns as the first table. Lastly, I will frequently join the two tables in my queries. My system has 4gb ram, 500 gb hard drive (result of a RAID 5 configuration) and 2 dual core Intel 64 bit procs. I have chosen to use Red Hat EL5. Here are the questions I have to help optimize the performance: * Should I continue with the RAID 5? I am not too concerned of recovery. I am more concerned about I/O performance. * Is there a hard drive partition scheme that would help the performance (separate the large db schema /var/lib/mysql/schema_name)? * Should I partition the tables? There is a natural partition for the 400m table by date; there is not a natural partition for the other. Should I make one up? * Are there specific additions to the /etc/my.cnf that I should add to maximize the systems capabilities? * Please let me know of other things I should consider. Thanks in advance, Jason This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. FXDirectDealer, LLC reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal confirmation. FXDirectDealer, LLC is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automation question
Everyone, So, I have been thinking recently about automation. This morning I listened to a talk by one of the three dbas at Youtube (from the MySQL Users Conference last year). Think about that. They mentioned 100,000,000 pageviews in one day (the data was from 2006). And THREE dbas. Of course this is done by automating anything you can. This isn't the first time I have heard of such ratios of servers/dbas. While I don't have the servers or the traffic that they do at Youtube we are growing quite nicely and adding db servers on a fairly regular basis. It is time to put some serious thought into automating some things. I was wondering what experience others have had with any technologies (I am modestly familier with expect and have touched dsh). Thoughts?? thanks, keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automation question
Martin, Martin Gainty wrote: 3 ideas come to mind- cron and or cruisecontrol for off-hours scripting ant for handling cross-platform scripting maven for implementing dependency checking as well as the ability to pull from online repositories I will take a look at these. I am certainly familiar with cron. The others not so much. Youtube.com I worked with a company last summer that implemented identical functionality and wanted to firewall youtube videos to use their (considerably more capable as far as supporting every codec on the planet) player..youtube said if you link/use/href any amount of content to our site then you MUST use our player..its no WONDER they have that incredible pageview traffic .. Is there some specific functionality you want to Automate??? I have some things in mind, but basically any task that I am performing across multiple servers I want to automate. While I might have time to manually do things across 20 servers (not really... but that is the current state) I won't be able to do so across 100. Make sense? Thanks/ Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Thu, 3 Jan 2008 13:06:07 -0500 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: automation question Everyone, So, I have been thinking recently about automation. This morning I listened to a talk by one of the three dbas at Youtube (from the MySQL Users Conference last year). Think about that. They mentioned 100,000,000 pageviews in one day (the data was from 2006). And THREE dbas. Of course this is done by automating anything you can. This isn't the first time I have heard of such ratios of servers/dbas. While I don't have the servers or the traffic that they do at Youtube we are growing quite nicely and adding db servers on a fairly regular basis. It is time to put some serious thought into automating some things. I was wondering what experience others have had with any technologies (I am modestly familier with expect and have touched dsh). Thoughts?? thanks, keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. Get it now! http://www.xbox.com/en-US/hardware/wheretobuy/ -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automation question
Martin, I took a quick look at the tools that you mentioned. Seems you are heavily involved in Java :) We don't use Java at all, and other than using cron (which I do use for backups and standard stuff like that) not really what I am looking for. After thinking about this, I suspect that this will all just be a custom scripting with something like dsh for the distributed part. Not really looking for source code control -- more like things to manage the distribution of standardized my.cnf files (which we already control using subversion) to 100 servers at a time. Looking to scale efforts so I don't have to manually do things. thanks, Keith B. Keith Murphy wrote: Martin, Martin Gainty wrote: 3 ideas come to mind- cron and or cruisecontrol for off-hours scripting ant for handling cross-platform scripting maven for implementing dependency checking as well as the ability to pull from online repositories I will take a look at these. I am certainly familiar with cron. The others not so much. Youtube.com I worked with a company last summer that implemented identical functionality and wanted to firewall youtube videos to use their (considerably more capable as far as supporting every codec on the planet) player..youtube said if you link/use/href any amount of content to our site then you MUST use our player..its no WONDER they have that incredible pageview traffic .. Is there some specific functionality you want to Automate??? I have some things in mind, but basically any task that I am performing across multiple servers I want to automate. While I might have time to manually do things across 20 servers (not really... but that is the current state) I won't be able to do so across 100. Make sense? Thanks/ Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Thu, 3 Jan 2008 13:06:07 -0500 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: automation question Everyone, So, I have been thinking recently about automation. This morning I listened to a talk by one of the three dbas at Youtube (from the MySQL Users Conference last year). Think about that. They mentioned 100,000,000 pageviews in one day (the data was from 2006). And THREE dbas. Of course this is done by automating anything you can. This isn't the first time I have heard of such ratios of servers/dbas. While I don't have the servers or the traffic that they do at Youtube we are growing quite nicely and adding db servers on a fairly regular basis. It is time to put some serious thought into automating some things. I was wondering what experience others have had with any technologies (I am modestly familier with expect and have touched dsh). Thoughts?? thanks, keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. Get it now! http://www.xbox.com/en-US/hardware/wheretobuy/ -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best practice MySQl backup onto tape
Jenny Chen wrote: Hi, I'm planing to do hot backup MySQL(innodb db) onto tape drive, and propose the following solution/script: # delete old mysql dumps rm -r -r /backup/mysql mkdir /backup/mysql # Dump all mysql databases mysqldump --all-databases -single-transaction --flush-logs all_databases.sql mysqldump --database=mysql --lock-all-tables --flush-logs system.sql # Do backup tar cvf /dev/rmt/0 /backup/mysql My question is: is there any other better solutions(including commercial solutions) to do hot MySQL backup to tape that can provide better performance, or can backup directly to the tape(no need to dump to disk first), etc. comparing to use mysqldump. Thanks in advance for your information, or comment on the above solution. Regards, Jenny You didn't specify if this was a master or slave. I certainly wouldn't dump off a master server. It will lock the database for the duration of the backup. One of the ways we do backups is to do an lvmsnapshot of the data partition on the slave and then just mount and rsync the snaphsot of the data off to the backup server. Inefficient for space, but would be vastly faster restoring a backup like this then running a restore from a mysldump. You will still need to tar this take it acceptable for tape. Another option would be to replace mysqldump with Baron Schwartz's mk-parallel-dump and mk-parallel-restore tools. They are faster than a traditional dump/restore and are much more compact. These tools (and a number of others) are located at maatkit.sourceforge.net.net. Hope that helps. Keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL analysis tool - open source?
MySQL Proxy is good. It doesn't fit in some environments however. Another option would be the QPP (Query Processing Programs) that I and a co-worker have developed. They can be downloaded at http://www.paragon-cs.com/queryprogs. thanks Keith Michael Dykman wrote: Have you considered: http://forge.mysql.com/wiki/MySQL_Proxy On Dec 5, 2007 5:14 PM, Russell E Glaue [EMAIL PROTECTED] wrote: Does anyone know of an open source SQL analysis tool? I have query logging turned on in MySQL. And I would like to analyze all the logged SQL select queries to find out how the database is being used, and then optimize MySQL databases or the SQL views/statements accordingly. Perhaps it would generate a report of most used columns for searching and retrieving, across all logged SQL queries. We have a software package used to generate reports from data in MySQL, and the users are allowed to make ad hoc querying. So we do not (necessarily) have control over the SQL queries that are executed. And once in a great while, some unknowing user will execute a query (unoptimized of course) that takes longer than satisfactory to execute. So I want to analyze all the queries users have issued, and try and optimize the database tables accordingly, or adjust the views in the reporting software. Any tools (open source only) out there to help? -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Falls Out Of Sync With LOAD DATA
The reason I asked about version is that it looks like there is problem replcating a load data infile command from some versions of 4.x to 5.x slaves. Baron Schwartz wrote: What do you mean by falls out of sync? LOAD DATA INFILE hasn't been a problem for me, and I use it a LOT. It's so simple that I suspect something else. But then again, I don't know what you mean by out of sync :) On Nov 28, 2007 4:32 PM, B. Keith Murphy [EMAIL PROTECTED] wrote: What versions of MySQL are you using on both the master adn the slaves? Keith Michael Stearne wrote: We have replication set up for 1 master and 4 slaves. When resynced everything appears to work fine. Come back a couple hours later and the machines are out of sync. The only thing I can think of that could cause this is that we are inserting some data on the master (updates,inserts,deletes) using LOAD DATA INFILE. Does this cause a problem for replication? Thanks, Michael -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql replication....
bruce wrote: hi... a quick question that i haven't found an answer to. i can use replicate-do-db=foo in a my.cnf file for replication, to replicate the master foo db on the slave. but this requires that i use/have a my.cnf set on the slave. is there a way to dynamically set this attribute/parameter within mysql on the fly. i thought it would be possible via change master to but didn't find the cmd when looking through the mysql information. basically, i'm going to have multiple databases, on multiple systems, that i'm going to be replicating to a single system. so, for each master server, i'd like to be able to set the databases that i'm going to replicate... thanks Can't do that currently in MySQL. It is called multi-master replication. You can do multi-slave replication which replicates from one master to multiple slaves, but not the other way around. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spfile in Mysql......
This has probably already been over-talked about :) but I will throw my two cents in. I would be very much opposed to a situation where changes made on-the-fly are stored permanently in the my.cnf file. If I decide to keep a change to a server setting I am very much capable of doing it myself. Besides, if it is permanent I need to make the changes to my subversion copies of my.cnf. Keith Sujatha S wrote: Yes! I think in oracle the dynamic changes are recorded in spfile.. so that it uses on instance startup.. rather making an permanent entry in pfile. Regards, On 11/28/07, Jay Pipes [EMAIL PROTECTED] wrote: Sujatha S wrote: Mysql should bring this as there new feature in there next release! Unlikely. Dynamic changes are, well, dynamic. Permanent stuff goes in the my.cnf. -jay Regards, Sujatha On Nov 27, 2007 11:44 AM, Shanmugam, Dhandapani [EMAIL PROTECTED] wrote: Hello, The dynamic changes made on mysql server instance gets vanished once the instance goes down...!! Is there any way for mysql to store the dynamic changes on my.cnf file automatically ..?(like Oracle) , so on next startup mysql automatically pickup the dynamic changes made from my.cnf file Unfortunately there is not. You should alter your my.cnf file to record the changes you make. -- MySQL General Mailing List For list archives: *http://lists.mysql.com/mysql* http://lists.mysql.com/mysql To unsubscribe: * http://lists.mysql.com/[EMAIL PROTECTED] 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]
Re: preferred way to backup a 20GB MySQL database
I would echo what Dan says. In addition, from the slave server, you might look at running the new mysql-parallel-dump tool that Baron Schwartz has developed. It essentially does a dump with a thread running (by default) for each CPU core you have. A dual core box will run two threads and dump roughly twice as fast as a normal mysqldump. In addition, it compresses the output making it much more compact. He has renamed the toolkit to Maatkit and it is available at http://maatkit.sourceforge.net/. Also, you might look into using an LVM snapshot to run the copy from. That way it doesn't interfere with your operations as much. I do that for some of our production slave servers myself. Keith Dan Buettner wrote: I'd strongly recommend setting up replication, and then taking your backups from the replica. mysqlhotcopy works great, I used it for years myself, but it does require freezing your database while the copy happens. And no matter how you do it, copying 20 GB takes a little bit of time. Dan On Nov 27, 2007 4:35 PM, David Campbell [EMAIL PROTECTED] wrote: Andras Kende wrote: Hi, What is the preferred way to backup a 20GB database daily, without taking offline ? MySQL 4.1 MyISAM - (will be updated to MySQL 5) 133 table(s) Sum 115,416,561 latin1_swedish_ci 20.1 GB Mysqlhotcopy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication vs. mysql-table-sync
Michael Stearne wrote: Is mysql-table-sync design to be used as a fix for when your replication is out of sync OR can it be used instead of replication? Thanks, Michael You need to use replication not mysql-table-sync for replication. mysql-table-sync is use to get it back in sync. keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb rollback question
Thanks everyone for the responses. Will put me on the right track here..something that was rolling through my head but I couldn't really define. I will be blogging about this later as I think it is fairly important, but often not understood by beginning/mid-level dbas. thank again, Keith William Newton wrote: Use smaller transactions that don't have 140 million rows. When attempting an action with important data, make sure you can survive the actions failure. If you can't, then you need to think of a different way of doing it that will allow a recoverable failure. - Original Message From: B. Keith Murphy [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 16, 2007 10:29:17 AM Subject: innodb rollback question I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb rollback question
I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with master/slave replication
Ryan, show slave status after it stops copying should give you some type of error information about the failure. What does it show? Keith Ryan Klein wrote: I am having an issue that I cannot determine the cause. We have a master server that is actually a production server and a slave server that is a fall back but after around 10 days, it stops coping data and the servers fall out of sync. Here is my my.cnf file for the master server: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 #max_connections= 100 #table_cache= 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. log = /var/log/mysql/mysql.log # # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. server-id = 1 log_bin = /var/log/mysql/mysql-bin # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian! #expire_logs_days = 10 max_binlog_size = 1000M binlog_do_db= mydns sync_binlog = 1 #binlog_ignore_db = include_database_name # and for the slave server: # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 #max_connections= 100 #table_cache= 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. log = /var/log/mysql/mysql.log # # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. server-id = 2 log_bin = /var/log/mysql/mysql-bin.log # WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian! #expire_logs_days = 10 max_binlog_size = 1000M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name #master-host= 74.202.241.24 #master-user= slavedb #master-password= )vKe8m)?Eod #master-connect-retry = 60 #replicate-do-db= mydns # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and filesystems question
In production we use both reiserfs and xfs. I am migrating our data partitions for mysql because it has faster write rates than reiserfs. You need to stop using fedora and at least use CentOS. Fedora is not stable for production work. Just my two cents :) Keith Ali Nebi wrote: Hi, i want to ask for your opinions about the best combination for mysql with filesystem (like ext2, ext3... and so on). We have problems with mysql under fedora 6 with ext3 and i want to know under wich filesystem mysql works best. I will be glad to read your opinions. Thanks in advanced. Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and filesystems question
I dashed off this email too fast. What I meant to say was I am migrating our data partitions for MySQL from reiserfs to xfs because xfs has faster write rates than reiserfs. Craig Huffstetler wrote: I agree, Fedora is not very good at production work. Reiserfs is one of the best file systems I have come across for MySQL Production. On 10/28/07, *B. Keith Murphy * [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: In production we use both reiserfs and xfs. I am migrating our data partitions for mysql because it has faster write rates than reiserfs. You need to stop using fedora and at least use CentOS. Fedora is not stable for production work. Just my two cents :) Keith Ali Nebi wrote: Hi, i want to ask for your opinions about the best combination for mysql with filesystem (like ext2, ext3... and so on). We have problems with mysql under fedora 6 with ext3 and i want to know under wich filesystem mysql works best. I will be glad to read your opinions. Thanks in advanced. Regards, Ali Nebi! -- 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 Magazine Call for articles
Hey everyone, I am starting on putting together the Winter issue of the MySQL Magazine. This will be our third issue and it is getting better each time! Here is your opportunity to contribute to the community. I am accepting ideas/outlines/proposals for articles for this issue through November the 9th. That gives you almost two weeks to get back to me about that great idea you have in your head. Articles will be due to be completed by December the 1st. If you aren't sure about an idea send me an email. Hope to hear from you soon! Keith Murphy Editor-Mysql Magazine www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: open_files_limit problem.
Paul, Take a look at the ulimit command. We had a similar issue, and it turns out there are OS level per-process limits as well. We have now configured the /etc/init.d/mysql file set the ulimit to what we need. here is the snippet form the file: echo $echo_n Starting MySQL ulimit -n 4096 We just added the ulimit line. Hope that helps. Keith - Original Message - From: Paul Halliday [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, October 11, 2007 3:02:27 PM (GMT-0500) America/New_York Subject: open_files_limit problem. I am trying to change this value and it doesn't seem to work. Looking at the processes I have: mysql 21752 0.0 0.1 1652 1092 p3 I 3:50PM 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/nsccmadb2.campus.nscc.ca.pid mysql 21770 0.0 1.3 58188 26168 p3 S 3:50PM 0:00.21 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --pid-file=/var/db/mysql/nsccmadb2.campus.nscc.ca.pid Within /var/db/mysql/my.cnf I have: [mysqld_safe] open_files_limit = 32768 [mysqld] open_files_limit = 32768 But when I try something like mysql show variables like '%files%';: I get: open_files_limit 11095 Is there another variable that needs to be adjusted to bump this up? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877
Re: how to restore mysql database after Crash
Recover your files first and then you can see where you can go. I suppose you now have backup plans in place now? Keith David Winslow wrote: Hey, Environment: Windows 2003 server Mysql 5.0 server Problem: Our server crashed and the c drive was formatted. Unfortunately we did not have a backup of the mysql databases which we were using. We can hopefully retrieve the file structure through a file system recovery utility. What would the steps in order to restore the databases again ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql user password funkyness
Thanks everyone for the replies. The problem revolved around '%' vs 'localhost'. To me, it seems logical that '%' would include localhost..not really true. So, I have it figured out. thanks, Keith - Original Message - From: B. Keith Murphy [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Wednesday, September 26, 2007 10:39:17 PM (GMT-0500) America/New_York Subject: mysql user password funkyness Everyone, So I have never seen this before. I set up a database yesterday. I added a user today in the normal manner.. ie grant usage on *.* to 'username'@'%' identified by 'password'; Here is the thing. You exit the console and try to log in with mysql -u username -ppassword and it won't let you in... BUT it log in with both mysql -u username and also mysql -u username -p (hit enter key). So the user account works without a password. executing the show grants command shows a password so this shouldn't work. Never had this problem before and kind of hard to google for mysql user login without password but has password.. show grants for 'user'@'%'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD 'password_hash' | ++ There are other privileges for the appropriate databases but they really aren't relevant. thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877 -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877
mysql user password funkyness
Everyone, So I have never seen this before. I set up a database yesterday. I added a user today in the normal manner.. ie grant usage on *.* to 'username'@'%' identified by 'password'; Here is the thing. You exit the console and try to log in with mysql -u username -ppassword and it won't let you in... BUT it log in with both mysql -u username and also mysql -u username -p (hit enter key). So the user account works without a password. executing the show grants command shows a password so this shouldn't work. Never had this problem before and kind of hard to google for mysql user login without password but has password.. show grants for 'user'@'%'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD 'password_hash' | ++ There are other privileges for the appropriate databases but they really aren't relevant. thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877
MySQL Magazine Issue Two released
Just wanted to let everyone know that the second issue of the MySQL magazine has been uploaded. It is available from http://www.paragon-cs.com/mag/mysqlmag.html Our featured articles include: MySQL Maintenance script -a multi-threaded tool for database maintenance MySQL Table Checksum How-To Optimizing MySQL with Rails - Part Two Index Optimization in MySQL Our regular features include news, a book review and the coding corner. Everyone enjoy!! If you want contribute to the next issue just get in contact with me via email. We are always looking for content about MySQL administration and application development with MySQL. In addition, I could use some help with graphics stuff. Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: make a slave db a master db
Ananda, The answer to your question is very simple actually, but there is some subtleties that you need to think of. To stop the slave from getting replication data from the master just issue a stop slave command. I would also recommend that you run a reset master command in addition. This will reset the master server information to empty (not pointing anywhere). The reason why it is subtle is that you need to know what state you want your data in. When you issue the stop slave command it breaks the replication with the master with your data on the old slave basically the same as the master. Is that what you really want? If not, you need to take other measures to get your data in the state you want before bringing it online as a standalone server. Hope that helps. Keith What data do you want on the new master? - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Thursday, August 30, 2007 3:37:48 AM (GMT-0500) America/New_York Subject: make a slave db a master db Hi All, We have one master and two slave dbs. Now we want one of the slaves to work as a stand alone master db, so what are the steps that i need to take to make one of the slaves as a stand alone master db. regards anandkl -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
So I stand corrected :) Sorry for the mis-information. Thanks Mark!! Keith - Original Message - From: Mark Leith [EMAIL PROTECTED] To: Baron Schwartz [EMAIL PROTECTED] Cc: Michael Dykman [EMAIL PROTECTED], Gu Lei(Tech) [EMAIL PROTECTED], Jen mlists [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Wednesday, August 15, 2007 12:51:19 PM (GMT-0500) America/New_York Subject: Re: run out of memory Baron Schwartz wrote: Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? There are a couple of things to beware of 64bit binaries - the main being buffer management.. The larger the buffer pools you have, the greater the risk of having buffer pool management operations taking longer and longer, and locking out operations. Some good examples of this are having a large query cache (see http://bugs.mysql.com/bug.php?id=21074, patch pending and in progress), and large InnoDB buffer pools, with some high load against the adaptive hash index (which has only recently become an issue since InnoDB have improved concurrency within the engine really) see http://bugs.mysql.com/bug.php?id=20358 - which is only showing itself on multi core 64bit machines, and is proving itself to be very hard to track down and reproduce. Of course, InnoDB also has to manage it's buffer pool over and above the adaptive hash index as well, and can show hanging in various other operations as well, such as large checkpointing or insert buffer merging operations. Playing around with innodb_max_dirty_pages_pct etc. can help with this also. With regards to the MyISAM key buffer - yes this is only safe up to 4G right now - even on 64bit - as well as a number of the other thread based variables (sort buffer, read buffer, join buffer etc.). Of course, most sane people would not set these thread variables that high, but we did not limit them, and some people *did* in fact try to set them very high! :) See: http://bugs.mysql.com/bug.php?id=5731 http://bugs.mysql.com/bug.php?id=29419 http://bugs.mysql.com/bug.php?id=29446 etc. However, this is per key buffer as well - one can create multiple key buffers, and assign indexes to be loaded in to each, to work around this issue with MyISAM. I'm not sure where the comment on indexing code only being 32bit safe comes from, maybe it is due to the limitation of the key buffer? I know of people that have pushed the InnoDB buffer up to 32G, and it hums along just fine, you just have to make sure that you do not get caught in huge flushing operations (keep the dirty pages low, try not to do too many huge insert operations all in a big batch at the same time etc.). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
I have had the same type of problems as this user when unknowing using 32-bit code. That was why I was asking about what distro he was using. As for your question Baron - I don't think that limit is true (anymore). I am fairly certain that it use to be, but has been corrected. If everyone is really curious I can dig around and even test it if need be. Keith - Original Message - From: Baron Schwartz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Michael Dykman [EMAIL PROTECTED], Gu Lei(Tech) [EMAIL PROTECTED], Jen mlists [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Wednesday, August 15, 2007 12:27:14 PM (GMT-0500) America/New_York Subject: Re: run out of memory Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? Thanks Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: run out of memory
What operating system are you running and is it 32 or 64 bit? Keith - Original Message - From: Jen mlists [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 14, 2007 2:35:34 AM (GMT-0500) America/New_York Subject: run out of memory Hello, My server box has 8G memory and 8CPU (DELL Standard Server),when I configure Mysql server (5.0.45) using large memory,mysqld would say it run out of memory. For example,when this line appear in my.cnf, key_buffer = 4000M mysqld can't startup.When I change it to, key_buffer = 2560M mysqld startup successfully. Why this mysqld can't support large memory usage?How to improve it? Thanks! --jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: Enterprise Wide Deployment
Really probably not the best list for this. But if it were up to me..I would use CentOS/RedHat Enterprise Linux or Debian. Keith - Original Message - From: john_sm [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 14, 2007 3:20:36 AM (GMT-0500) America/New_York Subject: Enterprise Wide Deployment Hey guys, for an enterprise wide deployment, what will you suggest and why among - Red Hat Linux, Suse Linux and Ubuntu Linux, also, do you think, we can negotiate the support pricing down? -- View this message in context: http://www.nabble.com/Enterprise-Wide-Deployment-tf4265688.html#a12139896 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://www.paragon-cs.com/wordpress (o) 919-433-0786 (c) 850-637-3877
Re: innodb to be removed? and...
Even if it is removed it would only be removed from future versions .. say 6.0. Even version 5.1 has been feature-frozen, if I am not mistaken, meaning that features will not be added or removed. But, as others have said, Falcon is what I think MySQL has in mind to replace Innodb This transition, however, will take years just like the transition from MyIsam to Innodb. DBA's tend to be very set in our ways so transitions are slow! As long as Oracle doesn't do something stupid (which they might) Innodb will probably still be available as an option in the GA release 10 years from now so I wouldn't sweat it. Keith - Original Message - From: Christian Parpart [EMAIL PROTECTED] To: MySQL General mysql@lists.mysql.com Sent: Tuesday, July 24, 2007 5:48:38 AM (GMT-0500) America/New_York Subject: innodb to be removed? and... Hi all, recently someone said to know alot about mysql told us that InnoDB is about to be removed from the mySQL server. however, InnoDB seems to be the fastest storage engine in our case, as myisam take a hell longer to insert new rows e.g. so is it true, that innodb is to be removed? (coudn't find any trace in the net yet) and what about potential future storage engines in mysql, or will there be myisam as the one and only feature blown one? Thanks in advance, Christian Parpart. -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: performance of extended insert vs. load data
I think you will find the load data infile will work faster. I am performing testing right now in preparation for a migration from 4.1 to 5.0 but I am confident that will be the case. Keith - Original Message - From: Sid Lane [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 23, 2007 1:44:53 PM (GMT-0500) America/New_York Subject: performance of extended insert vs. load data all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a load data infile against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables can be done in advance). thanks! -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
speeding imports
Hey all. I have a bit of a problem. We are upgrading from ver 4.1.x to 5.0.45. That's not the problem :). The problem is that MySQL recommends dumping and importing your data when doing this upgrade to make sure that things go as smoothly as possible. We have quite a bit of data spread over multiple servers. We have slaves off each master server and the plan is upgrade the slaves and then make them the masters. The problem is that I am realizing that this dump/import is going to take hours and in some cases days. I am looking for any way to speed this up. Any suggestions? Thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Next MySQL Magazine --call for articles
Hey everyone, I am starting to prepare for the second issue of MySQL Magazine. I am planning on putting it out on September 1. Thanks for the fabulous response to the first issue!! There was over a 1,000 downloads of the magazine. That is just fabulous!!! Special thanks to those who contributed (in no particular order): Peter Brawley, Dan Buettner and Baron Schwartz. It looks like there is going to be some publicity on a couple of podcasts over the next fews so there will probably be a decent upswing in subscribers. I am not going to concentrate on any particular theme this time. If you want to send me some ideas for articles you can either reply to this email address or bmurphy at paragon-cs.com. Thanks again, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
mysqldump/restore
hey all. Let me throw out a quick question. We are upgrading from some version of 4.1 to the latest version of 5.0. MySQL recommends that you dump and re-import data with this type of move. Not only do they recommend the dump/import - it would be nice to optimize all our tables at the same time!! I have done a test dump of a couple of gigs of data with just a straight mysqldump. That worked, but the import is happening slower than Christmas. I know that one technique for speeding up the imports is to where you configure the dump so that it generates file(s) with the schema for the table(s) and file(s) with actual data. Then you can use the load data infile command to import the data after generating the new tables (maybe by using 'mysql table1.sql'). So here is the problem. It worked, but it was slow. Not because of the speed of the import but because I am having to issue a command (and type in the root password) for each table import. I could do the following: 'cat *.sql database.sql' to get a complete schema for all the tables in the database and then just 'mysql database.sql' to set up all the tables at the same time. You can't do that with the import of the actual data. You have to individually load the data into each table. something like 'load data infile /root/table1.txt'. This is fine for one..but what happens when you have 100 tables? I can't take the time to sit there and hit the up-arrow to retrieve the command and then type in the new file name. Is there some way to automate this data import? Thanks, Keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: off-topic unsubscribe concern
I have been having the same problems. Have no idea what is up. - Original Message - From: Michael Dykman [EMAIL PROTECTED] To: MySQL General mysql@lists.mysql.com Sent: Friday, July 6, 2007 11:55:41 AM (GMT-0500) America/New_York Subject: off-topic unsubscribe concern Hi everyone. I have been on this list for a pretty long time but in the last few months I have started to receive random 'confirm unsubscribe' messages.. They always seem to originate from a Roadrunner IP (I have not thoroughly tested that hypothesis). I have no accounts on or near roadrunner, so I doubt I am inadvertantly kicking these off, which was my first theory. Is anyone else suffering from this or is it just me? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: acquire mysql knowledge in short time
I would agree with the other person who posted about the MySQL 5.0 cert guide. It is quite thorough. I would also recommend Pro MySQL which is more in-depth on some things like replication. If you are going to be going into clusters..there is only book that specifically covers it. MySQL Clustering is published by MySQL. It is rather dated (not covering 5.1 features) but it is pretty in-depth. Keith - Original Message - From: Peter Teunissen [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Tuesday, June 26, 2007 4:03:10 PM (GMT-0500) America/New_York Subject: acquire mysql knowledge in short time Hi All, For an opensource project at work, I need to get to know mysql in a few days. I need some advise on what to read and what tutorials to work through. We are going to build a data warehouse using mysql 5.0. We will try to build the entire ETL stream using sql scripts and stored procedures. It will be up to me to translate the data analysis and initial datamodel into a working database and ETL stream. I have a high working knowledge of sql on Oracle, DB2 and MS SQL Server 2k5. My knowledge of stored procedures is limited to an Oracle PL/SQL training some years ago with limited pracical experience. I have a basic knowledge of programming (Java, Python and VB) Within a few days, I will have about 4 full days to invest into getting to know MySQL 5.0. With the above objective, experience and most important, time limit in mind, what documentation (and important sections therof) and tutorials would you advise me to use? What parts of MySQL functionality should I focus on and what should I skip over for the moment? Thanks for your advise, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: toutrial needed
Weiqi, You might want to take a look at the book MySQL Tutorial. It is in a second edition and available from Amazon. In addition there are numerous guides available on the Internet. Devshed (http://www.devshed.com/c/b/MySQL/) for example has 90 articles on mysql. Of course many of them are high-level but it is a useful resource. http://www.tizag.com/mysqlTutorial/ is another. google mysql tutorial I would really recommend that you do start with the book that I mentioned..MySQL Tutorial. And Hal...really..the comment about the spelling was unnecessary. Not to mention that you provided no help what so ever. Hope that helps Weiqi. Keith - Original Message - From: Hal Wigoda [EMAIL PROTECTED] To: Weiqi Wang [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, June 27, 2007 11:45:37 AM (GMT-0500) America/New_York Subject: Re: toutrial needed first you need to learn to spell tutorial. On Jun 27, 2007, at 10:37 AM, Weiqi Wang wrote: Dear sir or madam: I'm totally new to mySQL and I have little pre-knowledge on database, could anyone suggest a toutrial on mySQL which is suitable in my case, please? I really appreciate any answer, very much! Best Regards, yours sincerely, Weiqi == Weiqi Wang Hertford College Dep. of Engineering Science Oxford -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
user backup tool
I know there is a tool out there to grab the user info from the mysql database and store it like versioning control. I thought it was in Baron's toolkit but unless I have had too much caffeine and can't read I don't see it on his website. What am I thinking of? Thanks, keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: user backup tool
I think it was Baron's toolkit I was thinking of... the command mysql-show-grants seems to do the trick. Thanks, Keith - Original Message - From: B. Keith Murphy [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Wednesday, June 27, 2007 4:11:54 PM (GMT-0500) America/New_York Subject: user backup tool I know there is a tool out there to grab the user info from the mysql database and store it like versioning control. I thought it was in Baron's toolkit but unless I have had too much caffeine and can't read I don't see it on his website. What am I thinking of? Thanks, keith -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877 -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877 -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
How to parition a table with a VARCHAR Primary Key?
Hi,
I'm struggling with getting my tables partitioned. All the tables I want to
partition have a single primary key id which is a varchar. They also have a
created datetime field to let me know when each record was added to the db.
Ex:
CREATE TABLE `document` (
`document_id` varchar(50) NOT NULL DEFAULT '',
`filename` varchar(255) NOT NULL DEFAULT '',
`document` longblob,
`created` datetime DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
`size` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`document_id`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Ideally, I want to partition the table on a per month basis. Originally I
though I could do the following:
partition by range( Year( created) * 1000 + month(created) )(
partition 2004_01 values less than (200401),
partition 2004_02 values less than (200402) );
but that gave me an error 1491: A PRIMARY KEY must include all columns in
the table's partitioning function. Incidentally, I couldn't find any
reference to this in the documentation anywhere, that the partitioning field
must belong to the PK. In fact, the examples in the docs don't even use a
PK.
So that didn't work. And adding the created field to my PK may cause too
many problems that I don't want to deal with at the moment. So instead, I
looked at my id field. Lucikly, the first 16 characters are hex
representation of the timestamp. So a little quick script generated ranges
for me to parition against that as well.
partition by range( id )
(
partition 2002_01 values less than ('00ebc0102c7f'),
partition 2002_02 values less than ('00ec50423c7f'),
partition 2002_03 values less than ('00ece0744c7f')
);
However, that causes me an error as well:
Error Code : 1064
VALUES value must be of same type as partition function near '),
partition 2002_02 values less than
('00ec50423c7f'),' at line 7
Can anyone point me in the right direction please? Is it impossible to
partition a table this way? I've read (and reread) the docs multiple times,
but can't seem to find anything to help me out.
I'm running version 5.1.19-beta-community-nt-debug.
Thanks!
Eric
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Magazine - Issue 1 available NOW!!!!
Everyone, I have just uploaded the first issue MySQL Magazine to http://paragon-cs.com/mag/issue1.pdf Please take a look at it. There is a great deal of information here and I think it is worth some time. Feedback is always welcome. thanks, Keith Murphy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SAN backend for DB's
So here is the brief situation. We have a coraid (www.coraid.com) SAN unit - the 1520 I believe. It is ATA-over-ethernet. Right now we have a about 500 gigs of data spread across five servers. To simplify things I would like to implement the coraid on the backend of these servers. Then all the data is served up out of the same place. Of course I would like to improve I/O throughput also. Googling shows that these units have good read speed but the write speed doesn't seem to be that impressive. Does anyone have any experience with these? Good? Bad? Maybe other SAN suggestions? Am I barking up the wrong tree? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection time out/slow response from server
Hi, Running 4.1.22 created from FreeBSD ports on FreeBSD 5.5 . I run under TCP Wrappers. Even if 1 foreign machine attempts to contact us on the tcp port, the database becomes incredibly sluggish or unresponsive at all. Is there something I can do to prevent this from happening? I'm using TCPWrappers to help control access to the database instead of going to firewall rules. I'd rather not do this, defeats what TCPWrappers was all about. Thanks, Tuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: time taken by mysqldump
Have you considered replicating to a backup server and then dumping from it? No matter your processors with a 100gb db it is going to take a significant amount of time. Keith Ananda Kumar wrote: Hi All, We have database of around 100GB, and planning to take dump using mysqldump. Can you please let how much time it would take for 100GB. We have 8GB RAM and 4 intel latest processor. The mysql db is running on linux. regards anandkl No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.467 / Virus Database: 269.7.7/816 - Release Date: 5/23/2007 3:59 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrity on large sites
Sometimes partitioning is absolutely necessary. If you can't run a cluster - how else can you really scale writes to the database? Some companies can't use clustering because in 5.0.x (the non-beta release) clustering is all done in memory - all tables have to be in memory (just like the old heap tables). It isn't until 5.1.x that clustering allows your data to be stored on disc. Many companies still consider 5.1 to not be production ready. You might disagree but that is their thinking. So, if you don't use clustering, how else are you going to scale an application? I suppose you can set up master-master replication - but that doesn't really scale to a large extent. Some companies have huge applications with hundreds of gigabytes or even terabytes of data. I think if you read carefully through the presentations from the recent MySQL conference by companies such as Digg and Flickr you will find that they do partitioning as well as caching and such. I recall specifically reading through a presentation by livejournal about how they split up their load across multiple machines by the very partitioning we are talking about. I might be missing something. I can understand why you wouldn't want to work on such a system as it certainly adds complexity to the entire database. But that doesn't mean that it isn't something that isn't necessary sometimes. Just my two cents :) Keith Naz Gassiep wrote: Data partitioning? Sorry, I disagree that partitioning a table into more and more servers is the way to scale properly. Perhaps putting databases' tables onto different servers with different hardware designed to meat different usage patterns is a good idea, but data partitioning was a very short lived idea in the world of databases and I'm glad that as an idea it is dying in practice. - Naz Evaldas Imbrasas wrote: Since the question was about *really* big websites, the answer is both yes and no. Yes, they do turn off RI on the database side, simply because it's not possible to enforce RI on a database system where data is partitioned across server farms (or shards) both vertically and horizontally. And really big websites can't survive without the data partioning. No, they don't usually turn off RI just to improve performance, because the gains would be minimal, and for big websites, scalability is a much bigger issue that performance (although sometimes one depends on the other), and data partitioning is the way to go to solve the scalability problem. On 5/24/07, Naz Gassiep [EMAIL PROTECTED] wrote: I'm working in a project at the moment that is using MySQL, and people keep making assertions like this one: *Really* big sites don't ever have referential integrity. Or if the few spots they do (like with financial transactions) it's implemented on the application level (via, say, optimistic locking), never the database level. A large DB working with no RI would give me nightmares. Is it really true that large sites turn RI off to improve performance? Am I just being naive in thinking that everyone runs their DBs with RI in production? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integrity on large sites
OK. Going to try this again. After reading through these emails I think I have learned a little more about the way you are thinking. I DO NOT want to start some kind of flame war. However, I disagree very strongly with what you are saying. Yes, you are right, sharding does require more complexity from the application layer. Sorry for all you developers out there (and I can safely say that I am NOT a developer!!). The fundamental issue for you, as I see it, is the increased complexity caused by sharding the application. That being said, I will say this...if you develop on some other RDBMS such as MS or Oracle is it possible to deleveop something like you are saying...an all-inclusive database that isn't sharded? Yep, when I worked at Netzero in 2001 for example we had two database servers running Oracle, one on the east coast in Virginia and one one the west coast in California. The east coast server was a backup of the west coast server. So one database server did the billing for all of Netzero's customers. Millions of customers..absolutely. All in one nice tidy box that I am sure was easier to develop the billing applications around. Here is the kicker. Each box was a top of the line Sun server that had 32 processors and 32 gigs of RAM. They could handle up to 64 procs and 64 gigs. And each cost well over a million dollars for the hardware alone. Running Oracle on it must have cost over 100,000 dollars for software licenses. Granted this was in 2001, but the licensing cost for Oracle haven't gone down any that I am aware of...and the hardware cost will still be quite steep to do this type of thing. So I ask you this.. Would it be better to go with that scenario or something like this: Implement the billing application using MySQL. Shard it. Create complexity. Your hardware cost saving alone will pay for multiple developers to handle any complexity increases. Any decent DBA is going to be able to handle multiple servers required to operate this setup. You will probably see a decrease in salary cost moving from Oracle to MySQL dbas. So for the bottom line of the company it is a overall win by far. It is only the inherent difficulty in moving complex systems from one type of DB to another that keep more companies from switching. Why hasn't this happend previously?? Because until version 4 of MySQL was stable there were many features not available in MySQL that were needed by these types of systems. It is my contention that as the clustering capabilities of MySQL continue to grow and mature (think of when version 6.0 goes stable) companies will move to MySQL in droves. THEN you have the ability to build a single virtual database (at least from the point of view of your application) that will scale simply and elegantly. As I said in the previous email it is only that 5.1 is in beta that keeps this from being available now. And many companies, such as Kaneva, are doing this right now. The only reason that companies like Digg and Flikr can exist and grow at such phenomenal rates is that they keep the cost of the development of the system to a minimum and the overhead of operating (licensing costs and hardware cost) down as low as possible. In addition, of course, they need the ability to scale out very quickly. Digg didn't get any significant funding until just recently. And yet they epitomize the web 2.0 companies. They did it by both keeping their cost down and having the ability to grow quickly. Couldn't have done it with Oracle or MS. Just my thoughts :) Keith Naz Gassiep wrote: Wow. The problem with sharding I have is the large amount of code required in the app to make it work. IMHO the app should be agnostic to the underlying database system (by that I don't mean the DB in use such as MySQL or whatever or the schema, I mean the way the DB has been deployed) so that changes can be made to it without having to worry about impacting app code. This is one of my fundamental design imperatives. Then again, I'm not a regular MySQL user so I don't know what is and is not the norm in the MySQL world. - Naz. Evaldas Imbrasas wrote: You certainly have a right to disagree, but pretty much every scalability talk at the MySQL conference a few weeks ago was focused on data partitioning and sharding. And those talks very given by folks working for some of the most popular (top 100) websites in the world. It certainly looks like data partitioning is the way to go in the MySQL world at this point, probably at least until production-ready and feature-full MySQL Cluster is out. And even then large percentage of dotcom companies would use data partitioning instead since it can be implemented on commodity hardware. Once again, we're talking *really* big websites using MySQL (not Oracle or SQL Server or whatever) here. Most websites won't ever need to partition their production databases, and different
TCPWrappers and database unavailibility
Hi, Running 4.1.22 created from FreeBSD ports on FreeBSD 5.5 . I run under TCP Wrappers. Even if 1 foreign machine attempts to contact us on the tcp port, the database becomes incredibly sluggish or unresponsive. phpmyadmin shows MySQL said: #2003 - The server is not responding Is there something I can do to prevent this from causing all the problems? Thanks, Tuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to limit usage of mysql server's memory/cpu for each databases ?
Halid Faith wrote: I use mysql4.1.22, php4.4.6 and apache2.0.59. I want to put an quota for each databases and mysql users. How can I do that mysql will limit each database's memory usage? Because some mysql users sometimes use much memory while doing a sql query. Also How can I put an quota for each databases as data size ( like 1 Mbyte ) ? Halid, I don't think there is any way to do this from within MySQL. If you were ambitious you could implement the datasize quota with some type of script. Hope that helps. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleted Root Account
I have had exactly the kind of trouble you are talking about. Sorry I didn't see the messages until just now. Had to manually remove crap too even though I purged (rpms - centos I think). Hopefully this will help others down the road. Keith John Kebbel wrote: Even though I completely removed everything I could find related to MySQL in Synaptic Package Manager, a folder still remained at /var/lib/mysql/ containing all my old table information. If that info hung around, it seemes logical to assume the Grants table were still somewhere interfering with my attempts to login to mysql as root. However, when I followed instructions at http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html , I was able to kill mysqld and restart it with the skipping the grants table option. I was then able to to login as the the weird Debian/Ubuntu equivalent of root (a user using sudo) and access the mysql database. From there I was able to reinvigorate root. The thing that surprised me was that all my data survived the reinstallation process. I thought the databases and tables I had created would go up in smoke when I removed mysql_server. They're all intact. Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing Data Format
Baron Schwartz wrote: Hi, Tim Milstead wrote: Hello, Is it possible to enforce data formatting in fields using something like a regular expression? varchar is great but does not stop someone putting in the wrong reference number. I suspect the answer is no, you have to do it at a higher level. What aprroach would people suggest? Perhaps another table with two fields - field_name and field_regex? If you are using 5.x or greater, you can do some kinds of data validation with a trigger. Baron I would argue that your data-validation should really not be done with triggers - I would look more at the php/asp/whatever code to do this. Shouldn't this be more efficient than using triggers? At the very least doing validation on the web server will make it easier to add capacity with multiple web servers. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Summer issue - MySQL e-zine
Everyone, I have received quite a bit of positive feedback on the idea of a MySQL e-zine and I am moving forward. I would like to have the first issue out by the 1st of June. I have decided to have a theme of the first issue on security? Why? Well, to be honest, it will any easier topic to start with. And second, unfortunately, I think security is a neglected aspect of many MySQL installations. Here is where the community comes in. I am looking for some articles. Article length will be around 550 words for a single page. An article can be up to 3 pages long. If you need to include graphics that will need to lower the word count of course. Please submit plain text and preferably png graphics although I can manage pretty much any graphic type if necessary. It would be best if you submitted an abstract of sorts - just a paragraph or two about what you want to write about. That way we can see if it will work before you write out the full length article. Some ideas for article topics: User account management for security Server hardening (OS and mysql server) SQL Injection attacks and coding to prevent them. Other non-security article ideas will certainly be considered as every article will not be about security. If a an article idea is of good quality and it won't fit in the first issue than it certainly be used for the fall issue. Unfortunately there is no way I can pay for articles right now. Down the road I would like to get some advertisers /sponsors and use that to pay for articles. Just remember that this a way that you can give back to the community. Thanks, Keith B. Keith Murphy wrote: Hey everyone, I have been considering putting together a e-zine for MySQL as my way to give back to the community. I was curious as to what type of interest there would be in this. I am thinking something quarterly to start with and probably 15 - 20 pages. Nothing huge. Topics would vary from everything like standard DBA stuff like backups, replication, system architecture and such to stuff for the developers out there..php, perl, python whatever. My background is more of a DBA so I would definitely need some input/articles for the developer side of things. It is just funny that in this day and age there is no magazines specifically for MySQL. I even saw a php magazine at the bookstore the other day! I can do the layout in QuarkXpress and generate pdfs that I can host on my website. Any thoughts? Any desires to contribute? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
