Andy Pieters <[EMAIL PROTECTED]> wrote on 06/14/2005 09:59:09 AM: > > 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.
> Thank you for your reply. I realize that there are limitations with the > MyISAM engine but the product has been designed from the ground up to use > MyISAM tables and is already deployed. Changing database design would be a > costly affair in my humble opinion. This is something that could be done if > the customer wants a big update, then I might be able to force that kind of > change too but until then I'm stuck in MyISAM. > Until someone can tell me "the" answer, I guess I will implement a sort of > thing as suggested here. > Its just that I want to avoid at all cost that a patch is only half applied. > Imagine finding out what is going on in a product that is "between" versions. > With kind regards > > Andy I am sorry I didn't catch on that this is an infrequent need (only for the upgrade). Here is how I would do the upgrade: Make a duplicate of the old database into a new database... #Assuming the original database is called Working CREATE DATABASE newWorking; USE newWorking; CREATE TABLE working1 LIKE Working.working1; INSERT working1 SELECT * FROM Working.working1; CREATE TABLE working2 LIKE Working.working2; INSERT working2 SELECT * FROM Working.working2; CREATE TABLE working3 LIKE Working.working3; INSERT working3 SELECT * FROM Working.working3; CREATE TABLE working4 ( # new table definition here ) INSERT working4 SELECT <field list> FROM Working.working4; # and so on until you "migrate" the tables from Working (old-style) to newWorking (new-style) At this point you have a set of "backup" tables in the original Working database and a set of your converted tables in newWorking. Assuming that everything checks out OK (now is when you perform any last-chance validation checks on the data in newWorking) you can easily dump the old Working tables and move all of the newWorking tables into it. #this assumes that you are still USE-ing newWorking DROP DATABASE Working; CREATE DATABASE Working; RENAME TABLE working1 to Working.working1, working2 to Working.working2, ..., workingN to Working.workingN; RENAME TABLE is an atomic function, it won't leave a copy behind and it can't be interfered with by another process (no INSERTS, SELECTS, UPDATES, or DELETES are permitted while the rename is in progress). By the time you have finished RENAME-ing the tables, newWorking will be empty and you can simply DROP DATABASE newWorking; Voila! your migration is complete. Again, sorry for the confusion. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
