A method of syncronising database changes from development to live using Toad for MySQL.

Requirements: advanced Drupal, database administration, experts only

One of the recurring problems faced by Drupal developers is database change management. If you don't encounter this problem yourself, then just imagine a scenario where you have a live site that needs to be running 24/7, while all your changes are made on a development server.

The big problem comes in migrating all the database changes from the dev server to the live one, without overwriting any data. Some people simply fiddle with dev until they’ve got it right, then duplicate all their work step-by-step on live, until they’re the same. You can imagine how perilous this process is!

I’ve been needing a solution to this problem for a while now, so I’ve pieced together this guide in the hope of receiving feedback from those who are wiser than me! I have not thoroughly tested this method on a real server configuration, only on my localhost, so I don’t know if it’s foolproof yet. But please take a look!

Issue #1: New primary keys need to be different on dev and live

If you’ve tried data syncronisation before, you may have run into this snag: Imagine dev and live both start with 3 nodes, primary key node-ids of 1, 2, 3. What happens when you add a node on dev, and a user adds a node on live?

Well, they both get a primary key id of 4, and this is irreconcilable by any data syncronisation application, as it views node 4 as having changed, rather than 2 new nodes being created.

I discovered a solution to this problem in this thread on France24’s Drupal staging solution. They force the dev server to create only even numbered ids, and the live server only odd numbered ids. That way, new content on one server can never create a primary key collision with new content on the other server.

This does involve a couple of lines hack on Drupal core. What, you don’t want to hack Drupal core? But sir, it is just one wafer-thin hack!

You could always do what the France24 guys do, and install PHP Runkit which will allow you to replace entire functions without modifying the original, but that seems like a bit of fiasco for a small change, and infeasible for shared hosting.

In Drupal 5, ids are managed in two different ways. The first is by using the sequences table, which stores the last created id for a number of different entities, such as users and nodes:

In order to force Drupal to use only odd or even ids, the function that needs to change is db_next_id(..), located in database.mysql.inc or database.mysqli.inc depending on which configuration you use (I would imagine mysqli for MySQL 5):

The following lines say “if the id is even, then increment it by one so it becomes odd”

And to force even ids:

Here’s where it goes:

Naturally, you’ll want to make sure to force evens on dev, and odds on live, or vice versa.

MySQL System Variables

Then you have other tables, for example watchdog and probably a lot of contrib, do not use the sequences table. They rely on the database to auto-increment their primary key ids, so when they insert a row there is no call to db_next_id(..).

To manage these, we can alter MySQL system variables that were introduced in MySQL 5.0.2. Their primary function is to facilitate master-master replication, but it serves us the same purpose. They let us specify how MySQL generates it's auto incremented values.

The variables are auto_increment_increment and auto_increment_offset. It works as follows:

Starting from the value auto_increment_offset, create a sequence that increases each time by N x auto_increment_increment, where N is the position in the sequence.

Not really that complicated. If we give it the following:

MySQL will generate a sequence of ids as follows:

Perfect!

One method of setting these variables in by adding them to your my.cnf configuration file, and restarting the MySQL servers. Obviously this is not possible for those of us without dedicated hosts. Luckily, there’s another per-session method which also works, but unluckily it requires another hack to Drupal core… :(

Here’s the function, db_connect(..), which is located once again either in database.mysql.inc or database.mysqli.inc:

Pretty simple change really. What we need to do is set the auto_increment variables here, where we initiate the connection. These lines, inserted immediately before return $connection;, do it for odd numbers:

And this is for even numbers:

And now for...

Drupal 6

The awesome thing about Drupal 6, is that there is no more sequences table. All ids are generated by the database, so the first hack to db_next_id(..) is not necessary. In fact, the function is gone now, so you’d struggle a bit to hack it anyway… :) You only need the changes that affect the auto increment variables.

Great! So now we’ve covered our bases. There are still two caveats though. What if a table doesn’t use a primary key? And what if a contributed module uses its own sequences table (for some bizarre reason)? I’d be interested to know why, firstly, but of course the answer is that you’ll just have to look out for these situations. This is made easier using the tool introduced in the next part.

Issue #2: Syncronising the data

I stumbled across a neat little tool called Toad for MySQL, a freeware application brought to you by the same guys that produce Toad for Oracle, an industry standard in Oracle database administration.

It’s free, but it’s Windows only, so fire up those virtual machines all you Mac and Linux guys!

In this example, I work by taking a copy of live’s database and importing it to dev, doing my changes on dev, then exporting the changes back to live.

