Debian GNU/Linux 7.1 (wheezy)

Hi,

Could you please give a specific example of the contents of the table, the resultset that you're getting, and resultset that you expect to get?

Thanks.

Hi there,

Offcourse, here you go.

–
-- Table structure for table `users`
–
CREATE TABLE IF NOT EXISTS `users` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`login` char(128) CHARACTER SET latin1 NOT NULL DEFAULT '',
`crypt` varchar(2048) CHARACTER SET latin1 NOT NULL DEFAULT '',
`salt` varchar(255) CHARACTER SET latin1 NOT NULL,
`name` char(128) CHARACTER SET latin1 NOT NULL DEFAULT '',
`uid` int(10) unsigned NOT NULL DEFAULT '8',
`gid` int(10) unsigned NOT NULL DEFAULT '8',
`home` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`maildir` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`spamdir` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`quota` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`status` varchar(5) CHARACTER SET latin1 NOT NULL DEFAULT 'User',
PRIMARY KEY (`id`),
UNIQUE KEY `login` (`login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

–
-- Table structure for table `aliases`
–
CREATE TABLE IF NOT EXISTS `aliases` (
`type` set('domain','system') CHARACTER SET latin1 NOT NULL DEFAULT 'domain',
`alias` char(128) CHARACTER SET latin1 NOT NULL DEFAULT '',
`login` char(128) CHARACTER SET latin1 NOT NULL DEFAULT '',
PRIMARY KEY (`alias`,`login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

–
-- Dumping data for table `users`
–
INSERT INTO `users` (`id`, `login`, `crypt`, `salt`, `name`, `uid`, `gid`, `home`, `maildir`, `spamdir`, `quota`, `status`) VALUES
(1, 'lenny@example.com', '<secret>', '<secret>', 'Lennart Weijl', 8, 8, '/home/mailboxes/example.com/lenny', '/home/mailboxes/example.com/lenny/Maildir/', '/home/mailboxes/example.com/lenny/Maildir/.Spam', '8388608', 'Admin');

–
-- Dumping data for table `aliases`
–
INSERT INTO `aliases` (`type`, `alias`, `login`) VALUES
('domain', 'lweijl@example.com', 'lenny@example.com'),
('domain', 'lennart@example.com', 'lenny@example.com'),
('domain', 'lfweijl@example.com', 'lenny@example.com');

–
-- Query
–
select login, group_concat(alias) as aliases, type from aliases where login in (select login from users) group by login order by type asc

–
– Expected output
–
lenny@example.com | lweijl@example.com, lennart@example.com, lfweijl@example.com | domain

–
-- Actual output
–
lenny@example.com | | domain

Hi,

It works all right for me...

MariaDB [test]> select login, group_concat(alias) as aliases, type from aliases where login in (select login from users) group by login order by type asc;
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
1 row in set (0.00 sec)

Please also paste the output of

explain extended select login, group_concat(alias) as aliases, type from aliases where login in (select login from users) group by login order by type asc;

select * from aliases where login in (select login from users) order by type asc;

and attach your config file(s) or output of
show variables;

Thanks.

That's strange, here is the output of the explain query.

------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------

query select * from aliases where login in (select login from users) order by type asc;
-----------------------------------------------------------------------

-----------------------------------------------------------------------

-----------------------------------------------------------------------

Query show variables; (which is quite big)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hi,

Thank you, I was able to reproduce the problem using your config options, the guilty option is
group_concat_max_len | 8589934592

With this value (actually with any value starting from 4Gb) the query returns an empty aliases value with a warning:

MariaDB [test]> set group_concat_max_len=1024*1024*1024*4;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select login, group_concat(alias) as aliases, type from aliases where login in (select login from users) group by login order by type asc;
--------------------------------

--------------------------------

--------------------------------
1 row in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;
--------------------------------------------

--------------------------------------------

--------------------------------------------
1 row in set (0.00 sec)

With group_concat_max_len less than 4 Gb it works:

set group_concat_max_len=1024*1024*1024*4 - 1;

select login, group_concat(alias) as aliases, type from aliases where login in (select login from users) group by login order by type asc;
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
1 row in set (0.01 sec)

As a workaround, I recommend you to reduce the value in your config, it doesn't make much sense to have it this big anyway (it sets the maximum permitted result length in bytes for the GROUP_CONCAT() function, I suppose if your query returns a 8 GB result set, you'll have much bigger problems than just an empty field).

Still, it shouldn't be allowed to set the option to a value that does not work, so it's a bug with the option value.

Also reproducible on all of MySQL 5.1 - 5.7.

Test case:

set group_concat_max_len=1024*1024*1024*4;

create table t1 (i int, j int) engine=MyISAM;
insert into t1 values (1,1),(1,2);
select i, group_concat(j) from t1 group by i;

drop table t1;

I wasn't aware that this value was set this high.

Maybe good to mention that I upgraded from MySQL 5.5.31+dfsg-0+wheezy1 to version 10.0.3 MariaDB. Before the update MySQL output of the query was the expected output, could this be causing the value from being set this high?

Either way, thanks a lot for the fast and good support!

This is strange, the behavior on the server side seems to be like that in all versions down to at least 5.1, both MariaDB and MySQL.
It could however happen that the bad value somehow crept in with the upgrade or afterwards.
I'll check upgrade on Wheezy to make sure it doesn't come with the package. Meanwhile, can you check your config files and see where exactly the value is set? Also, are you the only one who has access to the files, can it be that somebody else modified it recently?

– Meanwhile, can you check your config files and see where exactly the value is set?
Will do and get back to you on that.

– Also, are you the only one who has access to the files, can it be that somebody else modified it recently?
Yes, I am the only one with access to this files/machine. I didn't modify them for at least a year ago, so no recent modifications on any config file. (Maybe the upgrade process did something on the config, will investigate that.)

Also refiled at MySQL bug base as http://bugs.mysql.com/bug.php?id=69515

Must have made a mistake (one * 1024 too many) a while ago since I set that value into the config myself. No error from the upgrade progress there but a human error.

Still a bug though, so I'm sort of glad we've run into this.

Thanks again! Set the value to a more acceptable amount and all is working again.

Seems to reproduce also with group_concat_max_len=1024*1024*1024 (2^30), and works with group_concat_max_len=1024*1024*1024-1 (2^30-1).

Perhaps true maximum is 2^30-1 instead of 2^32-1.