Christian, ----- Original Message ----- From: "Christian Jaeger" <[EMAIL PROTECTED]> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
