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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

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

Post 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>
Last edited by raghavan20 on Fri Apr 07, 2006 6:53 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

don't you need to start a transaction to have rollback support?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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;
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Afaik, MyISAM does not support transactions (http://dev.mysql.com/doc/refman/5.0/en/ ... gines.html.)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
Post Reply