MySQL Bugs: #5280: Complex query hangs server with signal 11 responce

Bug #5280	Complex query hangs server with signal 11 responce
Submitted:	28 Aug 2004 13:09	Modified:	11 Sep 2004 1:11
Reporter:	Borisas Bursteinas	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.1.1. FreeBSD port: mysql-server-4.1.1_2	OS:	FreeBSD (freebsd5.2.1)
Assigned to:		CPU Architecture:	Any

Description:
When site became popular mysql suddenly started to hang not letting anybody to connect. After one week of playing with buffer sizes (increasing helped, but not completely, server kept reloading itself every couple of minutes depending on workload) and checking scripts we have identified a query which caused problem (contains nested SELECT, JOINS, GROUP BY and UNIONS). After disabling it server started to run as a cucumber, without any problems. Also when query is enabled server does not hang when load is rather small (at night time, for example). This is repeatable :).

Error log entry:

Version: '4.1.1-alpha'  socket: '/tmp/mysql.sock'  port: 0
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=134217728
read_buffer_size=1044480
max_used_connections=6
max_connections=750
threads_connected=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 243206
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

How to repeat:
Query which were causing problems is:

SELECT * FROM (SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              U.LAST_NAME FIELD1, U.FIRST_NAME FIELD2, U.MALE FIELD3, '' FIELD4,
              MAX(M.USER_TYPE) USER_TYPE, 0 TEAM_TYPE
            FROM keywords K, busers U 
                 LEFT JOIN members M ON U.USER_ID=M.USER_ID
                 LEFT JOIN teams T ON M.TEAM_ID=T.TEAM_ID
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE UPPER('olimpiada')) 
              
              AND U.PUBLISH='Y'
              AND U.USER_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=10
              AND K.OBJECT_ID != 0
            GROUP BY 
              K.OBJECT_ID, K.OBJECT_TYPE, U.LAST_NAME, U.FIRST_NAME, U.MALE
            
            UNION
            
            SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              T.TEAM_NAME FIELD1, T.CITY FIELD2, T.COUNTRY FIELD3, '' FIELD4,
              0 USER_TYPE, T.TEAM_TYPE
            FROM keywords K, teams T
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE UPPER('olimpiada')) 
              
              AND T.PUBLISH='Y'
              AND T.TEAM_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=15
              AND K.OBJECT_ID != 0
            GROUP BY K.OBJECT_ID, K.OBJECT_TYPE, 
              T.TEAM_NAME, T.CITY, T.COUNTRY, T.TEAM_TYPE
            
            UNION
            
            SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              T.TNAME FIELD1, T.PIC_LOCATION FIELD2, '' FIELD3, '' FIELD4,
              0 USER_TYPE, 0 TEAM_TYPE
            FROM keywords K, tournaments T
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE UPPER('olimpiada')) 
              
              AND T.PUBLISH='Y'
              AND T.TOURNAMENT_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=20
              AND K.OBJECT_ID != 0
            GROUP BY K.OBJECT_ID, K.OBJECT_TYPE, 
              T.TNAME, T.PIC_LOCATION
            
            UNION
            
            SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              O.TITLE FIELD1, T.ORGTYPE_TITLE FIELD2, '' FIELD3, '' FIELD4,
              0 USER_TYPE, 0 TEAM_TYPE
            FROM keywords K, organizations O
                LEFT JOIN orgtypes T ON O.ORGTYPE_ID=T.ORGTYPE_ID
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE UPPER('olimpiada')) 
              
              AND O.PUBLISH='Y'
              AND O.ORGANIZATION_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=30
              AND K.OBJECT_ID != 0
            GROUP BY K.OBJECT_ID, K.OBJECT_TYPE, O.TITLE, T.ORGTYPE_TITLE) SEARCH ORDER BY PRIORITY DESC, OBJECT_TYPE LIMIT 0,10><SELECT * FROM (SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              N.TITLE FIELD1, '' FIELD2, N.PIC_LOCATION FIELD3,
              SUBSTRING(N.DATE_PUBLISHED, 1, 16) FIELD4,
              0 USER_TYPE, 0 TEAM_TYPE
            FROM keywords K, news N
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE UPPER('olimpiada')) 
              
              AND N.PUBLISH='Y'
              AND N.NEWS_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=100
              AND K.OBJECT_ID != 58193
            GROUP BY K.OBJECT_ID, K.OBJECT_TYPE, N.TITLE,
              N.PIC_LOCATION, N.DATE_PUBLISHED
            ORDER BY FIELD4 DESC) SEARCH WHERE (OBJECT_TYPE <> 100 OR OBJECT_ID <> 58193)  ORDER BY FIELD4 DESC, OBJECT_ID DESC LIMIT 0,10

