How to rename table names after installation#

Kind of obvious but may help some people as I couldn't find anything in the manager interface to do this. Long after installation I discovered I wanted multiple modx sites to share the same database. The obvious way is to give each site a unique prefix for the table names, I chose to use the company name. Trouble is the first one had prefix 'modx_' and I wanted to change it for tidyness.

So my database name was modx and the database owner was modx, using the mysql tee command I listed all the tables to a text file.

mysql -u modx -p modx

give password

tee tempfile;

show tables;

\q

I edited tempfile to remove noise characters and create a sql script to rename the tables

my approach was to put a procedure at the top like

foo() {

echo RENAME OLDPFX_$1 TO NEWPFX_$1 ';'

}

then globally replace all occurrences of OLDPFX_ to 'foo '
exit the editor and run the script saving the output

sh tempfile >rename.sql

then use mysql to do the renames

mysql -u modx -p modx

\. rename.sql

\q

Finally edit manager/includes/config.inc.php to change the table prefix to NEWPFX_

Ed

Very cool Ed, and thanks!

Just a quick follow up on this. My client's hosting service does weekly backups and in the event of disaster for one site and no other backup we'd have to roll back to the previous snapshot and corresponding database. This is obviously much more troublesome if multiple clients are sharing a single mysql database because you'd want to restore one set of tables only and they would restore the whole database.

On reflection having multiple sites share a database is a poor idea unless e.g. you are constrained on the number of databases you can have. When planning database sharing, consider backups and restores.

Ed

My two tangential thouhgts:

1. Be careful about listing the "mysql" command the way you did: "mysql -u modx -p modx" note that this would mean that your password was actually preceded by a space. It took me a long time to figure that out. More correct would be "mysql -u modx -pmodx" (note the absence of a space).

2. Yes, use separate databases for separate purposes. The only reason the prefix option is available is for overly-restrictive hosts. It's just like using separate folders to contain separate files. Organize organize organize...

It's also important to note that if your SQL isn't running you'll need:

RENAME TABLE.. not just RENAME

great thanks
i need this information

Thanks a lot.
I searched for a long time ... And find here the answer in 5 minutes!

Great thanks..
It really helps...

central air conditioning and heating systems central heating