is CREATE TABLE not transactional?

Is CREATE TABLE not protected by transactions in MySQL? I just tried doing
this:

mysql> BEGIN WORK;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE foo (bar int) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;

And my newly created "foo" table shows up on the list, even though i've
rolled back the transaction. Is there any way to fix this??

Thanks,
Tyler




--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: is CREATE TABLE not transactional?

>Is CREATE TABLE not protected by transactions in MySQL? I just tried doing
>this:

See:

http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

>
>mysql> BEGIN WORK;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> CREATE TABLE foo (bar int) TYPE=InnoDB;
>Query OK, 0 rows affected, 1 warning (0.03 sec)
>
>mysql> ROLLBACK;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> SHOW TABLES;
>
>And my newly created "foo" table shows up on the list, even though i've
>rolled back the transaction. Is there any way to fix this??
>
> Thanks,
> Tyler

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: is CREATE TABLE not transactional?

Paul DuBois wrote:
> >Is CREATE TABLE not protected by transactions in MySQL? I just tried doing
> >this:
>
> See:
>
> http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

Okay, so it's documented at least.. I guess the new question now is
"why?!?!?!"... they actually *added* this functionality in 5.0.8, to serve
what purpose? :-(

- Tyler

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: is CREATE TABLE not transactional?

At 15:52 -0800 2/16/06, Tyler MacDonald wrote:
>Paul DuBois wrote:
>> >Is CREATE TABLE not protected by transactions in MySQL? I just tried doing
>> >this:
>>
>> See:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html
>
> Okay, so it's documented at least.. I guess the new question now is
>"why?!?!?!"... they actually *added* this functionality in 5.0.8, to serve
>what purpose? :-(

In effect, it's always been that way. The 4.1 page is here:

http://dev.mysql.com/doc/refman/4.1/en/implicit-commit.html

So it's caused an implicit transaction before 4.1.13 (and 5.0.0 which came
out about the same time) if the binary log was enabled. But that log
generally *should* be enabled.

As to the why ... dunno.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: is CREATE TABLE not transactional?

Paul DuBois wrote:
> http://dev.mysql.com/doc/refman/4.1/en/implicit-commit.html
>
> So it's caused an implicit transaction before 4.1.13 (and 5.0.0 which came
> out about the same time) if the binary log was enabled. But that log
> generally *should* be enabled.
>
> As to the why ... dunno.

Ah well. My application doesn't depend on it for any point except
the installation phase, where if the install was unsuccessful it would have
been nice to be able to just ROLLBACK the entire thing. Postgres deals with
this fine, hopefully MySQL will one day too.

- Tyler


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: is CREATE TABLE not transactional?

At 5:13 PM -0600 2/16/06, Paul DuBois wrote:
>>Is CREATE TABLE not protected by transactions in MySQL? I just tried doing
>>this:
>
>See:
>
>http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

From the above-linked page:

Each of the following statements (and any synonyms for them)
implicitly end a transaction, as if you had done a COMMIT before
executing the statement:

...

Transactions cannot be nested. This is a consequence of the
implicit COMMIT performed for any current transaction when you issue
a START TRANSACTION statement or one of its synonyms.

I think that's something which needs to be fixed. Nothing except an
explicit COMMIT should cause a commit, and nested transactions should
be supported. Doing so would greatly improve MySQL's reliability and
make it easier to use.

A good time to implement this would be for 5.1, or 5.2 if 5.1 is now
trying to stabilize.

-- Darren Duncan

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: is CREATE TABLE not transactional?

Darren Duncan wrote:
> Transactions cannot be nested. This is a consequence of the
> implicit COMMIT performed for any current transaction when you issue
> a START TRANSACTION statement or one of its synonyms.
>
> I think that's something which needs to be fixed. Nothing except an
> explicit COMMIT should cause a commit, and nested transactions should
> be supported. Doing so would greatly improve MySQL's reliability and
> make it easier to use.
>
> A good time to implement this would be for 5.1, or 5.2 if 5.1 is now
> trying to stabilize.

+1. No, +1,000.

Being able to use FOR in triggers would be nice too! Cursors are a
pain in the butt. Not to mention being able to do a DELETE query with a
sub-select in the same table. Sometimes a WHERE clause just isn't enough to
convey what you want gone.

I'm trying my best to keep supporting MySQL in my modules, but I'm
using Postgres on any new personal projects for the time being mainly
because of nusiances like this.

- Tyler


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Fwd: Undeliverable mail: Re: is CREATE TABLE not transactional?

--ReaqsoxgOBHFXBhH
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

I keep getting this bounce when I post to this list. Can this dead email
address be unsubscribed?