Tables involved:

CREATE TABLE `keywords` (
  `KEYWORD_ID` int(11) NOT NULL auto_increment,
  `KEYWORD` varchar(64) NOT NULL default '',
  `OBJECT_TYPE` int(11) NOT NULL default '0',
  `OBJECT_ID` int(11) NOT NULL default '0',
  `PRIORITY` int(11) NOT NULL default '0',
  PRIMARY KEY  (`KEYWORD_ID`),
  KEY `KEYWORDS_IDX` (`OBJECT_TYPE`,`OBJECT_ID`),
  KEY `KEYWORDS_IDX2` (`KEYWORD`)
) TYPE=MyISAM DEFAULT CHARSET=cp1257 AUTO_INCREMENT=91457 ;

CREATE TABLE `busers` (
  `FIRST_NAME` varchar(32) NOT NULL default '',
  `LAST_NAME` varchar(32) NOT NULL default '',
  `NICKNAME` varchar(25) NOT NULL default '',
  `PUBLISH` char(1) NOT NULL default 'Y',
  `MALE` char(1) default 'Y',
  `BIRTH_DATE` datetime default '0000-00-00 00:00:00',
  `PIC_LOCATION` varchar(128) default '',
  `ORGANIZATION` varchar(64) default '',
  `HEIGHT` int(11) default '0',
  `WEIGHT` int(11) default '0',
  `FAMILY_INFO` text,
  `CAREER_INFO` text,
  `HOBBY_INFO` text,
  `USER_TYPE` int(11) default '0',
  `CITIZENSHIP` varchar(64) default '',
  `PROFILE` text,
  `ADD_INFO` text,
  `ACH_INFO` text,
  `USER_ID` bigint(22) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`USER_ID`),
  KEY `PUBLISH` (`PUBLISH`),
  KEY `USER_TYPE` (`USER_TYPE`)
) TYPE=MyISAM DEFAULT CHARSET=cp1257 AUTO_INCREMENT=71066 ;

CREATE TABLE `teams` (
  `TEAM_ID` int(11) NOT NULL auto_increment,
  `TEAM_NAME` varchar(64) default '',
  `CAT_ID1` int(11) default '0',
  `CAT_ID2` int(11) default '0',
  `TEAM_TYPE` int(11) default '0',
  `CITY` varchar(32) default '',
  `COUNTRY` varchar(32) default '',
  `PUBLISH` char(1) NOT NULL default 'N',
  `PIC_LOCATION` varchar(128) default '',
  `FOUNDED_YEAR` int(11) default '0',
  `FOUNDED_MONTH` int(11) default '0',
  `FOUNDED_DAY` int(11) default '0',
  `ADDRESS` text,
  `PIC2_LOCATION` varchar(128) default '',
  `LINK` varchar(128) default '',
  `CAT_ID3` int(11) default '0',
  `TEAM_NAME2` varchar(64) default '',
  `TEAM_DESCR` text,
  PRIMARY KEY  (`TEAM_ID`),
  KEY `PUBLISH` (`PUBLISH`),
  KEY `CAT_ID1` (`CAT_ID1`),
  KEY `TEAM_TYPE` (`TEAM_TYPE`)
) TYPE=MyISAM DEFAULT CHARSET=cp1257 AUTO_INCREMENT=2070 ;

CREATE TABLE `tournaments` (
  `TOURNAMENT_ID` int(11) NOT NULL auto_increment,
  `TNAME` varchar(64) NOT NULL default '',
  `CAT_ID1` int(11) default '0',
  `CAT_ID2` int(11) default '0',
  `TTYPE` int(11) NOT NULL default '0',
  `FOUNDED_YEAR` int(11) default '0',
  `FOUNDED_MONTH` int(11) default '0',
  `FOUNDED_DAY` int(11) default '0',
  `ORGANIZATION_ID` int(11) default '0',
  `PUBLISH` char(1) NOT NULL default 'N',
  `PIC_LOCATION` varchar(128) default '',
  `PIC2_LOCATION` varchar(128) default '',
  `DESCR` text,
  PRIMARY KEY  (`TOURNAMENT_ID`)
) TYPE=MyISAM DEFAULT CHARSET=cp1257 AUTO_INCREMENT=270 ;

