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:averyanov@stripped
