debian 7 wheezy mysql_multi environment

Hi,

A complete test case would be perfect, but meanwhile, could you please provide

• the offending query;
• EXPLAIN for the offending query;
• the table structures for the involved tables (SHOW CREATE TABLE);
• SHOW INDEX for the tables;
• your config file(s)?

Thanks.

i'll do an upload for config

Example-Data for the test-tables to recreate the error.
The error also occures on MariaDB 5.5.34.

Here are the comments as file: config and tables.
The serialized-column of fd contains serilized java-object.

new testdata reduced on the only necessary information

Thanks for the reproducible test case.

The reported test case is attached as mdev5721.test. It is the exact same data and query, just all together in one file in the proper order.

I have also reduced and somewhat modified it a little more, below is what's left. It might be a bit more convenient for initial debugging, but it's absolutely necessary to also check the initial test case after the fix, because it the one below uses MyISAM, while the initial one InnoDB, and there might be some other important differences.

CREATE TABLE `td` (

  `id` varchar(255) NOT NULL DEFAULT '',

  `familyid` int(11) DEFAULT NULL,

  `withdrawndate` date DEFAULT NULL,

  KEY `index_td_familyid_id` (`familyid`,`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `fd` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `activefromts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `shortdescription` text,

  `useraccessfamily` varchar(512) DEFAULT NULL,

  `serialized` longtext,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into fd values (38,'2013-03-04 07:49:22','desc','CODE','string');

insert into td values ('picture/89/1369722032695.pmd',89,NULL);

insert into td values ('picture/90/1369832057370.pmd',90,NULL);

SELECT * FROM fd x,

(

SELECT

  fd.useraccessfamily,

  fd.serialized AS picturesubuser,

  COUNT(*)

FROM

  fd, td

GROUP BY fd.useraccessfamily, picturesubuser

) y

WHERE

  x.useraccessfamily = y.useraccessfamily 

;

Hi...

I also encounter this problem and disabling derived keys works for me as well. My test case is similar to that of the OP as I am also using sub selects and 'group by' statements. Interestingly the size of the tables matters. Having a few more entries in tables 'b' (+1) and 'c' (+2) triggers the error. Table 'a' is not used in the select and I haven't tested if this table is strictly necessary to reproduce the error.

I also created a reproducible test case. Which I will attach to this bug report as well.

ups... forgot to mention

the rows that reference the additional entry 'pa = AB098503' in table 'b' that is only present in the broken example is excluded by the 'rt is NULL' part when joining table 'b'.

Also, decreasing the width of the varchar fields a, i, t that are used in the group by statement to a combined width of less then 700 characters solves the problem without the need to disable derived keys.

The fix for this bug was pushed into the 5.3 tree (rev 3793).