By charlie_ on

Greetings,
I just discovered why my search queries aren't returning anything. As admin, I get the
following entry when performing any search:

 
 
Access denied for user 'ADMIN_USER'@'localhost' to database 'DRUPAL_DATABASE' query: CREATE TEMPORARY TABLE temp_search_sids 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_WORD') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 in /path/to/drupal_install/includes/database.mysql.inc on line 172.

I confirmed, and reapplied the privs to the drupal database for the drupal database user. But
I still get the error. Can anyone suggest why this is happening?

Thank you in advance.

--Charlie


⋅ Categories: Drupal 5.x

Comments

charlie_’s picture

charlie_ commented

CREATE TEMPORARY TABLES is the problem.
When I setup Drupal, I *attempted* to use the following PHP:

 
USE mysql;

DROP DATABASE IF EXISTS DRUPAL_DATABASE_NAME_HERE;

REPLACE INTO user (host, user, password)
    VALUES (
        'localhost',
        'DRUPAL_ADMIN_NAME_HERE',
        PASSWORD('DRUPAL_ADMIN_PASSWORD_HERE')
);

REPLACE INTO db (host, db, user, select_priv, insert_priv, grant_priv, update_priv,
                 delete_priv, create_priv, drop_priv, index_priv, alter_priv, create_temporary_tables_priv, lock_tables_priv)
    VALUES (
        'localhost',
        'DCMS',
        'dadmin',
        'Y', 'Y', 'Y', 'Y',
	'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'
);

FLUSH PRIVILEGES;

CREATE DATABASE DRUPAL_DATABASE_NAME_HERE;

Problem was, MySQL barfed with an error concerning the create_temporary_tables_priv
statement. So I simply re-ran it omitting that statement. So clearly this presents a
problem with searching. Is there any way to add this priv *after* an install?
If so, how should I do it?

Thank you for all your time and consideration.

--Charlie

charlie_’s picture

charlie_ commented

Greetings,
Well, I have discovered that the syntax for CREATE TEMPORARY TABLES is incorrect.
The correct syntax (for the script I posted above) is

 
create_tmp_table_priv

not

 
create_temporary_tables_priv

as is suggested by the documentation.
Should I report this as a bug?

--Charlie