CREATE TABLE `organizations` (
  `ORGANIZATION_ID` int(11) NOT NULL auto_increment,
  `CDATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `UDATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `TITLE` varchar(128) NOT NULL default '',
  `FOUNDED_YEAR` int(11) default '0',
  `FOUNDED_MONTH` int(11) default '0',
  `FOUNDED_DAY` int(11) default '0',
  `ADDRESS` text,
  `LINK` varchar(128) default '',
  `PIC_LOCATION` varchar(128) default '',
  `PIC2_LOCATION` varchar(128) default '',
  `ORGTYPE_ID` int(11) NOT NULL default '0',
  `PUBLISH` char(1) NOT NULL default '',
  `CAT_ID1` int(11) default '0',
  `CAT_ID2` int(11) default '0',
  `DESCR` text,
  PRIMARY KEY  (`ORGANIZATION_ID`),
  KEY `PUBLISH` (`PUBLISH`)
) TYPE=MyISAM DEFAULT CHARSET=cp1257 AUTO_INCREMENT=65 ;

CREATE TABLE `news` (
  `NEWS_ID` int(11) NOT NULL auto_increment,
  `TITLE` varchar(255) NOT NULL default '',
  `SOURCE2` varchar(100) default '',
  `CAT_ID1` int(11) default '0',
  `CAT_ID2` int(11) default '0',
  `DATE_PUBLISHED` datetime NOT NULL default '0000-00-00 00:00:00',
  `CUSER_ID` int(11) unsigned default '0',
  `DATE_CREATED` datetime NOT NULL default '0000-00-00 00:00:00',
  `PUBLISH` char(1) NOT NULL default 'Y',
  `LINK1` varchar(128) default '',
  `LINK1NAME` varchar(128) default '',
  `LINK2` varchar(128) default '',
  `LINK2NAME` varchar(128) default '',
  `LINK3` varchar(128) default '',
  `LINK3NAME` varchar(128) default '',
  `ZANRAS` smallint(1) default '0',
  `PIC_LOCATION` varchar(128) default '',
  `SOURCE` varchar(255) default '',
  `DATE_EXPIRED` datetime default '0000-00-00 00:00:00',
  `PIC2_LOCATION` varchar(128) default '',
  `DESCR` text,
  `TV_ID` int(11) default '0',
  `TV_ID2` int(11) default '0',
  `PIC2_SOURCE` varchar(100) default '',
  `ADMIN` varchar(20) default '',
  `PRIORITY` int(11) NOT NULL default '1',
  PRIMARY KEY  (`NEWS_ID`),
  KEY `DATE_EXPIRED` (`DATE_EXPIRED`),
  KEY `DATE_PUBLISHED` (`DATE_PUBLISHED`),
  KEY `DATE_CREATED` (`DATE_CREATED`),
  KEY `PUBLISH` (`PUBLISH`),
  KEY `ZANRAS` (`ZANRAS`),
  KEY `CAT_ID1_IDX` (`CAT_ID1`),
  KEY `CAT_ID2` (`CAT_ID2`)
) TYPE=MyISAM DEFAULT CHARSET=cp1257 AUTO_INCREMENT=58195 ;

Can provide data on demand

Forgot to mention hardware. 
Boxed Server Board SE7501BR2 "Bryson" Volume DP Prestonia with Single U320 SCSI
2GB RAM DIMM MaLabs
Intel Xeon™ Processor 2,4Ghz

Copied into query too much. Here is actual query

