CODEWITHSUNDEEP
databasedrupalprefix
Redox
Redox

Reputation: 993

Drupal MySQL error after prefix change

I setup a drupal website, which first had a prefix but I dumped an old database export in the database that does not have a prefix. So I removed it from my settings.php, as per below:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'db_name_here',    //hidden
      'username' => 'username_here',   //hidden
      'password' => '*******',         //hidden
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',                  //prefix used to be here 'example_'
    ),
  ),
);

Problem however is that when I connect to the database, it still seems to look for the table with prefix. What did I do wrong?

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 
'db_name_here.example_semaphore' doesn't exist: SELECT expire, value FROM
{semaphore} WHERE name = :name; Array ( [:name] => variable_init )
in lock_may_be_available() (line 167 of /home/public_html/demo/site/includes/lock.inc).

Upvotes: 0

Views: 351

Answers (1)

Vijay Bansod
Vijay Bansod

Reputation: 11

When you install Drupal with database prefix, all your database tables will also get that prefix so to remove prefix you need to rename all database tables.

Following script will help you to do this.

<?php
// current table prefix to be removed
$prefix = "drup_";
// echo generated statments rather then run them
$pretend = FALSE;
/////////////////////////////////////////////////////////////////////
$table_list = db_query("SHOW TABLES");
$prefix = strtolower($prefix);
foreach ($table_list as $r) {
  $r = (array)$r;
  $table_old = strtolower(current($r));
  // check for $prefix on this table
  if(substr($table_old,0,strlen($prefix)) == $prefix) {
    $table_new = substr($table_old, strlen($prefix));
    // first drop $table_new incase it already exists
    $clean_sql = "DROP TABLE IF EXISTS {$table_new}";
    // rename prefix table to standard/nonprefix name
    $rename_sql = "RENAME TABLE {$table_old} TO {$table_new}";
    if($pretend) {
      print $clean_sql."\n";
      print $rename_sql."\n";
    } else {
      if(!db_query($clean_sql)) {
        die("Aborting - $clean_sql \n");
      }
      if(!db_query($rename_sql)) {
        die("Aborting - $rename_sql \n");
      }
    }
  } else {
    print "$table_old skipped \n";
  }
}
print "\nDone \n\n";
?>

Upvotes: 1

Related Questions