Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Thu Apr 06, 2006 11:25 am
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Apr 06, 2006 11:32 am
don't you need to start a transaction to have rollback support?
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Thu Apr 06, 2006 1:22 pm
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 » Thu Apr 06, 2006 3:08 pm
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;
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Fri Apr 07, 2006 2:25 am
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 » Fri Apr 07, 2006 2:52 am
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Fri Apr 07, 2006 3:39 am
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
raghavan20
DevNet Resident
Posts: 1451 Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:
Post
by raghavan20 » Fri Apr 07, 2006 4:59 am
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.