Discussion Forum

tsamant

Member

From: Chicago

Registered: May 14, 2015

Posts: 52

Renaming tables

Hello,

Probably unusual, but: I am thinking of swapping bigtree_pages with another "shadow" table.

The context is this. There are extensive editorial changes over many pages that all have to publish at the same time, "atomically" so to say. They have to be ready and viewable beforehand (i.e. published on a staging server). There are a few other constraints as well.

This leads me to think that the simplest way to publish all the staged edits would be to populate a staged_bigtree_pages table in production, and rename it to bigtree_pages at the right time. (The schema will be the same.)

I am not sure this will work. Are there any caching gotchas, would I have to restart the server, etc. How realistic is this plan?

Best,
Tushar

timbuckingham

Administrator

From: Baltimore, MD

Registered: April 2, 2012

Posts: 978

Re: Renaming tables

If you can do a content freeze on the live site, I would think that would be the better option -- copy live to stage, do all your content edits and approvals there, then migrate the bigtree_pages table from stage -> live to publish all at once. If there's media assets being uploaded as well you'll want to make sure bigtree_resources and bigtree_caches is moved over as well.

tsamant

Member

From: Chicago

Registered: May 14, 2015

Posts: 52

Re: Renaming tables

Okay, we have now frozen content on live, copied live to dev, and begun publishing on dev.

Could you clarify the "migrate the bigtree_pages table from stage -> live" part? What is a safe way to do that?

I was wondering if importing the entire stage bigtree_pages to the live db (under a different name), then renaming it, would be okay. But is there a better way?

Thanks for all your help,
Tushar

timbuckingham

Administrator

From: Baltimore, MD

Registered: April 2, 2012

Posts: 978

Re: Renaming tables

You should be able to use a tool like MySQL Workbench or Sequel Pro to just create a database dump of the bigtree_pages table that can be cleanly imported overtop of the bigtree_pages table on live. There'll be a momentary hitch while the data is imported but there are no foreign key constraints in bigtree_pages so it should be a clean import.

You're right that renaming the tables would likely be quicker if you're worried about the slight downtime while the new table imports!

tsamant

Member

From: Chicago

Registered: May 14, 2015

Posts: 52

Re: Renaming tables

Foreign keys, changed parent ids, etc was precisely what I was in the dark about, but it looks like those are not a concern. (The tree structure of pages is also changing.)

I will go for the easy way—a Sequel Pro import is fine by me. But it's also good to know that table renames don't have any fundamental problems.

Thanks Tim!
Tushar