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