MySQL Bugs: #16809: Union/View of unions cause hard disk to fill up and mysql to crash

Bug #16809	Union/View of unions cause hard disk to fill up and mysql to crash
Submitted:	26 Jan 2006 15:48	Modified:	27 Jan 2006 13:44
Reporter:	Gareth Owen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.0.16-max	OS:	Linux (Linux, Solaris)
Assigned to:		CPU Architecture:	Any

Description:
When using InnoDB tables (23 of the in our case), each with around 150,000 rows (17 columns and 9 indexes), if i try to run a UNION query on all tables, over about 3 columns, the /tmp folder fills up, and i get an error along the lines of:

Incorrect key file for table '/tmp/#sql_52fc_0.MYI'; try to repair it

there is almost 2 gig free in the /tmp folder before searches - if just one query uses all this up, how can i possibly ever use this database for anything?

How to repeat:
Create a series of InnoDB tables, with about 150,000 rows each, and run an union query across these.

Thank you for a problem report. Send the SHOW CREATE TABLE and SHOW TABLE STATUS results for the typical table from your UNION statement.

SHOW CREATE TABLE:

CREATE TABLE `Real_Logs_12` (
  `AAA_UID` varchar(66) default NULL,
  `ClickFrom` varchar(255) default NULL,
  `ClickLocation` varchar(20) default NULL,
  `Country` varchar(5) default NULL,
  `DayOfMonth` int(11) default NULL,
  `Hour` int(11) default NULL,
  `Minutes` int(11) default NULL,
  `HourAndMinutes` int(11) default NULL,
  `Seconds` int(11) default NULL,
  `StoryId` int(11) default NULL,
  `PageURL` varchar(255) default NULL,
  `Edition` varchar(25) default NULL,
  `QueryString` varchar(255) default NULL,
  `Section` varchar(25) default NULL,
  `PageType` varchar(45) default NULL,
  `DataCheck` varchar(5) default NULL,
  KEY `ByStoryId` (`StoryId`),
  KEY `ByPageURL` USING BTREE (`PageURL`,`StoryId`,`Edition`),
  KEY `ByBBC_UID` USING BTREE (`AAA_UID`),
  KEY `BySection` (`Section`),
  KEY `ByPageType` (`PageType`),
  KEY `HourAndMinutes` (`HourAndMinutes`),
  KEY `ByCountry` (`Country`),
  KEY `ByClickLocation` (`ClickLocation`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=4294967295 AVG_ROW_LENGTH=50 ROW_FORMAT=DYNAMIC

SHOW TABLE STATUS:

'Real_Logs_12', 'InnoDB', 10, 'Compact', 87376, 210, 18399232, 0, 33767424, 0, , '2006-01-26 14:49:53', '', '', 'latin1_swedish_ci', , 'max_rows=4294967295 avg_row_length=50 row_format=DYNAMIC', 'InnoDB free: 9216 kB'

thanks.

From a first look at your table i'd asume that you don't need UNION to filter out duplicates?
In that case an UNION ALL should do the trick, just UNION defaults to a UNION DISTINCT
which needs to create a temporary sort table to identify any possible duplicate first, and
given an esitmated row size of about 1k, 150k rows per table, 23 tables total i end up at
a total of more than 3GB of data alone if you are doing a union over all rows in all tables
without any where condition (not having seen your actual query this is what i asume you 
are doing?)

Hi Hartmut,

sorry for not being more specific - i have been using a union all when getting this error.

something like:

Select count(Country), StoryId, PageUrl, Country from view_all group by Country, order by cc desc

where view_all is a view in the form of:

crate view view_all as select * from Real_Logs_00 union all select * from Real_Logs_01 union all select * from Real_Logs_02..... etc, for all 23...

thanks.

So, you are trying to group by rows from that huge view. All rows should be sorted for that, and as it was already pointed out, a lot of disk space will be used for this... Just add more disk space for temporary files and wait, if you use such a queries. 

Send the EXPLAIN results for your real query to clarify the situation completely. I am not sure that any RDBMS will be able to provide a better execution plan for queries like this.

If you got a crash during that query (it is not OK anyway), please, send the appropriate part of the error log.

On Linux we just saw an error when the hard disk filled up (no crash), but on Solaris (SunOS 5.8) it killed mysql, with the last line in the log being:

bash-2.03$ 060125 10:46:33 [ERROR] ./mysqld: Incorrect key file for table '/var/tmp/#sql_5a95_1.MYI'; try to repair it

I've decided to move away from InnoDB now because of this (back on myisam which seems better for big searches) - maybe this isn't a bug - apart from the crashing on sun (i was just expecting it to use resources like Oracle - sorry!)

Typical line from the explain is:

13, 'UNION', 'Real_Hits_12', 'ALL', '', '', '', '', 231824, ''

As you do not want to provide any more detailed information, I am not able neither to repeat the crash you probably got (any crash is a bug, but there is no reasonably large part of the error log provided to make sure it was a crash), nor prove that no crash happens in current versions (5.0.18 and 5.0.19-BK) on Solaris. 

As for temporary space usage, it is known and expected behaviour for queries like yours. 

So, I see a problem for your application but I see no repeatable test case to call this a bug.