I have inherited a site that was built in India. Looking at the database, I see that they used a database prefix. No, I have no idea why.
There is a little bit of data in the non-prefixed tables, although what I have looked at seems to be garbage. For example, the non-prefixed node table is empty.
Does anyone know an easy way to get rid of the prefix? Are there any dangers in the straight-forward manual process?
Nancy
Nancy, not a big deal. Besides using some clever regex, you could just download the DB, open in a "robust" text editor (BBEdit?), and search/replace away those prefixes... then upload to replace.
Change your settings.php DB connection to exclude the prefix. After that, it's best to use Drush to rebuild the registry, clear cache, etc.
Kevin
On Fri, Nov 8, 2013 at 12:39 PM, Nancy Wichmann nan_wich@bellsouth.netwrote:
I have inherited a site that was built in India. Looking at the database, I see that they used a database prefix. No, I have no idea why.
There is a little bit of data in the non-prefixed tables, although what I have looked at seems to be garbage. For example, the non-prefixed node table is empty.
Does anyone know an easy way to get rid of the prefix? Are there any dangers in the straight-forward manual process?
*Nancy*
-- [ Drupal support list | http://lists.drupal.org/ ]
I would warn against the generic search and replace. You could end up getting a string in one of the values that is the same as the prefix. The best route is through a couple mysql commands:
SELECT CONCAT('ALTER TABLE ',db,'.',old_table,' RENAME ',db,'.',new_table,';') FROM ( SELECT table_schema db, table_name old_table, substr(table_name,4) new_table FROM information_schema.tables WHERE table_schema='{database_name}' AND SUBSTR(table_name,1,3)='{prefix}' ) A
Change the {database_name} and {prefix} to the appropriate values. This is set for a 3 character prefix (ie: d6_), so if it's a different length then you need to adjust the substrs too. When you run that it will generate a list of commands to rename each table. Going from the command line, you can just pipe it to a text file, or export the results in something like PHPMyAdmin. After that just run the outputted file/commands and it will do all the renaming for you. It is best to look at the commands it generates first just to make sure it's renaming everything right (make sure you have your substr lengths set right).
Jamie Holly http://hollyit.net
On 11/8/2013 4:00 PM, Kevin Davison wrote:
Nancy, not a big deal. Besides using some clever regex, you could just download the DB, open in a "robust" text editor (BBEdit?), and search/replace away those prefixes... then upload to replace.
Change your settings.php DB connection to exclude the prefix. After that, it's best to use Drush to rebuild the registry, clear cache, etc.
Kevin
On Fri, Nov 8, 2013 at 12:39 PM, Nancy Wichmann <nan_wich@bellsouth.net mailto:nan_wich@bellsouth.net> wrote:
I have inherited a site that was built in India. Looking at the database, I see that they used a database prefix. No, I have no idea why. There is a little bit of data in the non-prefixed tables, although what I have looked at seems to be garbage. For example, the non-prefixed node table is empty. Does anyone know an easy way to get rid of the prefix? Are there any dangers in the straight-forward manual process? /*Nancy */ -- [ Drupal support list | http://lists.drupal.org/ ]
Jamie, this is great! You're exactly right about this warning, and my assumption there was that the prefix would be entirely unique. Much better solution to use the mysql commands. -K
On Fri, Nov 8, 2013 at 3:40 PM, Jamie Holly hovercrafter@earthlink.netwrote:
I would warn against the generic search and replace. You could end up getting a string in one of the values that is the same as the prefix. The best route is through a couple mysql commands:
SELECT CONCAT('ALTER TABLE ',db,'.',old_table,' RENAME ',db,'.',new_table,';') FROM ( SELECT table_schema db, table_name old_table, substr(table_name,4) new_table FROM information_schema.tables WHERE table_schema='{database_name}' AND SUBSTR(table_name,1,3)='{prefix}' ) A
Change the {database_name} and {prefix} to the appropriate values. This is set for a 3 character prefix (ie: d6_), so if it's a different length then you need to adjust the substrs too. When you run that it will generate a list of commands to rename each table. Going from the command line, you can just pipe it to a text file, or export the results in something like PHPMyAdmin. After that just run the outputted file/commands and it will do all the renaming for you. It is best to look at the commands it generates first just to make sure it's renaming everything right (make sure you have your substr lengths set right).
Jamie Hollyhttp://hollyit.net
On 11/8/2013 4:00 PM, Kevin Davison wrote:
Nancy, not a big deal. Besides using some clever regex, you could just download the DB, open in a "robust" text editor (BBEdit?), and search/replace away those prefixes... then upload to replace.
Change your settings.php DB connection to exclude the prefix. After that, it's best to use Drush to rebuild the registry, clear cache, etc.
Kevin
On Fri, Nov 8, 2013 at 12:39 PM, Nancy Wichmann nan_wich@bellsouth.netwrote:
I have inherited a site that was built in India. Looking at the database, I see that they used a database prefix. No, I have no idea why.
There is a little bit of data in the non-prefixed tables, although what I have looked at seems to be garbage. For example, the non-prefixed node table is empty.
Does anyone know an easy way to get rid of the prefix? Are there any dangers in the straight-forward manual process?
*Nancy *
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
Thanks,Jamie. Nancy
From: Jamie Holly hovercrafter@earthlink.net To: support@drupal.org Sent: Friday, November 8, 2013 6:40 PM Subject: Re: [support] Database Prefix
I would warn against the generic search and replace. You could end up getting a string in one of the values that is the same as the prefix. The best route is through a couple mysql commands:
SELECT CONCAT('ALTER TABLE ',db,'.',old_table,' RENAME
',db,'.',new_table,';') FROM
( SELECT table_schema db, table_name old_table, substr(table_name,4) new_table FROM information_schema.tables WHERE table_schema='{database_name}' AND SUBSTR(table_name,1,3)='{prefix}' ) A
Change the {database_name} and {prefix} to the appropriate values.
This is set for a 3 character prefix (ie: d6_), so if it's a different length then you need to adjust the substrs too. When you run that it will generate a list of commands to rename each table. Going from the command line, you can just pipe it to a text file, or export the results in something like PHPMyAdmin. After that just run the outputted file/commands and it will do all the renaming for you. It is best to look at the commands it generates first just to make sure it's renaming everything right (make sure you have your substr lengths set right).
Jamie Holly
http://hollyit.net On 11/8/2013 4:00 PM, Kevin Davison wrote:
Nancy, not a big deal. Besides using some clever regex, you could just download the DB, open in a "robust" text editor (BBEdit?), and search/replace away those prefixes... then upload to replace.
Change your settings.php DB connection to exclude the prefix. After that, it's best to use Drush to rebuild the registry, clear cache, etc.
Kevin
On Fri, Nov 8, 2013 at 12:39 PM, Nancy Wichmann nan_wich@bellsouth.net wrote:
I have inherited a site that was built in India. Looking at the database, I see that they used a database prefix. No, I have no idea why.
There is a little bit of data in the non-prefixed tables, although what I have looked at seems to be garbage. For example, the non-prefixed node table is empty.
Does anyone know an easy way to get rid of the prefix? Are there any dangers in the straight-forward manual process? Nancy
-- [ Drupal support list | http://lists.drupal.org/
]
-- [ Drupal support list | http://lists.drupal.org/ ]
So long as any custom modules used the proper {} wrap of table names in their queries, there would be no problem changing them. Just be sure to change the prefix in settings.php so you aren't pulling hair out wondering what went wrong (I just say that because I can see myself doing it!).
Jamie Holly http://hollyit.net
On 11/8/2013 3:39 PM, Nancy Wichmann wrote:
I have inherited a site that was built in India. Looking at the database, I see that they used a database prefix. No, I have no idea why.
There is a little bit of data in the non-prefixed tables, although what I have looked at seems to be garbage. For example, the non-prefixed node table is empty.
Does anyone know an easy way to get rid of the prefix? Are there any dangers in the straight-forward manual process? /*Nancy */
-
Jamie Holly -
Kevin Davison -
Nancy Wichmann