Re: Prevalidating queries?
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
Re: Prevalidating queries?
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 -- Registered Linux User Number 379093 -- --BEGIN GEEK CODE BLOCK- Version: 3.1 GAT/O/E$ d-(---)+ s:(+): a--(-)? C$(+++) UL$ P-(+)++ L+++$ E---(-)@ W++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++) PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+) e$@ h++(*) r--++ y--() -- ---END GEEK CODE BLOCK-- -- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- -- pgpJNAyRFdb6d.pgp Description: PGP signature
Re: Prevalidating queries?
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
Re: Prevalidating queries? [SOLVED]
Hi Thanks that really does answer my question and it meets my requirements as well. Thank you With kind regards Andy -- Registered Linux User Number 379093 -- --BEGIN GEEK CODE BLOCK- Version: 3.1 GAT/O/E$ d-(---)+ s:(+): a--(-)? C$(+++) UL$ P-(+)++ L+++$ E---(-)@ W++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++) PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+) e$@ h++(*) r--++ y--() -- ---END GEEK CODE BLOCK-- -- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- -- pgpUX6PhXMsIn.pgp Description: PGP signature
RE: Prevalidating queries?
Hi andy, Before starting your migration maake sur to stop mysql and copy all the datadir to another backup dir (just like directories). About single transaction, this will be difficult since you manage myisam tables. So what we can think to is : 1. execute one query 2. check log 3. if OK, continue with query n+1 4. else rexecute query n --- But here data can become incohrent. A workaround seems to me to alter your table to innodb engine just for the upgrade. Then start transactions with n grouped queries. Then decide a commit or rollback. At the end of teh upgrade, you can come back to myisam. This is simplier. But you can also decide to take intermadiate backups when upgrading. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Andy Pieters [mailto:[EMAIL PROTECTED] Sent: mardi 14 juin 2005 00:15 To: mysql@lists.mysql.com Subject: Prevalidating queries? 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 -- --BEGIN GEEK CODE BLOCK- Version: 3.1 GAT/O/E$ d-(---)+ s:(+): a--(-)? C$(+++) UL$ P-(+)++ L+++$ E---(-)@ W++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++) PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+) e$@ h++(*) r--++ y--() -- ---END GEEK CODE BLOCK-- -- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
