Re: Advanced query help
At a guess, because you use @team in an if statement before you actually define it. On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I've the following query SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,IF(@team teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM players JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND players.teams_id 0 ORDER BY players.teams_id, players_master.rating DESC) s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC I'm running MySQL 5.0.77. However, if I run this query nothing is returned, but if I run the query again I get the desired results. Why is this ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Advanced query help
Hi, I did try defining it before the IF statement, but still the same ? Cheers Neil On Mon, Sep 27, 2010 at 7:58 AM, Johan De Meersman vegiv...@tuxera.bewrote: At a guess, because you use @team in an if statement before you actually define it. On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I've the following query SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,IF(@team teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM players JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND players.teams_id 0 ORDER BY players.teams_id, players_master.rating DESC) s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC I'm running MySQL 5.0.77. However, if I run this query nothing is returned, but if I run the query again I get the desired results. Why is this ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Advanced query help
Then you'll probably need to define it with a separate select before using it. I'm half-guessing here, really, but that sounds like it makes sense :-) On Mon, Sep 27, 2010 at 11:49 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I did try defining it before the IF statement, but still the same ? Cheers Neil On Mon, Sep 27, 2010 at 7:58 AM, Johan De Meersman vegiv...@tuxera.be wrote: At a guess, because you use @team in an if statement before you actually define it. On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I've the following query SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,IF(@team teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM players JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND players.teams_id 0 ORDER BY players.teams_id, players_master.rating DESC) s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC I'm running MySQL 5.0.77. However, if I run this query nothing is returned, but if I run the query again I get the desired results. Why is this ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Update Table
Hi, I work on MySQL 5 with PHP 5 and use Apache 2 as the webserver. I have a simple query that searches matched row id and update the field via HTTP GET query. On a low load it succeed update the row. But on high traffic sometimes it failed to update some rows. No errors return by the script. What usually cause this and how to solve this problem? Thanks sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Table
On Mon, 2010-09-27 at 11:25 +0100, Willy Mularto wrote: Hi, I work on MySQL 5 with PHP 5 and use Apache 2 as the webserver. I have a simple query that searches matched row id and update the field via HTTP GET query. On a low load it succeed update the row. But on high traffic sometimes it failed to update some rows. No errors return by the script. What usually cause this and how to solve this problem? Thanks 1.) Are you sure the script is executed under those conditions? Is Apache refusing the request because to many children have been forked? 2.) Are you sure the script will report if MySQL fails with too many connections? sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
multiple aliases
I have a query with three subselects, all referencing the same table. I'd like to be able to combine them into one with aliases. Here's what I have now: select letter_codename, (select greek from letter_otherlanguages where letter ='A') as greek, (select french from letter_otherlanguages where letter ='A') as french, (select german from letter_otherlanguages where letter ='A') as german from intl_codes where letter='A'; I'd like to replace it with: select letter_codename, (select greek, french, german from letter_otherlanguages where letter ='A') as (greek, french, german) from intl_codes where letter='A'; Don't get hung up on the tables and structures, this is just a simple example. :) I want to use the three subselects because if I use a left join, the processing time goes from .4 to 5 seconds. Is this possible? Thanks!
Best encription method?
Hello experts! Can i have your valuable suggestion on the following? I would like to encrypt a particular table of records. I hope can choose two ways,number one, application level encryption method choosing our own encryption algorithm and an another is database level encryption. my question is, which is portable, which is consistent? or altogether the encryption should not degrades the performance. Note: The application is going to be across an intranet Suggestions are appreciated! Thank you in advance. Vikram A
Re: Best encription method?
Both have benefits. Application level: - data is encrypted during transmit, too - processing is offloaded from your hard-to-scale database server - decrypt keys don't pass your database, so dba or other users can't peek DB - Guaranteed consistent implementation regardless of client Can't think of other benefits of DB-side encryption, offhand :-) I would go for the app-side every time. Let the database worry about managing the data, don't bother it with application stuff. On Mon, Sep 27, 2010 at 3:15 PM, Vikram A vikkiatb...@yahoo.in wrote: Hello experts! Can i have your valuable suggestion on the following? I would like to encrypt a particular table of records. I hope can choose two ways,number one, application level encryption method choosing our own encryption algorithm and an another is database level encryption. my question is, which is portable, which is consistent? or altogether the encryption should not degrades the performance. Note: The application is going to be across an intranet Suggestions are appreciated! Thank you in advance. Vikram A -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Best encription method?
Dear Sir, Thank you for the suggestion, as you suggested i shall go for application level. I have another query too, please answer, Normally, If i need to store an integer value i have to define it as int, If I encrypt this, i must define its type as string of different size[it depend upon the encryption output] than its original size. It increases the db size. I am known that if it is secure i have to choose encryption. but when i choose encryption it leads the more memory usage. Any comment on this? Thank you Vikram From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Mon, 27 September, 2010 6:55:04 PM Subject: Re: Best encription method? Both have benefits. Application level: * data is encrypted during transmit, too * processing is offloaded from your hard-to-scale database server * decrypt keys don't pass your database, so dba or other users can't peekDB * Guaranteed consistent implementation regardless of client Can't think of other benefits of DB-side encryption, offhand :-) I would go for the app-side every time. Let the database worry about managing the data, don't bother it with application stuff. On Mon, Sep 27, 2010 at 3:15 PM, Vikram A vikkiatb...@yahoo.in wrote: Hello experts! Can i have your valuable suggestion on the following? I would like to encrypt a particular table of records. I hope can choose two ways,number one, application level encryption method choosing our own encryption algorithm and an another is database level encryption. my question is, which is portable, which is consistent? or altogether the encryption should not degrades the performance. Note: The application is going to be across an intranet Suggestions are appreciated! Thank you in advance. Vikram A -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: multiple aliases
I don't believe it's possible to do what you're suggesting. At least, according to the second example on this page: http://dev.mysql.com/doc/refman/5.1/en/subquery-errors.html. -Travis -Original Message- From: Ramsey, Robert L [mailto:robert-ram...@uiowa.edu] Sent: Monday, September 27, 2010 7:10 AM To: [MySQL] Subject: multiple aliases I have a query with three subselects, all referencing the same table. I'd like to be able to combine them into one with aliases. Here's what I have now: select letter_codename, (select greek from letter_otherlanguages where letter ='A') as greek, (select french from letter_otherlanguages where letter ='A') as french, (select german from letter_otherlanguages where letter ='A') as german from intl_codes where letter='A'; I'd like to replace it with: select letter_codename, (select greek, french, german from letter_otherlanguages where letter ='A') as (greek, french, german) from intl_codes where letter='A'; Don't get hung up on the tables and structures, this is just a simple example. :) I want to use the three subselects because if I use a left join, the processing time goes from .4 to 5 seconds. Is this possible? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: multiple aliases
On 9/27/2010 9:10 AM, Ramsey, Robert L wrote: I have a query with three subselects, all referencing the same table. I'd like to be able to combine them into one with aliases. Here's what I have now: select letter_codename, (select greek from letter_otherlanguages where letter ='A') as greek, (select french from letter_otherlanguages where letter ='A') as french, (select german from letter_otherlanguages where letter ='A') as german from intl_codes where letter='A'; I'd like to replace it with: select letter_codename, (select greek, french, german from letter_otherlanguages where letter ='A') as (greek, french, german) from intl_codes where letter='A'; Don't get hung up on the tables and structures, this is just a simple example. :) I want to use the three subselects because if I use a left join, the processing time goes from .4 to 5 seconds. Is this possible? Thanks! This should work - SELECT ic.letter_codename, lo.greek greek, lo.french french, lo.german german from intl_codes ic LEFT JOIN letter_otherlanguages lo on lo.letter = ic.letter WHERE ic.letter='A'; There should also be an index on both tables where `letter` is the leftmost element. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Posting to the mailing list.
This email may contain confidential information the use of which by an unintended recipient is unauthorized. For details please go to http://www.scotiabank.com/email_disclaimer/email_english.html Cette transmission peut contenir de l'information confidentielle et son utilisation par toute personne autre que la personne à laquelle cette transmission est destinée est interdite. Pour plus de détails, veuillez vous diriger vers http://www.scotiabank.com/email_disclaimer/email_francais.html Este correo electrónico puede contener información confidencial cuyo uso por parte de personas distintas de los destinatarios del mismo está prohibido. Para más detalles, por favor dirigirse a http://www.scotiabank.com/email_disclaimer/email_espanol.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Segmentation fault - Redhat Linux 64 Bit
This email may contain confidential information the use of which by an unintended recipient is unauthorized. For details please go to http://www.scotiabank.com/email_disclaimer/email_english.html Cette transmission peut contenir de l'information confidentielle et son utilisation par toute personne autre que la personne à laquelle cette transmission est destinée est interdite. Pour plus de détails, veuillez vous diriger vers http://www.scotiabank.com/email_disclaimer/email_francais.html Este correo electrónico puede contener información confidencial cuyo uso por parte de personas distintas de los destinatarios del mismo está prohibido. Para más detalles, por favor dirigirse a http://www.scotiabank.com/email_disclaimer/email_espanol.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Segmentation fault - Redhat Linux 64 Bit
Hi there, I installed Apache, MySQL and PHP on Redhat Linux 64 bit. However, when I try to invoke MySQL, I'm getting the Segmentation fault error and I'm not able to see the mysql prompt. Begin of problem: ### [...@xyz123 bin]$ sudo ./mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.0.91 Source distribution Segmentation fault ### End of problem: Could you please suggest a solution for this? Thanks in advace for your help. regards, Sharath
RE: Segmentation fault - Redhat Linux 64 Bit
Either 1. Use strace to find out where it's getting a segfault, or 2. Use gdb and get the backtrace where crashes. -Original Message- From: Sharath Babu Dodda [mailto:sharath.do...@gmail.com] Sent: Monday, September 27, 2010 3:17 PM To: mysql@lists.mysql.com Subject: Segmentation fault - Redhat Linux 64 Bit Hi there, I installed Apache, MySQL and PHP on Redhat Linux 64 bit. However, when I try to invoke MySQL, I'm getting the Segmentation fault error and I'm not able to see the mysql prompt. Begin of problem: ### [...@xyz123 bin]$ sudo ./mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.0.91 Source distribution Segmentation fault ### End of problem: Could you please suggest a solution for this? Thanks in advace for your help. regards, Sharath This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Persistent Connection VS One Time Connection Was Update Table
I have checked Apache's log. There is no refused connection. And also with MySQL I have set it to 999 connections and view the processes. Maximum connection ever reached was only around 200. What I'm thinking now is. Is it because of I use one time connection method? I mean every time the script's called I create new connection and disconnect it after execute the query. Thanks sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ On Sep 27, 2010, at 7:28 PM, Nigel Wood wrote: On Mon, 2010-09-27 at 11:25 +0100, Willy Mularto wrote: Hi, I work on MySQL 5 with PHP 5 and use Apache 2 as the webserver. I have a simple query that searches matched row id and update the field via HTTP GET query. On a low load it succeed update the row. But on high traffic sometimes it failed to update some rows. No errors return by the script. What usually cause this and how to solve this problem? Thanks 1.) Are you sure the script is executed under those conditions? Is Apache refusing the request because to many children have been forked? 2.) Are you sure the script will report if MySQL fails with too many connections? sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to extend innodb files?
Hello, Currently I have the setting: innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend Because the last file of ibdata4 is very large (more than 50G), if I want extend the data to more files, for example, ibdata5, ibdata6... how to do it? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
