Andy Pieters <[EMAIL PROTECTED]> wrote on 06/13/2005 06:14:42 PM: > Hi all
> As part of an automated patch system, I am facing the following problem: > * A script will update the program from version x to version y > * The script contains file actions, and database (mysql) actions > * The actions are executed in order > * For each action, a backup copy is created (if necessary) > example if action is deletedir then the dir is moved to a temp directory > * if an action (with status fail=abort) occurs, then the system must be > restored to previous state. > As far as file/directory operation is concerned, this is easy to implement, > and that's why we are using backup copies. > For the mysql part I don't really see how to do this. I am not using inodb > but MyIsam tables. > It is not that I need to know the result of the query in advance, only if > mysql will accept it or will errormessage on the query. > What I don't want is that query1, and 2 are already executed, and 3 fails > because how could I do a rollback then? > > Anybody got any ideas? > > With kind regards > > Andy > > -- > Registered Linux User Number 379093 <snip> Without allowing the database to manage your transaction (that is what you call what you are doing with your data, a "transaction". You are predicating the commitment of queries 1 and 2 based on the performance of query 3.) To stay with MyISAM as your storage engine, you will need to archive your original records and restore them through your code (in case of failure) just as you archiving and restoring your files. That means you will need a set of "backup" tables to hold the originals to the records that you are changing. InnoDB does this automatically. I seriously encourage you to read up on transactions and InnoDB and I strongly suggest you change your table design (to use InnoDB). That way you have an actual "ROLLBACK" command at your disposal. Otherwise you will be re-inventing the wheel by creating a versioning-locking system for MyISAM when one already exists in InnoDB. What you need is a wrench to turn that nut but right now you are trying to use a hammer. Change your tool and your task will become much easier to accomplish. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
