How do you add a prefix to an existing drupal database for single site use?

Question

I have a drupal 7 site running on a webserver, it is currently using a database that was created with out a prefix.

I am attempting to restore that database to a new drupal instance from a shared hosting provider. I am using the Backup and Restore Module to move the database. One thing I have no apparent control of is that their "quick install script" creates a new drupal database with a "drupal_" prefix.

Do I need to convert my existing database to have that prefix, because as it seems now my migrated changes are not even being read since it probably has both sites data sitting side by side, and the database information I want to be reading entries from don't have the prefix? Is that reasoning correct? If so how do I convert my Drupal database to the same one but now with the necessary prefix applied throughout?

Answer 1

If you have phpMyAdmin the simplest way is to:

• go to the "Structure" tab (where there is the list of tables).
• select all the tables (with the "Check All" checkbox at the end of the list)
• in the dropdown list close to the "Check All" select the action "Add table prefix" (or "Replace table prefix" if that is the case)
• insert the prefix, press the button and let it do all the work.

After that remember to specify the prefix in the site\default\settings.php

array (
  'database' => 'sample_drupal_db',
  'username' => 'sample_drupal_usr',
  'password' => 'sample_drupal_pwd',
  'host' => 'localhost',
  'port' => '',
  'driver' => 'mysql',
  'prefix' => 'prefix_', // <<== prefix here
),

Answer 2

Your reasoning sounds correct to me. You could try to edit your database dump, adding the prefix to all table names, and run the import again. This will surely work but it's also easy to make a mistake, so you may have some trial-and-error before you succeed.

Another option would be to go into the new database with a tool like phpMyAdmin, drop the prefixed tables and alter the non-prefixed ones adding the prefix.

A third method (which may or may not collide with the automatic installer you used) is to edit the settings.php and remove the prefix from the database array. While this is probably the fastest solution for now, I'm not sure if it will last in the ling run, for instance when the automatic installer does an upgrade.

Answer 3

If it is just the database with the 'drupal_' prefix, you do not need to create a table prefix.

The database dump created by the Backup and Restore module does not include any database definition.

If you need it, you can extract the mysqldump file and add the following to the top of the file:

CREATE DATABASE IF NOT EXISTS 'drupal_mydatabase';
USE 'drupal_mydatabase';

Otherwise, just change your database credentials in settings.php, and use the Backup and Restore module to reload your database.

Answer 4

The script below was posted on http://ruleant.blogspot.com/2009/03/rename-multiple-tables-in-mysql.html# by Dieter Adriaenssens .

This PHP script automates the renaming of multiple tables in a MySQL database. It lists all tables in a MySQL database, which contain a defined string pattern. The script creates and executes a series of SQL statements, which rename the table by replacing the search pattern in the original table name with another pattern in the new table name.

This script can easily be modified to rename multiple databases, or when stripping the original pattern (f.e. a prefix) and adding a new pattern (f.e. a suffix) is needed.

<?php  
$db_server = "localhost";    // hostname MySQL server  
$db_username = "username";   // username MySQL server  
$db_password = "password";   // password MySQL server  
$db_name = "database";       // database name  

$pattern = "pattern_";          // search string for example: "drpl_" 
$new_pattern = "new_pattern_";  // replacement string.
                            // can be empty. For example "" for no db prefix     

// login to MySQL server  
$link = mysql_connect( $db_server, $db_username, $db_password);  

if (!$link)  
{  
  die('Could not connect: ' . mysql_error());  
}  

// list all tables in the database containing the search pattern  
$sql = "SHOW TABLES FROM `" . $db_name . "`";  
$sql .= " LIKE '%" . $pattern . "%'";  

$result = mysql_query ( $sql, $link );  
if (!$result)  
{  
  die("Invalid query: " . mysql_error( $link ));  
}  

$renamed = 0;  
$failed = 0;  

while ( $row = mysql_fetch_array ($result) )  
{  
  // rename every table by replacing the search pattern   
  // with a new pattern  
  $table_name = $row[0];  
  $new_table_name = str_replace ( $pattern, $new_pattern, $table_name);  

  $sql = "RENAME TABLE `" . $db_name . "`.`" . $table_name . "`";  
  $sql .= " TO `" . $db_name . "`.`" . $new_table_name . "`";  

  $result_rename = mysql_query ( $sql, $link );  
  if ($result_rename)  
  {  
    echo "Table `" . $table_name . "` renamed to :`";  
    echo $new_table_name . "`.\n";  
    $renamed++;  
  }  
  else  
  {  
    // notify when the renaming failed and show reason why  
    echo "Renaming of table `" . $table_name . "` has failed: ";  
    echo mysql_error( $link ) . "\n";  
    $failed++;  
  }  
}  

echo $renamed . " tables were renamed, " . $failed . " failed.\n";  

// close connection to MySQL server  
mysql_close( $link );  
?> 

After running the script, you'll have to edit your settings.php to rename the db_prefix setting. After the script is run, you may get a pdo error: "PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table '[database].semaphore' doesn't exist ... in lock_may_be_available()". If this happens: 1) rename your settings.php to something like settings-old.php; 2) copy the default.settings.php to the sites/default folder and rename it to settings.php. This will cause the install script to run. When prompted for your database settings, enter the name of your existing database. The install script will inform you that the database already exists and offer to take you to your site.