Simple, ROLLBACK reverts DML (data) changes, not DDL (structure)
changes..
mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.7-nt |
+-----------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE "have_innodb";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | YES |
+---------------+-------+
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.03 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE foo (bar int) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.17 sec)
mysql> INSERT INTO foo VALUES (100);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM foo;
+------+
| bar |
+------+
| 100 |
+------+
1 row in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM foo;
Empty set (0.00 sec)
mysql> SHOW CREATE TABLE foo;
+-------+---------------------------------------------------------------
--------
-------------------+
| Table | Create Table
|
+-------+---------------------------------------------------------------
--------
-------------------+
| foo | CREATE TABLE `foo` (
`bar` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------
--------
-------------------+
1 row in set (0.00 sec)
See: http://dev.mysql.com/doc/mysql/en/cannot-roll-back.html
HTH
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-----Original Message-----
From: Ville Karjalainen [mailto:[EMAIL PROTECTED]
Sent: 04 February 2005 13:59
To: mysql@lists.mysql.com
Subject: Question: InnoDB transaction and table changes
Greetings,
I created a table during transaction and was surprised to find out it
still existed after I did a ROLLBACK. The same seems to apply to changes
made using ALTER TABLE statements.
Is there a simple logical explanation to this behaviour?
Any help would be appreciated.
Demonstration follows:
mysql> SELECT VERSION();
+--------------------+
| VERSION() |
+--------------------+
| 4.1.7-Debian_4-log |
+--------------------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE "have_innodb";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | YES |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.19 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE foo (bar int) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE foo;
+--------+--------------------------------------------------------------
-------------------------------+
| Table | Create Table
|
+--------+--------------------------------------------------------------
-------------------------------+
| foo | CREATE TABLE `foo` (
`bar` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)
--
Ville Karjalainen - [EMAIL PROTECTED]
Toiminto Media ky - [EMAIL PROTECTED] - http://toiminto.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
