MySQL Bugs: #64482: Feature Request: Reset TABLEs or whole schema to a state in the past

Bug #64482	Feature Request: Reset TABLEs or whole schema to a state in the past
Submitted:	28 Feb 2012 11:42	Modified:	28 Feb 2012 14:43
Reporter:	Peter Steiner	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Assume I prepared some TABLEs or even a whole Schema with some special test data.
 
I want to define the current content as a "restore point 123"/"snapshot123". 

Then I want to run some test programs which modify the data. 

After that I want to reset the TABLEs resp. Schema to the previously defined 
"restore point 123"/"snapshot123". 

This should be done NOT by external backup+restore but by a command (which can be issued e.g. from Java program).
Currently there are external, commercial programs for that purpose.
However this is a core task for database development. So it should be part of the engine itself.

So I setup a feature request for this new function/command.

Thank you

 

How to repeat:
Non existing 

Suggested fix:
See Description

Then is a feature request for Backup category?.

Not really.

Its not a backup to an external file but internally.
It si more similar to the "snapshot feature" of the big Oracle Database.
However a much easier, more intuitive, reset is possible and could implemented.

Thank you for the feature request. Yes, something similar to Oracle's flashback queries etc would be useful. Not sure there is an easy way to implement this in MySQL, with all non-transactional storage engines we have.

One important and drastical reduction of the efforts for implementation would be
to stop (internally, automatically) the (full) database.

Because most of the users which will use this command later have full control over their (test) database there is no need to setup such a restore point during running database (operations).

So if the user launches a "reset to restorepoint 123" command then MySQL should do internally automatically the following sequence of operations:

1.) Stop accepting new SQL commands
2.) Give running SQL command a pre-defined max grace period (e.g. 10 minutes) to complete their current jobs
3.) stop the full database 
4.) backup internally all defined TABLEs/SCHEMAs into a pre-defined area of the MySQL storage. If possible give user a progress indicator ("....restore point creation 32% done...") to the MySQL console.
5.) When finished restart the full database

As you can see this procedure should be easy to implement (at least much easier than handling the creation of restore points during online run of database.

Do you agree?