2013 - Lost Connection to MySQL server during query

gtk · #1 (**permalink**) 06-27-07, 05:41

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.

aschk · #2 (**permalink**) 06-28-07, 04:00

Argh!

Quote:

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?

aschk · #3 (**permalink**) 06-28-07, 04:10

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.

gtk · #4 (**permalink**) 06-28-07, 04:26

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.

gtk · #5 (**permalink**) 06-28-07, 04:30

Thanks aschk for the confirmation.
Can someone try with a version 4.1.x ?

mike_bike_kite · #6 (**permalink**) 06-28-07, 06:28

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

gtk · #7 (**permalink**) 06-29-07, 03:52

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

mike_bike_kite · #8 (**permalink**) 06-29-07, 04:14

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;

r937 · #9 (**permalink**) 06-29-07, 04:24

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."

r937 · #10 (**permalink**) 06-29-07, 04:27

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

mike_bike_kite · #11 (**permalink**) 06-29-07, 05:01

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).

Originally Posted by r937
that is not necessarily necessary

I stand corrected

aschk · #12 (**permalink**) 06-29-07, 05:29

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

sco08y · #13 (**permalink**) 06-29-07, 18:21

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.

r937 · #14 (**permalink**) 06-29-07, 18:23

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

sco08y · #15 (**permalink**) 07-07-07, 01:40

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech