What is the official word on doing a CREATE TABLE inside a transaction? Can I do one without causing the transaction to commit?
By experiment, it appears that 4.0.14 allows this, although, even if the CREATE TABLE is for an InnoDB table, a ROLLBACK doesn't remove the created table. It appears that CREATE TABLE caused a commit of the current transaction in 3.23.49. I'd like to create a temporary table during a transaction--mostly to be able to emulate things like subqueries and views that will come in some future production version of MySQL. I don't have a problem with the table creation not being rolled back. I tried to RTFM. I did find a note on how transactions are treated for CREATE TABLE SELECT..., but I wasn't able to find a clear statement that CREATE TABLE will no longer force a commit. From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Subject: Re: Questions about MySQL implementation Date: Thu, 18 Dec 2003 01:18:39 +0200 Chris, ----- Original Message ----- From: "Chris Nolan" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, December 13, 2003 7:24 AM Subject: Questions about MySQL implementation [snip] > 2. I've been told on good authority (by persons on this fine list) that > Sybase and PostgreSQL (and, from personal experience, SQLBase) support > ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE > etc. From what I can gather, neither BDB nor InnoDB do this. > > Does anyone know what sort of technical challenges making the above > statements "undoable" involve over and above INSERT, DELETE and UPDATE > statements? Would this functionality be something that MySQL AB / > Innobase Oy would be interested in developing should it be sponsored? Not very difficult: we could keep the 'old' table until the transaction commit. In a rollback we would fall back to the old table. But the demand for such a feature is so low that most databases do not have a rollback of DDL statements. [snip] > Chris Best regards, Heikki Tuuri Innobase Oy [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
