Stories
Slash Boxes
Comments
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

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Managing Database Upgrades (advice wanted) 4 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
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 [cpan.org] on the CPAN and I can't get the tests to pass, regardless of whether I run them through CPAN.pm 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/alter statements be in separate files or not? If a server is going from version 4 to version 7, then successive version 5, 6 and 7 update/alters would be applied. I don't know the best way to manage that in CVS or extract it automatically on the remote server.

      As for switching to a real database (don't I wish!), if you're volunteering to rewrite the apps and all of their Mysql specific SQL and switch over 2000 servers from Mysql to Postgres ...

      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. These drives are not large and that limits our options.

      • 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 [mysql.com].

        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.
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.