Search error

[Swampcritter]

As administrator, I try to do a simple search and get this error:

Code Select Expand


Database Error
DROP command denied to user 'forums'@'localhost' for table 'smf_tmp_log_search_topics'
File: /web/www.domain.com/htdocs/Sources/Search.php
Line: 1010

Note: It appears that your database may require an upgrade. Your forum's
files are currently at version SMF 1.1.3, while your database is at version
1.1.2. The above error might possibly go away if you execute the latest
version of upgrade.php.


Our development environment allows the DROP table permission for the MySQL database, but our production environment doesn't (i.e. security issues, etc). Today, we enabled the drop permission for production for a short period of time and the search module worked, however once the DROP permission was removed from the MySQL permission grants, the above message started to appear once again.

What do we need to do to allow for the search module to work but w/o having drop permissions in the MySQL database?

FYI: This is the first time the search module has been used. The customer we are migrating this code for never tested/utilized it in our development environment.

-- Michael

[jerm]

Did you try doing this first?

Note: It appears that your database may require an upgrade. Your forum's
files are currently at version SMF 1.1.3, while your database is at version
1.1.2. The above error might possibly go away if you execute the latest
version of upgrade.php.

[Swampcritter]

No, because the upgrade looks like it will overwrite a series of hard files that have been altered for Omniture marketing script needs.

Is it possible to get a list of what files and what MySQL tables are going to be altered prior to upgrade so we can give the customer's PHP developers a heads-up?

-- M

[Swampcritter]

Okay, I found the changelog.txt file in the upgrade data file.

Here is the steps I took:

1) Dumped MySQL database (mdw_forums_site) from production to developement.
2) Uploaded SQL code into development.
3) Uploaded 1.1.3 upgrade to development.
4) Untarred the upgrade package under /web/dev.forums.site.com/htdocs
5) Ran hxxp:dev.forums.site.com/upgrade.php [nonactive] file as stated in instructions.
6) Script made changes to MySQL database schema and several PHP files.

Code Select Expand


Making SMF MySQL strict compatible...
    Adding log_topics index. done.
Successful.
Adding more room for the buddy list
    Updating the members table ... done.
Successful.
Change some column types to accomodate more messages.
    Updating the boards table ... done.
    Updating the log_boards table ... done.
    Updating the log_mark_read table ... done.
    Updating the log_search_messages table ... done.
    Updating the log_search_words table ... done.
    Updating the log_topics table ... done.
    Updating the messages table ... done.
Successful.
Final clean up...
    Sorting the boards... done.
    Removing upgrade loop protection... done.
Successful.
   Updating version number... done.
Taking the forum out of maintenance mode... Successful.


7) Fixed the Settings.php to see the correct data path for themes
8) Tested search module while still having MySQL 'drop' permissions. Search command works.
9) Had Core team remove MySQL 'drop' permission from site account.
10) Tested search module again and encountered MySQL 'drop' permission error.

Error encountered:

Code Select Expand


Database Error
DROP command denied to user 'site'@'localhost' for table 'smf_tmp_log_search_topics'
File: /web/dev.forums.site.com/htdocs/Sources/Search.php
Line: 1010


After reading the code, it seems that the Search.php code requires the DROP permission, even for temporary tables. Is there a way this can be modified to use a data file directory to make the temp log file?

-- Michael

[Swampcritter]

I talked to our MySQL/Core team and the problem is that the SMF application isn't utilizing a temporary table correctly.

Code Select Expand


db_query("
  DROP TABLE IF EXISTS {$db_prefix}tmp_log_search_topics", __FILE__, __LINE__);
  $createTemporary = db_query("
  CREATE TEMPORARY TABLE {$db_prefix}tmp_log_search_topics (
    ID_TOPIC mediumint(8) unsigned NOT NULL default '0',
    PRIMARY KEY (ID_TOPIC)
    ) TYPE=HEAP", false, false) !== false;


Our MySQL administrator said the better option would be to remove the 'drop table if exists' portion, utilize 'CREATE TEMPORARY TABLE IF NOT EXISTS {$db_prefix}tmp_log_search_topics', then use 'TRUNCATE TABLE {$db_prefix}tmp_log_search_topics' or 'DELETE FROM {$db_prefix}tmp_log_search_topics' to empty the temporary table.

From the MySQL 5.0 Reference Manual:"You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed/disconnects."

-- M

[Swampcritter]

For those that want to use SMF in a production/enterprise environment, here is the fix for utilizing the Search module, using temporary tables. Under the Sources directory, modify the Search.php with the following changes:

The code needs to be modified in two places:

Code Select Expand


Under the -- if (empty($search_params['topic']))
Under the -- if (!empty($modSettings['search_index']))


Look for the following code snippets...

Code Select Expand


    db_query("
       DROP TABLE IF EXISTS {$db_prefix}tmp_log_search_messages", __FILE__, __LINE__);

$createTemporary = db_query("
CREATE TEMPORARY TABLE {$db_prefix}tmp_log_search_messages (
       ID_MSG int(10) unsigned NOT NULL default '0',
       PRIMARY KEY (ID_MSG)
       ) TYPE=HEAP", false, false) !== false;


Replace with fix...

Code Select Expand


//    db_query("
//       DROP TABLE IF EXISTS {$db_prefix}tmp_log_search_messages", __FILE__, __LINE__);

$createTemporary = db_query("
CREATE TEMPORARY TABLE IF NOT EXISTS {$db_prefix}tmp_log_search_messages (
         ID_MSG int(10) unsigned NOT NULL default '0',
         PRIMARY KEY (ID_MSG)
         ) TYPE=HEAP", false, false) !== false;


Why remove the 'DROP' permission? The DROP permission allows anyone with access to the user name and password (plain text in the php files) to drop entire tables or schemas. Anyone who gains access to the PHP files (through PHP, application security holes, SQL injections, malicious coworker, etc) can wreck massive damage with minimal work.

The DROP permission should only be needed when an upgrade or update is needed. After that point, it should be removed from the grants list for the user's id.

-- Michael