RE: Problem with having
Still more to this saga Comment 1: ... HAVING x; The expression ( x ) is evaluated as a true/false value, based on whether x is nonzero (true) or zero (false). Your 'x' is MIN(date_time) , which is very likely to be nonzero, hence TRUE. That is, the HAVING does nothing useful. Comment 2: This shows 1. a technique 2. how MariaDB optimizes it away, and 3. how you can get MariaDB to still do the group by trick: https://mariadb.com/kb/en/group-by-trick-has-been-optimized-away/ (I do not know of other MySQL variants that have any trouble with the trick.) -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 24, 2013 1:44 PM To: shawn green Cc: mysql mailing list Subject: Re: Problem with having On Tue, Sep 24, 2013 at 9:05 AM, shawn green shawn.l.gr...@oracle.comwrote: Hello Larry, On 9/23/2013 6:22 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.com** wrote: Hi Larry, On 9/23/2013 3:58 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com **wrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where mysql recipe_id = 19166; +-+-----+ | id | MAX(date_time) | +-+-----+ | 1151701 | 2013-02-07 18:38:13 | +-+-----+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta mysql where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+-----+ | id | MaxDateTime | +-+-----+ | 1151701 | 2010-12-13 16:16:55 | +-+-----+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry You have to do a two-stage match. One stage to find the MAX() of a value for each recipe_id, the other to match that MAX() to one or more rows to give you the best ID values. Here's a subquery method of doing it. There are many many others (google for groupwize maximum) SELECT a.id, b.MaxDateTime FROM data_cstmeta a INNER JOIN ( SELECT MAX(date_time) MaxDateTime FROM data_cstmeta WHERE recipe_id = 19166 ) b on b.MaxDateTime = a.date_time WHERE recipe_id = 19166; Having the recipe_id in the query was just to show an example. I really want the id's with the max date for each recipe_id: This is what I changed it to, which works, but is too slow. I need to find a more efficient solution: SELECT d1.id, d1.date_time as MaxDateTime FROM data_cstmeta d1 LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND d1.date_time d2.date_time WHERE d2.recipe_id IS NULL As I said, there are many many ways to solve this problem. Here is one that is going to perform much better for the generic case than what you are doing. CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) SELECT recipe_id, max(date_time) maxdatetime FROM data_cstmeta GROUP BY recipe_id; SELECT a.id, b.maxdatetime FROM data_cstmeta a INNER JOIN tmpMaxDates b on a.recipe_id = b.recipe_id and a.date_time = b.maxdatetime; DROP TEMPORARY TABLE tmpMaxDates; Of course, an appropriate multi-column index on data_cstmeta would also make your technique much faster than it is today. Thanks much Shawn! This ran in a few seconds vs. 30 minutes for my solution.
Re: Problem with having
I Sent from my D - Reply message - From: Rick James rja...@yahoo-inc.com To: Larry Martell larry.mart...@gmail.com, shawn green shawn.l.gr...@oracle.com Cc: mysql mailing list mysql@lists.mysql.com Subject: Problem with having Date: Thu, Sep 26, 2013 12:11 PM Still more to this saga Comment 1: ... HAVING x; The expression ( x ) is evaluated as a true/false value, based on whether x is nonzero (true) or zero (false). Your 'x' is MIN(date_time) , which is very likely to be nonzero, hence TRUE. That is, the HAVING does nothing useful. Comment 2: This shows 1. a technique 2. how MariaDB optimizes it away, and 3. how you can get MariaDB to still do the group by trick: https://mariadb.com/kb/en/group-by-trick-has-been-optimized-away/ (I do not know of other MySQL variants that have any trouble with the trick.) -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 24, 2013 1:44 PM To: shawn green Cc: mysql mailing list Subject: Re: Problem with having On Tue, Sep 24, 2013 at 9:05 AM, shawn green shawn.l.gr...@oracle.comwrote: Hello Larry, On 9/23/2013 6:22 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.com** wrote: Hi Larry, On 9/23/2013 3:58 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com **wrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where mysql recipe_id = 19166; +-+-----+ | id | MAX(date_time) | +-+-----+ | 1151701 | 2013-02-07 18:38:13 | +-+-----+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta mysql where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+-----+ | id | MaxDateTime | +-+-----+ | 1151701 | 2010-12-13 16:16:55 | +-+-----+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry You have to do a two-stage match. One stage to find the MAX() of a value for each recipe_id, the other to match that MAX() to one or more rows to give you the best ID values. Here's a subquery method of doing it. There are many many others (google for groupwize maximum) SELECT a.id, b.MaxDateTime FROM data_cstmeta a INNER JOIN ( SELECT MAX(date_time) MaxDateTime FROM data_cstmeta WHERE recipe_id = 19166 ) b on b.MaxDateTime = a.date_time WHERE recipe_id = 19166; Having the recipe_id in the query was just to show an example. I really want the id's with the max date for each recipe_id: This is what I changed it to, which works, but is too slow. I need to find a more efficient solution: SELECT d1.id, d1.date_time as MaxDateTime FROM data_cstmeta d1 LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND d1.date_time d2.date_time WHERE d2.recipe_id IS NULL As I said, there are many many ways to solve this problem. Here is one that is going to perform much better for the generic case than what you are doing. CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) SELECT recipe_id, max(date_time) maxdatetime FROM data_cstmeta GROUP BY recipe_id; SELECT a.id, b.maxdatetime FROM data_cstmeta a INNER JOIN tmpMaxDates b on a.recipe_id = b.recipe_id and a.date_time = b.maxdatetime; DROP TEMPORARY TABLE tmpMaxDates; Of course, an appropriate multi-column index on data_cstmeta would also make your technique much faster than it is today. Thanks much Shawn! This ran in a few seconds vs. 30 minutes for my solution.
Re: Problem with having
Hello Larry, On 9/23/2013 6:22 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.comwrote: Hi Larry, On 9/23/2013 3:58 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com **wrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+---**--+ | id | MAX(date_time) | +-+---**--+ | 1151701 | 2013-02-07 18:38:13 | +-+---**--+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+---**--+ | id | MaxDateTime | +-+---**--+ | 1151701 | 2010-12-13 16:16:55 | +-+---**--+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry You have to do a two-stage match. One stage to find the MAX() of a value for each recipe_id, the other to match that MAX() to one or more rows to give you the best ID values. Here's a subquery method of doing it. There are many many others (google for groupwize maximum) SELECT a.id, b.MaxDateTime FROM data_cstmeta a INNER JOIN ( SELECT MAX(date_time) MaxDateTime FROM data_cstmeta WHERE recipe_id = 19166 ) b on b.MaxDateTime = a.date_time WHERE recipe_id = 19166; Having the recipe_id in the query was just to show an example. I really want the id's with the max date for each recipe_id: This is what I changed it to, which works, but is too slow. I need to find a more efficient solution: SELECT d1.id, d1.date_time as MaxDateTime FROM data_cstmeta d1 LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND d1.date_time d2.date_time WHERE d2.recipe_id IS NULL As I said, there are many many ways to solve this problem. Here is one that is going to perform much better for the generic case than what you are doing. CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) SELECT recipe_id, max(date_time) maxdatetime FROM data_cstmeta GROUP BY recipe_id; SELECT a.id, b.maxdatetime FROM data_cstmeta a INNER JOIN tmpMaxDates b on a.recipe_id = b.recipe_id and a.date_time = b.maxdatetime; DROP TEMPORARY TABLE tmpMaxDates; Of course, an appropriate multi-column index on data_cstmeta would also make your technique much faster than it is today. Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Problem with having
On Tue, Sep 24, 2013 at 9:05 AM, shawn green shawn.l.gr...@oracle.comwrote: Hello Larry, On 9/23/2013 6:22 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.com** wrote: Hi Larry, On 9/23/2013 3:58 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com **wrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+-----+ | id | MAX(date_time) | +-+-----+ | 1151701 | 2013-02-07 18:38:13 | +-+-----+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+-----+ | id | MaxDateTime | +-+-----+ | 1151701 | 2010-12-13 16:16:55 | +-+-----+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry You have to do a two-stage match. One stage to find the MAX() of a value for each recipe_id, the other to match that MAX() to one or more rows to give you the best ID values. Here's a subquery method of doing it. There are many many others (google for groupwize maximum) SELECT a.id, b.MaxDateTime FROM data_cstmeta a INNER JOIN ( SELECT MAX(date_time) MaxDateTime FROM data_cstmeta WHERE recipe_id = 19166 ) b on b.MaxDateTime = a.date_time WHERE recipe_id = 19166; Having the recipe_id in the query was just to show an example. I really want the id's with the max date for each recipe_id: This is what I changed it to, which works, but is too slow. I need to find a more efficient solution: SELECT d1.id, d1.date_time as MaxDateTime FROM data_cstmeta d1 LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND d1.date_time d2.date_time WHERE d2.recipe_id IS NULL As I said, there are many many ways to solve this problem. Here is one that is going to perform much better for the generic case than what you are doing. CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) SELECT recipe_id, max(date_time) maxdatetime FROM data_cstmeta GROUP BY recipe_id; SELECT a.id, b.maxdatetime FROM data_cstmeta a INNER JOIN tmpMaxDates b on a.recipe_id = b.recipe_id and a.date_time = b.maxdatetime; DROP TEMPORARY TABLE tmpMaxDates; Of course, an appropriate multi-column index on data_cstmeta would also make your technique much faster than it is today. Thanks much Shawn! This ran in a few seconds vs. 30 minutes for my solution.
Re: Problem with having
Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. Regards. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+-+ | id | MAX(date_time) | +-+-+ | 1151701 | 2013-02-07 18:38:13 | +-+-+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+-+ | id | MaxDateTime | +-+-+ | 1151701 | 2010-12-13 16:16:55 | +-+-+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry
Re: Problem with having
On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+-+ | id | MAX(date_time) | +-+-+ | 1151701 | 2013-02-07 18:38:13 | +-+-+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+-+ | id | MaxDateTime | +-+-+ | 1151701 | 2010-12-13 16:16:55 | +-+-+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry
Re: Problem with having
Hi Larry, On 9/23/2013 3:58 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+-+ | id | MAX(date_time) | +-+-+ | 1151701 | 2013-02-07 18:38:13 | +-+-+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+-+ | id | MaxDateTime | +-+-+ | 1151701 | 2010-12-13 16:16:55 | +-+-+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry You have to do a two-stage match. One stage to find the MAX() of a value for each recipe_id, the other to match that MAX() to one or more rows to give you the best ID values. Here's a subquery method of doing it. There are many many others (google for groupwize maximum) SELECT a.id, b.MaxDateTime FROM data_cstmeta a INNER JOIN ( SELECT MAX(date_time) MaxDateTime FROM data_cstmeta WHERE recipe_id = 19166 ) b on b.MaxDateTime = a.date_time WHERE recipe_id = 19166; Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Problem with having
select recipe_id,max(maxdatetime) from data_csmeta group by recipe_id having recipe_id=19166; On Mon, Sep 23, 2013 at 4:15 PM, shawn green shawn.l.gr...@oracle.comwrote: Hi Larry, On 9/23/2013 3:58 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com **wrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+---**--+ | id | MAX(date_time) | +-+---**--+ | 1151701 | 2013-02-07 18:38:13 | +-+---**--+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+---**--+ | id | MaxDateTime | +-+---**--+ | 1151701 | 2010-12-13 16:16:55 | +-+---**--+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry You have to do a two-stage match. One stage to find the MAX() of a value for each recipe_id, the other to match that MAX() to one or more rows to give you the best ID values. Here's a subquery method of doing it. There are many many others (google for groupwize maximum) SELECT a.id, b.MaxDateTime FROM data_cstmeta a INNER JOIN ( SELECT MAX(date_time) MaxDateTime FROM data_cstmeta WHERE recipe_id = 19166 ) b on b.MaxDateTime = a.date_time WHERE recipe_id = 19166; Yours, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Problem with having
On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.comwrote: Hi Larry, On 9/23/2013 3:58 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com **wrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+---**--+ | id | MAX(date_time) | +-+---**--+ | 1151701 | 2013-02-07 18:38:13 | +-+---**--+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+---**--+ | id | MaxDateTime | +-+---**--+ | 1151701 | 2010-12-13 16:16:55 | +-+---**--+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry You have to do a two-stage match. One stage to find the MAX() of a value for each recipe_id, the other to match that MAX() to one or more rows to give you the best ID values. Here's a subquery method of doing it. There are many many others (google for groupwize maximum) SELECT a.id, b.MaxDateTime FROM data_cstmeta a INNER JOIN ( SELECT MAX(date_time) MaxDateTime FROM data_cstmeta WHERE recipe_id = 19166 ) b on b.MaxDateTime = a.date_time WHERE recipe_id = 19166; Having the recipe_id in the query was just to show an example. I really want the id's with the max date for each recipe_id: This is what I changed it to, which works, but is too slow. I need to find a more efficient solution: SELECT d1.id, d1.date_time as MaxDateTime FROM data_cstmeta d1 LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND d1.date_time d2.date_time WHERE d2.recipe_id IS NULL
Re: Problem with having
On Mon, Sep 23, 2013 at 2:17 PM, Sukhjinder K. Narula narula...@gmail.comwrote: Hi, I see that. So the query seems to be picking the first entry out of the after grouping by a field and displaying it. And it seems to make sense since Having clause seems incomplete. I believe we need to complete the condition by HAVING MIN(date_time) , or = something. After reading this, I see what the problem is: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html Then I read this: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html and changed it to this: SELECT d1.id, d1.date_time as MaxDateTime FROM data_cstmeta d1 LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND d1.date_time d2.date_time WHERE d2.recipe_id IS NULL Which works, but is painfully slow. For a table with 200k rows it's been running for 25 minutes and isn't done yet. That will be unacceptable to my users. On Mon, Sep 23, 2013 at 3:58 PM, Larry Martell larry.mart...@gmail.comwrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula narula...@gmail.com wrote: Hi, In your second query, you seem to have MIN(date_time), but you are talking about maximum. So your group by query is actually pulling the minimum date for this recipe. I pasted the wrong query in. I get the same results regardless of if I have MIN or MAX - I get the id of the max, but the date_time of the min. On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote: I want to find the rows from a table that have the max date_time for each recipe. I know I've done this before with group by and having, but I can't seem to get it to work now. I get the correct row id, but not the correct date_time. I'm sure I'm missing something simple. For purposes of showing an example, I'll use one recipe, 19166. For that recipe here's the row I would want: mysql select id, MAX(date_time) from data_cstmeta where recipe_id = 19166; +-+-+ | id | MAX(date_time) | +-+-+ | 1151701 | 2013-02-07 18:38:13 | +-+-+ 1 row in set (0.01 sec) I would think this query would give me that - it gives me the correct id, but not the correct date_time: mysql SELECT id, date_time as MaxDateTime FROM data_cstmeta where recipe_id = 19166 group by recipe_id HAVING MIN(date_time); +-+-+ | id | MaxDateTime | +-+-+ | 1151701 | 2010-12-13 16:16:55 | +-+-+ 1 row in set (0.01 sec) How can I fix this? Thanks! -larry
Re: Problem accessing phpmyadmin using IP from remote machine
Hi Abhishek, Yes I can do that. On Tue, Dec 11, 2012 at 12:50 PM, abhishek jain abhishek.netj...@gmail.comwrote: Can you ping ip 192.168.1.9 from other machines? On Dec 11, 2012 11:21 AM, Girish Talluru girish.dev1...@gmail.com wrote: Hi Guys, I have environment as specified below. Main server: Windows Server 2008 Virtual Box: Oracle VM VM OS: Ubuntu Inside Ubuntu I downloaded and configured Apache, MySql, PHP and PhpMyadmin individually following an article which is mentioned below. https://help.ubuntu.com/community/ApacheMySQLPHP IP Configuration: LAN Address: 192.168.1.9 VirtualBox Address: 192.168.56.1 As mentioned in the file I commented the bind address in my.cnf file. Using http://localhost/phpmyadmin or http://127.0.0.1/phpmyadmin I could able to access the phpmyadmin but when I tried with the LAN/VM address http://192.168.1.0/phpmyadmin or http://192.168.56.1/phpmyadmin I'm trying this because I have requirement to access phpmyadmin from other machines in LAN. I tried to change the bind address to VM and LAN address and checked it is also not working. Can anyone suggest where am I thinking wrong? Suggestions please. Thanks, Girish Talluru
Re: Problem accessing phpmyadmin using IP from remote machine
Can you ping ip 192.168.1.9 from other machines? On Dec 11, 2012 11:21 AM, Girish Talluru girish.dev1...@gmail.com wrote: Hi Guys, I have environment as specified below. Main server: Windows Server 2008 Virtual Box: Oracle VM VM OS: Ubuntu Inside Ubuntu I downloaded and configured Apache, MySql, PHP and PhpMyadmin individually following an article which is mentioned below. https://help.ubuntu.com/community/ApacheMySQLPHP IP Configuration: LAN Address: 192.168.1.9 VirtualBox Address: 192.168.56.1 As mentioned in the file I commented the bind address in my.cnf file. Using http://localhost/phpmyadmin or http://127.0.0.1/phpmyadmin I could able to access the phpmyadmin but when I tried with the LAN/VM address http://192.168.1.0/phpmyadmin or http://192.168.56.1/phpmyadmin I'm trying this because I have requirement to access phpmyadmin from other machines in LAN. I tried to change the bind address to VM and LAN address and checked it is also not working. Can anyone suggest where am I thinking wrong? Suggestions please. Thanks, Girish Talluru
Re: problem with INNODB tables
Am 15.03.2012 17:31, schrieb Malka Cymbalista: We are running MySQL version 5.0.45 on a Linux machine. Most of our tables are MyIASM but we have recently installed drupal 7 and drupal 7 requires INNODB tables. Every now and then when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. After much trial and error we came up with the following solution: We stop mysql We remove the files ib_logfile0 and ib_logfile1 We rename ibdata1 to ibdata1.old (mv ibdata1 ibdata1.old) We copy ibdata1.old back to ibdata1 (cp -a ibdata1.old ibdata1) We start mysql And everything is fine and the sites that use INNODB tables are fine. We clearly have a problem but we have no idea where to start looking. what about start with looking in the errorlog? signature.asc Description: OpenPGP digital signature
Re: Problem installing python MySQLdb on Mac OS X Version 10.5.8
On Mon, Jan 9, 2012 at 2:21 PM, Larry Martell larry.mart...@gmail.com wrote:
Some may consider this slightly off-topic, but I'm out of places to
turn to, so I'm hoping someone here can help me.
I am having no luck with getting the python module MySQLdb to work on
my Mac. I am getting the dreaded 'wrong architecture' message when I
try to import the module. I googled this, and found this page:
http://stackoverflow.com/questions/3061277/python-mysql-wrong-architecture-error
Following the instructions there I found that I did indeed have the 64
bit MySQL and a 32 bit python. I rectified that by deleting the 64-bit
install and then installing the 32-bit. Here's confirmation of that:
$ python -c 'import platform; print platform.platform()'
Darwin-9.8.0-i386-32bit
$ ls -l /usr/local/mysql
lrwxr-xr-x 1 root wheel 24 Jan 8 22:09 /usr/local/mysql -
mysql-5.5.19-osx10.5-x86
I rebuilt and reinstalled, but no joy. I added the env vars to my
.profile as suggested on that web page:
PATH=/usr/local/mysql/bin:${PATH}
export PATH
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib/
export VERSIONER_PYTHON_PREFER_64_BIT=no
export VERSIONER_PYTHON_PREFER_32_BIT=yes
export ARCHFLAGS=-arch i386
Sourced my .profile, rebuilt and reinstalled, same error.
I'm thinking that perhaps something from the build I did when I had I
the 64-bit MySQL is getting left behind and messing up the new build.
But I don't know what that could be.
I solved this, and this is exactly what it was. Although I was doing a
'sudo python setup.py clean' between builds, it was not removing
everything. I did a 'sudo rm -rf build/*', then rebuild and reinstall,
and this error went away.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: problem with installing MySQL 5.5 on OS Lion
* Martin Mueller martinmuel...@northwestern.edu [12 13:42]: .. In short, in my installation there seems a gap between mysql and mysql, and I can't find any good explanations for what I might have done wrong or how I could fix it. I'll be grateful for any help. I am probably not going to be very useful, but have you checked you environment? In particular $PATH. I recently transitioned from linux to mac lion and found out after some befuddlement that some terminal emulators provide environment variables that were not what I expected... -- Tim tim at tee jay forty nine dot com or akwebsoft dot com http://www.akwebsoft.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: problem with table
在 2011-10-31一的 16:18 +0530，nataraj 0102写道： Hello, I am using MYSQL database with Linux server. I have a problem with one table, when I tried to get the data from that table, I am getting error : “mysql error 4600: mysql server gone away. No connection, trying to re-connect” …. This error message getting and cursor getting stuck in next line…..mysql prompt does not appear. When “*check table tablename; “ * same problem getting….. And also When I tried to describe the table : “*desc tablename*;” its working properly on the same table…. Plz ……….. any one can help…. see here,http://dev.mysql.com/doc/refman/5.1/en/gone-away.html may it -- Best regards, Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) Using Gmail? Please read this important notice: http://www.fsf.org/campaigns/jstrap/gmail?10073. signature.asc Description: This is a digitally signed message part
Re: problem
- Original Message - From: Gavin Towey gto...@ffn.com The server will disconnect idle connections after a while. The wait_timeout variable controls how many seconds it will wait. You can set it for your connection when you connect by issuing a query like: SET SESSION wait_timeout=NNN; Just give it a large enough value. That will, of course, work; but it has as side effect that improperly terminated connections (application crash, ...) will stay around for that time, too. It is better to have your connection (-pooling) code be aware of connection timeouts, and have it transparantly reconnect. If I recall correctly, the client library has a ping() function or something similar. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: problem
The server will disconnect idle connections after a while. The wait_timeout variable controls how many seconds it will wait. You can set it for your connection when you connect by issuing a query like: SET SESSION wait_timeout=NNN; Just give it a large enough value. But also, since your program is going to be running continuously, there are many other reasons it could lose the connection. Your program should detect the error, and attempt to re-connect. Regards, Gavin Towey -Original Message- From: swaroop jois [mailto:jois_swar...@yahoo.com] Sent: Monday, May 02, 2011 12:35 AM To: mysql@lists.mysql.com Subject: problem Hello friends, I have MySQL server version 5.0.51a-Ubuntu installed on Ubuntu 8.04 machine . I would describe briefly what we are doing . Basically we have built a server that listen to Gprs connection from client and accepts data in form packets and inserts that data into MySQL database.I run three commands .1.listening to Gprs connection and displaying all the received packets on the terminal.2.Number of packets read will showed in Java serial forwarder (Tinyos for reference )which listens on another port 3.command that invokes Java files for inserting data into database table . Initially when i run the command everything works fine and when he receive packets he is able to insert data into table in MySQL database . He will still be listening on the port (i.e he is running 24*7)Assume i receive data after 12 hrs .i am experiencing the problem of .It may not necessarily be 12 hrs . If i have to insert data again i have to recompile the code again and run all the commands . The error that is troubling is Result for query failed. SQLState = 08003i googled the error and found that this Sqlstate indicates connection does not exist . I dont have any clues.Can any one help me please ? Regards,Swaroop IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. 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
Re: problem
Seems more a java issue than a mysql issue... check for your parameters for any keepalive or persostent option... I think you wouldn't need to recompile just re-run or at leat, reboot the server.. On Mon, May 2, 2011 at 2:34 AM, swaroop jois jois_swar...@yahoo.com wrote: Hello friends, I have MySQL server version 5.0.51a-Ubuntu installed on Ubuntu 8.04 machine . I would describe briefly what we are doing . Basically we have built a server that listen to Gprs connection from client and accepts data in form packets and inserts that data into MySQL database.I run three commands .1.listening to Gprs connection and displaying all the received packets on the terminal.2.Number of packets read will showed in Java serial forwarder (Tinyos for reference )which listens on another port 3.command that invokes Java files for inserting data into database table . Initially when i run the command everything works fine and when he receive packets he is able to insert data into table in MySQL database . He will still be listening on the port (i.e he is running 24*7)Assume i receive data after 12 hrs .i am experiencing the problem of .It may not necessarily be 12 hrs . If i have to insert data again i have to recompile the code again and run all the commands . The error that is troubling is Result for query failed. SQLState = 08003i googled the error and found that this Sqlstate indicates connection does not exist . I dont have any clues.Can any one help me please ? Regards,Swaroop
Re: problem
Swaroop: How are you handling the connections to db ? only one, with a pool, something adhoc ?. It looks like the connection is only one and expires after a time out and you get the error that there is no connection. Carlos On 5/2/2011 3:15 PM, Andrés Tello wrote: Seems more a java issue than a mysql issue... check for your parameters for any keepalive or persostent option... I think you wouldn't need to recompile just re-run or at leat, reboot the server.. On Mon, May 2, 2011 at 2:34 AM, swaroop joisjois_swar...@yahoo.com wrote: Hello friends, I have MySQL server version 5.0.51a-Ubuntu installed on Ubuntu 8.04 machine . I would describe briefly what we are doing . Basically we have built a server that listen to Gprs connection from client and accepts data in form packets and inserts that data into MySQL database.I run three commands .1.listening to Gprs connection and displaying all the received packets on the terminal.2.Number of packets read will showed in Java serial forwarder (Tinyos for reference )which listens on another port 3.command that invokes Java files for inserting data into database table . Initially when i run the command everything works fine and when he receive packets he is able to insert data into table in MySQL database . He will still be listening on the port (i.e he is running 24*7)Assume i receive data after 12 hrs .i am experiencing the problem of .It may not necessarily be 12 hrs . If i have to insert data again i have to recompile the code again and run all the commands . The error that is troubling is Result for query failed. SQLState = 08003i googled the error and found that this Sqlstate indicates connection does not exist . I dont have any clues.Can any one help me please ? Regards,Swaroop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: problem starting server
Am 26.04.2011 14:56, schrieb Gergely Buday: Hi there, I installed a mysql server on my Mac using homebrew. $ mysql --version mysql Ver 14.12 Distrib 5.0.91, for apple-darwin10.0 (i386) using EditLine wrapper When trying to start it I get $ mysql.server start Starting MySQL .. ERROR! The server quit without updating PID file (/usr/local/var/mysql/dbxserver.lan.pid). What should I do to start MySQL? does the mysql-user have write-permissions to /usr/local/var/mysql/? this is the default workingdir and there are the databases stored too in the data folder signature.asc Description: OpenPGP digital signature
RE: problem starting server
Go to the cmd line and type ps -ef , do you see mysqld listed? If so then it is already running. -Original Message- From: Gergely Buday [mailto:gbu...@gmail.com] Sent: Tuesday, April 26, 2011 5:56 AM To: mysql@lists.mysql.com Subject: problem starting server Hi there, I installed a mysql server on my Mac using homebrew. $ mysql --version mysql Ver 14.12 Distrib 5.0.91, for apple-darwin10.0 (i386) using EditLine wrapper When trying to start it I get $ mysql.server start Starting MySQL .. ERROR! The server quit without updating PID file (/usr/local/var/mysql/dbxserver.lan.pid). What should I do to start MySQL? - Gergely -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.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: Problem filtering with a like expression
Hi,
|| isn't the concatenation operator by default. If you want it to be set
sql_mode=PIPE_AS_CONCAT. Otherwise, use the CONCAT() function instead of
|| operator.
Peter Boros
On 03/21/2011 11:51 AM, Johan De Taeye wrote:
I ran into this case where a like expression is not evaluated correctly if
the pattern is an expression.
The example below shows a case where *AAA* is not considered *like 'A' ||
'%'*
Is this a known limitation? Or a bug?
create table lookup (
name varchar(60)
);
insert into lookup (name) values ('AAA');
select * from lookup where name like 'A%';
= 1 record returned. OK
select * from lookup where name like 'A' || '%';
= returns nothing. INCORRECT!
select * from lookup where name like ('A' || '%');
= same as previous and returns nothing. INCORRECT!
I reproduced this problem on win32 using versions 5.1 and 5.5.10
Best regards,
Johan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem filtering with a like expression
On Mon, 21 Mar 2011 11:51:27 +0100
Johan De Taeye johan.de.ta...@gmail.com wrote:
insert into lookup (name) values ('AAA');
select * from lookup where name like 'A%';
= 1 record returned. OK
select * from lookup where name like 'A' || '%';
= returns nothing. INCORRECT!
The query is incorrect. The OR switch does not act as an ellipsis, and does not
apply both values to the LIKE. You need to write LIKE X OR LIKE Y, as
select * from lookup where name like 'A' || or name like '%';
select * from lookup where name like ('A' || '%');
= same as previous and returns nothing. INCORRECT!
Again correct, you tried to match `name` against boolean TRUE (the evaluation
of you expression).
Best regards,
Johan
--
Simcha Younger sim...@syounger.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: Problem filtering with a like expression
After updating the SQL_MODE, it works as I expect.
Thanks for your prompt replies!
Johan
-Original Message-
From: petya [mailto:pe...@petya.org.hu]
Sent: Monday, March 21, 2011 12:10 PM
To: Johan De Taeye
Cc: mysql@lists.mysql.com
Subject: Re: Problem filtering with a like expression
Hi,
|| isn't the concatenation operator by default. If you want it to be set
sql_mode=PIPE_AS_CONCAT. Otherwise, use the CONCAT() function instead of
|| operator.
Peter Boros
On 03/21/2011 11:51 AM, Johan De Taeye wrote:
I ran into this case where a like expression is not evaluated
correctly if the pattern is an expression.
The example below shows a case where *AAA* is not considered *like 'A'
||
'%'*
Is this a known limitation? Or a bug?
create table lookup (
name varchar(60)
);
insert into lookup (name) values ('AAA');
select * from lookup where name like 'A%';
= 1 record returned. OK
select * from lookup where name like 'A' || '%';
= returns nothing. INCORRECT!
select * from lookup where name like ('A' || '%');
= same as previous and returns nothing. INCORRECT!
I reproduced this problem on win32 using versions 5.1 and 5.5.10
Best regards,
Johan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: problem open a innodb table
Dont you have mysql dump file for those table? It is best way to restore InnoDB tables. 2011/1/7 Elim PDT e...@pdtnetworks.net I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of openning innodb tables even after I copied the back-up files of few months ago. I don't have many innodb tables and so this not causing too much trouble. I don't know what to do for restoring those tables, and not even know if that is possible or not. Please help. Attached is a table of primes that contains 2+ million primes yet it is so small that I can attached here. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
Re: Problem with WHERE .. IN
The sub-select only returns a single row, so IN(...) is only looking at a single value in the list .. it doesn't expand to into IN (5,7,11). On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair msub...@gmail.com wrote: I have table post (id INT and parent VARCHAR) +--+-+ | id | parent | +--+-+ |1 | 0 | |2 | 0 | |3 | 1 | |4 | 0 | |5 | 1 | |6 | 0 | |7 | 1,5 | |8 | 1,5 | |9 | 1,5 | | 10 | 5,7,11 | | 11 | 1,5,7,10| | 12 | 1,5,7,10,11 | +--+-+ SELECT id FROM post WHERE where id IN (SELECT parent FROM post WHERE id = 10); +--+ | id | +--+ |5 | +--+ whereas the results I want is +--+ | id | +--+ |5 | |7 | | 11 | +--+ Please tell me, where is wrong Thanks Regards -- Muhammad Subair +62 8176583311 -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Problem with WHERE .. IN
In the last episode (Dec 20), Johnny Withers said: On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair msub...@gmail.com wrote: I have table post (id INT and parent VARCHAR) +--+-+ | id | parent | +--+-+ |1 | 0 | |2 | 0 | |3 | 1 | |4 | 0 | |5 | 1 | |6 | 0 | |7 | 1,5 | |8 | 1,5 | |9 | 1,5 | | 10 | 5,7,11 | | 11 | 1,5,7,10| | 12 | 1,5,7,10,11 | +--+-+ SELECT id FROM post WHERE where id IN (SELECT parent FROM post WHERE id = 10); +--+ | id | +--+ |5 | +--+ whereas the results I want is +--+ | id | +--+ |5 | |7 | | 11 | +--+ Please tell me, where is wrong The sub-select only returns a single row, so IN(...) is only looking at a single value in the list .. it doesn't expand to into IN (5,7,11). You might need to use the FIND_IN_SET function: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set so something like this should work (although the 2nd query won't be able to use any indexes): SELECT parent FROM post WHERE id = 10 into @parent; SELECT id from post where FIND_IN_SET(id, @parent) 0; If you normalize your table so that you have one row per relation: +--+-+ | id | parent | +--+-+ | 10 | 5 | | 10 | 7 | | 10 | 11 | +--+-+ , then your original query would work the way you expected. -- Dan Nelson dnel...@allantgroup.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: Problem with mytop
Hi Baron This tool works better than mytop, you solved my problem, thanks a lot Carlos Date: Tue, 17 Aug 2010 10:41:23 -0400 Subject: Re: Problem with mytop From: ba...@xaprb.com To: mysql@lists.mysql.com Carlos, Have you tried innotop instead? It's a better replacement for mytop. (I wrote it.) - Baron On Tue, Aug 17, 2010 at 8:50 AM, Carlos Eduardo Caldi ce_ca...@hotmail.com wrote: If sombody can help me I'll be gratefull I have a problem with mytop1.6 on Mandriva 2010.1, when I install it on a Slave server mytop works, but when I install it on a Master Server don't works, don't display the queries. I use mysql version 5.0.77 and linux mandriva 2010.1 Very Thanks Carlos Caldi - DBA -- Baron Schwartz Percona Inc http://www.percona.com/ Consulting, Training, Support Services for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ce_ca...@hotmail.com
Re: Problem with mytop
Carlos, Have you tried innotop instead? It's a better replacement for mytop. (I wrote it.) - Baron On Tue, Aug 17, 2010 at 8:50 AM, Carlos Eduardo Caldi ce_ca...@hotmail.com wrote: If sombody can help me I'll be gratefull I have a problem with mytop1.6 on Mandriva 2010.1, when I install it on a Slave server mytop works, but when I install it on a Master Server don't works, don't display the queries. I use mysql version 5.0.77 and linux mandriva 2010.1 Very Thanks Carlos Caldi - DBA -- Baron Schwartz Percona Inc http://www.percona.com/ Consulting, Training, Support Services for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with IF() inside of a select statement
On 7/8/2010 10:59 AM, John Nichel wrote: Hi, I'm hoping what I'm trying to do can be done, but I can't seem to find the right syntax. I have the following query: SELECT ... snipped ... if( ( select d.date from orders d left join order_details e on d.orderid = e.orderid where e.productid = a.productid order by d.date desc limit 1 ) 0, d.date, 0 The results of your dependent subquery should not exist beyond the evaluation portion of the IF() processing. Therefore, the date column of your results are also missing. To get this same effect you would need to double-execute the query within the IF() as in IF((...subquery...) 0, (...subquery...) ,0) Or, you can make certain your subquery returns either a value or zero as part of its logic and avoid the IF in the outer query. Or, you add this subquery to your main query as another JOIN. SELECT ... FROM products a INNER JOIN (...subquery...) as d This has the advantage of only needing to execute the subquery once per row of the main query and it gives you the chance to rewrite the IF() clause as simply IF(d.date 0 , d.date, 0) as last_sold. Of course, creating a separate table of just the appropriate orders.date values (even if it's a temporary table) would provide the results even faster (especially if you index it). Yours, -- Shawn Green MySQL Principle 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
Re: Problem with IF() inside of a select statement
Is there a way I can do a IF((SELECT.), expr2, expr3) and have expr2 populate with whatever is returned from the select statement? Yes, select if( (select count(*) from mytable ) 100, 1, 0) works fine. The alias inside your last If(...), though, is not visible outside its parentheses; why not move that join logic to the query's main clause? PB - On 7/8/2010 9:59 AM, John Nichel wrote to: Hi, I'm hoping what I'm trying to do can be done, but I can't seem to find the right syntax. I have the following query: SELECT a.productid, a.productcode, a.product, if( a.local_stock = 'y' || a.is_commercial = 'n' || freight_class = '', 'y', 'n' ) as local_stock, if( ( SELECT count(b.productid) FROM pricing b WHERE a.productid = b.productid ) 1, 'y', 'n' ) as price_breaks, if( a.productid in (select c.productid from variants c), 'y', 'n' ) as is_variant, if( a.forsale = 'N', 'y', 'n' ) as disabled, if( ( select d.date from orders d left join order_details e on d.orderid = e.orderid where e.productid = a.productid order by d.date desc limit 1 ) 0, d.date, 0 ) as last_sold FROM products a The query is erroring out on 'd.date' in expression two of the if statement, #1109 - Unknown table 'd' in field list. Is there a way I can do a IF((SELECT.), expr2, expr3) and have expr2 populate with whatever is returned from the select statement? Thank you. -- John C. Nichel IV System Administrator KegWorks http://www.kegworks.com 716.362.9212 x16 j...@kegworks.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.439 / Virus Database: 271.1.1/2989 - Release Date: 07/08/10 06:36: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: Problem with IF() inside of a select statement
-Original Message- From: Peter Brawley [mailto:peter.braw...@earthlink.net] Sent: Thursday, July 08, 2010 12:27 PM To: mysql@lists.mysql.com Subject: Re: Problem with IF() inside of a select statement Is there a way I can do a IF((SELECT.), expr2, expr3) and have expr2 populate with whatever is returned from the select statement? Yes, select if( (select count(*) from mytable ) 100, 1, 0) works fine. The alias inside your last If(...), though, is not visible outside its parentheses; why not move that join logic to the query's main clause? PB - Thank you for the reply. Unfortunately, not all of the rows I'm selecting in the main clause will have an entry in the other table, so if I do it that way, it won't return any data for the rows that don't have a match in the order_details table. On 7/8/2010 9:59 AM, John Nichel wrote to: Hi, I'm hoping what I'm trying to do can be done, but I can't seem to find the right syntax. I have the following query: SELECT a.productid, a.productcode, a.product, if( a.local_stock = 'y' || a.is_commercial = 'n' || freight_class = '', 'y', 'n' ) as local_stock, if( ( SELECT count(b.productid) FROM pricing b WHERE a.productid = b.productid ) 1, 'y', 'n' ) as price_breaks, if( a.productid in (select c.productid from variants c), 'y', 'n' ) as is_variant, if( a.forsale = 'N', 'y', 'n' ) as disabled, if( ( select d.date from orders d left join order_details e on d.orderid = e.orderid where e.productid = a.productid order by d.date desc limit 1 ) 0, d.date, 0 ) as last_sold FROM products a The query is erroring out on 'd.date' in expression two of the if statement, #1109 - Unknown table 'd' in field list. Is there a way I can do a IF((SELECT.), expr2, expr3) and have expr2 populate with whatever is returned from the select statement? 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
Re: Problem with installing MySQL
alba.albetti wrote: I've just installed MySQL on Windows 2000. I've opened the MS-DOS windows and I've written C:\Programs\MySQL\...\bin\mysqladmin -u root -p password mysql2010 After the enter the prompt says Enter password: and I've given enter and I get mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' After installing MySQL what do I have to do left? Sorry my this is my firt time with MySQL If you have not set a password for the root user, yet, then there is no password. Leave off the -p option and see if it allows you to set it that way. -- Shawn Green MySQL Principle 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
Re: Problem with installing MySQL
what is mysql2010, it it host name or password. If password, there cannot be space for a password, it its host, then dont use if u connecting from the server. regards anandkl On Fri, Apr 9, 2010 at 2:06 AM, Michael Dykman mdyk...@gmail.com wrote: If you want to specify the password itself on the command line use '--password' (double hyphen). '-p' requests interactive password prompt. - michael On Thu, Apr 8, 2010 at 4:31 PM, Carsten Pedersen cars...@bitbybit.dk wrote: don't have a space between '-p' and 'password', i.e. -ppassword / Carsten alba.albetti skrev: I've just installed MySQL on Windows 2000. I've opened the MS-DOS windows and I've written C:\Programs\MySQL\...\bin\mysqladmin -u root -p password mysql2010 After the enter the prompt says Enter password: and I've given enter and I get mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' After installing MySQL what do I have to do left? Sorry my this is my firt time with MySQL Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: problem reading array data
On Wed, 2010-04-07 at 21:37 -0700, Karthick Subramanian wrote:
I need to read an array's content using a while loop and inside the loop
read another array's content. Using the variables from the two arrays I need
to execute a query. My problem is the inner while loop reads all records of
the array whereas the outer while loop exits after reading the first record.
That is after execution of the inner while loop finishes, the control does
not move to the outer while loop to read the next array element.
I am appending my code below. Please help me solve this problem
$arrdata = mysql_query(SELECT OldDeptCode, MajorCode FROM
tblolddeptcodemajorcode);
$result2 = mysql_query(SELECT SSN, DeptCode, ActCode FROM
tblapprovedactivitydetail);
while($info = mysql_fetch_assoc($arrdata))
{
$OldDeptCode = $info['OldDeptCode'];
$MajorCode = $info['MajorCode'];
while($row2 = mysql_fetch_assoc($result2))
{
$SSN = $row2['SSN'];
$DeptCode = $row2['DeptCode'];
$ActCode = $row2['ActCode'];
$query = INSERT INTO test1 (SSN, MajorCode, ActCode) VALUES
('$SSN', '$MajorCode', '$ActCode');
if($OldDeptCode != 'COAS' $OldDeptCode != 'CSS' $OldDeptCode
!= 'EC' $OldDeptCode != 'EECS' $OldDeptCode != 'FW' $OldDeptCode !=
'GEO' $OldDeptCode != 'SED' $OldDeptCode != 'VM' $OldDeptCode ==
$DeptCode)
{
mysql_query($query);
}
}
echo done;
}
echo all done;
Thank you
OK, at a 5 minute glance:
1.) I think the reason it isn't working is that MySQL/PHP don't support
reading from two buffered result sets concurrently: read from one then
the other.
2.) The code you've written is very inefficent. Databases are fast at
mangling database results whilst PHP isn't. You can filter and combine
both data set into the single query:
SELECT OldDeptCode, MajorCode, SSN, DeptCode, ActCode FROM
tblolddeptcodemajorcode old
INNER JOIN tblapprovedactivitydetail new on new.DeptCode=old.OldDeptCode
where old.OldDeptCode not in
('COAS','CSS','EC','EECS','FW','GEO','SED','VM')
If locking tables isn't a problem in your envrioment the entire loop can
be replaced with and insert into test1 SELECT statement. Use SQL
for record manipulation, that's its purpose. For maximum efficeny make
sure both tblolddeptcodemajorcode.OldDeptCode and
tblapprovedactivitydetail.DeptCode have indexes and the same format. Use
explain on the query to check its performance.
3.) If this is part of a long running script or library make sure you
use mysq_free_result() on the result set
4.) You might want some error handling for the queries failing.
HTH
Nigel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: problem reading array data
On Thu, Apr 8, 2010 at 10:25 AM, nwood nw...@plus.net wrote: OK, at a 5 minute glance: 1.) I think the reason it isn't working is that MySQL/PHP don't support reading from two buffered result sets concurrently: read from one then the other. Specifically, it doesn't support multiple buffered result sets *on one connection*. That means that you *can* do this, but you'll have to open a separate connection for each buffered resultset. -- 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: Problem with installing MySQL
At 09:02 AM 4/8/2010, alba\.albetti wrote: I've just installed MySQL on Windows 2000. I've opened the MS-DOS windows and I've written C:\Programs\MySQL\...\bin\mysqladmin -u root -p password mysql2010 After the enter the prompt says Enter password: and I've given enter and I get mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' After installing MySQL what do I have to do left? Sorry my this is my firt time with MySQL Thanks! Don't add a space between the -u and the user name or -p and the password. Example, use: C:\Programs\MySQL\...\bin\mysqladmin -uroot -p mysql2010 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with installing MySQL
don't have a space between '-p' and 'password', i.e. -ppassword / Carsten alba.albetti skrev: I've just installed MySQL on Windows 2000. I've opened the MS-DOS windows and I've written C:\Programs\MySQL\...\bin\mysqladmin -u root -p password mysql2010 After the enter the prompt says Enter password: and I've given enter and I get mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' After installing MySQL what do I have to do left? Sorry my this is my firt time with MySQL 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: Problem with installing MySQL
If you want to specify the password itself on the command line use '--password' (double hyphen). '-p' requests interactive password prompt. - michael On Thu, Apr 8, 2010 at 4:31 PM, Carsten Pedersen cars...@bitbybit.dk wrote: don't have a space between '-p' and 'password', i.e. -ppassword / Carsten alba.albetti skrev: I've just installed MySQL on Windows 2000. I've opened the MS-DOS windows and I've written C:\Programs\MySQL\...\bin\mysqladmin -u root -p password mysql2010 After the enter the prompt says Enter password: and I've given enter and I get mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' After installing MySQL what do I have to do left? Sorry my this is my firt time with MySQL Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem starting connection pooling
On Jan 22, 2010, at 10:21 AM, Mark Witczak wrote: [snip] I create the WAR (jar cvf testapp.war *), undeploy the old version and redeploy the new one through Tomcat Web Application Manager. Then restart Tomcat (sudo /etc/init.d/tomcat restart). The result is: Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: ContextListener: contextInitialized() Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: SessionListener: contextInitialized() Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet jsp threw exception javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown Source) at org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188) at org.apache.jsp.test_jsp._jspService(test_jsp.java:138) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) Blah, Blah, Blah More info: The connection to MySQL tested successfully using the command line 'mysql'. There are no firewalls, that I can find, between the servers. Mark, What message is where you posted blah blah blah. *Usually* there's information from the driver right there, which will have the details of why there was a communications link failure. If I had to guess, your mysql server was started with --skip-networking (most debian-based distributions do this by default), and mysql is using unix domain sockets (which Java can't) to speak to mysqld. If that's the case, you'll have to reconfigure mysqld to listen at least on the loopback (127.0.0.1) by removing --skip-networking from my.cnf and adding --bind-address=127.0.0.1 -Mark -- Mark Matthews, Architect - Enterprise Tools MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem starting connection pooling
Alright, here is the entire log entry: Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: ContextListener: contextInitialized() Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: SessionListener: contextInitialized() Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet jsp threw exception javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: org.apache.commons.dbcp.SQLNe stedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any pack ets from the server.) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown Source) at org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188) at org.apache.jsp.test_jsp._jspService(test_jsp.java:138) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:269) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAsPrivileged(Subject.java:537) at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:301) at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:162) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:283) at org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56) at org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189) at java.security.AccessController.doPrivileged(Native Method) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454) at java.lang.Thread.run(Thread.java:636) Also, the MySQL instance I'm connecting to is hosted by dreamhost. I don't have any control over their networking configuration. Is there a command that will tell me if the --skip-networking flag was used for startup? On 1/22/2010 11:38 AM, Mark Matthews wrote: On Jan 22, 2010, at 10:21 AM, Mark Witczak wrote: [snip] I create the WAR (jar cvf testapp.war *), undeploy the old version and redeploy the new one through Tomcat Web Application Manager. Then restart Tomcat (sudo /etc/init.d/tomcat restart). The result is: Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: ContextListener: contextInitialized() Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: SessionListener: contextInitialized() Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet jsp threw exception javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source)
Re: Problem starting connection pooling
On Jan 22, 2010, at 10:49 AM, Mark Witczak wrote: Alright, here is the entire log entry: Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: ContextListener: contextInitialized() Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: SessionListener: contextInitialized() Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet jsp threw exception javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: org.apache.commons.dbcp.SQLNe stedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any pack ets from the server.) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown Source) at org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188) at org.apache.jsp.test_jsp._jspService(test_jsp.java:138) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:269) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAsPrivileged(Subject.java:537) at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:301) at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:162) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:283) at org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56) at org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189) at java.security.AccessController.doPrivileged(Native Method) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454) at java.lang.Thread.run(Thread.java:636) Also, the MySQL instance I'm connecting to is hosted by dreamhost. I don't have any control over their networking configuration. Is there a command that will tell me if the --skip-networking flag was used for startup? Mark, In mysql, issuing show variables like 'skip_networking' should tell you. -Mark -- Mark Matthews, Architect - Enterprise Tools MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem starting connection pooling
skip-networking is OFF On 1/22/2010 12:09 PM, Mark Matthews wrote: On Jan 22, 2010, at 10:49 AM, Mark Witczak wrote: Alright, here is the entire log entry: Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: ContextListener: contextInitialized() Jan 21, 2010 9:40:35 PM org.apache.catalina.core.ApplicationContext log INFO: SessionListener: contextInitialized() Jan 21, 2010 9:43:06 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet jsp threw exception javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: org.apache.commons.dbcp.SQLNe stedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any pack ets from the server.) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.getConnection(Unknown Source) at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doStartTag(Unknown Source) at org.apache.jsp.test_jsp._jspx_meth_sql_005fquery_005f0(test_jsp.java:188) at org.apache.jsp.test_jsp._jspService(test_jsp.java:138) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:269) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAsPrivileged(Subject.java:537) at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:301) at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:162) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:283) at org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56) at org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189) at java.security.AccessController.doPrivileged(Native Method) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454) at java.lang.Thread.run(Thread.java:636) Also, the MySQL instance I'm connecting to is hosted by dreamhost. I don't have any control over their networking configuration. Is there a command that will tell me if the --skip-networking flag was used for startup? Mark, In mysql, issuing show variables like 'skip_networking' should tell you. -Mark No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.432 / Virus Database: 271.1.1/2638 - Release Date: 01/22/10 07:34:00
Re: Problem with mysql
On Monday 11 January 2010 08:53, Mihamina Rakotomandimby wrote: Jørn Dahl-Stamnes sq...@dahl-stamnes.net : I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? Reverse resolution? I have checked that, and reverse DNS is OK - both resolve to 192.18.2.22 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
use mysql; select * from user; see if you able to see 'r2-d2' entry in this table. also you can try this grant all on . to 'root'@'%' idenfified by 'secret-password'; regards anandkl On Mon, Jan 11, 2010 at 1:40 PM, Jørn Dahl-Stamnes sq...@dahl-stamnes.netwrote: On Monday 11 January 2010 08:53, Mihamina Rakotomandimby wrote: Jørn Dahl-Stamnes sq...@dahl-stamnes.net : I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? Reverse resolution? I have checked that, and reverse DNS is OK - both resolve to 192.18.2.22 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: Problem with mysql
On Monday 11 January 2010 09:55, Ananda Kumar wrote: use mysql; select * from user; see if you able to see 'r2-d2' entry in this table. also you can try this grant all on . to 'root'@'%' idenfified by 'secret-password'; I just found the cause of the problem... /etc/nsswitch.conf. It said that files should be used before dns. After I changed the order, it worked (so did the strange sendmail problem I had). But still I wonder why it worked on the other host, since it also had the order files dns in it's nsswitch.conf file... Thanks for your support. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
Jørn Dahl-Stamnes sq...@dahl-stamnes.net : I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? Reverse resolution? I have checked that, and reverse DNS is OK - both resolve to 192.18.2.22 I read you solved your problem, but by reverse I meant what r2-d2.dahl-stamnes.net resolves to. -- Architecte Informatique chez Blueline/Gulfsat: Administration Systeme, Recherche Developpement +261 34 29 155 34 / +261 33 11 207 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
(Correction, seel below) Jørn Dahl-Stamnes sq...@dahl-stamnes.net : I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? Reverse resolution? I have checked that, and reverse DNS is OK - both resolve to 192.18.2.22 I read you solved your problem, but by reverse I meant what r2-d2.dahl-stamnes.net resolves to. Sorry (...) what 192.18.2.22 resolves to. -- Architecte Informatique chez Blueline/Gulfsat: Administration Systeme, Recherche Developpement +261 34 29 155 34 / +261 33 11 207 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
On Monday 11 January 2010 12:33, Mihamina Rakotomandimby wrote: (Correction, seel below) Jørn Dahl-Stamnes sq...@dahl-stamnes.net : I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? Reverse resolution? I have checked that, and reverse DNS is OK - both resolve to 192.18.2.22 I read you solved your problem, but by reverse I meant what r2-d2.dahl-stamnes.net resolves to. Sorry (...) what 192.18.2.22 resolves to. it resolv to r2-d2.dahl-stamnes.net -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
Hi, The grant stmt has to be as below. Grant all on databasename.tablename to usern...@iporhostname identified by 'password'; Use as above Grant stmt doesn't require a flush stmt. Suresh Kuna MySQL DBA --Original Message-- From: Jørn Dahl-Stamnes To: mysql@lists.mysql.com ReplyTo: sq...@dahl-stamnes.net Subject: Problem with mysql Sent: Jan 10, 2010 3:20 PM Hello I have installed mysql, apache and phpmyadmin on a new server running Fedora Core 12. For some unknown reason I cannot connect to the local host using a full hostname. This is what I have done: r2-d2# mysql -u root -p grant all on . to 'root'@'r2-d2' idenfified by 'secret-password'; grant all on . to 'root'@'r2-d2.dahl-stamnes.net' idenfified by 'secret-password'; flush privileges; quit r2-d2# mysql -h r2-d2 -u root -p (works) r2-d2# mysql -h r2-d2.dahl-stamnes.net -u root -p ERROR 1130 (HY000): Host 'r2-d2.dahl-stamnes.net.' is not allowed to connect to this MySQL server Then I did: r2-d2# mysql -u root -p grant all on . to 'root'@'jedi.dahl-stamnes.net' idenfified by 'secret-password'; jedi# mysql -h r2-d2.dahl-stamnes.net -u root -p (works) I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com Sent from BlackBerry® on Airtel
Re: Problem with mysql
On Sunday 10 January 2010 12:34, sureshkumar...@gmail.com wrote: Hi, The grant stmt has to be as below. Grant all on databasename.tablename to usern...@iporhostname identified by 'password'; Use as above Grant stmt doesn't require a flush stmt. Woops... Seems like the * in my first mail has dissapeared. The correct grant statment I used was: r2-d2# mysql -u root -p grant all on *.* to 'root'@'r2-d2' idenfified by 'secret-password'; grant all on *.* to 'root'@'r2-d2.dahl-stamnes.net' idenfified by 'secret-password'; grant all on *.* to 'root'@'jedi.dahl-stamnes.net' idenfified by 'secret-password'; --Original Message-- From: Jørn Dahl-Stamnes To: mysql@lists.mysql.com ReplyTo: sq...@dahl-stamnes.net Subject: Problem with mysql Sent: Jan 10, 2010 3:20 PM Hello I have installed mysql, apache and phpmyadmin on a new server running Fedora Core 12. For some unknown reason I cannot connect to the local host using a full hostname. This is what I have done: r2-d2# mysql -u root -p grant all on . to 'root'@'r2-d2' idenfified by 'secret-password'; grant all on . to 'root'@'r2-d2.dahl-stamnes.net' idenfified by 'secret-password'; flush privileges; quit r2-d2# mysql -h r2-d2 -u root -p (works) r2-d2# mysql -h r2-d2.dahl-stamnes.net -u root -p ERROR 1130 (HY000): Host 'r2-d2.dahl-stamnes.net.' is not allowed to connect to this MySQL server Then I did: r2-d2# mysql -u root -p grant all on . to 'root'@'jedi.dahl-stamnes.net' idenfified by 'secret-password'; jedi# mysql -h r2-d2.dahl-stamnes.net -u root -p (works) I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
Jørn Dahl-Stamnes sq...@dahl-stamnes.net : I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? Reverse resolution? -- Architecte Informatique chez Blueline/Gulfsat: Administration Systeme, Recherche Developpement +261 34 29 155 34 / +261 33 11 207 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problem with MySQL user
Hi John, You can turn of name resolution by adding skip-name-resolve to the [mysqld] section of your my.cnf file. Regards, Gavin Towey -Original Message- From: John Oliver [mailto:joli...@john-oliver.net] Sent: Wednesday, September 16, 2009 4:24 PM To: mysql@lists.mysql.com Subject: Problem with MySQL user I'm working with two VMs, one a web server, one a MySQL database server. In mysql, I added a 'user'@'172.16.1.2' with privileges appropriate for the web site, and that works. The VMs got shipped off to a hosting facility. They got the 172.16.1.X network between the two VMs up, but when they try to mysql -h 172.16.1.1 -uuser -p and enter the password, they get an error denying 'user'@'hostname' Why is MySQL resolving the IPs? I figured it would be quicker to fix that than it would be to look up the specific privileges for that user, add 'user'@'hostname', and then go in and start modifying the config (it's set in settings.php as well as several places in the httpd.conf) I do have /etc/hosts entries on both VMs resolving both 172.16.1.1 / 2 to known hostnames. -- *** * John Oliver http://www.john-oliver.net/ * * * *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original 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: Problem w/ mysqldump
On 9/2/2009 3:43 AM Victor Subervi said... Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) First, test this at the system command line -- you'll likely get an empty file there as well, so calling from within python simply does the same. Then read the mysqldump docs for the command arguments and supply the database name... Emile This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problem w/ mysqldump
While not python, maybe this bash script will give you some clues? http://daevid.com/content/examples/daily_backup.php Also, please don't cross post to multiple lists. Not everyone on this mySQL list is on the python list and vice versa. It's just bad netiquette. -Original Message- From: news [mailto:n...@ger.gmane.org] On Behalf Of Emile van Sebille Sent: Wednesday, September 02, 2009 7:18 AM To: mysql@lists.mysql.com Cc: python-l...@python.org Subject: Re: Problem w/ mysqldump On 9/2/2009 3:43 AM Victor Subervi said... Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) First, test this at the system command line -- you'll likely get an empty file there as well, so calling from within python simply does the same. Then read the mysqldump docs for the command arguments and supply the database name... Emile This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem w/ mysqldump
I checked my own backup script from earlier years and everything was good. You know, if I could simply figure out where the data was actually stored, in what file, I could copy it over to another computer. Any ideas? Thanks, V On Wed, Sep 2, 2009 at 4:09 PM, Daevid Vincent dae...@daevid.com wrote: While not python, maybe this bash script will give you some clues? http://daevid.com/content/examples/daily_backup.php Also, please don't cross post to multiple lists. Not everyone on this mySQL list is on the python list and vice versa. It's just bad netiquette. -Original Message- From: news [mailto:n...@ger.gmane.org] On Behalf Of Emile van Sebille Sent: Wednesday, September 02, 2009 7:18 AM To: mysql@lists.mysql.com Cc: python-l...@python.org Subject: Re: Problem w/ mysqldump On 9/2/2009 3:43 AM Victor Subervi said... Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) First, test this at the system command line -- you'll likely get an empty file there as well, so calling from within python simply does the same. Then read the mysqldump docs for the command arguments and supply the database name... Emile This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=victorsube...@gmail.com
RE: Problem w/ mysqldump
If it's MYISAM tables, then they're all self contained in folders in /var/lib/mysql/spreadsheets. Remember that if you do copy the files, to shut down mysql first or you could copy corrupt files. If you're using INNODB, then the schema is in that folder, but the actual data is in the /var/lib/mysql/ib* files. Along with all the other INNODB databases you may have on the system -- all mashed together. Hence the need for a mysql dump tool ;-) Mysql dump is the better way to go in either situation. -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Wednesday, September 02, 2009 1:43 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: Re: Problem w/ mysqldump I checked my own backup script from earlier years and everything was good. You know, if I could simply figure out where the data was actually stored, in what file, I could copy it over to another computer. Any ideas? Thanks, V On Wed, Sep 2, 2009 at 4:09 PM, Daevid Vincent dae...@daevid.com wrote: While not python, maybe this bash script will give you some clues? http://daevid.com/content/examples/daily_backup.php Also, please don't cross post to multiple lists. Not everyone on this mySQL list is on the python list and vice versa. It's just bad netiquette. -Original Message- From: news [mailto:n...@ger.gmane.org] On Behalf Of Emile van Sebille Sent: Wednesday, September 02, 2009 7:18 AM To: mysql@lists.mysql.com Cc: python-l...@python.org Subject: Re: Problem w/ mysqldump On 9/2/2009 3:43 AM Victor Subervi said... Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) First, test this at the system command line -- you'll likely get an empty file there as well, so calling from within python simply does the same. Then read the mysqldump docs for the command arguments and supply the database name... Emile This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=victorsube...@gmail.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: Problem w/ mysqldump
Not in Windoze. The only folders I have inside the Programs/MySQL are Docs, bin and Shared V On Wed, Sep 2, 2009 at 5:29 PM, Daevid Vincent dae...@daevid.com wrote: If it's MYISAM tables, then they're all self contained in folders in /var/lib/mysql/spreadsheets. Remember that if you do copy the files, to shut down mysql first or you could copy corrupt files. If you're using INNODB, then the schema is in that folder, but the actual data is in the /var/lib/mysql/ib* files. Along with all the other INNODB databases you may have on the system -- all mashed together. Hence the need for a mysql dump tool ;-) Mysql dump is the better way to go in either situation. -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Wednesday, September 02, 2009 1:43 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: Re: Problem w/ mysqldump I checked my own backup script from earlier years and everything was good. You know, if I could simply figure out where the data was actually stored, in what file, I could copy it over to another computer. Any ideas? Thanks, V On Wed, Sep 2, 2009 at 4:09 PM, Daevid Vincent dae...@daevid.com wrote: While not python, maybe this bash script will give you some clues? http://daevid.com/content/examples/daily_backup.php Also, please don't cross post to multiple lists. Not everyone on this mySQL list is on the python list and vice versa. It's just bad netiquette. -Original Message- From: news [mailto:n...@ger.gmane.org] On Behalf Of Emile van Sebille Sent: Wednesday, September 02, 2009 7:18 AM To: mysql@lists.mysql.com Cc: python-l...@python.org Subject: Re: Problem w/ mysqldump On 9/2/2009 3:43 AM Victor Subervi said... Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) First, test this at the system command line -- you'll likely get an empty file there as well, so calling from within python simply does the same. Then read the mysqldump docs for the command arguments and supply the database name... Emile This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=victorsube...@gmail.com
Re: Problem with configuring 32-bit MySQL 5.0 on Windows Vista x64
Did you deleted the data dir (inside Program Files) after uninstall ?? i think that is was not empty and when the new install tries to set up finds it and get messy (maybe because the 32 and 64 bit issue). Carlos On 7/13/2009 11:07 PM, Edward Diener wrote: I had previously installed the 64-bit version of MySQL 5.1 server under Windows Vista x64 and both the installation and configuration were successful. Then for compatibility reasons with something on which I am working I realized I needed to install the 32-bit version of MySQL 5.0 server. Since I did not see anything about installing both on the same machine, I decided to uninstall the 64-bit version and then install the 32-bit version. The uninstall ran successfully. When I installed MySQL 5.083 32-bit under Windows Vista x64 it installed successfully. When I try to configure an instance of that version, all steps work successfully until the Apply Security Settings step, which fails. The failure message is: The security settings could not be applied to the database because the connection has failed with the following error. Error Nr. 1045 Accessed denied for user 'root'@'localhost' [using password: YES]' I have never changed the password from the previous 64-bit version I installed and then uninstalled and the 32-bit version I installed. Any ideas of solving this problem ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with Dynamic table names.
I accidentally sent it before finishing...
I am looking for a way to do the following with a single query:
SELECT `ID`, `Name`, `Interface`,
(SELECT count(*) FROM CONCAT('listings_', `ID`) WHERE `Status`='Active') as
`activeListings`
FROM `sites`
I am querying the sites table and I want to have a field showing active
listings.
Each row in the sites table has a corresponding 'listings' table. so a site
where `ID`=1 will have a listings_1 table.
If I want to return data from 100 sites is there a way to get the active
listings count without making 100 extra querys?
Re: Problem with Dynamic table names.
Marco Bartz wrote:
I accidentally sent it before finishing...
I am looking for a way to do the following with a single query:
SELECT `ID`, `Name`, `Interface`,
(SELECT count(*) FROM CONCAT('listings_', `ID`) WHERE `Status`='Active') as
`activeListings`
FROM `sites`
I am querying the sites table and I want to have a field showing active
listings.
Each row in the sites table has a corresponding 'listings' table. so a site
where `ID`=1 will have a listings_1 table.
If I want to return data from 100 sites is there a way to get the active
listings count without making 100 extra querys?
At first glance your table design looks to be sub optimal, a table per
site certainly isn't normalised particularly if the structure of each
listings table is the same. Do you have this structure for performance
or archiving reasons?
You certainly could do this in two queries if you used the results of
the first to build a large write a left join or UNION query for the
second but it maybe just be using clever SQL to make up for poor table
design and not scalable in the long term. The queries would be:
select ID from sites;
SELECT `ID`, `Name`, `Interface`, count(*) as active
FROM `sites`
begin one left join per ID
left join on listings_[ID] where site.id = '[ID]' and listings_[ID].status =
'Active'
end one left join per ID
group by sites.id;
Assuming you've a good reason for the table design perhaps a merge table
is more what you need?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with Dynamic table names.
Marco, all,
Marco Bartz wrote:
I accidentally sent it before finishing...
I am looking for a way to do the following with a single query:
SELECT `ID`, `Name`, `Interface`,
(SELECT count(*) FROM CONCAT('listings_', `ID`) WHERE `Status`='Active') as
`activeListings`
FROM `sites`
There is no such thing as dynamic identifiers in SQL:
You cannot construct any identifier (like database, table, or column
name) inside an SQL statement.
I am querying the sites table and I want to have a field showing active
listings.
Each row in the sites table has a corresponding 'listings' table. so a site
where `ID`=1 will have a listings_1 table.
If you keep your current database schema (not change it, as proposed by
Nigel Wood), your only chance is dynamic SQL:
construct the whole SQL statement at run time, within your application.
The MySQL command line client supports this in prepared statements,
and of course any programming language (like Perl, PHP, ...) that
provides string operations (and allows using strings for SQL statements)
also supports this.
If I want to return data from 100 sites is there a way to get the active
listings count without making 100 extra querys?
If the data of these 100 sites is stored in 100 different tables, you
need SQL statements accessing them.
If you like, you can try with a single big UNION of 100 SELECTs, but I
would not advise you to do so.
HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem setting up slow logging in my,ini file
Try putting in a full existing pathname the server has write access to as opposed to just a file name. Walter Heck On Sun, May 17, 2009 at 12:13 PM, mos mo...@fastmail.fm wrote: I'm having a problem defining slow logging file in my.ini (Windows XP). I'm using MySQL 5.1.30. In the MySQL reference manual it says: As of MySQL 5.1.29, the --log-slow-queries option is deprecated and will be removed (along with the log_slow_queries system variable) in MySQL 7.0. Instead, use the --slow_query_log option to enable the slow query log and the --slow_query_log_file=file_name option to set the slow query log file name. So my.ini has: [mysqld] general_log=1 log-output=FILE general_log_file=LOG.TXT slow_query_log=1 slow_query_log_file=SLOWLOG.TXT After I restart MySQL, the variables have: Variable_name Value --- -- log ON log_bin OFF log_output FILE log_queries_not_using_indexes OFF log_slow_queries ON log_warnings 1 slow_query_log ON slow_query_log_file OFF sql_log_bin ON sql_log_off OFF sql_log_update ON So why is the variable slow_query_log_file set to OFF because it is not boolean and should be a file name? Why isn't it set to the file name SLOWLOG.TXT? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.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: Problem setting up slow logging in my,ini file
At 01:22 PM 5/17/2009, Walter Heck - OlinData.com wrote: Try putting in a full existing pathname the server has write access to as opposed to just a file name. Walter Heck Walter, That didn't work either if I put it in the my.ini file. Without a path it should default the location to the mysql/data directory which is where I'd like it to be. The only way I got it to work was to set the global variable after MySQL was running. It doesn't not appear I can get to specify the slow log file from the my.ini file. Mike On Sun, May 17, 2009 at 12:13 PM, mos mo...@fastmail.fm wrote: I'm having a problem defining slow logging file in my.ini (Windows XP). I'm using MySQL 5.1.30. In the MySQL reference manual it says: As of MySQL 5.1.29, the --log-slow-queries option is deprecated and will be removed (along with the log_slow_queries system variable) in MySQL 7.0. Instead, use the --slow_query_log option to enable the slow query log and the --slow_query_log_file=file_name option to set the slow query log file name. So my.ini has: [mysqld] general_log=1 log-output=FILE general_log_file=LOG.TXT slow_query_log=1 slow_query_log_file=SLOWLOG.TXT After I restart MySQL, the variables have: Variable_name           Value ---  -- log                ON log_bin              OFF log_output            FILE log_queries_not_using_indexes   OFF log_slow_queries         ON log_warnings           1 slow_query_log          ON slow_query_log_file        OFF sql_log_bin            ON sql_log_off            OFF sql_log_update          ON So why is the variable slow_query_log_file set to OFF because it is not boolean and should be a file name?  Why isn't it set to the file name SLOWLOG.TXT? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:   http://lists.mysql.com/mysql?unsub=li...@olindata.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: Problem compiling mysql-5.1.33
most likely a missing include it should have at least: #include sys/time.h #include sys/resource.h Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem schrieb: make all-am Making all in mysql-test Making all in lib/My/SafeProcess g++ -DHAVE_CONFIG_H -I. -I../../../../include -O3 -fno-implicit-templates -fno-exceptions -fno-rtti -MT safe_process.o -MD -MP -MF .deps/safe_process.Tpo -c -o safe_process.o safe_process.cc In file included from safe_process.cc:48: /usr/include/sys/resource.h:63: field `ru_utime' has incomplete type /usr/include/sys/resource.h:64: field `ru_stime' has incomplete type why is this taking place? Compile options ./configure --prefix=/usr/contrib --localstatedir=/usr/contrib/mysqld --without-innodb --disable-debug --with-ssl=/usr/contrib --enable-thread-safe-client -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with fetching values...
OK, Now I have gotten something...
[ '~/.tdphp-vserver/includes/10_electronica_admin.inc' ]
snip
# while ($DB_list=db_fetch_array($DB_result)) {
#$output .= lia href=\/?what=adminwhere= . $VAL2 . action=edit
#if ($_COOKIE[$VAL2 . '_desc_short'] == 'show') {
# $output .= dt / . $DB_list['desc_short'];
#}
#$output .= /li\n;
# }
echo pre\n;
while ($DB_list=db_fetch_array($DB_result)) {
print_r ($DB_list);
}
echo /pre\n;
exit();
snip
which output:
[ STDIN ]---
Array
(
[0] = 1
[1] = 24V DC Modular ATX PSU
[2] = foo
)
Array
(
[0] = 2
[1] = 24V DC Modular Solar-Wind-Charger and Distributor
[2] = foo
)
Array
(
[0] = 3
[1] = 24V DC Multichemistry Charger
snip
So, PostgreSQL catch the array by name and MySQL use the position?
But positional values do not work, since the sequence can change...
How can I get the db_fetch_array($DB_result) to give me the name instead
the numerical position?
I need:
Array
(
[serno] = 1
[name] = 24V DC Modular ATX PSU
[dec_short] = foo
)
Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant
--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
http://www.tamay-dogan.net/ http://www.can4linux.org/
Michelle Konzack Apt. 917 ICQ #328449886
+49/177/935194750, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)
signature.pgp
Description: Digital signature
Re: Problem with MySQL prompt
Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/ mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example / etc/mysql.config/my50_SIA_SVILUPPO.cnf), the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it
Re: Problem with MySQL prompt
Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.itwrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf),the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it email%3astefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
Re: Problem with MySQL prompt
Hi Krishna, the problem is that you use --default-file on the command line and in this mode it's working fine, I also tried, but I have the configuration of the variable default-file in the general my.cnf and I enter in MySQL in this way: mysql --socket=/tmp/mysql50_SIA_SVILUPPO.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1408 Server version: 5.0.51b-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql in my case the prompt doesn't change Il giorno 23/dic/08, alle ore 11:01, Krishna Chandra Prajapati ha scritto: Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/ mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/ mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf), the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it
Re: Problem with MySQL prompt
Hi Stefano, I believe that, different mysql configuration file for different mysql instance is a better idea. In your case, if any thing goes wrong with my.cnf Then, it is going to effect all mysql instance (either restart mysql server or any other way). It has a lots of disadvantages. You might be using mysql_multi to manage mysql instance (restart, start, stop) As, you told me. You have 1 general my.cnf and three different my.cnf for mysql instance. In one of my.cnf add and try [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' But again, if you are connecting with socket then it will not show the changed prompt. Try, to connect using defaults-file. I feel that connecting to mysql server using socket is not reading the configuration file. i mean to say the it's using default available to it. Thanks, Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 5:14 PM, Stefano Elmopi stefano.elm...@sociale.itwrote: Hi Krishna, the problem is that you use --default-file on the command line and in this mode it's working fine, I also tried, but I have the configuration of the variable default-file in the general my.cnf and I enter in MySQL in this way: mysql --socket=/tmp/mysql50_SIA_SVILUPPO.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1408 Server version: 5.0.51b-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql in my case the prompt doesn't change Il giorno 23/dic/08, alle ore 11:01, Krishna Chandra Prajapati ha scritto: Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf),the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel.
Re: Problem with MySQL prompt
Hi Krishna, I use the script mysql_multi to stop and the start several, or all, instances. At the end, to work with more ease, I will create scripts to connect to each instance and in the script force the variable prompt at the connection seems easy Thanks for all !!! Il giorno 23/dic/08, alle ore 13:51, Krishna Chandra Prajapati ha scritto: Hi Stefano, I believe that, different mysql configuration file for different mysql instance is a better idea. In your case, if any thing goes wrong with my.cnf Then, it is going to effect all mysql instance (either restart mysql server or any other way). It has a lots of disadvantages. You might be using mysql_multi to manage mysql instance (restart, start, stop) As, you told me. You have 1 general my.cnf and three different my.cnf for mysql instance. In one of my.cnf add and try [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' But again, if you are connecting with socket then it will not show the changed prompt. Try, to connect using defaults-file. I feel that connecting to mysql server using socket is not reading the configuration file. i mean to say the it's using default available to it. Thanks, Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 5:14 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, the problem is that you use --default-file on the command line and in this mode it's working fine, I also tried, but I have the configuration of the variable default-file in the general my.cnf and I enter in MySQL in this way: mysql --socket=/tmp/mysql50_SIA_SVILUPPO.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1408 Server version: 5.0.51b-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql in my case the prompt doesn't change Il giorno 23/dic/08, alle ore 11:01, Krishna Chandra Prajapati ha scritto: Hi Stefano, I have tested. It's working fine without any issue. Open mysql configuration(.cnf) file referencing to 1st mysql instance. Add the below entires and restart the mysql server. [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates prompt = 'mysql5.1.30 ' [r...@linux18 ~]# mysql --defaults-file=/etc/my1.cnf Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql5.1.30 Thanks, -Krishna Chandra Prajapati On Tue, Dec 23, 2008 at 2:00 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: Hi Krishna, thanks for the your answer !!! I have three different my.cnf for my three instances but one general my.cnf to manage all instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/ mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/ mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO but if I put the variable prompt in the specific my.cnf (for example /etc/mysql.config/my50_SIA_SVILUPPO.cnf), the prompt doesn't change By Il giorno 22/dic/08, alle ore 11:59, Krishna Chandra Prajapati ha scritto: Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.it wrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put
Re: Problem with MySQL prompt
Ciao Stefano, how do you connect to the different instances? if you use different operating system users or different mysql home directories just put in the home directory of each user a .my.cnf file (or my.cnf in the mysql home) with just the prompt setting in the [mysql] section. NOTE: in the home directory of the user the file starts with the dot(.my.cnf). The search order of mysql cnf files is, if my memory does not betray me, /etc/my.cnf $MYSQL_HOME/my.cnf $MYSQL_USER/.my.cnf they are all taken into consideration (like cascading style sheets) with the last setting overwriting the prevoius read ones. The only thing the prevent from reading all of the configuration files is specifying the option --defaults-file=/pat/to/my.cnf in the command line. Sorry I cannot test it now, so please try it. Aloha Claudio 2008/12/22 Stefano Elmopi stefano.elm...@sociale.it I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it email%3astefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Problem with MySQL prompt
Stefano Elmopi wrote: If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. This is because the client reads from my.cnf before it starts talking to the server, and it can read any my.cnf file it likes. There's no way you can mandate that a single my.cnf file gets used for both the server and any client session connecting to it. Instead of running mysql(1) directly, I'd run it via one of three scripts, each of which sets up its environment so as to force mysql(1) to read a different my.cnf file in each case. This will also let you hard-code other information about each particular server that isn't important to keep secret, like the login name and the DB name. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with MySQL prompt
Hi Stefano, You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. On Mon, Dec 22, 2008 at 3:46 PM, Stefano Elmopi stefano.elm...@sociale.itwrote: I need advice. I have three instances of MySQL on a single server. How can I change the MySQL prompt depending of the instance on which connecting ? If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. Thanks Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it email%3astefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com
Re: Problem with MySQL prompt
Thanks for the all answers !!! It's ok, even if I have three different my.cnf for my three instances, my general my.cnf is: [mysqld_multi] mysqld = /opt/local/mysql50/bin/mysqld_safe mysqladmin = /opt/local/mysql50/bin/mysqladmin user = root password = mysys2008srv log = /MYSQL/MYSQL_LOG/Mysqld_Multi_Log/mysqld_multi.log ## ISTANZE DELLA PARTE PRODUZIONE ##--- ## ISTANZA SIA DI PRODUZIONE ##-- [mysqld01] defaults-file = /etc/mysql.config/my50_SIA.cnf socket = /tmp/mysql50_SIA.sock port = 3307 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/SIA/mysql50_SIA.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/SIA ## ISTANZA IPPOFONO DI PRODUZIONE ##--- [mysqld02] defaults-file = /etc/mysql.config/my50_IPPOFONO.cnf socket = /tmp/mysql50_IPPOFONO.sock port = 3306 pid-file = /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO/mysql50_IPPOFONO.pid datadir= /DB_MYSQL_PRODUZIONE/MYSQL50/IPPOFONO ### ## ISTANZE DELLA PARTE SVILUPPO ##- ## ISTANZA SIA DI SVILUPPO ## [mysqld11] defaults-file = /etc/mysql.config/my50_SIA_SVILUPPO.cnf socket = /tmp/mysql50_SIA_SVILUPPO.sock port = 3317 pid-file = /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO/ mysql50_SIA_SVILUPPO.pid datadir= /DB_MYSQL_SVILUPPO/MYSQL50/SIA_SVILUPPO to have three different prompts, I have to create three scripts connection where I configure the prompt variable. I say right? By Il giorno 22/dic/08, alle ore 12:07, Warren Young ha scritto: Stefano Elmopi wrote: If I put the variable prompt in the general my.cnf, the prompt is changed but for all instances. If I put in the variable prompt in the my.cnf associated with the instance in the [mysql] section, the prompt does not change. This is because the client reads from my.cnf before it starts talking to the server, and it can read any my.cnf file it likes. There's no way you can mandate that a single my.cnf file gets used for both the server and any client session connecting to it. Instead of running mysql(1) directly, I'd run it via one of three scripts, each of which sets up its environment so as to force mysql(1) to read a different my.cnf file in each case. This will also let you hard-code other information about each particular server that isn't important to keep secret, like the login name and the DB name. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=stefano.elm...@sociale.it Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma cell. 3466147165 tel. 0657060500 email:stefano.elm...@sociale.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with MySQL prompt
prajapat...@gmail.com (Krishna Chandra Prajapati) writes: You are running three mysql instance on single server. You can have three my.cnf say my.cnf, my1.cnf, my2.cnf with different port and socket and other information in them. In this way you can set the prompt for different instance. It's a shame that the prompt can't be defined dynamically based on characteristics of the current connection. That would make it much easier to distinguish which db instance you are talking to and only have a single configuration. Is there not an entry in bugs.mysql.com for this? I had a quick look but couldn't find one. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with GROUP BY
Learnt! On Wed, Oct 15, 2008 at 5:28 PM, philip [EMAIL PROTECTED] wrote: Date: Tue, 14 Oct 2008 16:55:11 +0300 From: Olexandr Melnyk [EMAIL PROTECTED] To: [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Problem with GROUP BY http://jan.kneschke.de/projects/mysql/groupwise-max 2008/10/14 Peter Brawley [EMAIL PROTECTED] Philip mysql SELECT number, MAX(event), name FROM info GROUP BY number; For discussion examples see Within-group aggregates at http://www.artfulsoftware.com/queries.php. Thank you both very much for your replies. Of course the solution is 'obvious' now I know the answer but as a relative newcomer to MySQL I had spent the best part of a day trying to find it. TTFN, Philip Riebold, [EMAIL PROTECTED] /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Problem with GROUP BY
Date: Tue, 14 Oct 2008 16:55:11 +0300 From: Olexandr Melnyk [EMAIL PROTECTED] To: [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Problem with GROUP BY http://jan.kneschke.de/projects/mysql/groupwise-max 2008/10/14 Peter Brawley [EMAIL PROTECTED] Philip mysql SELECT number, MAX(event), name FROM info GROUP BY number; For discussion examples see Within-group aggregates at http://www.artfulsoftware.com/queries.php. Thank you both very much for your replies. Of course the solution is 'obvious' now I know the answer but as a relative newcomer to MySQL I had spent the best part of a day trying to find it. TTFN, Philip Riebold, [EMAIL PROTECTED] /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with GROUP BY
Philip mysql SELECT number, MAX(event), name FROM info GROUP BY number; For discussion examples see Within-group aggregates at http://www.artfulsoftware.com/queries.php. PB - philip wrote: I created a table with, CREATE TABLE info ( number INTEGER UNSIGNED, event INTEGER UNSIGNED, name VARCHAR(2000) NOT NULL, PRIMARY KEY (number, event) ); and populated it with data to produce this, ++---+---+ | number | event | name | ++---+---+ | 67 | 1 | Alice | | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 1 | Bob | | 72 | 2 | Alice | | 72 | 3 | David | ++---+---+ What I want to produce is a table with rows from the original with only the maximum value of event for each corresponding number selected, like this +++---+ | number | event | name | +++---+ | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | David | +++---+ The closest I have managed to produce using GROUP BY is, mysql SELECT number, MAX(event), name FROM info GROUP BY number; +++---+ | number | MAX(event) | name | +++---+ | 67 | 2 | Alice | - should be Bob | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | Bob | - should be David +++---+ I tried using a HAVING clause but got nowhere. Can anybody help please ? TTFN, Philip Riebold, [EMAIL PROTECTED] /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
Re: Problem with GROUP BY
http://jan.kneschke.de/projects/mysql/groupwise-max 2008/10/14 Peter Brawley [EMAIL PROTECTED] Philip mysql SELECT number, MAX(event), name FROM info GROUP BY number; For discussion examples see Within-group aggregates at http://www.artfulsoftware.com/queries.php. PB - philip wrote: I created a table with, CREATE TABLE info ( number INTEGER UNSIGNED, event INTEGER UNSIGNED, name VARCHAR(2000) NOT NULL, PRIMARY KEY (number, event) ); and populated it with data to produce this, ++---+---+ | number | event | name | ++---+---+ | 67 | 1 | Alice | | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 1 | Bob | | 72 | 2 | Alice | | 72 | 3 | David | ++---+---+ What I want to produce is a table with rows from the original with only the maximum value of event for each corresponding number selected, like this +++---+ | number | event | name | +++---+ | 67 | 2 | Bob | | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | David | +++---+ The closest I have managed to produce using GROUP BY is, mysql SELECT number, MAX(event), name FROM info GROUP BY number; +++---+ | number | MAX(event) | name | +++---+ | 67 | 2 | Alice | - should be Bob | 69 | 1 | Carol | | 70 | 1 | Alex | | 71 | 1 | David | | 72 | 3 | Bob | - should be David +++---+ I tried using a HAVING clause but got nowhere. Can anybody help please ? TTFN, Philip Riebold, [EMAIL PROTECTED] /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM -- -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
make sure to FLUSH PRIVILAGES; - Original Message From: [EMAIL PROTECTED] [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Monday, 14 July, 2008 10:59:35 AM Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe
RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
Hi Parikh, Yes.. It worked with IP i.e when I granted privileges to 'root'@'ip of my machine' and flushed it, it worked fine. But I have a qestion below: GRANT ALL PRIVILEGES ON *.* to 'root'@'IP'; FLUSH PRIVILEGES; However, Would '*.def.com' work in the place of IP?, as I feel that giving privileges for each IP for each machine that is going to access the mysql server would be tedious and not recommended choice. Please let me know. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:34 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Ok It means that DNS not configured so better give the IP address instead of hsostname and check using in mysql as Show grants for [EMAIL PROTECTED]; This should show u the grants Thanks Regards, Dilipkumar MphasiS an EDS Company | No 25,Steeple Reach,Cathedral Road | Chennai | India | 91 44 28113801 |Extn 2216 Mobile: 9884430998 | 9962029004 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:30 PM To: Parikh, Dilip Kumar; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary
Re: ***SPAM*** RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice..
grant all on *.* to root@'%.def.com'; /* The percent sign is your wildcard character. */ flush privileges; I don't think you need to flush privileges as of 5.0. I still do just to be sure. Curtis [EMAIL PROTECTED] wrote: Hi Parikh, Yes.. It worked with IP i.e when I granted privileges to 'root'@'ip of my machine' and flushed it, it worked fine. But I have a qestion below: GRANT ALL PRIVILEGES ON *.* to 'root'@'IP'; FLUSH PRIVILEGES; However, Would '*.def.com' work in the place of IP?, as I feel that giving privileges for each IP for each machine that is going to access the mysql server would be tedious and not recommended choice. Please let me know. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:34 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Ok It means that DNS not configured so better give the IP address instead of hsostname and check using in mysql as Show grants for [EMAIL PROTECTED]; This should show u the grants Thanks Regards, Dilipkumar MphasiS an EDS Company | No 25,Steeple Reach,Cathedral Road | Chennai | India | 91 44 28113801 |Extn 2216 Mobile: 9884430998 | 9962029004 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:30 PM To: Parikh, Dilip Kumar; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Hi, I did that too, I executed the command - GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' ; I tried also GRANT ALL PRIVILEGES ON *.* to 'root'@'abc.def.com' ; where abc.def.com is my machine name But both of these did not work, I did not mention this in my earlier mail. Can you please let me know if there is any other way out. Regards Ahmad -Original Message- From: Parikh, Dilip Kumar [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:26 PM To: Ahmadbasha Shaik (WT01 - E-ENABLING); Raghavendra Hosabettu (WT01 - Innovation Group) Subject: FW: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi all, First try checking out the grant for the particular user ? Show grants for user@'abc.def.com'; If u don't find the results u can give grant as :- Grant select on *.* to user@'abc.def.com' identified by ''; Flush privileges; Thanks Regards, Dilipkumar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2008 3:21 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Problem - Host 'abc.def.com' is not allowed to connect to this MySQL server, Please advice.. Importance: High Hi All, I am facing a particular problem which i have explained here. Can you please let me know a solution for this. From my web application, I am trying to connect the MySQL server by using the IP address as the server name, and it says the following error: Host 'abc.def.com' is not allowed to connect to this MySQL server Options that I tried: (from the information availabe on internet) a) I tried modifying the hosts file in WinNT directory to include this host name (assuming that it was not understanding the IP), but it did not work b) I tried adding a record with the IP as host and user as root in user table of mysql database it did not work c) I tried adding a record with the 'abc.def.com' as host and user as root in user table of mysql database it did not work d) I tried enabling Remote Access in MySQL Server instance config wizard but since the root users password is not set, it is not allowing me go forward (i.e. the next button is disabled) e) If I try to reset the password while configuring the MySQL Server Instance, it does not allow me to do so f) I tried adding a record with the '%' as host and user as root in user table of mysql database it did not allow me to add the record Regards Ahmad Please do not print this email unless it is absolutely necessary. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED
Re: Problem with CREATE TABLE/DROP TABLE
On Jun 24, 2008, at 2:57 AM, Gwynne Raskind wrote: I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. I've discovered more information about this problem since, as posted in the bug report: I've tracked down this bug such that it only happens when the table in question is accessed via INFORMATION_SCHEMA after being created. A FLUSH TABLES command eliminates the error without need for any other intervention. I use INFORMATION_SCHEMA this way: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=TestData This also happens with any other table accessed in this way; based on the output of mysqladmin extended-status it looks like the server isn't closing the table definition correctly after accessing it via INFORMATION_SCHEMA. If anyone has any information about how to deal with this problem, or fix it, please let me know; it's very annoying. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with CREATE TABLE/DROP TABLE
My first impression is to say: Sounds like the InnoDB internal data dictionary still has the table recorded somewhere. According to Page 566 Paragraph 3 of MySQL Administrator's Guide and Language Reference (ISBN 0-672-32870-4), InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. This is may explain why the bug persists. Innobase Oy never got to fix it, and now Oracle owns Innobase Oy (I am still in mourning over that). You may want to look into Falcon if you want ACID transaction supported tables. -Original Message- From: Gwynne Raskind [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 2:58 AM To: mysql@lists.mysql.com Subject: Problem with CREATE TABLE/DROP TABLE I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -- 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: Problem with CREATE TABLE/DROP TABLE
On Jun 24, 2008, at 10:13 AM, Rolando Edwards wrote: My first impression is to say: Sounds like the InnoDB internal data dictionary still has the table recorded somewhere. According to Page 566 Paragraph 3 of MySQL Administrator's Guide and Language Reference (ISBN 0-672-32870-4), InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. This is may explain why the bug persists. Innobase Oy never got to fix it, and now Oracle owns Innobase Oy (I am still in mourning over that). This was my first thought, but the original bug report suggests that the bug occurs with other table types besides InnoDB. You may want to look into Falcon if you want ACID transaction supported tables. Transactional support isn't critical to my implementation, but foreign keys are. Also, it's my undertanding that Falcon is unsupported before MySQL 6, and I'm not prepared to upgrade that far yet. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -Original Message- From: Gwynne Raskind [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 2:58 AM To: mysql@lists.mysql.com Subject: Problem with CREATE TABLE/DROP TABLE I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -- 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]
Re: Problem with character set and connection collation
Hi Sulo, Open the file you are importing as it is probably that it contains references of another character set in the table creation. If so replace that character set for '' or 'utf8'. I hope it helps, Leandro sulochan acharya wrote: Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. I don't know how it would apply directly to your situation, but I think you are running into a similar kind of problem. I also can't guarantee that my conclusions were entirely correct. Perhaps someone else can add to this. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: sulochan acharya [mailto:[EMAIL PROTECTED] Sent: Sunday, April 27, 2008 6:01 AM To: mysql@lists.mysql.com Subject: Problem with character set and connection collation Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
On Mon, 28 Apr 2008, Jerry Schwartz [EMAIL PROTECTED] wrote: A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. Latin-1 is not Microsoft Windows codepage 1252: Latin-1 has control characters in the 0x80 - 0x9F block of code points, but 1252 replaces some of those code points with printable characters. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
Well, if latin1 is not CP-1252, then that explains why it didn't fix my problem; but here's what 5.0.45-community-nt says: mysql show character set; +--+-+-++ | Charset | Description | Default collation | Maxlen | +--+-+-++ | big5 | Big5 Traditional Chinese| big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850| DOS West European | cp850_general_ci| 1 | | hp8 | HP West European| hp8_english_ci | 1 | | koi8r| KOI8-R Relcom Russian | koi8r_general_ci| 1 | | latin1 | cp1252 West European| latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish| swe7_swedish_ci | 1 | | ascii| US ASCII| ascii_general_ci| 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci| 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci| 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr| EUC-KR Korean | euckr_korean_ci | 2 | | koi8u| KOI8-U Ukrainian| koi8u_general_ci| 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek| ISO 8859-7 Greek| greek_general_ci| 1 | | cp1250 | Windows Central European| cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866| DOS Russian | cp866_general_ci| 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce| Mac Central European| macce_general_ci| 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852| DOS Central European| cp852_general_ci| 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | cp1251 | Windows Cyrillic| cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian| geostd8_general_ci | 1 | | cp932| SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +--+-+-++ Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Tim McDaniel [mailto:[EMAIL PROTECTED] Sent: Monday, April 28, 2008 12:19 PM Cc: mysql@lists.mysql.com Subject: RE: Problem with character set and connection collation On Mon, 28 Apr 2008, Jerry Schwartz [EMAIL PROTECTED] wrote: A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. Latin-1 is not Microsoft Windows codepage 1252: Latin-1 has control characters in the 0x80 - 0x9F block of code points, but 1252 replaces some of those code points with printable characters. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:29 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 2:01 PM, Rob Wultsch wrote: On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. Hi Rob, Where would I set that? I tried to add it to the load data infile line and it didn't like that... Should I try it before I do the indata? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
Hi again everyone, After taking the advice of someone offlist I tried the IGNORE 1 LINES and that didn't help... Same result. I've tried a tab delimited file, and a comma separated file. Same result with both. Any other ideas? :) On Apr 14, 2008, at 1:29 PM, Jason Pruim wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/ raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:33 PM, Jason Pruim [EMAIL PROTECTED] wrote: On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P I don't know you from Adam, you insignificant little cur! ;-P Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
