Roleback CREATE TABLE using MySql Transactions

Question

I'm writing a upgrader for a mysql database using PHP. The behavior of the upgrader should be as follows.

If all the queries executed successfully the changes should be committed.
If a sinngle query get faild eveything should be roled back to previouse state.

Part of my program is as follows.

$host = 'localhost';
$user = 'root';
$password = 'root';
$db = 'transaction';
$con = mysqli_connect($host, $user, $password);
mysqli_select_db($con, $db);
mysqli_autocommit($con, FALSE);

$query1 = "create table `status` (
                      `id` int not null auto_increment,
                      `name` varchar(60) not null,
                    primary key  (`id`)
                    ) engine=innodb default charset=utf8;";
$result1 = mysqli_query($con, $query1);
$query2 = "ALTER TABLE status
                    CHANGE name value varchar(512);";

$result2 = mysqli_query($con, $query2);

if(!($result1 && $result2)) {
    mysqli_rollback($con);
} else {
    mysqli_commit($con);
}

mysqli_close($con);

But if the 'status' table already exists the first create table query is failing. So both queries should be rolled back. But the alter query has executed and not rolled back.

I saw a post which list all the queries which cannot be rolled back in mysql. http://www.sitepoint.com/mysql-transaction-gotchas-good-parts/

Is there any possible way to do this role back in mysql.

The first thing I would do is to make a backup of the database if you are about to start changing its structure. — Ed Heal, Mar 15 '12 at 08:42
Yes @EdHeal. I'm running these commands on a copy of the original database. So if the thing get failed I can replace the copy with a dump manually. But the problem is I need to do it automatically. — Thilanka, Mar 16 '12 at 09:47

score 1 · Answer 1 · answered Mar 15 '12 at 08:40

1

No. You would need to run a new alter table query undoing your previous alter statement.

answered Mar 15 '12 at 08:40

Ilion

6,772
3
24
47

Thanks @llion for the quick reply. I can do it manually since this is a small change. But the problem is this is a small part of my script and it contains 150 such table creations and alterations. So it is difficult to do that manually. Is there any automatic way to roll back it to the initial state. – Thilanka Mar 16 '12 at 09:48

score 1 · Answer 2 · answered Mar 15 '12 at 08:41

1

do it manualy

if(!($result1 && $result2)) {

    #drop table
    $query1 = "drop table `status`";
    $result = mysqli_query($con, $query1);   

}

answered Mar 15 '12 at 08:41

Haim Evgi

123,187
45
217
223

Thanks @Haim Evgi for the quick reply. I can do it manually since this is a small change. But the problem is this is a small part of my script and it contains 150 such table creations and alterations. So it is difficult to do that manually. Is there any automatic way to roll back it to the initial state. – Thilanka Mar 16 '12 at 09:44

score 0 · Answer 3 · answered Mar 16 '12 at 17:33

0

Would it be better to just export the data into (say) a collection of CSV files. Then modify any dataif needed to match the new structure. Then just create the database with the new structure and import the data into it.

Seems a simpler solution that trying to make an upgrader.

answered Mar 16 '12 at 17:33

Ed Heal

59,252
17
87
127

No @Ed Heal assume that if something went wrong in the middle of the process. Since this system upgrade is done by the existing users it will be very difficult them to do that by their selves. They just need a one click upgrader. So we have to so it automatically and if something goes wrong we should be able to role back the changes to the initial state. – Thilanka Mar 18 '12 at 09:46
I was being pragmatic. You posed the question that something might go south. I just offered a safer solution – Ed Heal Mar 18 '12 at 14:52

Roleback CREATE TABLE using MySql Transactions

3 Answers3