Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: 2013 - Lost Connection to MySQL server during query

    Hello,

    I have MySQL 4.1.12.
    I have found a way to reproduce this error with this query:
    Code:
    SELECT
    	tmp.cat AS "cat"
    	, CONCAT(tmp.nom, " ", tmp.prenom) AS "learner"
    	, CONCAT(tmp.produit, " ", tmp.langue) AS "prod"
    	, tmp.langue AS "lang"
    	, tmp.division AS "div"
    	, tmp.departement AS "dept"
    	, tmp.centre AS "center"
    	, tmp.tuteur AS "tutor"
    	, tmp.date AS "date"
    	, DATE_FORMAT(tmp.date, "%M") AS "month"
    	, DATE_FORMAT(tmp.date, "%d %b %y") AS "fdate"
    	, tmp.statut AS "status"
    	, CONCAT(tmp.division, " - ", tmp.langue) AS "div_lang"
    	, tmp.cost_center AS "cost_center"
    	, tmp.matricule AS "mat"
    	, COUNT(DISTINCT IF(tmp.statut = "Present", tmp.sest_id, NULL)) AS "R"
    	, COUNT(DISTINCT IF(tmp.statut = "Absent", tmp.sest_id, NULL)) AS "A"
    	, COUNT(DISTINCT tmp.sest_id) AS "T"
    FROM
    	test_data AS tmp
    WHERE
    	tmp.statut <> ""
    GROUP BY
    	tmp.langue
    	, tmp.tuteur
    ORDER BY
    	tmp.langue
    	, tmp.tuteur
    ;
    from the table enclosed

    I'm sure that this bug is known but actually I can't change my version
    so I'd like to know if there is a way to override this but avoiding to use
    some function in the query or something.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Argh!

    COUNT(DISTINCT IF(tmp.statut = "Present", tmp.sest_id, NULL)) AS "R"
    , COUNT(DISTINCT IF(tmp.statut = "Absent", tmp.sest_id, NULL)) AS "A"
    , COUNT(DISTINCT tmp.sest_id) AS "T"
    What is it you're trying to achieve using the table/SQL you have provided?

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Just to give you a confirmation test, i've loaded up your table in MySQL version 5.0.27 community edition and your query works fine.

  4. #4
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Actually the table provided is a temporary table which my program generates
    for the client as detail, so this table should be humain readable.
    After that, I use it to perform a kind of ajustable pivot table with MySQL and this
    is the query that I put before.

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Thanks aschk for the confirmation.
    Can someone try with a version 4.1.x ?

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You're grouping by
    • tmp.langue
    • tmp.tuteur

    but then in the select part you are pulling out the following fields without using any aggregate function.
    • tmp.cat
    • tmp.nom
    • tmp.prenom
    • tmp.produit
    • tmp.langue
    • tmp.langue
    • tmp.division
    • etc ect

    Either get rid of the aggregate functions and grouping or apply aggregates to all fields (that you're not grouping by).

    Mike

  7. #7
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    I just tryied something like that
    Code:
    SELECT
    	GROUP_CONCAT(DISTINCT tmp.cat) AS "cat"
    	, CONCAT(tmp.nom, " ", tmp.prenom) AS "learner"
    	, CONCAT(tmp.produit, " ", tmp.langue) AS "prod"
    	, GROUP_CONCAT(DISTINCT tmp.langue) AS "lang"
    	, GROUP_CONCAT(DISTINCT tmp.division) AS "div"
    	, GROUP_CONCAT(DISTINCT tmp.departement) AS "dept"
    	, GROUP_CONCAT(DISTINCT tmp.centre) AS "center"
    	, GROUP_CONCAT(DISTINCT tmp.tuteur) AS "tutor"
    	, GROUP_CONCAT(DISTINCT tmp.date) AS "date"
    	, DATE_FORMAT(tmp.date, "%M") AS "month"
    	, DATE_FORMAT(tmp.date, "%d %b %y") AS "fdate"
    	, GROUP_CONCAT(DISTINCT tmp.statut) AS "status"
    	, CONCAT(tmp.division, " - ", tmp.langue) AS "div_lang"
    	, GROUP_CONCAT(DISTINCT tmp.cost_center) AS "cost_center"
    	, GROUP_CONCAT(DISTINCT tmp.matricule) AS "mat"
    	, COUNT(DISTINCT IF(tmp.statut = "Present", tmp.sest_id, NULL)) AS "R"
    	, COUNT(DISTINCT IF(tmp.statut = "Absent", tmp.sest_id, NULL)) AS "A"
    	, COUNT(DISTINCT tmp.sest_id) AS "T"
    FROM
    	test_data AS tmp
    WHERE
    	tmp.statut <> ""
    GROUP BY
    	tmp.langue
    	, tmp.tuteur
    ORDER BY
    	tmp.langue
    	, tmp.tuteur
    ;
    It's doing the same error

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It is difficult to debug your code as the field names don't make much sense to me and it's very difficult to understand what it is you want to do. Your last version of SQL is still mixed up between whether it wants to group the data or select it out individually. I suggest you find a web page dedicated to learning SQL and have a good look at what the group by command does. I hope the following works for you.

    Code:
    SELECT  DISTINCT tmp.cat AS "cat"
            , tmp.nom, " ", tmp.prenom AS "learner"
            , CONCAT(tmp.produit, " ", tmp.langue) AS "prod"
            , tmp.langue AS "lang"
            , tmp.division AS "div"
            , tmp.departement AS "dept"
            , tmp.centre AS "center"
            , tmp.tuteur AS "tutor"
            , tmp.date AS "date"
            , DATE_FORMAT(tmp.date, "%M") AS "month"
            , DATE_FORMAT(tmp.date, "%d %b %y") AS "fdate"
            , tmp.statut AS "status"
            , CONCAT(tmp.division, " - ", tmp.langue) AS "div_lang"
            , tmp.cost_center AS "cost_center"
            , tmp.matricule AS "mat"
            , IF(tmp.statut = "Present", tmp.sest_id, NULL) AS "R"
            , IF(tmp.statut = "Absent", tmp.sest_id, NULL) AS "A"
            , tmp.sest_id AS "T"
    FROM    test_data AS tmp
    WHERE   tmp.statut <> ""
    GROUP BY tmp.langue , tmp.tuteur
    ORDER BY tmp.langue , tmp.tuteur;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    Either get rid of the aggregate functions and grouping or apply aggregates to all fields (that you're not grouping by).
    that is not necessarily necessary

    please read Debunking GROUP BY myths --
    "It is completely safe to write partial GROUP BY clauses as long as all non-aggregated columns in the SELECT list are functionally dependent upon the GROUP BY clause. In fact, a partial GROUP BY list can result in better performance, because it keeps the server from evaluating the entire GROUP BY list."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gtk
    Can someone try with a version 4.1.x ?
    ran your test data and query in 4.1.20-community-nt and it worked just fine

    results of query:
    Code:
    cat	learner	prod	lang	div	dept	center	tutor	date	month	fdate	status	div_lang	cost_center	mat	R	A	T
    Face  face	DREUILLAUD Sebastien	EN-Tutorat English	English	AAA		AAA	Altagracia MORENO	2007-05-23	May	23 May 07	Absent	AAA - English			36	6	42
    Face  face	NIKITINE Boris	EN-Tutorat English	English	AAA		AAA	Claire BOOTH-VILAIN	2007-05-23	May	23 May 07	Present	AAA - English			1	0	1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by mike_bike_kite
    Either get rid of the aggregate functions and grouping or apply aggregates to all fields (that you're not grouping by).

    Originally Posted by r937
    that is not necessarily necessary
    I stand corrected

  12. #12
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    It's worth noting however that he is not grouping on a primary/unique key and thus he "ought" to adhere to utilising aggregates on his results, or including them in his group by clause as per SQL 99 (they are not functionally dependent columns)
    Code:
    	tmp.langue
    	, tmp.tuteur

  13. #13
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by r937
    that is not necessarily necessary

    please read Debunking GROUP BY myths --
    "It is completely safe to write partial GROUP BY clauses as long as all non-aggregated columns in the SELECT list are functionally dependent upon the GROUP BY clause. In fact, a partial GROUP BY list can result in better performance, because it keeps the server from evaluating the entire GROUP BY list."
    From same article:

    What did we mean when we specified the birth column in the SELECT list?

    There is no good answer to this question. It is certainly possible to select just one of the possible values for the birth column: in fact, this is exactly what MySQL does.


    How is returning the wrong data "safe"?! MySQL has no way of knowing what is functionally dependent on what, so it should either provide such a facility or do the safe thing by default.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, there you go, that was the whole point of the article

    obviously, that column was not dependent on the GROUP BY column

    did you finish the article? it's explained in some detail

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by r937
    well, there you go, that was the whole point of the article

    obviously, that column was not dependent on the GROUP BY column

    did you finish the article? it's explained in some detail

    I hate SQL's grouping and summaries facility, personally. So there's my bias: you can't make me happy either way.

    Here's the bigger problem. If I do a relational operation on a table I get another table, right? But while we all know what data is returned by those operations, we are rarely cognizant of what structure the result has. Most DBMSs I've seen don't even show you the data types.

    I know that I only ever worry about functional dependencies if it's not immediately obvious how to normalize a database. I certainly don't do that for derived tables, and I've never seen anyone note it down. And that's why I think he says this:

    Of course, it is impossible to distinguish between a query that intentionally omits columns from the GROUP BY clause and one that accidentally forgot to include them.

    If the DBMS had syntax like CREATE TABLE Foo (a AS INTEGER FUNC DEP ON (b, c), ...) it could tell you when you were using a partial grouping clause incorrectly. I'd be fine with partial GROUP BY clauses in that case.

    Even with a saavy DBA it seems far more likely that a partial GROUP BY would be due to adding a field and not updating a query. This feature, then, is much more likely to lead to bugs than to any useful optimization.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •