Maintaining live (production) and development versions of large dynamic site

On a current project we're using SVN.

The trunk is the development site and we have a branch for the live version. Changes are merged to the branch when stable. We have a seperate database for each site.

A fair amount of content was added to the dev site before it was branched and went live (enough to populate each site section). At times I've needed some additonal test content - that is no problem as I can just create it without worrying about what is going over on the live site.

We have used vids, tids & nids as you describe, but these were all set in stone before the site went live.

subscribing. I see this as a real problem.

Example:

Let's say you want to add a new CCK node type that uses a field type already in play for just one other CCK node type. The CCK module will split that content field out into its own table in your db. How do you manage this sort of change across a dev and a production server?

-s

Not sure if it could work for you, but on my sites whenever we are developing new functionality/features/design we create a mirror of the site based on a snapshot - then set up a team (of users) that has access to it to help test.

The test install is nearly always on a new domain.

This gives us 100% stability on the production site (as it's untouched) and we get what we want, a safe testing platform.

We're dealing with the exact same issue. Has there been any progress or at least flashes of insight between now and the start of this thread?

I currently have a site with blogs and forums, dynamic features which I have just added. My development (dev) site is on my laptop, which is running XAMPP. When I make changes to my dev site, I export the entire database from the dev server using PHPMyadmin, then import the database into the live server using PHPMyadmin. I transfer any files necessary, like new module files, images, tweaks to theme files, etc..., from the root directory of the dev server to the live server. If I am unsure that I know all the neccesary files that need to be transfered, then I just drop all the drupal files from my dev root directory into my live root directory. This way I can make changes to the dev site and then dump them live when ready without working on the live site, and making it unstable while live.

Now that I am adding dynamic content I see three problems with my production system. Users, forum content, and blog content that are added by users to the live site while I am developing the dev site will be overwritten when I update the live site with my dev version.

I have thought that I could not export from dev and import into live the forum and user tables, thereby updating the site info other then forums and users. Would this work, or will I encounter problems with other tables that interact with these tables. For example, it seems as if the node table interacts with the forum table to coordinate nid and vid entries. Also, I don't know where blog info is stored in the database, and how to deal with that content.

Finally, I would like to introduce a staging site into my workflow. This would be served from a password protected subdomain on my live domain, and would allow me to test changes on my live server over the internet before moving them to the live primary domain site. My strategy for this was to change my database name by version, tweak my settings.php to dial into the newly named db, test it on the subdomain, and when ready move the newly named db and all drupal directory files to the live primary domain, thus replacing the old site. Again, this method would overwrite dynamic content generated on the live site during dev and staging development and testing.

Any advice or strategy sharing would be greatly appreciated.

Thanks in advance,

code

Have you guys tried looking at something like MySQL Sync from SpectralCore? http://www.spectralcore.com/syncdatabase/index.php It lets you do a diff between DBs and then select what you want to merge.

Here are some related threads:

http://www.dave-cohen.com/node/1779
http://drupal.org/node/181128
http://groups.drupal.org/node/786

The first details my personal approach.

This thread has inspired me to suggest a BOF about this very topic at DrupalCon. If you're interested please vote and/or comment here:
http://boston2008.drupalcon.org/session/updating-and-upgrading-live-sites

Thanks.

This saturday will be held a session about staging. France24.com team will explain there odd and even numbers solution.

That way we will catch up with BOF session at last Drupalcamp when we were not able to engage a dialogue across the ocean because the sound was not working.

More information on https://barcamp.pbwiki.com/DrupalCampParisStaging
Fell free to edit the page and tag the video.

We are considering around 3PM French time.

As there is time differences could you please tell me when at the earliest you would be available ? on the comment.

Subscribing...

Tecito.com
The growing Hispanic community in Canada.

subscribing

There are three problems:

1. The codebase - I use the CVS versions of core, modules and themes, and svn/rsync for my own stuff
2. The file system (as in everything in the drupal files directory) - I use drush and rsync
3. The database - Ah! Hell of a problem...

The France24/Dave Cohen solutions are (I discovered) actually quite old, but probably not well known to anyone except a DBA:

• 2003: INF: Managed Range Identities in Merge Replication
• 2005: The identity crisis in replication
• 2006: untitled OnLamp article

Warning: I'm still fiddling with this, so I'll just outline the basics, some of which are still hypothetical. I'm a programmer, not a DBA.
Taking the 'normal' approach (as in the approach most people seem to use in this thread):

1. Grab a snapshot of the production database - which I'll call ProdSnap, and transfer it to development/staging
2. Make all the necessary changes, add modules, remove modules, hack some code, whatever, then snapshot this database - which I'll call ProdMod
3. Stop the production server, and grab another snapshot- uhm, ProdNow

The snapshots are all pure SQL, as in the results of a mysqldump, in other words galumphing great big text files.

You can now merge the differences, either twice, using your version control software, or once using diff3.
There will be conflicts for database tables that don't use ids (typically variables and other admin tables), but since no one administers the production site from the production site, I force the ProdMod values as being the 'more correct'.

Now I can transfer the database back to production, update the codebase, and start the server again.

The bits I'm still struggling with are

1. removing the test data, because it looks like any other (production) data.
2. Avoiding filename collisions in uploaded files

Maybe I'll put a 'test' column in the node table for the first, an ugly hack, but I think it can be done without 'hacking core'.
For the second I'll just have to rsync frequently (yuk), or use the nid in the upload filepath using Upload path.

To get all these Drupal instances working correctly, that is, using the correct SET @@auto_increment_increment and SET @@auto_increment_offset values, I'm thinking about a very simple module which uses the URL domain to set the offset. Instead of 'hacking core' as France24 did, I'm pretty sure that these values can be set up using hook_boot

I've already flung these ideas (which are not specific to Drupal) on a couple of Italian forums, and I'd be glad of any feedback here too. I've also sent a link to Staging Content to Production Servers

HTH

John