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
