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.


----- 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 :
> 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 :
> 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

Reply via email to