SELECT * FROM (SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              U.LAST_NAME FIELD1, U.FIRST_NAME FIELD2, U.MALE FIELD3, ''
FIELD4,
              MAX(M.USER_TYPE) USER_TYPE, 0 TEAM_TYPE
            FROM keywords K, busers U 
                 LEFT JOIN members M ON U.USER_ID=M.USER_ID
                 LEFT JOIN teams T ON M.TEAM_ID=T.TEAM_ID
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE
UPPER('olimpiada')) 
              
              AND U.PUBLISH='Y'
              AND U.USER_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=10
              AND K.OBJECT_ID != 0
            GROUP BY 
              K.OBJECT_ID, K.OBJECT_TYPE, U.LAST_NAME, U.FIRST_NAME, U.MALE
            
            UNION
            
            SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              T.TEAM_NAME FIELD1, T.CITY FIELD2, T.COUNTRY FIELD3, '' FIELD4,
              0 USER_TYPE, T.TEAM_TYPE
            FROM keywords K, teams T
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE
UPPER('olimpiada')) 
              
              AND T.PUBLISH='Y'
              AND T.TEAM_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=15
              AND K.OBJECT_ID != 0
            GROUP BY K.OBJECT_ID, K.OBJECT_TYPE, 
              T.TEAM_NAME, T.CITY, T.COUNTRY, T.TEAM_TYPE
            
            UNION
            
            SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              T.TNAME FIELD1, T.PIC_LOCATION FIELD2, '' FIELD3, '' FIELD4,
              0 USER_TYPE, 0 TEAM_TYPE
            FROM keywords K, tournaments T
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE
UPPER('olimpiada')) 
              
              AND T.PUBLISH='Y'
              AND T.TOURNAMENT_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=20
              AND K.OBJECT_ID != 0
            GROUP BY K.OBJECT_ID, K.OBJECT_TYPE, 
              T.TNAME, T.PIC_LOCATION
            
            UNION
            
            SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE, 
              O.TITLE FIELD1, T.ORGTYPE_TITLE FIELD2, '' FIELD3, '' FIELD4,
              0 USER_TYPE, 0 TEAM_TYPE
            FROM keywords K, organizations O
                LEFT JOIN orgtypes T ON O.ORGTYPE_ID=T.ORGTYPE_ID
            WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE
UPPER('olimpiada')) 
              
              AND O.PUBLISH='Y'
              AND O.ORGANIZATION_ID=K.OBJECT_ID 
              AND K.OBJECT_TYPE=30
              AND K.OBJECT_ID != 0
            GROUP BY K.OBJECT_ID, K.OBJECT_TYPE, O.TITLE, T.ORGTYPE_TITLE)
SEARCH ORDER BY PRIORITY DESC, OBJECT_TYPE LIMIT 0,10

Yes of course provide us the data for to run the offended query:

ftp://ftp.mysql.com/pub/mysql/upload/

please zip the file using a name identifying this bug report and
let me know here when done.

Thanks in advance

I have uploaded bug_#5280.zip. Here is also table with current server variables

 Variable 	 Session value 	 Global value 
