Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

  (email not shown publicly)
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Tuesday June 27, 2006
03:53 AM

Managing Database Upgrades (advice wanted)

[ #30060 ]

I've been asked to write a system which will manage database upgrades for some of our servers. Basically, if a developer needs to apply a change to the database, this change must be pushed out to 2000 servers and must not conflict with any other changes.

Since we use RPC, my thought was, whenever the code is pushed out to the server, the server then makes an RPC call back to a central server (already set up and handling RPC requests), providing its database version and, if the database version is old, the RPC call responds with YAML of successive version numbers and the UPDATE/ALTER SQL to bring the database to the current version required for the code and then tests will run to verify the new schema. This portion of the process can be completely automated.

From the development side, when a developer needs to make a change, he or she will first need to run:

db_upgrade start

That will reserver a row in the db_upgrade table and write out a .db_version file in their home directory. Then when they're finished with their changes, they would run:

db_upgrade end < some.sql

The SQL file would resemble something like:

ALTER TABLE customer
DROP COLUMN current;

UPDATE config
SET    value=<% version %>
WHERE  variable='db_version';

The version number would be pulled from the .db_version file. There are multiple issues with this approach and some of the more pressing are:

  • Two developers changing the database at the same time could change the same table, but this is always a problem.
  • Developers need to always remember to check their changes into the database along with source control.
  • Remote servers with Mysql running MyISAM means no transactions.
  • The SQL must not be active until such time that code is both committed to source control and updated on the server. This makes synchronization difficult.

This updates a huge number of servers in data centers which means that this process must not fail, but it requires enough human intervention that I can see plenty of room for failure. Any advice on how to make this more robust would be appreciated.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
    • Two developers changing the database at the same time could change the same table, but this is always a problem.

    You need some centralised locking mechanism. One developer writes a file somewhere saying "I'm altering the database" (or "I'm altering this table" if you need finer control) and then no-one else can start an update until that file is removed.

    • Developers need to always remember to check their changes into the database along with source control.

    Can't you drive the changes from your sou

    • They've agreed to the centralized locking mechanism. As for using source control to manage the SQL, I'm not clear as to the best way to do that. I've checked out Cvs [] on the CPAN and I can't get the tests to pass, regardless of whether I run them through or do it by hand. I admit that having CVS contain all of the changes makes for a better solution, but trying to figure out the best way to cobble all of that together with the automated database upgrades sounds problematic. Would all of the update

      • We can't even use InnoDB because of table space issues. When using InnoDB, we apparently have to allocate a fixed amount of space on the hard disk and if it's not used, it's wasted space.

        Did you know that by default InnoDB will automatically increase the size of the data file as needed? That means you can specify some relatively small size to start, and the DB will automatically change it as needed. See the documentation for autoextend [].

        This probably a moot point anyway, but I wanted to point this f

        "Perl users are the Greatful Dead fans of computer science." --slashdot comment
      • As you're on mysql, you can use lock table for some central table. I'm thinking about your config table that holds the version number, for example.
  • I worked for many years in an environment where we faced the same issue. However, we had the benefit of a real RDBMS with transactions. I apologize for the emphasis. I don't see how you can solve your problem without a bulletproof locking mechanism. ...Or serializing your schema modifications through one.

    Suggestion #1
    To prevent simultaneous attempts to modify a schema, setup a RDBMS that can pass the ACID test (MySQL w/ InnoDB or BDB types). Stage updates on it and serialize the schema updates through it.

  • What database are you using? PostgreSQL does almost all DDL inside transactions. This makes it possible to apply a change as a single unit and rollback on failure. Oracle autocommits most DDL which makes cleaning up after problems a bigger issue.

    I would make creating an update atomic. State between commands makes thing more complicated. Locking is easier when it is inside a single program. db_update insert.sql update.sql delete.sql

    How do you deploy code changes? Database schema changes are us

  • I think you have an impossible problem. You can make it halfway work, but then it will all the way break. And when it does, you'll have a thousand messed up machines. Good luck fixing it.

    The right place to start your question is asking how the release cycle works. Hopefully you have a development/QA/production cycle already. In that case I'd use the QA step as your useful bottleneck to make this problem tractable.

    Developers in development can do whatever they like. That includes messing up their devel