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?) Christian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb transactions and drop table
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]
Re: Innodb transactions and drop table
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. Regards, -- Stefan Hinz [EMAIL PROTECTED] 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb transactions and drop table
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 [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]