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" <[email protected]>
>> To: "MySql" <[email protected]>
>> 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