Description:
256MB Memory
When the following SQL query is sent, MySQL closes all connections and restarts:
SELECT artists.artist, albums.album, albums.tracks, albums.year, albums.alid FROM artists LEFT JOIN albums ON artists.aid = albums.aid WHERE 1 AND MATCH(artists.artist) AGAINST('\"doggy style\"' IN BOOLEAN MODE) GROUP BY albums.alid ORDER BY artists.artist, albums.album
Yet this one works:
SELECT artists.artist, albums.album, albums.tracks, albums.year, albums.alid FROM artists LEFT JOIN albums ON artists.aid = albums.aid WHERE 1 AND MATCH(albums.album) AGAINST('\"spaz ass\"' IN BOOLEAN MODE) GROUP BY albums.alid ORDER BY artists.artist, albums.album
However as long as I do not use any of the BOOLEAN methods on the artists.artist field (still with IN BOOLEAN MODE in the query), the query works fine. I have rebuilt the FULLTEXT index and still have the same result.
Here is a dump of the tables:
#
# Table structure for table `albums`
#
CREATE TABLE `albums` (
`alid` int(10) unsigned NOT NULL auto_increment,
`aid` int(10) unsigned NOT NULL default '0',
`gid` int(10) unsigned NOT NULL default '0',
`discid` varchar(8) NOT NULL default '',
`album` varchar(80) NOT NULL default '',
`year` smallint(4) unsigned NOT NULL default '0',
`tracks` tinyint(2) unsigned NOT NULL default '0',
`comments` varchar(255) NOT NULL default '',
`disclength` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`alid`),
KEY `aid` (`aid`),
KEY `gid` (`gid`),
KEY `discid` (`discid`),
FULLTEXT KEY `album` (`album`)
) TYPE=MyISAM;
# --------------------------------------------------------
#
# Table structure for table `artists`
#
CREATE TABLE `artists` (
`aid` int(10) unsigned NOT NULL auto_increment,
`artist` varchar(80) NOT NULL default '',
PRIMARY KEY (`aid`),
FULLTEXT KEY `artist` (`artist`)
) TYPE=MyISAM;
MySQL was compiled using the following script and strip(ped):
#!/bin/sh
CFLAGS="-O3 -mcpu=pentium3 -march=pentium3 -fomit-frame-pointer" \
CXX=gcc \
CXXFLAGS="-O3 -mcpu=pentium3 -march=pentium3 -felide-constructors -fno-exceptions -fno-rtti -fomit-frame-pointer" \
./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --without-debug --enable-assembler \
--with-vio --with-openssl --without-innodb --without-isam --enable-thread-safe-client \
--enable-local-infile
How to repeat:
See Description
Description: 256MB Memory When the following SQL query is sent, MySQL closes all connections and restarts: SELECT artists.artist, albums.album, albums.tracks, albums.year, albums.alid FROM artists LEFT JOIN albums ON artists.aid = albums.aid WHERE 1 AND MATCH(artists.artist) AGAINST('\"doggy style\"' IN BOOLEAN MODE) GROUP BY albums.alid ORDER BY artists.artist, albums.album Yet this one works: SELECT artists.artist, albums.album, albums.tracks, albums.year, albums.alid FROM artists LEFT JOIN albums ON artists.aid = albums.aid WHERE 1 AND MATCH(albums.album) AGAINST('\"spaz ass\"' IN BOOLEAN MODE) GROUP BY albums.alid ORDER BY artists.artist, albums.album However as long as I do not use any of the BOOLEAN methods on the artists.artist field (still with IN BOOLEAN MODE in the query), the query works fine. I have rebuilt the FULLTEXT index and still have the same result. Here is a dump of the tables: # # Table structure for table `albums` # CREATE TABLE `albums` ( `alid` int(10) unsigned NOT NULL auto_increment, `aid` int(10) unsigned NOT NULL default '0', `gid` int(10) unsigned NOT NULL default '0', `discid` varchar(8) NOT NULL default '', `album` varchar(80) NOT NULL default '', `year` smallint(4) unsigned NOT NULL default '0', `tracks` tinyint(2) unsigned NOT NULL default '0', `comments` varchar(255) NOT NULL default '', `disclength` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`alid`), KEY `aid` (`aid`), KEY `gid` (`gid`), KEY `discid` (`discid`), FULLTEXT KEY `album` (`album`) ) TYPE=MyISAM; # -------------------------------------------------------- # # Table structure for table `artists` # CREATE TABLE `artists` ( `aid` int(10) unsigned NOT NULL auto_increment, `artist` varchar(80) NOT NULL default '', PRIMARY KEY (`aid`), FULLTEXT KEY `artist` (`artist`) ) TYPE=MyISAM; MySQL was compiled using the following script and strip(ped): #!/bin/sh CFLAGS="-O3 -mcpu=pentium3 -march=pentium3 -fomit-frame-pointer" \ CXX=gcc \ CXXFLAGS="-O3 -mcpu=pentium3 -march=pentium3 -felide-constructors -fno-exceptions -fno-rtti -fomit-frame-pointer" \ ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --without-debug --enable-assembler \ --with-vio --with-openssl --without-innodb --without-isam --enable-thread-safe-client \ --enable-local-infile How to repeat: See Description