title: Managing mysql schema changes with SQL scripts and transactions
body: <p>I am working with multiple databases in a PHP/MySQL application. I have development, testing, staging and production databases to keep in sync. </p>
  
  <p>Currently we're still building the thing, so it's easy to keep them in sync. I use my dev db as the master and when I want to update the others I just nuke them and recreate them from mine. However, in future once there's real data I can't do this. </p>
  
  <p>I would like to write SQL scripts as text files that I can version with the PHP changes that accompany them in svn, then apply the scripts to each db instance as I update them.</p>
  
  <p>I would like to use transactions so that if there are any errors during the script, it will roll back any partial changes made. All tables are InnoDB</p>
  
  <p>When I try to add a column that already exists, and add one new column like this:</p>
  
  <pre><code>SET FOREIGN_KEY_CHECKS = 0;
  START TRANSACTION;
  ALTER TABLE `projects` ADD COLUMN `foo1` varchar(255) NOT NULL after `address2`;
  ALTER TABLE `projects` ADD COLUMN `foo2` varchar(255) NOT NULL after `address2`;
  COMMIT;
  SET FOREIGN_KEY_CHECKS = 1;
  </code></pre>
  
  <p>... it still commits the new column even though it failed to add the first one, of course, because I issued COMMIT instead of ROLLBACK.</p>
  
  <p>I need it to issue the rollback command conditionally upon error. How can I do this in an adhoc SQL script?</p>
  
  <p>I know of the 'declare exit handler' feature of stored procs but I don't want to store this; I just want to run it as an adhoc script.</p>
  
  <p>Do I need to make it into a stored proc anyway in order to get conditional rollbacks, or is there another way to make the whole transaction atomic in a single adhoc SQL script?</p>
  
  <p>Any links to examples welcome - I've googled but am only finding stored proc examples so far</p>
  
  <p>Many thanks</p>
  
  <p>Ian </p>
  
  <p>EDIT - This is never going to work; ALTER TABLE causes an implicit commit when encountered: <a href="http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html</a> Thanks to Brian for the reminder</p>