Re: Examples of savepoints and transactions
Hello Lars, On 1/24/2018 8:50 AM, Johan De Meersman wrote: What you're looking for is simple backup and restore :-) Savepoints are, simply put, markers within a transaction; allowing you to rollback only part of a transaction instead of the whole thing. A commit will inevitably commit the ENTIRE transactions, and thus remove the savepoints. A typical workflow for the kind of thing you're trying to do is to have your (automated) testing framework restore last night's backup after the test run. You could also make a backup before the test run and restore that afterwards; have an automated nightly db copy from prod to dev; or in very specific cases you could simply have your test system revert the data by issuing the "reverse" queries - although that one is rarely an option in real life. Another alternative would be to take a filesystem (or virtual machine) snapshot, and revert to that after the tests. Filesystem snapshots will require your database to be stopped and started, though. /Johan - Original Message - From: "Lars Nielsen" <l...@lfweb.dk> To: "MySql" <mysql@lists.mysql.com> Sent: Tuesday, 23 January, 2018 23:19:29 Subject: Re: Examples of savepoints and transactions Den 22-01-2018 kl. 22:01 skrev shawn l.green: Hello Lars, On 1/21/2018 3:37 PM, Lars Nielsen wrote: Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ? I think that the term "test data" is too general to make much sense to most of us in the context you described. Yours, Hello Shawn, Thanks for your interest. Here is an example of my idea. I have a php site working through PDO connections. I insert some data through php like this : |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; ||| ||Now I want to do automated tests that create "dummy" data that i want to remove after the test has finished: like this : |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL OPERATIONS ROLLBACK TO autotest1; ||| ||All done. I have tested the application and have cleaned up the dummy test-data. The issue is that when I call the first commit then the savepoint is deleted. Is this possible at all? Regards Lars || Is table2 what you want to return to its earlier state? Other techniques to do what Johan suggested include: * Make a copy of your "base" data for each test run. That way you don't change your starting point. When that test run is over, drop the copy. This way your "data to be tested" exists (or ceases to exist) outside the boundaries of the transactions you are creating to test/change that data but the original state of that data persists somewhere else. * Use a non-transactional storage engine for table3 (being non-transactional means that the changes you store there will not be affected by a ROLLBACK or COMMIT. They become "permanent" the moment you do them). Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Examples of savepoints and transactions
Kind Regards / Med venlig hilsen Lars Nielsen > - Original Message - >> From: "Lars Nielsen" <l...@lfweb.dk> >> To: "MySql" <mysql@lists.mysql.com> >> Sent: Tuesday, 23 January, 2018 23:19:29 >> Subject: Re: Examples of savepoints and transactions > >>> Den 22-01-2018 kl. 22:01 skrev shawn l.green: >>> Hello Lars, >>> >>>> On 1/21/2018 3:37 PM, Lars Nielsen wrote: >>>> Hi, >>>> I have a system that uses begin and commit transactions. It works >>>> like a dream! ;) >>>> Now I want to test it by creating test data. This how ever cannot be >>>> rolled back. I think the solution for rolling back test data is to >>>> use savepoints and rollback. I think it is hard to find examples of >>>> this scenario. Are there some good guides or tutorials out there >>>> somewhere? Any suggestions are welcome. >>>> >>>> Best regards >>>> Lars Nielsen >>>> >>> >>> Can you mock up an example (a simple text walkthrough) of how you >>> think a savepoint should work with what you are calling "test data" ? >>> I think that the term "test data" is too general to make much sense to >>> most of us in the context you described. >>> >>> >>> Yours, >> Hello Shawn, >> Thanks for your interest. Here is an example of my idea. >> >> I have a php site working through PDO connections. I insert some data >> through php like this : >> >> |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; >> UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, >> y, z); COMMIT; ||| >> >> ||Now I want to do automated tests that create "dummy" data that i want >> to remove after the test has finished: >> like this : >> >> |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM >> table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT >> INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL >> OPERATIONS ROLLBACK TO autotest1; ||| >> >> ||All done. I have tested the application and have cleaned up the dummy >> test-data. >> >> The issue is that when I call the first commit then the savepoint is >> deleted. >> >> Is this possible at all? >> >> Regards Lars >> >> || >> > > -- > The bay-trees in our country are all wither'd > And meteors fright the fixed stars of heaven; > The pale-faced moon looks bloody on the earth > And lean-look'd prophets whisper fearful change. > These signs forerun the death or fall of kings. > -- Wm. Shakespeare, "Richard II" > Den 24. jan. 2018 kl. 14.50 skrev Johan De Meersman <vegiv...@tuxera.be>: > > What you're looking for is simple backup and restore :-) > > Savepoints are, simply put, markers within a transaction; allowing you to > rollback only part of a transaction instead of the whole thing. A commit will > inevitably commit the ENTIRE transactions, and thus remove the savepoints. > > A typical workflow for the kind of thing you're trying to do is to have your > (automated) testing framework restore last night's backup after the test run. > You could also make a backup before the test run and restore that afterwards; > have an automated nightly db copy from prod to dev; or in very specific cases > you could simply have your test system revert the data by issuing the > "reverse" queries - although that one is rarely an option in real life. > > Another alternative would be to take a filesystem (or virtual machine) > snapshot, and revert to that after the tests. Filesystem snapshots will > require your database to be stopped and started, though. > > /Johan > Thanks Johan, I understood the savepoints could be around transactions and not within! I know how to restore from a backup. I just wanted to avoid loading 500+GB after each test-run. :) Thanks for your help everyone. /Lars
Re: Examples of savepoints and transactions
What you're looking for is simple backup and restore :-) Savepoints are, simply put, markers within a transaction; allowing you to rollback only part of a transaction instead of the whole thing. A commit will inevitably commit the ENTIRE transactions, and thus remove the savepoints. A typical workflow for the kind of thing you're trying to do is to have your (automated) testing framework restore last night's backup after the test run. You could also make a backup before the test run and restore that afterwards; have an automated nightly db copy from prod to dev; or in very specific cases you could simply have your test system revert the data by issuing the "reverse" queries - although that one is rarely an option in real life. Another alternative would be to take a filesystem (or virtual machine) snapshot, and revert to that after the tests. Filesystem snapshots will require your database to be stopped and started, though. /Johan - Original Message - > From: "Lars Nielsen" <l...@lfweb.dk> > To: "MySql" <mysql@lists.mysql.com> > Sent: Tuesday, 23 January, 2018 23:19:29 > Subject: Re: Examples of savepoints and transactions > Den 22-01-2018 kl. 22:01 skrev shawn l.green: >> Hello Lars, >> >> On 1/21/2018 3:37 PM, Lars Nielsen wrote: >>> Hi, >>> I have a system that uses begin and commit transactions. It works >>> like a dream! ;) >>> Now I want to test it by creating test data. This how ever cannot be >>> rolled back. I think the solution for rolling back test data is to >>> use savepoints and rollback. I think it is hard to find examples of >>> this scenario. Are there some good guides or tutorials out there >>> somewhere? Any suggestions are welcome. >>> >>> Best regards >>> Lars Nielsen >>> >> >> Can you mock up an example (a simple text walkthrough) of how you >> think a savepoint should work with what you are calling "test data" ? >> I think that the term "test data" is too general to make much sense to >> most of us in the context you described. >> >> >> Yours, > Hello Shawn, > Thanks for your interest. Here is an example of my idea. > > I have a php site working through PDO connections. I insert some data > through php like this : > >|START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; > UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, > y, z); COMMIT; ||| > >||Now I want to do automated tests that create "dummy" data that i want > to remove after the test has finished: > like this : > >|SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM > table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT > INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL > OPERATIONS ROLLBACK TO autotest1; ||| > >||All done. I have tested the application and have cleaned up the dummy > test-data. > > The issue is that when I call the first commit then the savepoint is > deleted. > > Is this possible at all? > > Regards Lars > >|| > -- The bay-trees in our country are all wither'd And meteors fright the fixed stars of heaven; The pale-faced moon looks bloody on the earth And lean-look'd prophets whisper fearful change. These signs forerun the death or fall of kings. -- Wm. Shakespeare, "Richard II" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Examples of savepoints and transactions
Den 22-01-2018 kl. 22:01 skrev shawn l.green: Hello Lars, On 1/21/2018 3:37 PM, Lars Nielsen wrote: Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ? I think that the term "test data" is too general to make much sense to most of us in the context you described. Yours, Hello Shawn, Thanks for your interest. Here is an example of my idea. I have a php site working through PDO connections. I insert some data through php like this : |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; ||| ||Now I want to do automated tests that create "dummy" data that i want to remove after the test has finished: like this : |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL OPERATIONS ROLLBACK TO autotest1; ||| ||All done. I have tested the application and have cleaned up the dummy test-data. The issue is that when I call the first commit then the savepoint is deleted. Is this possible at all? Regards Lars ||
Re: Examples of savepoints and transactions
Hello Lars, On 1/21/2018 3:37 PM, Lars Nielsen wrote: Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ? I think that the term "test data" is too general to make much sense to most of us in the context you described. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