back log 	100 	100 
basedir 	/usr/local/ 	/usr/local/ 
bdb cache size 	8388600 	8388600 
bdb log buffer size 	65536 	65536 
bdb home 	/var/db/mysql/ 	/var/db/mysql/ 
bdb max lock 	10000 	10000 
bdb logdir 		
bdb shared data 	OFF 	OFF 
bdb tmpdir 	/var/tmp/ 	/var/tmp/ 
binlog cache size 	32768 	32768 
bulk insert buffer size 	8388608 	8388608 
character set server 	cp1257 	cp1257 
character set system 	utf8 	utf8 
character set database 	cp1257 	cp1257 
character set client 	cp1257 	cp1257 
character set connection 	cp1257 	cp1257 
character-sets-dir 	/usr/local/share/mysql/charsets/ 	/usr/local/share/mysql/charsets/ 
character set results 	cp1257 	cp1257 
collation connection 	cp1257_general_ci 	cp1257_general_ci 
collation database 	cp1257_general_ci 	cp1257_general_ci 
collation server 	cp1257_general_ci 	cp1257_general_ci 
concurrent insert 	ON 	ON 
connect timeout 	5 	5 
datadir 	/var/db/mysql/ 	/var/db/mysql/ 
date format 	%Y-%m-%d 	%Y-%m-%d 
datetime format 	%Y-%m-%d %H:%i:%s 	%Y-%m-%d %H:%i:%s 
default week format 	0 	0 
delay key write 	ON 	ON 
delayed insert limit 	100 	100 
delayed insert timeout 	300 	300 
delayed queue size 	1000 	1000 
expire logs days 	0 	0 
flush 	OFF 	OFF 
flush time 	0 	0 
ft boolean syntax 	+ -><()~*:""&| 	+ -><()~*:""&| 
ft min word len 	4 	4 
ft max word len 	254 	254 
ft query expansion limit 	20 	20 
ft stopword file 	(built-in) 	(built-in) 
have bdb 	YES 	YES 
have crypt 	YES 	YES 
have compress 	YES 	YES 
have innodb 	YES 	YES 
have isam 	NO 	NO 
have raid 	NO 	NO 
have symlink 	YES 	YES 
have openssl 	NO 	NO 
have query cache 	YES 	YES 
init file 		
innodb additional mem pool size 	1048576 	1048576 
innodb buffer pool size 	8388608 	8388608 
innodb buffer pool awe mem mb 	0 	0 
innodb data file path 	ibdata1:10M:autoextend 	ibdata1:10M:autoextend 
innodb data home dir 		
innodb file io threads 	4 	4 
innodb open files 	300 	300 
innodb force recovery 	0 	0 
innodb thread concurrency 	8 	8 
innodb flush log at trx commit 	1 	1 
innodb fast shutdown 	ON 	ON 
innodb file per table 	OFF 	OFF 
innodb flush method 		
innodb lock wait timeout 	50 	50 
innodb log arch dir 	./ 	./ 
innodb log archive 	OFF 	OFF 
innodb log buffer size 	1048576 	1048576 
innodb log file size 	5242880 	5242880 
innodb log files in group 	2 	2 
innodb log group home dir 	./ 	./ 
innodb mirrored log groups 	1 	1 
innodb max dirty pages pct 	90 	90 
interactive timeout 	28800 	28800 
join buffer size 	2093056 	2093056 
key buffer size 	134217728 	134217728 
key cache block size 	1024 	1024 
key cache division limit 	100 	100 
key cache age threshold 	300 	300 
language 	/usr/local/share/mysql/english/ 	/usr/local/share/mysql/english/ 
large files support 	ON 	ON 
local infile 	ON 	ON 
locked in memory 	OFF 	OFF 
log 	OFF 	OFF 
log update 	OFF 	OFF 
log bin 	OFF 	OFF 
log slave updates 	OFF 	OFF 
log slow queries 	OFF 	OFF 
log warnings 	OFF 	OFF 
long query time 	10 	10 
low priority updates 	OFF 	OFF 
lower case table names 	OFF 	OFF 
max allowed packet 	523264 	523264 
max binlog cache size 	4294967295 	4294967295 
max binlog size 	1073741824 	1073741824 
max connections 	750 	750 
max connect errors 	10 	10 
max error count 	64 	64 
max delayed threads 	20 	20 
max heap table size 	16777216 	16777216 
max join size 	4294967295 	4294967295 
max relay log size 	0 	0 
max seeks for key 	4294967295 	4294967295 
max length for sort data 	1024 	1024 
max prepared statements 	64 	64 
max sort length 	1024 	1024 
max user connections 	0 	0 
max tmp tables 	32 	32 
max write lock count 	4294967295 	4294967295 
myisam max extra sort file size 	268435456 	268435456 
myisam max sort file size 	2147483647 	2147483647 
myisam repair threads 	1 	1 
myisam recover options 	OFF 	OFF 
myisam sort buffer size 	8388608 	8388608 
net buffer length 	16384 	16384 
net read timeout 	30 	30 
net retry count 	1000000 	1000000 
net write timeout 	60 	60 
new 	OFF 	OFF 
old passwords 	OFF 	OFF 
open files limit 	11095 	11095 
pid file 	/var/db/mysql/krepsinis.pid 	/var/db/mysql/krepsinis.pid 
log error 		
port 	0 	0 
protocol version 	10 	10 
preload buffer size 	32768 	32768 
pseudo thread id 	8667 	0 
query alloc block size 	8192 	8192 
query cache limit 	1048576 	1048576 
query cache min res unit 	4096 	4096 
query cache size 	0 	0 
query cache type 	ON 	ON 
secure auth 	OFF 	OFF 
query prealloc size 	8192 	8192 
range alloc block size 	2048 	2048 
read buffer size 	1044480 	1044480 
read only 	OFF 	OFF 
read rnd buffer size 	2093056 	2093056 
relay log purge 	ON 	ON 
rpl recovery rank 	0 	0 
server id 	0 	0 
slave net timeout 	3600 	3600 
skip external locking 	ON 	ON 
skip networking 	ON 	ON 
skip show database 	OFF 	OFF 
slow launch time 	2 	2 
socket 	/tmp/mysql.sock 	/tmp/mysql.sock 
sort buffer size 	2097144 	2097144 
sql mode 		
table cache 	128 	128 
table type 	MYISAM 	MYISAM 
thread cache size 	200 	200 
thread stack 	196608 	196608 
tx isolation 	REPEATABLE-READ 	REPEATABLE-READ 
time format 	%H:%i:%s 	%H:%i:%s 
timezone 	AZOST 	AZOST 
tmp table size 	2097152 	2097152 
tmpdir 		
transaction alloc block size 	8192 	8192 
transaction prealloc size 	4096 	4096 
version 	4.1.1-alpha 	4.1.1-alpha 
version bdb 	Sleepycat Software: Berkeley DB 4.1.24: (December 1, 2003) 	Sleepycat Software: Berkeley DB 4.1.24: (December 1, 2003) 
version comment 	FreeBSD port: mysql-server-4.1.1_2 	FreeBSD port: mysql-server-4.1.1_2 
version compile machine 	i386 	i386 
version compile os 	portbld-freebsd5.2.1 	portbld-freebsd5.2.1 
wait timeout 	28800 	28800

