Rollback-enabled Alter Script
What is Alter script?
Alter script contains set of DML and/or DDL statements that are executed on database to apply appropriate changes on existing database.
The purpose of alter-script is to synchronize the existing database to meet the new release of an application.
Context
Once the development of an application is finished application moves to the production environment. As client uses the application he/she want to change the existing workflow or need to added additional functionalities which is common requirements handle by the all Software development company. For every new release or patches, client’s production database requires appropriate database alteration to meet the functionalities.
DBMs and Rollback functionality
1) DBMs rollback only DML statements which is part of a Transaction
2) DBMS enforce the ACID properties
3) Rollback applies to only that section which is between start transaction and commit /rollback block.
4) Transaction only rollback’s the DML statements but can’t rollbacks the DDL statements.
5) A DDL statement modifies the Meta-data whereas DML statements only manipulate the records.
There are several alternative ways to undo the DDL and DML statements. Solution 1 & 2 requires manually run the appropriate rollback alter script action whereas solution 3 executes rollback action based on DDL & DML statements.
Solution 1
1) Take the backup of the database before applying database changes
2) Apply the database changes; on any error/exception restore the previous database backup.
Solution 2
1) Take the backup of the database before applying database changes
2) Apply the database changes; on any error/exception run the script that will undo all the changes
Alter script action | Reverse action on error |
Add table | Drop table |
Add column | Drop table column |
Drop existing column | Restore previous table if restoring of column is not possible |
Change data type of columns | Restore previous table if restoring of column is not possible |
Solution 3
1) Take the backup of the database before applying database changes to deal with the condition.
2) Prepare alter-script which rollbacks the DDL & DML statements too.
I have implements the solution 3 in MySQL whose skeleton are as shown in diagram.
The above diagram shows the execution flow for the alter-script whose skeleton are as below
/*
--------------------------------------------------------------------------------------------------------
rollback enabled alter script.
@author Ashish.Chudasama
--------------------------------------------------------------------------------------------------------
*/
Use test;
/*
--------------------------------------------------------------------------------------------------------
Configure script parameters
Note : To execute Alter-script on database, User should have 'select' Privileges on Information Schema.
--------------------------------------------------------------------------------------------------------
*/
set @use_database_name='test';
/*
--------------------------------------------------------------------------------------------------------
Usage Guide
--------------------------------------------------------------------------------------------------------
1) DDL_STATEMENTS
a.This procedure should contain all DDL statements like create table ,add columns ,rename columns etc.
2) drop procedure if exists ROLLBACK_DDL_STATEMENTS
This contain exactly the opposite action that mention in DDL_STATEMENTS
DDL_STATEMENTS actions ROLLBACK_DDL_STATEMENTS actions
Create table xyz; Drop table if exists xyz;
Add column Drop column
Rename column Restore column to previous value
3) DML_STATEMENTS
a. All select ,update ,delete statements goes to this procedure
4) ALTERSCRIPT_HANDLER
a. This will execute handle rollback functionality.
--------------------------------------------------------------------------------------------------------
*/
/*
--------------------------------------------------------------------------------------------------------
The alterscript uses the following procedure name to build the Rollback-enabled alterscript.
DDL_STATEMENTS;
ROLLBACK_DDL_STATEMENTS;
DML_STATEMENTS;
ALTERSCRIPT_HANDLER;
ROLLBACK_DDL_AND_DML_STATEMENTS;
--------------------------------------------------------------------------------------------------------
*/
drop procedure if exists DDL_STATEMENTS;
drop procedure if exists ROLLBACK_DDL_STATEMENTS;
drop procedure if exists DML_STATEMENTS;
drop procedure if exists ALTERSCRIPT_HANDLER;
drop procedure if exists ROLLBACK_DDL_AND_DML_STATEMENTS;
DELIMITER $$
CREATE PROCEDURE ROLLBACK_DDL_AND_DML_STATEMENTS()
BEGIN
/*
--------------------------------------------------------------------------------------------------------
Print the
--------------------------------------------------------------------------------------------------------
*/
select 'Rollbacking DML Statements ' as Error ;
ROLLBACK;
If @rollback_all_ddl_statements is not null then
select 'Rollabck DML Done & begins rollbacking DDL statements' as Error ;
call ROLLBACK_DDL_STATEMENTS();
select 'Rollabck DDL Done ' as Error ;
end if;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE ROLLBACK_DDL_STATEMENTS()
BEGIN
/*
--------------------------------------------------------------------------------------------------------
Print the type of activity executed by the ROLLBACK_DDL_STATEMENTS.
print
--------------------------------------------------------------------------------------------------------
*/
select 'Rollbacking DDL Statement......' as Error ;
/*
--------------------------------------------------------------------------------------------------------
Drop colum example.
--------------------------------------------------------------------------------------------------------
*/
if exists (select * from information_schema.columns where table_name ='MyTableName' and column_name = 'demo_column' and TABLE_SCHEMA=@use_database_name) then
ALTER TABLE MyTableName DROP demo_column;
select 'demo_column --Column dropped from the MyTableName' as RollBackStatus;
end if;
/*
--------------------------------------------------------------------------------------------------------
Rollback table example.
--------------------------------------------------------------------------------------------------------
*/
if exists (select * from information_schema.columns where table_name ='MyDemoTableName' and TABLE_SCHEMA=@use_database_name) then
drop table if exists MyDemoTableName ;
select 'MyDemoTableName --Table dropped from the CurrentSchema' as RollBackStatus;
end if;
select 'ROLLBACK_DDL_STATEMENTS() execution completed' as RollBackStatus;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE DDL_STATEMENTS()
BEGIN
/*
--------------------------------------------------------------------------------------------------------
Attached handler to handle automatic rollback of DDL statements on SQLEXCEPTION.
--------------------------------------------------------------------------------------------------------
*/
DECLARE EXIT HANDLER FOR SQLEXCEPTION CALL ROLLBACK_DDL_STATEMENTS();
/*DECLARE EXIT HANDLER FOR SQLWARNING CALL ROLLBACK_DDL_STATEMENTS(); */
/*DECLARE EXIT HANDLER FOR NOT FOUND CALL ROLLBACK_DDL_STATEMENTS(); */
if exists (select * from information_schema.columns where table_name ='MyTableName' and column_name = 'demo_column'
and TABLE_SCHEMA=@use_database_name) then
ALTER TABLE MyTableName ADD demo_column Double(9,2) DEFAULT NULL;
select 'demo_column --Column added to the MyTableName' as Status;
set @rollback_all_ddl_statements='true';
end if;
if exists (select * from information_schema.columns where table_name ='MyDemoTableName' and TABLE_SCHEMA=@use_database_name) then
CREATE TABLE `MyDemoTableName` (
`col_id` int(11) NOT NULL DEFAULT '0',
`col_name` varchar(100) NOT NULL,
`updated_by` varchar(50) DEFAULT NULL,
PRIMARY KEY (`col_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
select 'MyDemoTableName --Table added to the CurrentSchema' as Status;
set @rollback_all_ddl_statements='true';
end if;
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE DML_STATEMENTS()
BEGIN
select 'DML statements execution ......' as Status;
/*
--------------------------------------------------------------------------------------------------------
rollback of DML statements can be done by DBMS we have to take care of only DDL statements.
--------------------------------------------------------------------------------------------------------
*/
update MyTableName set demo_column=12;
insert into MyDemoTableName(col_id,col_name,updated_by) values(1,'Test','Ashish.Chudasama');
END $$
DELIMITER ;
/*
--------------------------------------------------------------------------------------------------------
This procedure is heart if the rollback-enabled script.
--------------------------------------------------------------------------------------------------------
*/
DELIMITER $$
CREATE PROCEDURE ALTERSCRIPT_HANDLER()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION CALL ROLLBACK_DDL_AND_DML_STATEMENTS();
/*DECLARE EXIT HANDLER FOR NOT FOUND CALL ROLLBACK_DDL_AND_DML_STATEMENTS(); */
/*
Print some information before executing alterscript.
*/
select 'Alterscript version details' as AlterScript
union
select CONCAT('Database Name ', @use_database_name)
Union
select 'Note : To execute Alter-script on database, User should have select Privileges on Information Schema.';
set @rollback_all_ddl_statements=null;
set @DDL_ROLLBACK_STATUS =null;
select 'call DDL_STATEMENTS ';
CALL DDL_STATEMENTS();
select 'DDL_STATEMENTS executed';
IF @DDL_ROLLBACK_STATUS IS NULL THEN
START TRANSACTION;
CALL DML_STATEMENTS();
COMMIT;
select 'COMMIT statements......' as Commited;
ELSE
select 'All Data RollBack' as Result;
END IF;
select 'Completed';
END $$
DELIMITER ;
/*
--------------------------------------------------------------------------------------------------------
Start the execution......
--------------------------------------------------------------------------------------------------------
*/
CALL ALTERSCRIPT_HANDLER();
/*
--------------------------------------------------------------------------------------------------------
Drop the procedure created by the Rollback-enabled script.
--------------------------------------------------------------------------------------------------------
*/
drop procedure if exists DDL_STATEMENTS;
drop procedure if exists ROLLBACK_DDL_STATEMENTS;
drop procedure if exists DML_STATEMENTS;
drop procedure if exists ALTERSCRIPT_HANDLER;
drop procedure if exists ROLLBACK_DDL_AND_DML_STATEMENTS;
Download script file here. Script contains demo DDL and DML statements.
If you find blog content is useful then leave your comments and suggestion ……
LinkedIn Groups
ReplyDelete* Group: Java Developer Network
* Discussion: Rollback-enabled Alter Script for MySQL
You have identified a major problem for MySQL developers: you can't put data definition statements such as CREATE TABLE or ALTER TABLE inside a transaction, so it's impossible to undo a failed CREATE or ALTER using ROLLBACK. Thank you for raising awareness of this problem.
Your proposed solution is an interesting use of MySQL stored procedures, but it can only be useful for data definition statements which are reversible.
Here's an example which is not reversible: suppose I drop a column from a table. I have now lost all of the data in that column, and unless I have saved a copy of the data, I cannot reverse the "DROP COLUMN" operation.
Saving the data could be difficult, if not impossible. Imagine that my table does not have a primary key or any other unique index. I cannot identify any row uniquely, so I cannot restore the data from a dropped column. My only option is to make a perfect duplicate of the entire table, in case I need to undo the DROP COLUMN.
But now, suppose that my database is part of a production system, where new data are being added to the table constantly. My duplicate copy is going to be out-of-date very quickly, as new data are added to the original table after I make the duplicate.
You might say: okay, we will lock the table so no clients can add new data.
But if I do this in a live production system, and I begin a DROP COLUMN on a very large table, the clients will be locked out of the table for many minutes, perhaps even hours (I have actually seen this happen!). Then I will soon have a crowd of angry users at my office door, all wanting to know why the system has stopped accepting new data.
Please don't be disheartened by my comments. I think your solution is an imaginative use of stored procedures. But real-life systems are often more complex than your simple example!
Posted by David Harper