Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.35, 5.5.36, 10.0.8
-
None
-
debian 7 wheezy mysql_multi environment
Description
We got this error:
Using too big key for internal temp tables
it seems that the bug described in https://bugs.launchpad.net/maria/+bug/957409 is not fixed for all cases.
the testcase described there works.
But in our case the workaround (SET GLOBAL optimizer_switch = 'derived_with_keys=off';) is needed, so that our complex statement works.
The statement is a complex statement on innodb-tables with inner selects and substring_index-fkt and group by/order by having count. belonging to three tables.
Test case and statement delivered. Tables.sql contains select statement which causes the error.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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.
new testdata reduced on the only necessary information