Here's an example situation. I'm not going to step through exactly how to perform these steps, because they're all core functions of Drupal:

• development and live databases begin syncronised
• you, the developer, add a content type called "dog" to your dev site
• you create a new "dog", with some info
• you change the site name to "World of Pets"
• a user creates a story node on live, while you're busy with dev
• a user comments on a story on live
• a user edits a pre-existing story node, changes some text
• a new user signs up on live

Nightmare, right? ;)

Not necessarily. If you have your odd/even database ids working, this is easily reconcilable. Basically, all we need to do is migrate changes on dev up to live, then copy the entire database from live back to dev in order to be synced up again.

Step 1: Toad and its connections

Start by downloading and installing Toad. Once you're up and running, choose View->Connection Manager to bring up the following window (that is already populated with my connections in the screenshot):

Click the "add connection" button to add a connection to both your live and dev MySQL servers. It's a simple form to fill out for each:

Of course, your live host's MySQL server could potentially be configured to only allow connections from its localhost (i.e. PHP scripts running on itself), in which case connecting from your local machine will not work. GoDaddy.com imposes this restriction. Some hosts do not, and they let you to change the allowed hosts (for example using CPanel), so you can temporarily grant yourself remote access to your database.

Step 2: Comparing the data

Connect to one of your databases through Toad, then choose Advanced -> Data Compare from the main menu.

Up pops a wizard! Just select the source and target databases from the dialog:

In this case, I have two installations of Drupal on my localhost, to make things easier. One is called drupal-6-live, and the other drupal-6-local, 'local' meaning 'dev' in the context of this article.

Click "Next" and wait while it does its magic. You should then be presented with another step of the wizard:

Here you are telling Toad which tables to compare. Clicking the "Find Target(s)" button will automatically pair tables with the same name, which is definitely what we want. You can also see in this step if you have unpaired tables, for example if you created a new table on dev. Toad will not syncronise these, but exporting a complete table from dev to live is really easy, even using a tool like phpMyAdmin, so I won’t worry about that here.

Click "Next" and "Next" again, watch it do some more magic, then finish the wizard after reviewing the summary.

Step 3: Syncronising the correct changes

Here's the fun part. You'll be presented with this screen, which is a list of the findings of the database compare:

Step 4: Stare at it for a long, long, time until everything becomes clear or your brain tries to escape out of your ears...

No, not really. Maximise the window and shuffle the columns around so you can see the information clearly. You'll see in the 'status' column, that an equals sign indicates the table is the same on dev and live. A red/green arrow pair indicates that there are changes, and you should take a closer look!

Some knowledge of Drupal's database structure is absolutely critical. You should know that you don't need to syncronise the 'cache' or 'sessions' tables, for example. But the first one on the alphabetical list of changed tables that should catch your eye would be the comments:

Aha! The red arrow indicates the row was found on the live table, but not on our dev table. So a user has made a new comment. However, we don’t need to syncronise this back to dev, because we’re only looking for changes to move up to live, not the other way around. A green arrow is what we’re after.

It’s difficult to get a good screenshot, but here’s an example from the node table. You’ll see the red arrow indicating a new node on live with the id of 2, and the green arrow with a vid (and nid) of 3! So the odd/even numbering has protected us from collisions in this case. We select this row for syncronisation. Also notice the green/red arrows together, which means there’s a difference.

Continue through all the tables, checking for the green arrows, and ticking them off as necessary. Note that you need a sync tick next to the table, in order for any ticks next to the rows to be synced. A little user interface quirk that’s fine once you ‘get it’!

Now you can click the sync button, select the direction and prepare the SQL script, which can either be run immediately or in the Toad editor:

There’s no doubt that this is a finicky process that requires knowledge of the tables. However, I would argue that it’s not as laborious or error-prone as trying to replicate the changes using Drupal’s front end. Up for discussion, and definitely depends on your particular setup and the changes you are performing.

Conclusion

That’s it really! I’m not in a position to properly use this yet, as some of my sites don’t allow external MySQL connections, and the ones that do are still on PHP 4. So although I’ve looked into it as a possible solution, I am really hoping for input from the community on whether this is a good idea and if there’s perhaps a simpler way.

The hack that is used on Drupal 6 to force odd/even ids could possibly be submitted as a patch for inclusion into 7, if it presented an option to switch on/off in an advanced menu. Although that may be a hard sell...

You should also take your site onto offline mode while performing the sync itself… that’s quite important.

And don’t get me started on backups… ;)