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:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]