By Mr. Sharkey on

Code changes to includes/database.mysql.inc in v. 5.20 causes my up-until-now stable installation of Drupal to return the following when attempting to use the Search function:

user warning: You have an error in your SQL syntax near 'Engine=HEAP SELECT i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) ' at line 1 query: CREATE TEMPORARY TABLE temp_search_sids Engine=HEAP SELECT i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) AS matches FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid WHERE n.status = 1 AND (i.word = 'search') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 in /var/www/html/user/public_html/includes/database.mysql.inc on line 174.

user warning: Table 'user.temp_search_sids' doesn't exist query: SELECT MAX(relevance) FROM temp_search_sids in /var/www/html/user/public_html/includes/database.mysql.inc on line 174.

Apparently the added code on line 363 of includes/database.mysql.inc "Engine=HEAP SELECT" is the cause of the problem. For the time being I have fallen back to the 5.19 version of this file to make search functional once again.

For the record:

MySQL version: 3.23.58
PHP version: 4.3.8

Please don't tell me that these need to be more recent versions, if that was possible on this server, I'd already be running 6.x instead of 5.x

Thanks for any insights. I find the Drupal.org site very confusing and difficult to navigate in search of answers. If this needs to be moved or redirected, I understand.

⋅ Categories: Drupal 5.x

Comments

drupal-king’s picture

drupal-king commented

I am on 5.19 and the search does not work. I have tried everything but can't seem to get it to work. I will hold off on 5.20 given the above comments - thanks for posting this.

on 5.19

- i have enabled search
- provided permissions
- checked the search_index table - i have 10000+ rows of content
- checked the other two search tables - and they too have records
- run cron manually and it completes successfully all the time

not sure what else to do - i saw someone mentioned a patch but I can't find it anywhere -
can anyone help with this - please

Mr. Sharkey’s picture

Mr. Sharkey commented

Apparently, "Engine=HEAP" is not supported in versions of MySQL prior to 4.1.

Changing the code on line 363 to read "TYPE=HEAP" has corrected the problem and my search is working again. Complete code correction follows:

As contained in the 5.20 release:
$query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' Engine=HEAP SELECT', db_prefix_tables($query));

Corrected to:
$query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' TYPE=HEAP SELECT', db_prefix_tables($query));

Hopefully, this issue will be brought to the attention of the developers before the next update release...

drupal-king’s picture

drupal-king commented

will this help with the fact that my hosting company will not grant permissions to create temporary tables?

what hosting company do you use?