On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • 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<br />give password<br />tee tempfile;<br />show tables;<br />\q<br />

    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() {<br />echo RENAME OLDPFX_$1 TO NEWPFX_$1 ’;’<br />}<br />

    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<br />\. rename.sql<br />\q<br />

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

    Ed
    • Very cool Ed, and thanks! laugh
        Ryan Thrash, MODX Co-Founder
        Follow me on Twitter at @rthrash or catch my occasional unofficial thoughts at thrash.me
      • 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!
                • thank for sharing a useful trick.