use Perl

• Some Suggestions (Score:2)

  by davorg (18)

  • 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
  • Re:Some Suggestions (Score:2)

    by Ovid (2709)

    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
    • Re:Some Suggestions (Score:1)

      by ajtaylor (1283)

      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
    • Re:Some Suggestions (Score:2)

      by bart (450)

      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.
• Suggestions (Score:1)

  by ggoebel (893)

  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.
  
  Su
• Re: Managing Database Upgrades (Score:1)

  by iburrell (4155)

  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
• Why are developers touching production systems? (Score:1)

  by btilly (5037)

  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