I downloaded your test case and when running your last query I got:

  -> SEARCH ORDER BY PRIORITY DESC, OBJECT_TYPE LIMIT 0,10;
ERROR 1146 (42S02): Table 'testbug.members' doesn't exist

then at first glance a table was missed.

Sorry. Uploaded missing table, file bug_#5280_addition1.zip

Now table orgtypes is missing:

ERROR 1146 (42S02): Table 'bug5280.orgtypes' doesn't exist

cannot find to describe how I am annoyed with myself :). Here is that small table with its records. 

#
# Table structure for table `orgtypes`
#

CREATE TABLE `orgtypes` (
  `ORGTYPE_ID` int(11) NOT NULL auto_increment,
  `ORGTYPE_TITLE` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`ORGTYPE_ID`)
) TYPE=MyISAM DEFAULT CHARSET=cp1257 AUTO_INCREMENT=43 ;

#
# Dumping data for table `orgtypes`
#

INSERT INTO `orgtypes` (`ORGTYPE_ID`, `ORGTYPE_TITLE`) VALUES (42, 'Asociacija');
INSERT INTO `orgtypes` (`ORGTYPE_ID`, `ORGTYPE_TITLE`) VALUES (2, 'Federacija');
INSERT INTO `orgtypes` (`ORGTYPE_ID`, `ORGTYPE_TITLE`) VALUES (21, 'Departamentas');
INSERT INTO `orgtypes` (`ORGTYPE_ID`, `ORGTYPE_TITLE`) VALUES (22, 'Lyga');
INSERT INTO `orgtypes` (`ORGTYPE_ID`, `ORGTYPE_TITLE`) VALUES (41, 'Sàjunga');

double checked everything, seems that it nothing else is missing

Right, let me test again.

Thanks

I tested your test case with latest 4.1.XX BK source and I wasn't able to
repeat the behavior reported:

