Bug Database error on search

[ricktas]

If extra spaces or * characters are in the search it fails.

Database error in vBulletin 4.2.2:

Invalid SQL:

        SELECT
                COUNT(imageid) AS imagecount
        FROM dbtech_gallery_images
        WHERE
                instanceid      = 1 &&
                approved                = 1 &&
                deleted         = 0 &&
        MATCH
                (title, text, tagging, username)
        AGAINST
                ('+jewellery* +jewel* +crystal* +fashion* +* +abstract* ' IN BOOLEAN MODE);

MySQL Error   : syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*'
Error Number  : 1064
Request Date  : Monday, May 5th 2014 @ 05:04:21 PM
Error Date    : Monday, May 5th 2014 @ 05:04:21 PM

 

[Fillip H.]

I'm unable to replicate this issue. What version of MySQL are you running?

 

[ricktas]

MySQL 5.6.16, InnoDB tables as they now support full text

 

[ricktas]

Ok, If I make the table MyISAM the problem goes away... looks like a bug in MySQL InnoDB full text search

 

[ricktas]

Reported to MySQL MySQL Bugs: #72605: Syntax error fulltext search InnoDB tables 5.6.16

 

[Fillip H.]

InnoDB tables are only better than MyISAM if there's a very high concurrency (i.e. a table that's frequently used for both insertion and selection, i.e. the post table), as it supports row-level locking as opposed to table-level locking.

For low activity sites or sites that use a master/slave replication setup, the performance detriment of InnoDB is in most cases not worth it.

 

[ricktas]

InnoDB tables are only better than MyISAM if there's a very high concurrency (i.e. a table that's frequently used for both insertion and selection, i.e. the post table), as it supports row-level locking as opposed to table-level locking.

For low activity sites or sites that use a master/slave replication setup, the performance detriment of InnoDB is in most cases not worth it.

Agreed for earlier versions of MySQL.
As of MySQL 5.5 and moreso 5.6 InnoDB performance has increased immensely (200% - 1500%) to the point that InnoDB is now the default engine unless specifically set otherwise. See: Oracle set to launch MySQL 5.5 GA release - Computerworld
Oracle/MySQL are pushing InnoDB as the engine of choice.

Anyway I've left it as MySQL for now and await a patch from Oracle.

FYI I added a full example of the MySQL bug as a script fragment:
MySQL Bugs: #72605: Syntax error fulltext search InnoDB tables 5.6.16

 

[ricktas]

FWIW the "+*" pat of the query is the issue and it is also pointless

 

[ricktas]

In the following components... These fix the SQL issue

./dbtech/gallery/modules/gallery/actions/search.php

for($n=0;$n < count($query_array);$n++)
{
	$element = $vbulletin->db->escape_string($query_array[$n]);
	if ($element != "")
	{
		$boolean_query .= '+' . $element . '* ';
	}
}

./dbtech/gallery/modules/gallery/functions/class_module.php

			for($i=0; $i < count($query_array); $i++)
			{
				$element = self::$vbulletin->db->escape_string($query_array[$i]);
				if ($element != "")
				{
					$boolean_query .= '+' . $element . '* ';
				}
			}

			for($n=0;$n < count($criteria_array);$n++)
			{
				$element = self::$vbulletin->db->escape_string($criteria_array[$n]);
				if ($element != "")
				{
					$boolean_query .= '+' . $element . '* ';
				}
			}

			for($n=0;$n < count($criteria_array);$n++)
			{
				$element = $criteria_array[$n];
				if ($element != "")
				{
					$boolean_query .= '+' . $element . '* ';
				}
			}

 

[Fillip H.]

This should now be patched in, thank you

 

[ricktas]

FYI The MySQL has been updated to reflect the +* issue MySQL :: MySQL 5.6 Reference Manual :: 12.9.2 Boolean Full-Text Searches