I experienced a very disturbing bug and I was wondering if there was any previous
documentation on this. I am presently runnning MySQL 3.22.30 on Cobalt RAQ3 Linux 5.0
server. I ran the following SQL statement in phpMyAdmin as well as in command-line and
in conjuction with a PHP script:
SELECT * FROM KS_page WHERE (page LIKE 'user_page') and
(FIND_IN_SET(ELT(0,'subsidizer','sponsor','referral','subscriber'),user_level));
All of the responses were the same. MySQL shut down and required a full restart of the
server to start again. The load rates jumped to 130%!
The structure for this table is as follows:
#
# Table structure for table 'KS_page'
#
CREATE TABLE KS_page (
ID int(11) DEFAULT '0' NOT NULL auto_increment,
page varchar(50) NOT NULL,
type varchar(15),
description text,
title varchar(200),
location varchar(50) NOT NULL,
user_level set('admin','subsidizer','sponsor','subscriber','referral','visitor')
NOT NULL,
PRIMARY KEY (ID),
KEY ID (ID),
UNIQUE ID_2 (ID)
);
#
# Dumping data for table 'KS_page'
#
INSERT INTO KS_page VALUES( '1', 'SAMPLE 1', 'frontend', 'RANDOM DATA', '', '');
INSERT INTO KS_page VALUES( '15', 'SAMPLE 2', 'information', 'RANDOM DATA', 'Become a
Subscriber', '', '');
INSERT INTO KS_page VALUES( '2', 'SAMPLE 3', 'frontend', 'RANDOM DATA', 'Little Known
Facts', '', '');
INSERT INTO KS_page VALUES( '3', 'SAMPLE 4', 'frontend', 'RANDOM DATA', 'Other Web
Sites', '', '');
INSERT INTO KS_page VALUES( '5', 'SAMPLE 5', 'frontend', 'RANDOM DATA', 'Pension And
Profit Sharing', '', '');
INSERT INTO KS_page VALUES( '7', 'SAMPLE 6', 'downloads', 'RANDOM DATA', 'Slide
Shows', '', '');
INSERT INTO KS_page VALUES( '10', 'SAMPLE 7', 'information', 'RANDOM DATA',
'Sponsor/Referral/Subscriber Log In', '', '');
INSERT INTO KS_page VALUES( '13', 'SAMPLE 8', 'information', 'RANDOM DATA', 'Etcs
Info', '', '');
INSERT INTO KS_page VALUES( '14', 'SAMPLE 9', 'information', 'RANDOM DATA', 'TITLE
NAME 14',', 'subsidizer,sponsor,subscriber,referral');
INSERT INTO KS_page VALUES( '16', 'SAMPLE 10', 'information', 'RANDOM DATA', '', '');
INSERT INTO KS_page VALUES( '17', 'SAMPLE 11', 'information', 'RANDOM DATA', 'ENews
Archives', '', '');
INSERT INTO KS_page VALUES( '18', 'SAMPLE 12', 'information', 'RANDOM DATA', 'Become a
Sponsor/Subscriber', '', '');
According to the on-line manual, which is for 3.23.17:
FIND_IN_SET(str,strlist)
Returns a value 1 to N if the string str is in the list strlist consisting of N
substrings. A string list is a string composed of substrings separated by `,' characters.
If the first argument is a constant string and the second is a column of type SET, the
FIND_IN_SET() function is optimized to use bit arithmetic! Returns 0 if str is not in
strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This
function will not work properly if the first argument contains a `,'.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
ELT(N,str1,str2,str3,...)
Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or
greater than the number of arguments. ELT() is the complement of FIELD().
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
According to the above documentation I should be receiving a NULL value for the ELT() part
of the function, which should result in a NULL value for the FIND_IN_SET(). Assuming that
we can get this far, I can't test this again because the people who are hosting the server
are getting slightly annoyed by my requests to turn the machine on again and again, then
the WHERE clause has a NULL value in it. Could this be the culplrit? Is it true that if
you write SELECT * FROM tablename WHERE NULL; the database will turn off? Any help on
this one would be very much appreciated. I have never seen anything like this before.
Feel free to contact me for any more information. Mike Krolak van
Schouwen Associates, LLC.
