Managing mysql schema changes with SQL scripts and transactions

Question

Hi all

I am working with multiple databases in a PHP/MySQL application. I have development, testing, staging and production databases to keep in sync.

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.

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.

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

When I try to add a column that already exists, and add one new column like this:

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;

... it still commits the new column even though it failed to add the first one, of course, because I issued COMMIT instead of ROLLBACK.

I need it to issue the rollback command conditionally upon error. How can I do this in an adhoc SQL script?

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.

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?

Any links to examples welcome - I've googled but am only finding stored proc examples so far

Many thanks

Ian

EDIT - This is never going to work; ALTER TABLE causes an implicit commit when encountered: http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html Thanks to Brian for the reminder

Answer 1

I learned the other day that data definition language statements are always acted on in MySQL and cause transactions to be committed when they are applied. I think you'll probably have to do this interactively if you want to be sure of success.

I can't find the question on this website where this was discussed (it was only a couple of days ago).

If you need to keep multiple databases in synch, you could look into replication. Although replication isn't to be trifled with, it may be what you need. See http://dev.mysql.com/doc/refman/5.0/en/replication-features.html