Page 1 of 1

Rollback does not work for me....[solved]

Posted: Thu Apr 06, 2006 11:25 am
by raghavan20
I am making an insert command and trying to rollback but it is still inserted...

Code: Select all

rollback problem:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.15-log |
+------------+

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set 
 
mysql> set autocommit = 0;
Query OK, 0 rows affected 
 
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set 
 
mysql> insert into Country values ('tempC', 'tempCountryName');
Query OK, 1 row affected 
 
mysql> select * from Country where code = 'tempC';
+-------+-----------------+
| code  | name            |
+-------+-----------------+
| tempC | tempCountryName |
+-------+-----------------+
1 row in set 
 
mysql> rollback;
Query OK, 0 rows affected 
 
mysql> select * from Country where code = 'tempC';
+-------+-----------------+
| code  | name            |
+-------+-----------------+
| tempC | tempCountryName |
+-------+-----------------+
1 row in set 
 
mysql>

Posted: Thu Apr 06, 2006 11:32 am
by feyd
don't you need to start a transaction to have rollback support?

Posted: Thu Apr 06, 2006 1:22 pm
by raghavan20
I took the example from the PHP Manual, they did not use start transaction or anything similar to it.

The link to their page

Posted: Thu Apr 06, 2006 3:08 pm
by timvw
I've always thought that you explicitely need to call START TRANSACTION first...

The following quote suggests this too (imho):

http://dev.mysql.com/doc/refman/5.0/en/commit.html
BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION is standard SQL syntax and is the recommended way to start an ad-hoc transaction.

The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not begin a transaction. See Section 17.2.5, “BEGIN ... END Compound Statement Syntax”.

You can also begin a transaction like this:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

Posted: Fri Apr 07, 2006 2:25 am
by raghavan20
this does not work as well...

does myisam support transactions...because all my tables are myisam

Code: Select all

mysql> start transaction;
Query OK, 0 rows affected 
 
mysql> set autocommit =0 ;
Query OK, 0 rows affected 
 
mysql> insert into Country values ('tempB', 'tempBCountry');
ERROR 1046 : No database selected
mysql> use OnlineSystem;
Database changed
mysql> insert into Country values ('tempB', 'tempBCountry');
Query OK, 1 row affected 
 
mysql> select * from Country where code like 'tempB';
+-------+--------------+
| code  | name         |
+-------+--------------+
| tempB | tempBCountry |
+-------+--------------+
1 row in set 
 
mysql> rollback;
Query OK, 0 rows affected 
 
mysql> select * from Country where code like 'tempB';
+-------+--------------+
| code  | name         |
+-------+--------------+
| tempB | tempBCountry |
+-------+--------------+
1 row in set 
 
mysql>

Posted: Fri Apr 07, 2006 2:52 am
by timvw
Afaik, MyISAM does not support transactions (http://dev.mysql.com/doc/refman/5.0/en/ ... gines.html.)

Posted: Fri Apr 07, 2006 3:39 am
by raghavan20
thanks for confirming it, I am now trying to make my tables use InnoDB but it does not allow me to do it, even when I specify InnoDB, it reverts back to MyIsam....I am not either able to create a new table with InnoDB

Posted: Fri Apr 07, 2006 4:59 am
by raghavan20
there was a setting in the configuration file which said skip innoDB and when I commented this off, I was able to create InnoDB tables.


and for the start transaction statement, it is not really necessary to use it as rollback works without it from Mysql and PHP scripts.