List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:March 27 2003 8:34pm
Subject:Re: Innodb transactions and drop table
View as plain text  
Christian,

----- Original Message -----
From: "Christian Jaeger" <christian.jaeger@stripped>
Newsgroups: mailing.database.mysql
Sent: Thursday, March 27, 2003 1:42 PM
Subject: Innodb transactions and drop table


> Hello
>
> 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?)

http://www.innodb.com/ibman.html#InnoDB_transaction_model

"
8.5 When does MySQL implicitly commit or rollback a transaction?


MySQL has the autocommit mode switched on in a session if you do not do SET
AUTOCOMMIT=0. In the autocommit mode MySQL does a commit after each SQL
statement, if that statement did not return an error.
If an error is returned by an SQL statement, then the commit/rollback
behavior depends on the error. See section 13 for details.
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 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.
If you you have the autocommit mode off and end a connection without calling
an explicit COMMIT of your transaction, then MySQL will roll back your
transaction.

"

Hmm... an implicit commit in DROP TABLE is necessary as MySQL writes the
DROP TABLE to the binlog immediately. In that case it would break
replication if possible INSERTs to that table were written later to the
binlog than the DROP TABLE.

Actually, there is a slight bug in replication now: inserts by OTHER users
may still be written to the binlog AFTER the table is dropped. InnoDB should
roll back any transaction by other users who have modified the dropped
table, but have not committed yet!

We must write inserts to a temporary table to the binlog because inserts to
other tables may depend on them. We cannot just ignore temporary tables in
binlogging.

As a workaround I recommed dropping your temporary tables only AFTER you
have performed the transaction.

> Christian.

Best regards,

Heikki Tuuri
Innobase Oy
---
MySQL Users Conference, San Jose, California, April 10-12
Register at http://www.mysql.com/events/uc2003/attendee.html



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