mysql> SELECT * FROM (SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE,
    ->               U.LAST_NAME FIELD1, U.FIRST_NAME FIELD2, U.MALE FIELD3, ''
    -> FIELD4,
    ->               MAX(M.USER_TYPE) USER_TYPE, 0 TEAM_TYPE
    ->             FROM keywords K, busers U
    ->                  LEFT JOIN members M ON U.USER_ID=M.USER_ID
    ->                  LEFT JOIN teams T ON M.TEAM_ID=T.TEAM_ID
    ->             WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE
    -> UPPER('olimpiada'))
    ->
    ->               AND U.PUBLISH='Y'
    ->               AND U.USER_ID=K.OBJECT_ID
    ->               AND K.OBJECT_TYPE=10
    ->               AND K.OBJECT_ID != 0
    ->             GROUP BY
    ->               K.OBJECT_ID, K.OBJECT_TYPE, U.LAST_NAME, U.FIRST_NAME, U.MALE
    ->
    ->             UNION
    ->
    ->             SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE,
    ->               T.TEAM_NAME FIELD1, T.CITY FIELD2, T.COUNTRY FIELD3, '' FIELD4,
    ->               0 USER_TYPE, T.TEAM_TYPE
    ->             FROM keywords K, teams T
    ->             WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE
    -> UPPER('olimpiada'))
    ->
    ->               AND T.PUBLISH='Y'
    ->               AND T.TEAM_ID=K.OBJECT_ID
    ->               AND K.OBJECT_TYPE=15
    ->               AND K.OBJECT_ID != 0
    ->             GROUP BY K.OBJECT_ID, K.OBJECT_TYPE,
    ->               T.TEAM_NAME, T.CITY, T.COUNTRY, T.TEAM_TYPE
    ->
    ->             UNION
    ->
    ->             SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE,
    ->               T.TNAME FIELD1, T.PIC_LOCATION FIELD2, '' FIELD3, '' FIELD4,
    ->               0 USER_TYPE, 0 TEAM_TYPE
    ->             FROM keywords K, tournaments T
    ->             WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE
    -> UPPER('olimpiada'))
    ->
    ->               AND T.PUBLISH='Y'
    ->               AND T.TOURNAMENT_ID=K.OBJECT_ID
    ->               AND K.OBJECT_TYPE=20
    ->               AND K.OBJECT_ID != 0
    ->             GROUP BY K.OBJECT_ID, K.OBJECT_TYPE,
    ->               T.TNAME, T.PIC_LOCATION
    ->
    ->             UNION
    ->
    ->             SELECT K.OBJECT_ID, SUM(15-K.PRIORITY) PRIORITY, K.OBJECT_TYPE,
    ->               O.TITLE FIELD1, T.ORGTYPE_TITLE FIELD2, '' FIELD3, '' FIELD4,
    ->               0 USER_TYPE, 0 TEAM_TYPE
    ->             FROM keywords K, organizations O
    ->                 LEFT JOIN orgtypes T ON O.ORGTYPE_ID=T.ORGTYPE_ID
    ->             WHERE (UPPER(KEYWORD) LIKE UPPER('atënai') OR UPPER(KEYWORD) LIKE
    -> UPPER('olimpiada'))
    ->
    ->               AND O.PUBLISH='Y'
    ->               AND O.ORGANIZATION_ID=K.OBJECT_ID
    ->               AND K.OBJECT_TYPE=30
    ->               AND K.OBJECT_ID != 0
    ->             GROUP BY K.OBJECT_ID, K.OBJECT_TYPE, O.TITLE, T.ORGTYPE_TITLE)
    -> SEARCH ORDER BY PRIORITY DESC, OBJECT_TYPE LIMIT 0,10\G
*************************** 1. row ***************************
  OBJECT_ID: 268
   PRIORITY: 15
OBJECT_TYPE: 20
     FIELD1: Olimpiada
     FIELD2:
     FIELD3:
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 0
*************************** 2. row ***************************
  OBJECT_ID: 269
   PRIORITY: 15
OBJECT_TYPE: 20
     FIELD1: Olimpiada (moterys)
     FIELD2:
     FIELD3:
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 0
*************************** 3. row ***************************
  OBJECT_ID: 221
   PRIORITY: 14
OBJECT_TYPE: 15
     FIELD1: Panathinaikos
     FIELD2: Atënai
     FIELD3: Graikija
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 10
*************************** 4. row ***************************
  OBJECT_ID: 1963
   PRIORITY: 14
OBJECT_TYPE: 15
     FIELD1: Maroussi
     FIELD2: Atënai
     FIELD3: Graikija
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 10
*************************** 5. row ***************************
  OBJECT_ID: 224
   PRIORITY: 14
OBJECT_TYPE: 15
     FIELD1: Peristeri
     FIELD2: Atënai
     FIELD3: Graikija
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 10
*************************** 6. row ***************************
  OBJECT_ID: 2015
   PRIORITY: 14
OBJECT_TYPE: 15
     FIELD1: Ilysiakos
     FIELD2: Atënai
     FIELD3: Graikija
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 10
*************************** 7. row ***************************
  OBJECT_ID: 923
   PRIORITY: 14
OBJECT_TYPE: 15
     FIELD1: Panionios
     FIELD2: Atënai
     FIELD3: Graikija
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 10
*************************** 8. row ***************************
  OBJECT_ID: 208
   PRIORITY: 14
OBJECT_TYPE: 15
     FIELD1: AEK
     FIELD2: Atënai
     FIELD3: Graikija
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 10
*************************** 9. row ***************************
  OBJECT_ID: 1784
   PRIORITY: 14
OBJECT_TYPE: 15
     FIELD1: Ionikos
     FIELD2: Atënai
     FIELD3: Graikija
     FIELD4:
  USER_TYPE: 0
  TEAM_TYPE: 10
9 rows in set (0.06 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 4.1.5-gamma-debug |
+-------------------+
1 row in set (0.00 sec)

mysql>

Have you tried to simulate heavy load situation? Something like 300 connections at the same time. If to disable thread cache the situation must be reached after few minutes. I will try to upgrade my current server as well.