8 messages in com.mysql.lists.mysqlRe: concat function problems
FromSent OnAttachments
aver...@gmail.com26 Jul 2005 07:38 
Michael Stassen26 Jul 2005 08:53 
Nuno Pereira26 Jul 2005 09:53 
aver...@gmail.com26 Jul 2005 22:30 
Nuno Pereira27 Jul 2005 03:30 
aver...@gmail.com27 Jul 2005 04:13 
aver...@gmail.com29 Jul 2005 03:59 
aver...@gmail.com29 Jul 2005 06:37 
Subject:Re: concat function problems
From:aver...@gmail.com (aver@gmail.com)
Date:07/26/2005 10:30:47 PM
List:com.mysql.lists.mysql

Hello mysql,

i've got a strange problem with concat() function

i have the following data structure:

CREATE TABLE table1 ( field1 int(11) NOT NULL auto_increment, PRIMARY KEY (field1) ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3;

CREATE TABLE table2 ( field2 varchar(255) NOT NULL default '' ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

INSERT INTO table1 VALUES (1); INSERT INTO table1 VALUES (2); INSERT INTO table2 VALUES ('test');

When i try to execute the following query (the query is meaningless -- it is generated just for debug purpose. the original query where the problem occured is too long and complicated) SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '-') as value2 FROM table1, table2 GROUP BY value2 i get this error:

ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 6 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query

This is an important point. Do you get the reported error for the simplified query below? If not, it is useless. To find the problem, we must have a query that produces the problem.

Yes, i have the problem exactly in THIS query

SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '-') as value2 FROM table1, table2 GROUP BY value2

With 4.1.11 on Mac OS X 10.3.9, I get

+--------+--------+ | value1 | value2 | +--------+--------+ | 2 | test- | +--------+--------+ 1 row in set (0.09 sec)

i get this error:

ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 6 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query

Does the mysql server actually crash, or are you just losing the connection from your client? Have you checked the error log? Have you checked the manual <http://dev.mysql.com/doc/mysql/en/gone-away.html>?

Yes, it really crashes. mysqld restarts after that

but if i change my query to this one everything is fine SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '') as value2 FROM table1, table2 GROUP BY value2

(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )

does anyone know what the matter is?

Does the simple query

SELECT CONCAT('test', '-');

work or produce the same error?

This query works. This one does NOT: SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '-') as value2 FROM table1, table2 GROUP BY value2

All of the following ones WORK fine too: SELECT max(field1) as value1, CONCAT(field2, '-') as value2 FROM table1, table2 GROUP BY value2 (here count is replaced with MAX() for experimental purposes)

SELECT COUNT(field1) as value1, CONCAT(field2, '-') as value2 FROM table1, table2 GROUP BY value2 (here distinct is removed)

SELECT COUNT(field1) as value1, CONCAT(field2, '-') as value2 FROM table2, table1 GROUP BY value2 (here tables order in FROM is changed)

ps my mysql version is mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.6 (i386) using EditLine
wrapper and my system is FreeBSD 4.6

I've not used "EditLine wrapper" with mysql. Is there any chance it is doing something with '-'?

no, i've tried other symbols too

-- Best regards, averyanov mailto:aver@gmail.com