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
