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
