RE: MySQL Transaction Problem
I got it figured out, sort of. My machine was not loading the version of
MySQL I thought it was. Now it is, and I have BDB support. My rollbacks
now work.
Thanks for the help.
David
-Original Message-
From: David Woods [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 09, 2001 10:25 AM
To: 'Paul DuBois'
Cc: [EMAIL PROTECTED]
Subject: RE: MySQL Transaction Problem
I'm sorry, but apparently I'm too green to be able to figure this out.
You are right, when I do the SHOW CREATE TABLE, it tells me my table is
MyISAM. If I use ALTER TABLE Episode TYPE=BDB;, it accepts the command but
does not change the table type.
I am using MySQLD-max-nt, which the manual says should support BDB tables.
But apparently I am missing something, because on my system it doesn't.
I read about configuring, but cannot figure out how to apply the
"/configure --with-berkeley-db" option to an NT service. (This part of the
manual appears to me to only to apply to *nix here, but maybe that just
shows my ignorance.) The output from the executable tells me there is no
such option, but suggests there is a "-skip-bdb" option to turn BDB off,
which I *don't* want.
How do I get BDB working under Windows 2000? Any help would be greatly
appreciated.
David Woods
Wisconsin Center for Education Research
University of Wisconsin, Madison
>I am writing a system that requires transactions. I understand that I need
>to use Transaction-safe tables, such as BDB or InnoDB, but even when I do,
I
>get an error when I try to roll back my transaction.
>
>Here is some SQL that illustrates my problem:
>
>I create a table of type BDB (failure is the same if I use InnoDB):
>
>CREATE TABLE Episodes(
> EpisodeID varchar(100) NOT NULL,
> RecordLock varchar(25),
> Primary Key (EpisodeID))
> TYPE=BDB ;
If you issue a SHOW CREATE TABLE Episodes statement, does the output
indicate that the table is indeed of type BDB? If your server wasn't
compiled with support for transactional tables, the table may default
to MyISAM (and you won't get a warning, alas).
>I put a couple of records in it to have something to work with:
>
>INSERT INTO Episodes
> (EpisodeID, RecordLock)
> VALUES
> ('Boychoir', '');
>
>INSERT INTO Episodes
> (EpisodeID, RecordLock)
> VALUES
> ('Demo', '');
>
>I start a transaction and look at the contents of my table:
>
>BEGIN;
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I change something in the table, still within the transaction and check
that
>it is changed:
>
>UPDATE Episodes
> SET RecordLock = 'DavidW'
> WHERE EpisodeID = 'Boychoir';
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I discover (programmatically) that I need to roll the transaction back, so
I
>do:
>
>ROLLBACK;
>
>I get the following message: ERROR 1196: Warning: Some non-transactional
>changed tables couldn't be rolled back
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I check again, and indeed the data is changed and the rollback failed.
>
>What am I doing wrong? I am using version 3.23.43-nt under Windows 2000.
>
>Thanks for your help,
>David Woods, Ph.D.
>Wisconsin Center for Education Research
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Transaction Problem
I'm sorry, but apparently I'm too green to be able to figure this out.
You are right, when I do the SHOW CREATE TABLE, it tells me my table is
MyISAM. If I use ALTER TABLE Episode TYPE=BDB;, it accepts the command but
does not change the table type.
I am using MySQLD-max-nt, which the manual says should support BDB tables.
But apparently I am missing something, because on my system it doesn't.
I read about configuring, but cannot figure out how to apply the
"/configure --with-berkeley-db" option to an NT service. (This part of the
manual appears to me to only to apply to *nix here, but maybe that just
shows my ignorance.) The output from the executable tells me there is no
such option, but suggests there is a "-skip-bdb" option to turn BDB off,
which I *don't* want.
How do I get BDB working under Windows 2000? Any help would be greatly
appreciated.
David Woods
Wisconsin Center for Education Research
University of Wisconsin, Madison
>I am writing a system that requires transactions. I understand that I need
>to use Transaction-safe tables, such as BDB or InnoDB, but even when I do,
I
>get an error when I try to roll back my transaction.
>
>Here is some SQL that illustrates my problem:
>
>I create a table of type BDB (failure is the same if I use InnoDB):
>
>CREATE TABLE Episodes(
> EpisodeID varchar(100) NOT NULL,
> RecordLock varchar(25),
> Primary Key (EpisodeID))
> TYPE=BDB ;
If you issue a SHOW CREATE TABLE Episodes statement, does the output
indicate that the table is indeed of type BDB? If your server wasn't
compiled with support for transactional tables, the table may default
to MyISAM (and you won't get a warning, alas).
>I put a couple of records in it to have something to work with:
>
>INSERT INTO Episodes
> (EpisodeID, RecordLock)
> VALUES
> ('Boychoir', '');
>
>INSERT INTO Episodes
> (EpisodeID, RecordLock)
> VALUES
> ('Demo', '');
>
>I start a transaction and look at the contents of my table:
>
>BEGIN;
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I change something in the table, still within the transaction and check
that
>it is changed:
>
>UPDATE Episodes
> SET RecordLock = 'DavidW'
> WHERE EpisodeID = 'Boychoir';
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I discover (programmatically) that I need to roll the transaction back, so
I
>do:
>
>ROLLBACK;
>
>I get the following message: ERROR 1196: Warning: Some non-transactional
>changed tables couldn't be rolled back
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I check again, and indeed the data is changed and the rollback failed.
>
>What am I doing wrong? I am using version 3.23.43-nt under Windows 2000.
>
>Thanks for your help,
>David Woods, Ph.D.
>Wisconsin Center for Education Research
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Transaction Problem
>I am writing a system that requires transactions. I understand that I need
>to use Transaction-safe tables, such as BDB or InnoDB, but even when I do, I
>get an error when I try to roll back my transaction.
>
>Here is some SQL that illustrates my problem:
>
>I create a table of type BDB (failure is the same if I use InnoDB):
>
>CREATE TABLE Episodes(
> EpisodeID varchar(100) NOT NULL,
> RecordLock varchar(25),
> Primary Key (EpisodeID))
> TYPE=BDB ;
If you issue a SHOW CREATE TABLE Episodes statement, does the output
indicate that the table is indeed of type BDB? If your server wasn't
compiled with support for transactional tables, the table may default
to MyISAM (and you won't get a warning, alas).
>I put a couple of records in it to have something to work with:
>
>INSERT INTO Episodes
> (EpisodeID, RecordLock)
> VALUES
> ('Boychoir', '');
>
>INSERT INTO Episodes
> (EpisodeID, RecordLock)
> VALUES
> ('Demo', '');
>
>I start a transaction and look at the contents of my table:
>
>BEGIN;
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I change something in the table, still within the transaction and check that
>it is changed:
>
>UPDATE Episodes
> SET RecordLock = 'DavidW'
> WHERE EpisodeID = 'Boychoir';
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I discover (programmatically) that I need to roll the transaction back, so I
>do:
>
>ROLLBACK;
>
>I get the following message: ERROR 1196: Warning: Some non-transactional
>changed tables couldn't be rolled back
>
>SELECT EpisodeID, RecordLock FROM Episodes;
>
>I check again, and indeed the data is changed and the rollback failed.
>
>What am I doing wrong? I am using version 3.23.43-nt under Windows 2000.
>
>Thanks for your help,
>David Woods, Ph.D.
>Wisconsin Center for Education Research
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL transaction problem (Again...)
I have started testing MySQL 3.23.30g with latest MySQL provided Berkeley source (3.2.3g) I am using RedHat 6.2 and RPM source of MySQL. I compiled MySQL and altered my existing tables to BDB. So far my tests show that transactions are working. They are working for new BDB tables and the altered MyISAM tables. I would like to get a sense of how transactions are working for others. I get the feeling that not many MySQL users are testing/using the transaction support. - / Rick Mehalick Senior Consultant / Shell Services International SSI-GPAX / Phone: 281-544-5092(WCK) / Fax:281-544-2646(WCK) / email: [EMAIL PROTECTED] - -Original Message- From: Tomi Junnila [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 11, 2001 12:59 AM To: '[EMAIL PROTECTED]' Subject: Re: MySQL transaction problem (Again...) * Carfield Yim <[EMAIL PROTECTED]> wrote on 11.01.01 06:19: > MySQL 3.23.29 or 3.23.30, with RedHat Linux 7.0, compile from tarball > source, can't rollback. > > Do you mean that it is platform?? There are errors build with RedHat 7.0? Hmm. It certainly does seem so as there have been a few reports after my initial report on December 4 when I encountered this problem. I do believe there would have been more if the problem was more widespread. I'm using RH7, and the versions I have noticed this behaviour in are 3.23.28, 3.23.29 and 3.23.30. I upgraded my glibc on December 21 from 2.1.92 to 2.2.9, and my gcc from 2.96-54 to 2.96-69. This did not help. I've tried compiling MySQL from the source RPMS (first the RedHat ones, hacking them with the correct version and Berkeley DB support; then the MySQL ones, putting the Berkeley DB support in them), and manually from the tarball sources. When I first encountered this, rollback did work for some tables but not others. Nowadays, it doesn't seem to work for any tables. This may be due to the change of MySQL and/or Berkeley DB versions, or the glibc/gcc update, or just some difference in the build itself (which I doubt). I don't know which change to blame as I didn't test rollback except with the table that rollback didn't work with initially, and only now tested rollback on other tables and noticed it didn't work at all anymore. For example, this used to work but now it doesn't anymore: create table tt (id integer) type=bdb; show table status; # Ensure it actually is BDB set autocommit=0; begin; insert into tt (id) values (55); rollback; select * from tt; # Earlier this didn't result in anything, nowadays it does # have the row id=55 -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL transaction problem (Again...)
* Carfield Yim <[EMAIL PROTECTED]> wrote on 11.01.01 06:19: > MySQL 3.23.29 or 3.23.30, with RedHat Linux 7.0, compile from tarball > source, can't rollback. > > Do you mean that it is platform?? There are errors build with RedHat 7.0? Hmm. It certainly does seem so as there have been a few reports after my initial report on December 4 when I encountered this problem. I do believe there would have been more if the problem was more widespread. I'm using RH7, and the versions I have noticed this behaviour in are 3.23.28, 3.23.29 and 3.23.30. I upgraded my glibc on December 21 from 2.1.92 to 2.2.9, and my gcc from 2.96-54 to 2.96-69. This did not help. I've tried compiling MySQL from the source RPMS (first the RedHat ones, hacking them with the correct version and Berkeley DB support; then the MySQL ones, putting the Berkeley DB support in them), and manually from the tarball sources. When I first encountered this, rollback did work for some tables but not others. Nowadays, it doesn't seem to work for any tables. This may be due to the change of MySQL and/or Berkeley DB versions, or the glibc/gcc update, or just some difference in the build itself (which I doubt). I don't know which change to blame as I didn't test rollback except with the table that rollback didn't work with initially, and only now tested rollback on other tables and noticed it didn't work at all anymore. For example, this used to work but now it doesn't anymore: create table tt (id integer) type=bdb; show table status; # Ensure it actually is BDB set autocommit=0; begin; insert into tt (id) values (55); rollback; select * from tt; # Earlier this didn't result in anything, nowadays it does # have the row id=55 -- Tomi Junnila <[EMAIL PROTECTED]> http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL transaction problem (Again...)
MySQL 3.23.29 or 3.23.30, with RedHat Linux 7.0, compile from tarball source, can't rollback. Do you mean that it is platform?? There are errors build with RedHat 7.0? On Wed, 10 Jan 2001, goEbusiness.com Mail Lists wrote: > MySQL 3.23.29a-gamma > Solaris 7 > > No problems here. > > On Wed, 27 Dec 2000, Carfield Yim wrote: > > > Hi all, I have bulid MySQL from source. From the output of ./configure, I > > know that MySQL know where is BDB. > > After table bulid complete, I create the table for testing using following > > SQL: > > > > create table test (id integer) type = BDB > > set autocommit=0; > > begin; > > insert into test values (1); > > rollback; > > > > But table test don't rollback as expected, where do I get wrong?
