By usera on

I have a hosting account with a database already created with a password. I have a few other programs that is already using this database so I need to be able to organize the database field with a prefix.

I searched thru the forums and found no easy way of doing that. The only thing I've read is about it being available in a later release of the software...

Can someone tell me if I can do it now with 4.4.2 or must I wait for a newer version of the software? I know mamba has an advance install script that can change the database prefix on the fly. Does such a script exist for drupal or might be available in the new release?

Thanks for any help.

Comments

bart jansens’s picture

bart jansens commented

It is available in 4.4.2 but there is no install script yet (there might be one in the next release but i'm not sure, haven't heard about it in a while).

To use a database prefix you basically have to do two things,
1) set the $db_prefix variable in conf.php
2) edit database.mysql and add the database prefix to every table name. This can be done with find & replace (my $db_prefix is set to "drup_"):
find: "CREATE TABLE" replace by: "CREATE TABLE drup_"
find: "INSERT INTO" replace by: "INSERT INTO drup_"
find: "REPLACE " replace by: "REPLACE drup_"

Of course, replacing drup_ by the prefix you want.

usera’s picture

usera commented

that was what i was looking for. Hopefully there will be one available that you can use to change the prefix on the fly on an existing install.

Anonymous’s picture

Anonymous (not verified) commented

Works great!

ahales’s picture

ahales commented

My SQL dump of the database.mysql has the table names in single quotes hence:

find: "CREATE TABLE '" replace by: "CREATE TABLE 'drup_"
find: "INSERT INTO'" replace by: "INSERT INTO 'drup_"
find: "REPLACE '" replace by: "REPLACE 'drup_"

will work

axel@drupal.ru’s picture

axel@drupal.ru commented 
#!/bin/sh
#
# Simple script for setting table prefixes in SQL-dump

if [ $# != 2 ]; then
        cat >&2 << EOH
This is Drupal database prefixer.

Usage:
  $0 prefix original_db.sql >prefixed_db.sql

- all tables will prefixed with 'prefix'
EOH

exit 1;
fi

PRFX=$1;
sed "s/^CREATE TABLE /CREATE TABLE $PRFX/;
     s/^INSERT INTO /INSERT INTO $PRFX/;
     s/^REPLACE /REPLACE $PRFX/;
     s/^ALTER TABLE /ALTER TABLE $PRFX/" $2

--
Axel,
Russian Drupal Community

drakeguan’s picture

drakeguan commented

complementarity: sometimes, a module's database schema uses lower case. therefore, you can just use this modified one to overcome it.

#!/bin/sh
#
# Simple script for setting table prefixes in SQL-dump
if [ $# != 2 ]; then
        cat >&2 << EOH
This is Drupal database prefixer.
Usage:
  $0 prefix original_db.sql >prefixed_db.sql
- all tables will prefixed with 'prefix'
EOH
exit 1;
fi
PRFX=$1;
sed "s/^CREATE TABLE /CREATE TABLE $PRFX/i;
     s/^INSERT INTO /INSERT INTO $PRFX/i;
     s/^REPLACE /REPLACE $PRFX/i;
     s/^ALTER TABLE /ALTER TABLE $PRFX/i" $2
shopgirl_11’s picture

shopgirl_11 commented

Hi there,

Can anyone here help me out on db prefixing? I have searched few threads regarding this but the instructions were a bit complicated to me as i'm only a drupal newbie.

Please help me out on the basic settings or codes that i need to change on settings.php to get this feature working.

Just a background of what i intend to do on my website -- I am running windows server '08 with IIS7, i have one site named www.stage1.com and created an alias for my second site (using multi site) named www.stage2.com. I wanted both of these websites to share the same database except sharing the themes. I wanted to have my websites different themes for individuality purposes.

Can anyone please let me know how to start with this?

Thank you.

mradcliffe’s picture

mradcliffe
he/him
Primary language English
commented

When you're running through the Drupal installation (Run Script and table prefix), you can set a table prefix for that particular Drupal instance. This is what this thread is about.

Are you sure you're thinking about prefixes and not a Multi-Site installation?

shopgirl_11’s picture

shopgirl_11 commented

Hi,

Thanks a lot for your immediate response, appreciate it.

I got multisite working already, what i want to do now is to have both sites share a single database only i do not want to share the themes (so I could grant each of my site a different look)... from my understanding, db prefixing could fix this. I'm sorry, i am not sure how to further explain this but i hope you are getting my point. Honestly, I am not 100% familiar with MySQL and so as tweaking the source codes so I hope you can bear with me.

jh3’s picture

jh3 commented

I know this post is super old, but I ended up here after searching for a solution to this problem.

Here's something I did real quick that allows you to take your current, non-prefixed db and add a prefix to all tables. Not fully automated, but it does the tedious stuff.

http://gist.github.com/483135

lee20’s picture

lee20 commented

@ejh3 - Your script also prefixes the column names within each table. I used this script instead: http://drupal.org/node/403742

hapydoyzer’s picture

hapydoyzer commented

If you use scripts posted above or rename tables by hand in Drupal 5 then there is one thing you need to do in addition.
Table "sequences" holds table names with prefixes. You need to rename values in this table too.
If you don`t, then you got a BIG security hole: e.g. the first registered user (after table renaming) will got Administrative rights (uid 1).

Drupal versions >5 doesn`t use sequences.