List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 28 2003 2:43am
Subject:Re: Innodb transactions and drop table
View as plain text  
At 13:22 +0100 3/27/03, Stefan Hinz wrote:
>Christian,
>
>>  It looks like 'drop table' implicitely does a 'commit', at least when
>>  issued by the mysql commandline utility with mysql 3.23.51. This
>>  happens even if it was a temporary heap table as typically used to
>>  emulate subselects.
>
>>  I think this should be documented. (Or better yet, not do a commit,
>>  at least for temporary tables?)
>
>It _is_ documented, but it's hard to find. The MySQL reference manual
>is not up to date, regarding InnoDB, so you should have a look at the
>InnoDB reference manual:
>
>http://www.innodb.com/ibman.html#InnoDB_transaction_model
>
>Scroll down to 8.5 (When does MySQL implicitly commit or rollback a
>transaction?). And here are those crucial sentences:
>
>"The following SQL statements cause an implicit commit of the current
>transaction in MySQL: CREATE TABLE (if MySQL binlogging is used),
>ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME
>TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES, SET AUTOCOMMIT=1. The
>CREATE TABLE statement in InnoDB is processed as a single transaction.
>It means that a ROLLBACK from the user does not undo CREATE TABLE
>statements the user made during his transaction."

I'll second Stefan's remarks, and go further: If you're using
InnoDB, the primary documentation for it is the InnoDB reference
manual.  Go to http://www.innodb.com/ibman.html and READ THE
WHOLE THING.

>
>Regards,
>--
>   Stefan Hinz <hinz@stripped>
>   iConnect GmbH <http://iConnect.de>
>   Heesestr. 6, 12169 Berlin (Germany)
>   Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3
>
>[filter fodder: sql, mysql, query]


-- 
Paul DuBois
http://www.kitebird.com/
sql, query
Thread
Innodb transactions and drop tableChristian Jaeger27 Mar
Re: Innodb transactions and drop tableHeikki Tuuri27 Mar
Re: Innodb transactions and drop tableStefan Hinz28 Mar
  • Re: Innodb transactions and drop tablePaul DuBois28 Mar