Thanks,
Tyler

--ReaqsoxgOBHFXBhH
Content-Type: message/rfc822
Content-Disposition: inline

Return-Path: <>
X-Spam-Checker-Version: SpamAssassin 3.1.0 (2005-09-13) on
libertas.crackerjack.net
X-Spam-Level:
X-Spam-Status: No, score=-1.5 required=5.0 tests=BAYES_00,NO_REAL_NAME,
OPTING_OUT_CAPS autolearn=no version=3.1.0
X-Original-To: tyler@yi.org
Delivered-To: tyler@libertas.crackerjack.net
Received: by libertas.crackerjack.net (Postfix)
id 8BB4868F0E; Fri, 17 Feb 2006 19:20:48 -0500 (EST)
Delivered-To: faraway@libertas.crackerjack.net
Received: from server45.appriver.com (server45.appriver.com [69.20.58.226])
by libertas.crackerjack.net (Postfix) with ESMTP id 7C5B468F00
for ; Fri, 17 Feb 2006 19:20:48 -0500 (EST)
Subject: Undeliverable mail: Re: is CREATE TABLE not transactional?
From:
To:
Date: Fri, 17 Feb 2006 19:16:22 -0500
Message-ID:
X-MAPI-Message-Class: REPORT.IPM.Note.NDR
MIME-Version: 1.0
Content-Type: multipart/report; report-type="delivery-status"; boundary="_===410849417====server45.appriver.com===_"


--_===410849417====server45.appriver.com===_
Content-Type: text/plain; charset="utf-8"

Failed to deliver to 'dweingart@plsvc.com'
SMTP module(domain [38.209.15.191]) reports:
host 38.209.15.191 says:
550 5.1.1 Mailbox does not exist


--_===410849417====server45.appriver.com===_
Content-Type: message/delivery-status

Reporting-MTA: dns; server45.appriver.com

Original-Recipient: rfc822;
Final-Recipient: rfc822;
Action: failed
Status: 5.0.0

--_===410849417====server45.appriver.com===_
Content-Type: text/rfc822-headers

Received: from [10.238.11.135] (HELO server92.appriver.com)
by server45.appriver.com (CommuniGate Pro SMTP 5.0.6)
with ESMTP id 410849416 for dweingart@plsvc.com; Fri, 17 Feb 2006 19:16:22 -0500
Received: by server92.appriver.com (CommuniGate Pro PIPE 5.0.6)
with PIPE id 80246705; Fri, 17 Feb 2006 19:16:23 -0500
Received: from [213.136.52.31] (HELO lists.mysql.com)
by server92.appriver.com (CommuniGate Pro SMTP 5.0.6)
with SMTP id 80247187 for dweingart@plsvc.com; Fri, 17 Feb 2006 19:16:13 -0500
Received: (qmail 18831 invoked by uid 510); 18 Feb 2006 00:16:03 -0000
Mailing-List: contact perl-help@lists.mysql.com; run by ezmlm
List-ID:
Precedence: bulk
List-Help:
List-Unsubscribe:
List-Post:
List-Archive: http://lists.mysql.com/perl/3758
Delivered-To: mailing list perl@lists.mysql.com
Received: (qmail 18753 invoked by uid 509); 18 Feb 2006 00:16:02 -0000
Received-SPF: neutral (lists.mysql.com: local policy)
Date: Fri, 17 Feb 2006 16:12:21 -0800
From: Tyler MacDonald
Subject: Re: is CREATE TABLE not transactional?
In-reply-to:
To: Darren Duncan
Cc: perl@lists.mysql.com
Message-id: <20060218001221.GC26027@yi.org>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-disposition: inline
References: <20060216230432.GD16423@yi.org>

User-Agent: Mutt/1.5.11+cvs20060126
X-Policy: plsvc.com
X-Note: This Email was scanned by AppRiver SecureTide
X-Warn: BOUNCETRACKER Bounce User Tracking Found
X-Warn: OPTOUT
X-Warn: WEIGHT10
X-Note: Spam Tests Failed: BOUNCETRACKER, OPTOUT, WEIGHT10
X-Note: User Rule Hits:
X-Country-Path: SWEDEN->destination
X-Note-Sending-IP: 213.136.52.31
X-Note-Reverse-DNS: lists2.mysql.com
X-Note-WHTLIST: perl-return-3758-dweingart=plsvc.com@lists.mysql.com

--_===410849417====server45.appriver.com===_--



--ReaqsoxgOBHFXBhH
Content-Type: text/plain; charset=us-ascii


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
--ReaqsoxgOBHFXBhH--