Re: store transaction rollback information
On 26 Jul 2012, at 21:43, James Devine wrote: > I have a large series of mysql changes(inserts/deletes/updates) taking > place in a transaction. After committing there may be some times where I > need to roll those changes back later on. Is there an easy way of > determining what was changed in a transaction in a way I can store it and > rollback later? James, The way you describe it sounds like you have a modeling issue with your system. Committed transactions are not supposed to be rolled back. Your System Architect has to arrange things in such a way that all the information required to decide if a change to the database can be made permanent is available to the application *before* COMMIT-time. Until then, you're supposed to hold your transaction (and all locks resulting from it) open and uncommitted. In other words: once a transaction is committed, the changes are permanent. Rolling it back may still be possible, but it will be complicated and extremely expensive, computationally speaking. I strongly recommend you to review your design choices. I hope this helps. Kind regards, -- Luis Motta Campos is a DBA, Foodie, and Photographer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
store transaction rollback information
I have a large series of mysql changes(inserts/deletes/updates) taking place in a transaction. After committing there may be some times where I need to roll those changes back later on. Is there an easy way of determining what was changed in a transaction in a way I can store it and rollback later? Thanks
Re: Mysql Schema design & Rollback necessity Question
Hello Lightingale,
Lightingale wrote:
Hi there,
I am new to using mysql. I want to prepare an application for my employer. The
application will be accessed by staff from as many as 10 different departments
such as sales, marketing, admin, finance etc. The users will be using DML
commands on the tables. My question has two parts:
First problem: you are letting your users run direct commands against
the database.
One of the biggest roles in an application is to isolate and protect the
data from stupid user mistakes. Not only should your application
filter, validate, and sanitize their input but you also need to
encapsulate (with your application code) all of the functions they need
to perform against the data. That way, if there is a problem with how
things are going you will know exactly where to look.
If it is a requirement that the users change data directly, then why
write an application in the first place?
Part I:
While designing the schema of the database, I have two choices:
Scenarios:
1. Create multiple tables, one for each department. The relationship for most
of the tables is one-to-one.
2. Create one master table so that each department updates its respective columns in the same table.
Please advise which choice is better.
You actually have more choices than that. You could create multiple
databases, each with a full compliment of application tables.
#2 may be a bad option - it's fine to have columns that only certain
users can update but if you propose to have several sets of columns
copies where each set belongs to a single group, that would be horrible.
Work up from a rational database design and build an application to
support it. Try very hard to not design a database that works with your
code. Databases operate most efficiently when you use "set theory" and
not "iterative application design principles" to access your data. What
that means, specifically, is avoid writing code that does dozens or
hundreds of small single-row manipulations when one statement could be
written to process the entire batch of data. Of course, there are rare
exceptional cases to consider but at this stage, I don't think you are
there yet.
Questions:
1. With single table will table locking become an issue if multiple users edit
the table simultaneously or is it something that mysql can handle without
problem?
It depends on how you use the table, how it is organized, and which
storage engine you choose.
2. What is the maximum recommended size of a table for mysql? How many columns
should be master table should have ? Is it recommended to design a master table
having more than 200 columns?
For me, the design any table with more than about 20 or so columns is
suspicious. Please do some homework and learn more about relational data
modeling and the principles of "normalization"
We, the other members on the list, will be happy to answer any specific
questions you may have.
PART II:
Secondly, I am using PHP, Mysql, ADODB, APACHE on windows 7 platform. This is
my typical DML command:
$query="update users set
id='$id',password=\"$password\",pin=\"$pin\",hint=\"$hint\",fname=\"$fname\",lname=\"$lname\",manager=\"$manager\",deptt=\"$deptt\"
where username=\"$myuser\"";
if ($debug && $dbgusr == $ses_username) { echo("$query"); }
if (!($rs1 = $db->execute("$query")))
{
DisplayErrMsg(sprintf("Data Select Error: %d:%s\n", mysql_errno(),
mysql_error()));
return 0;
}
else
{
// updatelog($id,"users","$query","usrmgr.php",$ses_username,$myip);
DispMsg("User Profile edited successfully");
}
I am not using any rollback statement to rollback the db if the DML command is
not completed successfully. Is it advisable to use rollback? If it is how
should I modify the above statement to include it ?
Thanks in advance for your help.
As mentioned in the other reply, ROLLBACK only applies to active
transactions. Please do some additional homework and figure out which
storage engines support transactions and how you start and end a
multiple-statement transaction.
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql Schema design & Rollback necessity Question
Hi,
I am new to using mysql. I want to prepare an application for my employer.
The
application will be accessed by staff from as many as 10 different
departments
such as sales, marketing, admin, finance etc. The users will be using DML
commands
on the tables. My question has two parts:
Part I:
While designing the schema of the database, I have two choices:
Scenarios:
1. Create multiple tables, one for each department. The relationship for
most of the tables is one-to-one.
2. Create one master table so that each department updates its respective
columns in the same table.
Please advise which choice is better.
I fail to see what kind of data a table ("one for each department") would
hold?
How would a single table ("one master table") hold the data for the
processes of this
application?
What should the application do?
Questions:
1. With single table will table locking become an issue if multiple users
edit the table simultaneously
or is it something that mysql can handle without problem?
It depends on the storage engine.
2. What is the maximum recommended size of a table for mysql? How many
columns should be
master table should have ? Is it recommended to design a master table
having more than 200 columns?
Have you ever read a book on database design & normalization?
PART II:
Secondly, I am using PHP, Mysql, ADODB, APACHE on windows 7 platform. This
is my typical DML command:
I am not using any rollback statement to rollback the db if the DML command
is not completed successfully. Is
it advisable to use rollback? If it is how should I modify the above
statement to include it ?
If the DML command failed and modifies a single row only, the "rollback"
won't do anything,
but if it updates multiple rows or trying to do a "unit of work" inside the
same transaction,
things become different.
Have you ever read about atomicy on database transactions?
With regards,
Martijn Tonies
Upscene Productions
http://www.upscene.com
Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!
Database questions? Check the forum:
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql Schema design & Rollback necessity Question
Hi there,
I am new to using mysql. I want to prepare an application for my employer. The
application will be accessed by staff from as many as 10 different departments
such as sales, marketing, admin, finance etc. The users will be using DML
commands on the tables. My question has two parts:
Part I:
While designing the schema of the database, I have two choices:
Scenarios:
1. Create multiple tables, one for each department. The relationship for most
of the tables is one-to-one.
2. Create one master table so that each department updates its respective
columns in the same table.
Please advise which choice is better.
Questions:
1. With single table will table locking become an issue if multiple users edit
the table simultaneously or is it something that mysql can handle without
problem?
2. What is the maximum recommended size of a table for mysql? How many columns
should be master table should have ? Is it recommended to design a master table
having more than 200 columns?
PART II:
Secondly, I am using PHP, Mysql, ADODB, APACHE on windows 7 platform. This is
my typical DML command:
$query="update users set
id='$id',password=\"$password\",pin=\"$pin\",hint=\"$hint\",fname=\"$fname\",lname=\"$lname\",manager=\"$manager\",deptt=\"$deptt\"
where username=\"$myuser\"";
if ($debug && $dbgusr == $ses_username) { echo("$query"); }
if (!($rs1 = $db->execute("$query")))
{
DisplayErrMsg(sprintf("Data Select Error: %d:%s\n", mysql_errno(),
mysql_error()));
return 0;
}
else
{
// updatelog($id,"users","$query","usrmgr.php",$ses_username,$myip);
DispMsg("User Profile edited successfully");
}
I am not using any rollback statement to rollback the db if the DML command is
not completed successfully. Is it advisable to use rollback? If it is how
should I modify the above statement to include it ?
Thanks in advance for your help.
Regards,
Lightingales
Re: START TRANSACTION COMMIT ROLLBACK
There seems to be some confusion about 'multi-db'.Within a single MySQL instance, assuming that all your tables are a transactional type (InnoDB isn't the only one), you don't have to do anything special to cross database boundaries. XA is required if you plan to spread your transactions out across multiple database instances. Right, makes sense, thanks for clearing that up. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: START TRANSACTION COMMIT ROLLBACK
There seems to be some confusion about 'multi-db'.Within a single MySQL instance, assuming that all your tables are a transactional type (InnoDB isn't the only one), you don't have to do anything special to cross database boundaries. XA is required if you plan to spread your transactions out across multiple database instances. - michael On Wed, Oct 28, 2009 at 10:08 AM, Paul DuBois wrote: > If all the tables are InnoDB, XA isn't needed. It doesn't matter whether all > tables are in the same database. > > On Oct 28, 2009, at 5:48 AM, Martijn Tonies wrote: > >> Ah, works for InnoDB I see. >> >> Nice. >> >> >> With regards, >> >> Martijn Tonies >> Upscene Productions >> http://www.upscene.com >> >> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL >> Anywhere, MySQL, InterBase, NexusDB and Firebird! >> >> Database questions? Check the forum: >> http://www.databasedevelopmentforum.com >> >> >> Looks to me we should use XA transaction syntax instead. Check this: >> >> >> http://dev.mysql.com/doc/refman/5.0/en/xa.html >> >> Thanks, >> YY >> >> >> >> 2009/10/28 Martijn Tonies >> >> Michael, >> >> Does MySQL support multi-db transactions? >> >> With regards, >> >> Martijn Tonies >> Upscene Productions >> http://www.upscene.com >> >> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL >> Anywhere, MySQL, InterBase, NexusDB and Firebird! >> >> Database questions? Check the forum: >> http://www.databasedevelopmentforum.com >> >> >> >> >> That is correct. Many db interfaces off programmatic abstractions of >> these facilities, but you may certainly just issue the statments. >> >> START TRANSACTION >> >> INSERT that >> UPDATE that >> >> on success: COMMIT >> >> on error: ROLLBACK >> >> - michael dykman >> >> >> >> On Wed, Oct 28, 2009 at 12:07 AM, Mosaed AlZamil >> wrote: >> >> Hello Everyone, >> I am a newbie using innodb. >> How can I implement START TRANSACTION COMMIT ROLLBACK when I need to >> update >> two tables >> that are located in two different databases. Would a single START >> TRANSACTION be sufficient ? >> Any help would be appreciated. >> TIA >> Mos >> >> >> >> >> >> -- >> - michael dykman >> - mdyk...@gmail.com >> >> "May you live every day of your life." >> Jonathan Swift > > -- > Paul DuBois > Sun Microsystems / MySQL Documentation Team > Madison, Wisconsin, USA > www.mysql.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com "May you live every day of your life." Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: START TRANSACTION COMMIT ROLLBACK
If all the tables are InnoDB, XA isn't needed. It doesn't matter whether all tables are in the same database. On Oct 28, 2009, at 5:48 AM, Martijn Tonies wrote: Ah, works for InnoDB I see. Nice. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com Looks to me we should use XA transaction syntax instead. Check this: http://dev.mysql.com/doc/refman/5.0/en/xa.html Thanks, YY 2009/10/28 Martijn Tonies Michael, Does MySQL support multi-db transactions? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com That is correct. Many db interfaces off programmatic abstractions of these facilities, but you may certainly just issue the statments. START TRANSACTION INSERT that UPDATE that on success: COMMIT on error: ROLLBACK - michael dykman On Wed, Oct 28, 2009 at 12:07 AM, Mosaed AlZamil > wrote: Hello Everyone, I am a newbie using innodb. How can I implement START TRANSACTION COMMIT ROLLBACK when I need to update two tables that are located in two different databases. Would a single START TRANSACTION be sufficient ? Any help would be appreciated. TIA Mos -- - michael dykman - mdyk...@gmail.com "May you live every day of your life." Jonathan Swift -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: START TRANSACTION COMMIT ROLLBACK
Ah, works for InnoDB I see. Nice. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com Looks to me we should use XA transaction syntax instead. Check this: http://dev.mysql.com/doc/refman/5.0/en/xa.html Thanks, YY 2009/10/28 Martijn Tonies Michael, Does MySQL support multi-db transactions? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com That is correct. Many db interfaces off programmatic abstractions of these facilities, but you may certainly just issue the statments. START TRANSACTION INSERT that UPDATE that on success: COMMIT on error: ROLLBACK - michael dykman On Wed, Oct 28, 2009 at 12:07 AM, Mosaed AlZamil wrote: Hello Everyone, I am a newbie using innodb. How can I implement START TRANSACTION COMMIT ROLLBACK when I need to update two tables that are located in two different databases. Would a single START TRANSACTION be sufficient ? Any help would be appreciated. TIA Mos -- - michael dykman - mdyk...@gmail.com "May you live every day of your life." Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=m.ton...@upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yuan4...@gmail.com
Re: START TRANSACTION COMMIT ROLLBACK
Looks to me we should use XA transaction syntax instead. Check this: http://dev.mysql.com/doc/refman/5.0/en/xa.html Thanks, YY 2009/10/28 Martijn Tonies > Michael, > > Does MySQL support multi-db transactions? > > With regards, > > Martijn Tonies > Upscene Productions > http://www.upscene.com > > Download Database Workbench for Oracle, MS SQL Server, Sybase SQL > Anywhere, MySQL, InterBase, NexusDB and Firebird! > > Database questions? Check the forum: > http://www.databasedevelopmentforum.com > > > > > That is correct. Many db interfaces off programmatic abstractions of > these facilities, but you may certainly just issue the statments. > > START TRANSACTION > > INSERT that > UPDATE that > > on success: COMMIT > > on error: ROLLBACK > > - michael dykman > > > > On Wed, Oct 28, 2009 at 12:07 AM, Mosaed AlZamil > wrote: > >> Hello Everyone, >> I am a newbie using innodb. >> How can I implement START TRANSACTION COMMIT ROLLBACK when I need to >> update >> two tables >> that are located in two different databases. Would a single START >> TRANSACTION be sufficient ? >> Any help would be appreciated. >> TIA >> Mos >> >> > > > -- > - michael dykman > - mdyk...@gmail.com > > "May you live every day of your life." > Jonathan Swift > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=m.ton...@upscene.com > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=yuan4...@gmail.com > >
Re: START TRANSACTION COMMIT ROLLBACK
Michael, Does MySQL support multi-db transactions? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com That is correct. Many db interfaces off programmatic abstractions of these facilities, but you may certainly just issue the statments. START TRANSACTION INSERT that UPDATE that on success: COMMIT on error: ROLLBACK - michael dykman On Wed, Oct 28, 2009 at 12:07 AM, Mosaed AlZamil wrote: Hello Everyone, I am a newbie using innodb. How can I implement START TRANSACTION COMMIT ROLLBACK when I need to update two tables that are located in two different databases. Would a single START TRANSACTION be sufficient ? Any help would be appreciated. TIA Mos -- - michael dykman - mdyk...@gmail.com "May you live every day of your life." Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=m.ton...@upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: START TRANSACTION COMMIT ROLLBACK
That is correct. Many db interfaces off programmatic abstractions of these facilities, but you may certainly just issue the statments. START TRANSACTION INSERT that UPDATE that on success: COMMIT on error: ROLLBACK - michael dykman On Wed, Oct 28, 2009 at 12:07 AM, Mosaed AlZamil wrote: > Hello Everyone, > I am a newbie using innodb. > How can I implement START TRANSACTION COMMIT ROLLBACK when I need to update > two tables > that are located in two different databases. Would a single START > TRANSACTION be sufficient ? > Any help would be appreciated. > TIA > Mos > -- - michael dykman - mdyk...@gmail.com "May you live every day of your life." Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
START TRANSACTION COMMIT ROLLBACK
Hello Everyone, I am a newbie using innodb. How can I implement START TRANSACTION COMMIT ROLLBACK when I need to update two tables that are located in two different databases. Would a single START TRANSACTION be sufficient ? Any help would be appreciated. TIA Mos
Re: innodb rollback 30x slower than commit normal?
nik...@doppelganger.com (Nikita Tovstoles) writes: > We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTED. > We use Hibernate and optimistic concurrency, so periodically concurrent write > attempts cause app-level Exceptions that trigger rollbacks (and then we retry > tx). We've added app-level caching and turned down our tomcat NIO thread > count to just 8 (very little contention inside the app) but now we're seeing > that rollbacks appear to be up to 30x slower than commits?! Is that normal? > > Here's a typical TX: > > Set autocommit=0; > Select * from users where name="bob"; > Update users set visit_count=X where id=bobId and version=Y > Commit; > Set autocommit=1; > > When this tx is executed about 100 times/sec, appserver latency is about > 10-15 ms per http request (including db time). However, when instead of > commit a 'rollback' is issued, the latency spikes to 600-1100 ms (nearly all > of that time in appserver appears to be spent waiting on db). > > So is that expected cost of a rollback? InnoDB is heavily optimised and assumes that a transaction will commit successfully. As such it's not optimised to do the rollback, and as such a rollback *IS* very expensive. I've seen similar behaviour on some servers I use at work so what you are seeing is I think normal. > Can anything be done to speed it up? I'm not aware of anything so I think you have to accept it and make sure that where possible you try to avoid situations where you need to rollback. That's not always possible of course but sometimes the scope of the transaction can be narrowed and that should help a bit. However in your example you could easily do a single "atomic" update involving the SELECT and UPDATE. That would be much easier as you would either run the "combined UPDATE" or not. Perhaps that would work for you? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
innodb rollback 30x slower than commit normal?
We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTED. We use Hibernate and optimistic concurrency, so periodically concurrent write attempts cause app-level Exceptions that trigger rollbacks (and then we retry tx). We've added app-level caching and turned down our tomcat NIO thread count to just 8 (very little contention inside the app) but now we're seeing that rollbacks appear to be up to 30x slower than commits?! Is that normal? Here's a typical TX: Set autocommit=0; Select * from users where name="bob"; Update users set visit_count=X where id=bobId and version=Y Commit; Set autocommit=1; When this tx is executed about 100 times/sec, appserver latency is about 10-15 ms per http request (including db time). However, when instead of commit a 'rollback' is issued, the latency spikes to 600-1100 ms (nearly all of that time in appserver appears to be spent waiting on db). So is that expected cost of a rollback? Can anything be done to speed it up? Thanks! -nikita
Re: innodb rollback question
Thanks everyone for the responses. Will put me on the right track here..something that was rolling through my head but I couldn't really define. I will be blogging about this later as I think it is fairly important, but often not understood by beginning/mid-level dbas. thank again, Keith William Newton wrote: Use smaller transactions that don't have 140 million rows. When attempting an action with important data, make sure you can survive the actions failure. If you can't, then you need to think of a different way of doing it that will allow a recoverable failure. - Original Message From: B. Keith Murphy <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Friday, November 16, 2007 10:29:17 AM Subject: innodb rollback question I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb rollback question
At 02:05 PM 11/16/2007, you wrote: How do you import the data? Load data from file is faster thought so better to export myisam -> file and then you do load data from file make sure you set autocommit=0 to make it faster Ady, Sure but won't the entire Load Data will still be wrapped in a single transaction? How long would it take to rollback that transaction if it had over 130 million rows? Mike On Nov 17, 2007 12:29 AM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: > I have something to throw out. I just got done importing 140 million > rows from a myisam table to a innodb table. While it worked I had a > thought about 3/4ths of the way through. What if the transaction had > been canceled about 130 million rows in? It would have taken weeks to > roll back. > > The only way I know of to stop a rollback like that is to bring out the > sledgehammer and kill the mysql processes and then rip out the entire > database and re-import. Faster than the rollback granted - but not very > elegant. Not something you want to do on a production server either > (the only time I had this happen it was in a test environment so there > were no consequences to my subsequent actions :) > > Any better way to do this? > > Thanks, > > Keith > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb rollback question
How do you import the data? Load data from file is faster thought so better to export myisam -> file and then you do load data from file make sure you set autocommit=0 to make it faster On Nov 17, 2007 12:29 AM, B. Keith Murphy <[EMAIL PROTECTED]> wrote: > I have something to throw out. I just got done importing 140 million > rows from a myisam table to a innodb table. While it worked I had a > thought about 3/4ths of the way through. What if the transaction had > been canceled about 130 million rows in? It would have taken weeks to > roll back. > > The only way I know of to stop a rollback like that is to bring out the > sledgehammer and kill the mysql processes and then rip out the entire > database and re-import. Faster than the rollback granted - but not very > elegant. Not something you want to do on a production server either > (the only time I had this happen it was in a test environment so there > were no consequences to my subsequent actions :) > > Any better way to do this? > > Thanks, > > Keith > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/
Re: innodb rollback question
Use smaller transactions that don't have 140 million rows. When attempting an action with important data, make sure you can survive the actions failure. If you can't, then you need to think of a different way of doing it that will allow a recoverable failure. - Original Message From: B. Keith Murphy <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Friday, November 16, 2007 10:29:17 AM Subject: innodb rollback question I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
innodb rollback question
I have something to throw out. I just got done importing 140 million rows from a myisam table to a innodb table. While it worked I had a thought about 3/4ths of the way through. What if the transaction had been canceled about 130 million rows in? It would have taken weeks to roll back. The only way I know of to stop a rollback like that is to bring out the sledgehammer and kill the mysql processes and then rip out the entire database and re-import. Faster than the rollback granted - but not very elegant. Not something you want to do on a production server either (the only time I had this happen it was in a test environment so there were no consequences to my subsequent actions :) Any better way to do this? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote: Is there any difference between calling rollback or commit on a transaction that did not alter data? For example, not a read-only transaction but a transaction that only performed read-only selects. Any difference in performance between calling rollback or commit? I know they are functionally the same at the high level. I just thought of a difference. If you are using LOCK TABLES and UNLOCK TABLES, these interact differently with COMMIT and ROLLBACK. More info: http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
Well, assume a higher level abstraction that does not give clients to
that abstraction access to the raw connection. It only has methods like
update, search, commit, or rollback. What the connection is doing is a
kind of implementation detail.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 18, 2007 10:00 AM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
I realize that wasn't the question, but it does seem like a lot of
trouble to get the equivalent of setAutoCommit(true);
On 9/17/07, Robert DiFalco <[EMAIL PROTECTED]> wrote:
> Sure, but that wasn't really the question.
>
> -Original Message-
> From: Michael Dykman [mailto:[EMAIL PROTECTED]
> Sent: Monday, September 17, 2007 2:56 PM
> To: Robert DiFalco
> Cc: Baron Schwartz; mysql@lists.mysql.com
> Subject: Re: Rollback on a Transaction with No Updates
>
> If your transaction are only 1 query deep, why use them at all? An
> individual query is already atomic, regardless of table type/server
> mode.
>
> - michael dkyman
>
>
> On 9/17/07, Robert DiFalco <[EMAIL PROTECTED]> wrote:
> > While it is functionally equivalent I wonder if it the code paths
> > taken are the same. I suppose for both commit and rollback mysql
> > would
>
> > have to look for any pending work, if there were none both would do
> nothing.
> > That's what makes me think that there is probably no performance
> > difference between the two. I ask this because my programmers like
> > to do
> > this:
> >
> > con = ...
> > try
> > {
> >queryOnlyWith( con );
> > }
> > finally
> > {
> >con.rollback();
> > }
> >
> > And I wanted to make sure that this would perform the same and act
> > the
>
> > same as issuing a commit (unless there was an exception but I'm not
> > analyzing that case).
> >
> > -Original Message-
> > From: Baron Schwartz [mailto:[EMAIL PROTECTED]
> > Sent: Monday, September 17, 2007 2:36 PM
> > To: Robert DiFalco
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Rollback on a Transaction with No Updates
> >
> > Robert DiFalco wrote:
> > > Is there any difference between calling rollback or commit on a
> > > transaction that did not alter data? For example, not a read-only
> > > transaction but a transaction that only performed read-only
selects.
> > > Any difference in performance between calling rollback or commit?
> > > I know they are functionally the same at the high level.
> >
> > The only thing I could think of was possibly rollback would leave
> > open
>
> > transaction and its read view if you are running in REPEATABLE READ
> > isolation mode, whereas commit begins a new transaction and discards
> > the read view. But I just tested that, and both commands start a
> > new transaction and discard the read view.
> >
> > That's a long way of saying they are functionally equivalent as far
> > as
>
> > I know, as long as there are no changes to discard.
> >
> > Baron
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
> --
> - michael dykman
> - [EMAIL PROTECTED]
>
> - All models are wrong. Some models are useful.
>
>
>
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback on a Transaction with No Updates
I realize that wasn't the question, but it does seem like a lot of
trouble to get the equivalent of setAutoCommit(true);
On 9/17/07, Robert DiFalco <[EMAIL PROTECTED]> wrote:
> Sure, but that wasn't really the question.
>
> -Original Message-
> From: Michael Dykman [mailto:[EMAIL PROTECTED]
> Sent: Monday, September 17, 2007 2:56 PM
> To: Robert DiFalco
> Cc: Baron Schwartz; mysql@lists.mysql.com
> Subject: Re: Rollback on a Transaction with No Updates
>
> If your transaction are only 1 query deep, why use them at all? An
> individual query is already atomic, regardless of table type/server
> mode.
>
> - michael dkyman
>
>
> On 9/17/07, Robert DiFalco <[EMAIL PROTECTED]> wrote:
> > While it is functionally equivalent I wonder if it the code paths
> > taken are the same. I suppose for both commit and rollback mysql would
>
> > have to look for any pending work, if there were none both would do
> nothing.
> > That's what makes me think that there is probably no performance
> > difference between the two. I ask this because my programmers like to
> > do
> > this:
> >
> > con = ...
> > try
> > {
> >queryOnlyWith( con );
> > }
> > finally
> > {
> >con.rollback();
> > }
> >
> > And I wanted to make sure that this would perform the same and act the
>
> > same as issuing a commit (unless there was an exception but I'm not
> > analyzing that case).
> >
> > -Original Message-
> > From: Baron Schwartz [mailto:[EMAIL PROTECTED]
> > Sent: Monday, September 17, 2007 2:36 PM
> > To: Robert DiFalco
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Rollback on a Transaction with No Updates
> >
> > Robert DiFalco wrote:
> > > Is there any difference between calling rollback or commit on a
> > > transaction that did not alter data? For example, not a read-only
> > > transaction but a transaction that only performed read-only selects.
> > > Any difference in performance between calling rollback or commit? I
> > > know they are functionally the same at the high level.
> >
> > The only thing I could think of was possibly rollback would leave open
>
> > transaction and its read view if you are running in REPEATABLE READ
> > isolation mode, whereas commit begins a new transaction and discards
> > the read view. But I just tested that, and both commands start a new
> > transaction and discard the read view.
> >
> > That's a long way of saying they are functionally equivalent as far as
>
> > I know, as long as there are no changes to discard.
> >
> > Baron
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
> --
> - michael dykman
> - [EMAIL PROTECTED]
>
> - All models are wrong. Some models are useful.
>
>
>
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
Sure, but that wasn't really the question.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:56 PM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
If your transaction are only 1 query deep, why use them at all? An
individual query is already atomic, regardless of table type/server
mode.
- michael dkyman
On 9/17/07, Robert DiFalco <[EMAIL PROTECTED]> wrote:
> While it is functionally equivalent I wonder if it the code paths
> taken are the same. I suppose for both commit and rollback mysql would
> have to look for any pending work, if there were none both would do
nothing.
> That's what makes me think that there is probably no performance
> difference between the two. I ask this because my programmers like to
> do
> this:
>
> con = ...
> try
> {
>queryOnlyWith( con );
> }
> finally
> {
>con.rollback();
> }
>
> And I wanted to make sure that this would perform the same and act the
> same as issuing a commit (unless there was an exception but I'm not
> analyzing that case).
>
> -Original Message-
> From: Baron Schwartz [mailto:[EMAIL PROTECTED]
> Sent: Monday, September 17, 2007 2:36 PM
> To: Robert DiFalco
> Cc: mysql@lists.mysql.com
> Subject: Re: Rollback on a Transaction with No Updates
>
> Robert DiFalco wrote:
> > Is there any difference between calling rollback or commit on a
> > transaction that did not alter data? For example, not a read-only
> > transaction but a transaction that only performed read-only selects.
> > Any difference in performance between calling rollback or commit? I
> > know they are functionally the same at the high level.
>
> The only thing I could think of was possibly rollback would leave open
> transaction and its read view if you are running in REPEATABLE READ
> isolation mode, whereas commit begins a new transaction and discards
> the read view. But I just tested that, and both commands start a new
> transaction and discard the read view.
>
> That's a long way of saying they are functionally equivalent as far as
> I know, as long as there are no changes to discard.
>
> Baron
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rollback on a Transaction with No Updates
Is there any difference between calling rollback or commit on a transaction that did not alter data? For example, not a read-only transaction but a transaction that only performed read-only selects. Any difference in performance between calling rollback or commit? I know they are functionally the same at the high level. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback on a Transaction with No Updates
If your transaction are only 1 query deep, why use them at all? An
individual query is already atomic, regardless of table type/server
mode.
- michael dkyman
On 9/17/07, Robert DiFalco <[EMAIL PROTECTED]> wrote:
> While it is functionally equivalent I wonder if it the code paths taken
> are the same. I suppose for both commit and rollback mysql would have to
> look for any pending work, if there were none both would do nothing.
> That's what makes me think that there is probably no performance
> difference between the two. I ask this because my programmers like to do
> this:
>
> con = ...
> try
> {
>queryOnlyWith( con );
> }
> finally
> {
>con.rollback();
> }
>
> And I wanted to make sure that this would perform the same and act the
> same as issuing a commit (unless there was an exception but I'm not
> analyzing that case).
>
> -Original Message-
> From: Baron Schwartz [mailto:[EMAIL PROTECTED]
> Sent: Monday, September 17, 2007 2:36 PM
> To: Robert DiFalco
> Cc: mysql@lists.mysql.com
> Subject: Re: Rollback on a Transaction with No Updates
>
> Robert DiFalco wrote:
> > Is there any difference between calling rollback or commit on a
> > transaction that did not alter data? For example, not a read-only
> > transaction but a transaction that only performed read-only selects.
> > Any difference in performance between calling rollback or commit? I
> > know they are functionally the same at the high level.
>
> The only thing I could think of was possibly rollback would leave open
> transaction and its read view if you are running in REPEATABLE READ
> isolation mode, whereas commit begins a new transaction and discards the
> read view. But I just tested that, and both commands start a new
> transaction and discard the read view.
>
> That's a long way of saying they are functionally equivalent as far as I
> know, as long as there are no changes to discard.
>
> Baron
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
While it is functionally equivalent I wonder if it the code paths taken
are the same. I suppose for both commit and rollback mysql would have to
look for any pending work, if there were none both would do nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like to do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
> Is there any difference between calling rollback or commit on a
> transaction that did not alter data? For example, not a read-only
> transaction but a transaction that only performed read-only selects.
> Any difference in performance between calling rollback or commit? I
> know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards the
read view. But I just tested that, and both commands start a new
transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far as I
know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote: Is there any difference between calling rollback or commit on a transaction that did not alter data? For example, not a read-only transaction but a transaction that only performed read-only selects. Any difference in performance between calling rollback or commit? I know they are functionally the same at the high level. The only thing I could think of was possibly rollback would leave open transaction and its read view if you are running in REPEATABLE READ isolation mode, whereas commit begins a new transaction and discards the read view. But I just tested that, and both commands start a new transaction and discard the read view. That's a long way of saying they are functionally equivalent as far as I know, as long as there are no changes to discard. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ROLLBACK/COMMIT in Stored Procedures
Hi, In my stored procedures, i want to ROLLBACK when I encounter any invalid values. However, as it happens, I cannot because MySQL does not support COMMIT/ROLLBACK functionality right now (as of ver. 5.0.22 on WinXP Pro). I am setting session variables (Set @XX="Error Message') according ot each anomally I find in the IN args of the procedures and inserting duplicate values in a temporary table to make MySQL throw me a duplicate-key error that I can then check from VB6 using another procedure giving me the @XX value. Is this the correct way? Is there any other better way of doing the same thing? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ROLLBACK question.
Hi, > How to know MySQL enable for ROLLBACK ? Use InnoDB tables. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ROLLBACK question.
Dear Sir, How to know MySQL enable for ROLLBACK ? Thanks you and best regards, -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
| 0| | Select_range | 0| | Select_range_check| 0| | Select_scan | 2| | Slave_open_temp_tables| 0| | Slave_retried_transactions| 0| | Slave_running | OFF | | Slow_launch_threads | 0| | Slow_queries | 0| | Sort_merge_passes | 0| | Sort_range| 0| | Sort_rows | 0| | Sort_scan | 0| | Table_locks_immediate | 12 | | Table_locks_waited| 0| | Tc_log_max_pages_used | 0| | Tc_log_page_size | 0| | Tc_log_page_waits | 0| | Threads_cached| 0| | Threads_connected | 1| | Threads_created | 2| | Threads_running | 1| | Uptime| 449 | +---+--+ 222 rows in set (0.00 sec) Please check what is wrong and teach me. Thanks you, - Original Message - From: "Pooly" <[EMAIL PROTECTED]> To: "MySQL General" Sent: Tuesday, March 14, 2006 2:53 PM Subject: Re: Rollback is not take effect on MySQL 5.0.18 2006/3/14, Truong Tan Son <[EMAIL PROTECTED]>: Dear Sir, I could not find table of innoDB in mysql. Tables in the mysql are MyISAM and should stay that way. Odds are that there is a skip-innodb in your my.cnf on your RHE, and not you XP. what produces a "show status" ? mysql> show tables; +--+ | Tables_in_mysql | +---+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--+ I set innodb_table_locks=0 in my.cnf , but ROLLBACK is still not effect. Could you teach me more ? Thanks and best regards, - Original Message - From: "Pooly" <[EMAIL PROTECTED]> To: "MySQL General" Sent: Monday, March 13, 2006 5:13 PM Subject: Re: Rollback is not take effect on MySQL 5.0.18 2006/3/11, Truong Tan Son <[EMAIL PROTECTED]>: > Dear Sir, > > On RedHat Enterprise 4, and MySQL 5.0.18, I did : > > mysql> set autocommit=0; > > mysql> savepoint abc; > > mysql> insert something > > mysql> rollback to save point abc; > > Query OK, 0 rows affected, 1 warning (0.00 sec) > ^^ > > RollBack is NOT take effect. But on WindowsXP, it is GOOD. > > > What is wrong ? Did you check if the table are innoDB ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
2006/3/14, Truong Tan Son <[EMAIL PROTECTED]>: > Dear Sir, > > I could not find table of innoDB in mysql. > Tables in the mysql are MyISAM and should stay that way. Odds are that there is a skip-innodb in your my.cnf on your RHE, and not you XP. what produces a "show status" ? > mysql> show tables; > +--+ > | Tables_in_mysql | > +---+ > | columns_priv | > | db | > | func | > | host | > | tables_priv | > | user | > +--+ > > > I set innodb_table_locks=0 in my.cnf , but ROLLBACK is still not effect. > > > Could you teach me more ? > > > Thanks and best regards, > > > - Original Message - > From: "Pooly" <[EMAIL PROTECTED]> > To: "MySQL General" > Sent: Monday, March 13, 2006 5:13 PM > Subject: Re: Rollback is not take effect on MySQL 5.0.18 > > > 2006/3/11, Truong Tan Son <[EMAIL PROTECTED]>: > > Dear Sir, > > > > On RedHat Enterprise 4, and MySQL 5.0.18, I did : > > > > mysql> set autocommit=0; > > > > mysql> savepoint abc; > > > > mysql> insert something > > > > mysql> rollback to save point abc; > > > > Query OK, 0 rows affected, 1 warning (0.00 sec) > > ^^ > > > > RollBack is NOT take effect. But on WindowsXP, it is GOOD. > > > > > > What is wrong ? > > > Did you check if the table are innoDB ? > > > -- > Pooly > Webzine Rock : http://www.w-fenec.org/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
Dear Sir, I could not find table of innoDB in mysql. mysql> show tables; +--+ | Tables_in_mysql | +---+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--+ I set innodb_table_locks=0 in my.cnf , but ROLLBACK is still not effect. Could you teach me more ? Thanks and best regards, - Original Message - From: "Pooly" <[EMAIL PROTECTED]> To: "MySQL General" Sent: Monday, March 13, 2006 5:13 PM Subject: Re: Rollback is not take effect on MySQL 5.0.18 2006/3/11, Truong Tan Son <[EMAIL PROTECTED]>: Dear Sir, On RedHat Enterprise 4, and MySQL 5.0.18, I did : mysql> set autocommit=0; mysql> savepoint abc; mysql> insert something mysql> rollback to save point abc; Query OK, 0 rows affected, 1 warning (0.00 sec) ^^ RollBack is NOT take effect. But on WindowsXP, it is GOOD. What is wrong ? Did you check if the table are innoDB ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback is not take effect on MySQL 5.0.18
2006/3/11, Truong Tan Son <[EMAIL PROTECTED]>: > Dear Sir, > > On RedHat Enterprise 4, and MySQL 5.0.18, I did : > > mysql> set autocommit=0; > > mysql> savepoint abc; > > mysql> insert something > > mysql> rollback to save point abc; > > Query OK, 0 rows affected, 1 warning (0.00 sec) > ^^^^^^ > > RollBack is NOT take effect. But on WindowsXP, it is GOOD. > > > What is wrong ? Did you check if the table are innoDB ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rollback is not take effect on MySQL 5.0.18
Dear Sir, On RedHat Enterprise 4, and MySQL 5.0.18, I did : mysql> set autocommit=0; mysql> savepoint abc; mysql> insert something mysql> rollback to save point abc; Query OK, 0 rows affected, 1 warning (0.00 sec) ^^ RollBack is NOT take effect. But on WindowsXP, it is GOOD. What is wrong ? Thanks you and best regards, -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: invalidation rollback?!
Hi everybody!
Martijn Tonies wrote:
I execute follow procedure:
---
create procedure test_transation_rollback()
begin
start transaction;
create table t_34d (c1 int,c2 char(30));
rollback;
end
After execute this procedure,i find the table "t_34d" in my schema.Why?
Metadata transactions are not supported in MySQL.
Right.
Most systems call it "DML" ("data manipulation language": insert,
update, select, and delete commands) versus "DDL" ("data definition
language": create/alter/drop table/view, grant/revoke, ... commands),
but the result is the same:
Typically, transactions only cover DML commands,
whereas for DDL often an implicit "autocommit" applies.
Some systems even implicitly commit DML commands when executing a
following DDL.
As a consequence, the recommendation is not to try to mix DML and DDL
within one transaction, and to issue a "commit" immediately after any
DDL command. In this way, effects on all systems should be identical.
Every other style of application programming may be specific to one
DBMS, and requires detailed checking against the manual.
HTH,
Jörg Brühe
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: invalidation rollback?!
> I execute follow procedure: > > --- > create procedure test_transation_rollback() > begin >start transaction; >create table t_34d (c1 int,c2 char(30)); >rollback; > end > > > After execute this procedure,i find the table "t_34d" in my schema.Why? > Metadata transactions are not supported in MySQL. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
invalidation rollback?!
I execute follow procedure: --- create procedure test_transation_rollback() begin start transaction; create table t_34d (c1 int,c2 char(30)); rollback; end After execute this procedure,i find the table "t_34d" in my schema.Why?
Re: rollback after crash on OS X
Jaime, please post the COMPLETE UNEDITED .err log. The log sequence number would mean that your InnoDB tablespace has not been used after it was created: 051130 19:01:26 InnoDB: Started; log sequence number 0 43634 Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: "Jaime Magiera" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, December 02, 2005 8:07 PM Subject: rollback after crash on OS X Hello, Something weird happened on Wednesday: My Mac OSX Server unexpectedly restarted itself around 7PM. Everything seemed to be functional after that. However, I just noticed that basically a week worth of changes to a MySQL database have disappeared. Gone. Vanished. Objects that were created within a week leading to the crash have completely disappeared. Objects that were changed during that time show no sign of those changes. I could maybe understand if data from one session somehow was lost, but these changes were done over multiple sessions over a period of a week. The mysql log shows no shutdown info. The last error entry before restart is from the 21st. It definitely was an unclean shutdown. However, there are no startup errors. - 051130 19:01:15 mysqld started 051130 19:01:15 [Warning] Setting lower_case_table_names=2 because file system for /var/mysql/ is case insensitive 051130 19:01:26 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.13a' socket: '/var/mysql/mysql.sock' port: 3306 Source distribution - I not familiar enough with MySQL to even know where to begin looking for what could have happened. Any thoughts/suggestions? thanks, Jaime -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rollback after crash on OS X
Hello, Something weird happened on Wednesday: My Mac OSX Server unexpectedly restarted itself around 7PM. Everything seemed to be functional after that. However, I just noticed that basically a week worth of changes to a MySQL database have disappeared. Gone. Vanished. Objects that were created within a week leading to the crash have completely disappeared. Objects that were changed during that time show no sign of those changes. I could maybe understand if data from one session somehow was lost, but these changes were done over multiple sessions over a period of a week. The mysql log shows no shutdown info. The last error entry before restart is from the 21st. It definitely was an unclean shutdown. However, there are no startup errors. - 051130 19:01:15 mysqld started 051130 19:01:15 [Warning] Setting lower_case_table_names=2 because file system for /var/mysql/ is case insensitive 051130 19:01:26 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.13a' socket: '/var/mysql/mysql.sock' port: 3306 Source distribution - I not familiar enough with MySQL to even know where to begin looking for what could have happened. Any thoughts/suggestions? thanks, Jaime -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to rollback in MySql?
Hello, > I have just started using My-sql 4.1.9 mysql Ver 14.7 Distrib 4.1.9, > for pc-linux-gnu (i686) > > Is there a way I could rollback my changes , i tried to go through the > documentation but it wasn't of much help. Transactions are only used when you use the InnoDB or BDB table types. The default MYISAM doesn't support transactions. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to rollback in MySql?
Hi All, I have just started using My-sql 4.1.9 mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) Is there a way I could rollback my changes , i tried to go through the documentation but it wasn't of much help. regards Digvijoy
Re: InnoDB Rollback - 162 hrs remaining!?
John, Marko will add a commit at every 10 000 rows to CREATE INDEX in 4.1.11. Then this kind of a runaway rollback can not happen any more. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- - Original Message - From: ""Heikki Tuuri"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Sunday, February 27, 2005 12:33 AM Subject: Re: InnoDB Rollback - 162 hrs remaining!? John, - Original Message - From: "John Taylor" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 11:37 PM Subject: Re: InnoDB Rollback - 162 hrs remaining!? Thanks for the quick response. I note within the database directory a #sql- file but it has not been modified for two days, around the time the index was begun. that is probably the .frm file for the temp table you need to DROP. Does this have a bearing on matters? Last thing we need is to kill mysqld processes and find the innodb data file is corrupt beyond repair. I almost always shut down InnoDB with killall -9 mysqld It never gets corrupt. Unfortunately the server.err file is not giving me any output, nor .log and I don't have a .status file either. Thanks, John. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Rollback - 162 hrs remaining!?
John, - Original Message - From: "John Taylor" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 11:37 PM Subject: Re: InnoDB Rollback - 162 hrs remaining!? Thanks for the quick response. I note within the database directory a #sql- file but it has not been modified for two days, around the time the index was begun. that is probably the .frm file for the temp table you need to DROP. Does this have a bearing on matters? Last thing we need is to kill mysqld processes and find the innodb data file is corrupt beyond repair. I almost always shut down InnoDB with killall -9 mysqld It never gets corrupt. Unfortunately the server.err file is not giving me any output, nor .log and I don't have a .status file either. Thanks, John. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Rollback - 162 hrs remaining!?
Thanks for the quick response. I note within the database directory a #sql- file but it has not been modified for two days, around the time the index was begun. Does this have a bearing on matters? Last thing we need is to kill mysqld processes and find the innodb data file is corrupt beyond repair. Unfortunately the server.err file is not giving me any output, nor .log and I don't have a .status file either. Thanks, John. On Sat, 26 Feb 2005 21:42:27 +0200, Heikki Tuuri <[EMAIL PROTECTED]> wrote: > John, > > - Original Message - > From: "John Taylor" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Saturday, February 26, 2005 6:54 PM > Subject: InnoDB Rollback - 162 hrs remaining!? > > > Hi, > > > > We have a large InnoDB table to which we recently added an index. That > > index creation thread was issued a kill yesterday due to length of > > time, unfortunately according to 'show innodb status' the rollback is > > now 162 hrs away from completion (1 every 5 secs). > > > > We are not using per-table tablespaces. MySQL version is 4.1.9, server > > is 2x Xeon hyperthreading, 2Gb ram. > > > > 1. Can we safely kill the Linux PID of the thread doing this? What is > > the risk to the data? > > you can safely kill mysqld. > > Here you find advice how to DROP the temporary table the rollback is running > on: > http://dev.mysql.com/doc/mysql/en/innodb-troubleshooting-datadict.html > http://dev.mysql.com/doc/mysql/en/forcing-recovery.html > > > 2. We note the server has many default settings, looks like for a > > 256MB machine. Can we adjust some of those mentioned in the manual for > > performance tuning and see a hopefully positive effect on the time? > > http://dev.mysql.com/doc/mysql/en/innodb-configuration.html > > > Many thanks, > > > > John. > > Best regards, > > Heikki Tuuri > Innobase Oy > Foreign keys, transactions, and row level locking for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > tables > http://www.innodb.com/order.php > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Rollback - 162 hrs remaining!?
John, - Original Message - From: "John Taylor" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 26, 2005 6:54 PM Subject: InnoDB Rollback - 162 hrs remaining!? Hi, We have a large InnoDB table to which we recently added an index. That index creation thread was issued a kill yesterday due to length of time, unfortunately according to 'show innodb status' the rollback is now 162 hrs away from completion (1 every 5 secs). We are not using per-table tablespaces. MySQL version is 4.1.9, server is 2x Xeon hyperthreading, 2Gb ram. 1. Can we safely kill the Linux PID of the thread doing this? What is the risk to the data? you can safely kill mysqld. Here you find advice how to DROP the temporary table the rollback is running on: http://dev.mysql.com/doc/mysql/en/innodb-troubleshooting-datadict.html http://dev.mysql.com/doc/mysql/en/forcing-recovery.html 2. We note the server has many default settings, looks like for a 256MB machine. Can we adjust some of those mentioned in the manual for performance tuning and see a hopefully positive effect on the time? http://dev.mysql.com/doc/mysql/en/innodb-configuration.html Many thanks, John. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Rollback - 162 hrs remaining!?
Hi, We have a large InnoDB table to which we recently added an index. That index creation thread was issued a kill yesterday due to length of time, unfortunately according to 'show innodb status' the rollback is now 162 hrs away from completion (1 every 5 secs). We are not using per-table tablespaces. MySQL version is 4.1.9, server is 2x Xeon hyperthreading, 2Gb ram. 1. Can we safely kill the Linux PID of the thread doing this? What is the risk to the data? 2. We note the server has many default settings, looks like for a 256MB machine. Can we adjust some of those mentioned in the manual for performance tuning and see a hopefully positive effect on the time? Many thanks, John. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB crash and runaway rollback - help pls
Tobias, - Alkuperäinen viesti - Lähettäjä: "Tobias Asplund" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Kopio: Lähetetty: Tuesday, February 08, 2005 7:46 PM Aihe: Re: InnoDB crash and runaway rollback - help pls On Tue, 8 Feb 2005, Heikki Tuuri wrote: You should upgrade to 4.1.9. That version commits ALTER TABLE at every 10 000 rows, and a runaway rollback can no longer happen. This is very nice! Are there any plans for the same with INSERT ... SELECT -type statements? the problem in INSERT ... SELECT ... is that if we commit that kind of statement at every 10 000 rows, and mysqld crashes, then we cannot roll back the entire SQL statement, and the binlog will not be consistent with the actual contents of the database. Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB crash and runaway rollback - help pls
On Tue, 8 Feb 2005, Heikki Tuuri wrote: > You should upgrade to 4.1.9. That version commits ALTER TABLE at every 10 > 000 rows, and a runaway rollback can no longer happen. This is very nice! Are there any plans for the same with INSERT ... SELECT -type statements? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB crash and runaway rollback - help pls
Devananda, the ALTER TABLE creates a temporary table #sql... The rollback is running in that table. The manual contains instructions on how to rename and drop such table. You should upgrade to 4.1.9. That version commits ALTER TABLE at every 10 000 rows, and a runaway rollback can no longer happen. Also, in that version it is easier to drop #sql... tables, because you can simply write: DROP TABLE `#sql...`; Also note that for InnoDB it is fastest to create the table with all the index definitions first, and only after that import the rows. Creating indexes AFTER you have imported the rows is much slower. Your buffer pool is extremely small, only 8 MB. Below you are confusing it to the log buffer. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Devananda" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, February 07, 2005 8:23 PM Subject: InnoDB crash and runaway rollback - help pls Hi, I'm looking for two things - help understanding why InnoDB crashed on our= server this morning, and help speeding up recovery from that crash. We a= re running 4.0.21 binaries on Fedora Core 2 (Tettnang) Linux, 2.6.5-1.358= smp. I'll explain what happened We spent the weekend importing all our data into InnoDB. The last step wa= s to create an index on a table with 28mil rows (ALTER TABLE master_dna_l= ist ADD PRIMARY KEY (email(55)). Since this table isn't critical to other= areas of our website, we turned the site back on while the alter table w= as still running. Things seemed ok for several hours, but this morning, l= ots of threads started locking. Looking in the log showed "InnoDB: Warnin= g: difficult to find free blocks from the buffer pool", and vmstat showed= that the CPU and disks were completely idle. "SHOW INNODB STATUS" would = just hang indefinitely. Seeing no other recourse, I restarted mysqld, whi= ch of course caused a rollback of the alter table. The roll back is progr= essing at about 1% every 12 minutes, which equates to about 20hrs before = the server can be back up - not a good thing. Reading through the mysql d= ocumentation, we found this... http://dev.mysql.com/doc/mysql/en/forcing-recovery.html Starting from MySQL 3.23.53 and 4.0.4, you are allowed to |DROP| = or |CREATE| a table even if forced recovery is used. If you know tha= t a certain table is causing a crash in rollback, you can drop it. Y= ou can use this also to stop a runaway rollback caused by a failing mass imp= ort or |ALTER TABLE|. You can kill the *mysqld* process and set |innodb_force_recovery| to |3| to bring your database up without the rollback. Then |DROP| the table that is causing the runaway rollback. So we tried it - stopped the server (kill -9 at this point since a proper= shutdown just hangs), edit my.cnf and added "innodb_force_recovery=3D3" = and "skip-networking", start up, "drop table master_dna_list", "show tabl= es" and it's not there - good, stop the server again. waiting the my= sqld process would not terminate, after waiting 10 minutes with the syste= m totally idle, kill -9 again. start mysqld normally, and the rollback is= STILL going. Is there something else I must do to abort this rollback? A= ny help would be appreciated :) My other question is just why did InnoDB crash to begin with? At 3:40am t= his message began to show up in the log file. 050207 3:40:04 InnoDB: WARNING: over 4 / 5 of the buffer pool is occup= ied by=20 InnoDB: lock heaps or the adaptive hash index! Check that your=20 InnoDB: transactions do not set too many row locks.=20 InnoDB: Your buffer pool size is 8 MB. Maybe you should make=20 InnoDB: the buffer pool bigger?=20 InnoDB: Starting the InnoDB Monitor to print diagnostics, including=20 InnoDB: lock heap and hash index sizes. In the documentation, there is this # The size of the buffer InnoDB uses for buffering log data. As soon as # it is full, InnoDB will have to flush it to disk. As it is flushed # once per second anyway, it does not make sense to have it very large # (even with long transactions). innodb_log_buffer_size =3D 8M It explicitly states that it "does not make sense to have it very large",= so I had left it at 8M on our server. Later in the morning, this message= begins appearing in the log file... this is about the time when I woke u= p and saw that the server was completely idle - no disk i/o and no cpu ac= tivity. Could anyone please try to explain what happened? Thanks very muc= h for any help or advice. 050207 7:42:33 InnoDB: Warning: difficult to find free blocks from=20 InnoDB: the buffer pool (13
InnoDB crash and runaway rollback - help pls
Hi, I'm looking for two things - help understanding why InnoDB crashed on our server this morning, and help speeding up recovery from that crash. We are running 4.0.21 binaries on Fedora Core 2 (Tettnang) Linux, 2.6.5-1.358smp. I'll explain what happened We spent the weekend importing all our data into InnoDB. The last step was to create an index on a table with 28mil rows (ALTER TABLE master_dna_list ADD PRIMARY KEY (email(55)). Since this table isn't critical to other areas of our website, we turned the site back on while the alter table was still running. Things seemed ok for several hours, but this morning, lots of threads started locking. Looking in the log showed "InnoDB: Warning: difficult to find free blocks from the buffer pool", and vmstat showed that the CPU and disks were completely idle. "SHOW INNODB STATUS" would just hang indefinitely. Seeing no other recourse, I restarted mysqld, which of course caused a rollback of the alter table. The roll back is progressing at about 1% every 12 minutes, which equates to about 20hrs before the server can be back up - not a good thing. Reading through the mysql documentation, we found this... http://dev.mysql.com/doc/mysql/en/forcing-recovery.html Starting from MySQL 3.23.53 and 4.0.4, you are allowed to |DROP| or |CREATE| a table even if forced recovery is used. If you know that a certain table is causing a crash in rollback, you can drop it. You can use this also to stop a runaway rollback caused by a failing mass import or |ALTER TABLE|. You can kill the *mysqld* process and set |innodb_force_recovery| to |3| to bring your database up without the rollback. Then |DROP| the table that is causing the runaway rollback. So we tried it - stopped the server (kill -9 at this point since a proper shutdown just hangs), edit my.cnf and added "innodb_force_recovery=3" and "skip-networking", start up, "drop table master_dna_list", "show tables" and it's not there - good, stop the server again. waiting the mysqld process would not terminate, after waiting 10 minutes with the system totally idle, kill -9 again. start mysqld normally, and the rollback is STILL going. Is there something else I must do to abort this rollback? Any help would be appreciated :) My other question is just why did InnoDB crash to begin with? At 3:40am this message began to show up in the log file. 050207 3:40:04 InnoDB: WARNING: over 4 / 5 of the buffer pool is occupied by InnoDB: lock heaps or the adaptive hash index! Check that your InnoDB: transactions do not set too many row locks. InnoDB: Your buffer pool size is 8 MB. Maybe you should make InnoDB: the buffer pool bigger? InnoDB: Starting the InnoDB Monitor to print diagnostics, including InnoDB: lock heap and hash index sizes. In the documentation, there is this # The size of the buffer InnoDB uses for buffering log data. As soon as # it is full, InnoDB will have to flush it to disk. As it is flushed # once per second anyway, it does not make sense to have it very large # (even with long transactions). innodb_log_buffer_size = 8M It explicitly states that it "does not make sense to have it very large", so I had left it at 8M on our server. Later in the morning, this message begins appearing in the log file... this is about the time when I woke up and saw that the server was completely idle - no disk i/o and no cpu activity. Could anyone please try to explain what happened? Thanks very much for any help or advice. 050207 7:42:33 InnoDB: Warning: difficult to find free blocks from InnoDB: the buffer pool (1342 search iterations)! Consider InnoDB: increasing the buffer pool size. InnoDB: It is also possible that in your Unix version InnoDB: fsync is very slow, or completely frozen inside InnoDB: the OS kernel. Then upgrading to a newer version InnoDB: of your operating system may help. Look at the InnoDB: number of fsyncs in diagnostic info below. InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 InnoDB: 59958419 OS file reads, 46937102 OS file writes, 3223373 OS fsyncs InnoDB: Starting InnoDB Monitor to print further InnoDB: diagnostics to the standard output. This is the last complete INNODB MONITOR output in the log file, from 5:46. There is one a few seconds after this, but the monitor's output is truncated :( = 050207 5:46:22 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 48037914, signal count 46488800 Mutex spin waits 1167745252, rounds 3021712380, OS waits 24059258 RW-shared spins 3802825, OS waits 1886668; RW-excl spins 5144033, OS waits 2480352 TRANSACTIONS Trx id counter 0 1396082 Purge done for trx's n:o <
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Andre, I would recommend a table for recovering id's that are lost due to rollback. Before you actually rollback, take the generated ID and push it into this table. Then change the way you acquire id's on insert. You will want to check to see if this table has an ID before you auto_increment the table you are inserting the record into. This should be a little less resource intensive than to put all data into temporary tables. Clint From: Joerg Bruehe <[EMAIL PROTECTED]> To: mysql@lists.mysql.com CC: Andre Matos <[EMAIL PROTECTED]>, Paul DuBois <[EMAIL PROTECTED]> Subject: Re: Rollback and INSERT_ID() or LAST_INSERT_ID() Date: Tue, 18 Jan 2005 11:08:40 +0100 Hi! Am Di, den 18.01.2005 schrieb Paul DuBois um 3:53: > At 21:27 -0500 1/17/05, Andre Matos wrote: > >Thanks Eric, but I can let it increment because I cannot have a gave in the > >numbers. I think I will need to use MAX() in this case. > > Using MAX() won't guarantee that you won't have gaps. > > What you're describing cannot be achieved in the general case. > Consider this scenario: > > - Transaction T1 begins, generates an AUTO_INCREMENT value n. > - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. > - Transaction T2 commits. > - Transaction T1 rolls back. > > You now have a gap at value n, and a used value of n+1. > MAX() at this point returns n+1, not n, so that won't > help you reuse n. > > With more than two transactions running simultaneously, each > of which can roll back or commit, the situation becomes more > complex. IMO, Andre's only chance is to code his transactions in such a way that they need not rollback (only do so if the whole system stops). One way that comes to my mind is to accumulate all data in some temporary table, using some other value as ID (or in application variables), and only after the final "yes, do it" confirmation transfer them to the "true" tables with the auto-increment ID. In future releases, stored procedures might be another way to ensure all actions are grouped without an intervening parallel rollback. If your concurrency requirements are low and you can stand wait time, you could keep the "next ID" in an application-controlled table, locked from its retrieval to a final increment at transaction commit; but I agree these low requirements are unusual. As an alternative, a rollback might create a dummy record using that ID which acts as a placeholder, maybe with a remark "user rollback" or similar - if that is permissible in the application. > > Might be worth reconsidering whether you really require no > gaps. It's generally better to try to design an application > not to have that dependency. Paul, while I agree with that preference, I know that sometimes there is no choice. As an example, some German bookkeeping regulation requires you to use dense booking numbers (without gap). So I know of a software project that used _descending_ numbers because they were faster to generate in their environment than ascending ones. (This does not solve the rollback issue, of course.) Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Hi! Am Di, den 18.01.2005 schrieb Paul DuBois um 3:53: > At 21:27 -0500 1/17/05, Andre Matos wrote: > >Thanks Eric, but I can let it increment because I cannot have a gave in the > >numbers. I think I will need to use MAX() in this case. > > Using MAX() won't guarantee that you won't have gaps. > > What you're describing cannot be achieved in the general case. > Consider this scenario: > > - Transaction T1 begins, generates an AUTO_INCREMENT value n. > - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. > - Transaction T2 commits. > - Transaction T1 rolls back. > > You now have a gap at value n, and a used value of n+1. > MAX() at this point returns n+1, not n, so that won't > help you reuse n. > > With more than two transactions running simultaneously, each > of which can roll back or commit, the situation becomes more > complex. IMO, Andre's only chance is to code his transactions in such a way that they need not rollback (only do so if the whole system stops). One way that comes to my mind is to accumulate all data in some temporary table, using some other value as ID (or in application variables), and only after the final "yes, do it" confirmation transfer them to the "true" tables with the auto-increment ID. In future releases, stored procedures might be another way to ensure all actions are grouped without an intervening parallel rollback. If your concurrency requirements are low and you can stand wait time, you could keep the "next ID" in an application-controlled table, locked from its retrieval to a final increment at transaction commit; but I agree these low requirements are unusual. As an alternative, a rollback might create a dummy record using that ID which acts as a placeholder, maybe with a remark "user rollback" or similar - if that is permissible in the application. > > Might be worth reconsidering whether you really require no > gaps. It's generally better to try to design an application > not to have that dependency. Paul, while I agree with that preference, I know that sometimes there is no choice. As an example, some German bookkeeping regulation requires you to use dense booking numbers (without gap). So I know of a software project that used _descending_ numbers because they were faster to generate in their environment than ascending ones. (This does not solve the rollback issue, of course.) Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Yes, I know about this. What I plan is to remove the auto_increment. I will do this by hand locking the entire table just right before inserting the field. Actually, it is running like this. I am just upgrading the PHP. I will keep the auto_increment in other tables that does not have this kind of problem. Thanks a lot for your help. Andre On 1/17/05 9:53 PM, "Paul DuBois" <[EMAIL PROTECTED]> wrote: > At 21:27 -0500 1/17/05, Andre Matos wrote: >> Thanks Eric, but I can let it increment because I cannot have a gave in the >> numbers. I think I will need to use MAX() in this case. > > Using MAX() won't guarantee that you won't have gaps. > > What you're describing cannot be achieved in the general case. > Consider this scenario: > > - Transaction T1 begins, generates an AUTO_INCREMENT value n. > - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. > - Transaction T2 commits. > - Transaction T1 rolls back. > > You now have a gap at value n, and a used value of n+1. > MAX() at this point returns n+1, not n, so that won't > help you reuse n. > > With more than two transactions running simultaneously, each > of which can roll back or commit, the situation becomes more > complex. > > Might be worth reconsidering whether you really require no > gaps. It's generally better to try to design an application > not to have that dependency. > >> >> Thanks. >> >> Andre >> >> >> >> On 1/17/05 8:14 PM, "Eric Bergen" <[EMAIL PROTECTED]> wrote: >> >>> Just let it increment. Keeping it incremented is MySQL's way ot >>> insuring that the same id doesn't get used twice for different >>> records. It's doing everything correctly. >>> >>> -Eric >>> >>> On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos >>> <[EMAIL PROTECTED]> wrote: >>>> Hi List, >>>> >>>> I have a field in one of my tables that uses auto-increment from MySQL >>>> 4.1.8-nt (Windows XP). >>>> >>>> My problem is to get the last insert ID when the insert fails and I use >>>> rollback. The MySQL is still incrementing the field. How can I >>>> avoid this if >>>> it is possible? I am trying to avoid to use the function MAX() to get the >>>> last ID inserted. >>>> >>>> Thanks for any help. >>>> >>>> Andre > -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Thanks. Andre On 1/17/05 8:14 PM, "Eric Bergen" <[EMAIL PROTECTED]> wrote: Just let it increment. Keeping it incremented is MySQL's way ot insuring that the same id doesn't get used twice for different records. It's doing everything correctly. -Eric On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos <[EMAIL PROTECTED]> wrote: Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. >> Andre -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Thanks. Andre On 1/17/05 8:14 PM, "Eric Bergen" <[EMAIL PROTECTED]> wrote: > Just let it increment. Keeping it incremented is MySQL's way ot > insuring that the same id doesn't get used twice for different > records. It's doing everything correctly. > > -Eric > > On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos > <[EMAIL PROTECTED]> wrote: >> Hi List, >> >> I have a field in one of my tables that uses auto-increment from MySQL >> 4.1.8-nt (Windows XP). >> >> My problem is to get the last insert ID when the insert fails and I use >> rollback. The MySQL is still incrementing the field. How can I avoid this if >> it is possible? I am trying to avoid to use the function MAX() to get the >> last ID inserted. >> >> Thanks for any help. >> >> Andre >> >> -- >> Andre Matos >> [EMAIL PROTECTED] >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> > -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Just let it increment. Keeping it incremented is MySQL's way ot insuring that the same id doesn't get used twice for different records. It's doing everything correctly. -Eric On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos <[EMAIL PROTECTED]> wrote: > Hi List, > > I have a field in one of my tables that uses auto-increment from MySQL > 4.1.8-nt (Windows XP). > > My problem is to get the last insert ID when the insert fails and I use > rollback. The MySQL is still incrementing the field. How can I avoid this if > it is possible? I am trying to avoid to use the function MAX() to get the > last ID inserted. > > Thanks for any help. > > Andre > > -- > Andre Matos > [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rollback and INSERT_ID() or LAST_INSERT_ID()
Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reg Table spaces and Rollback segments in MySQL 4.0.21
Lakshmi, - Original Message - From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, November 18, 2004 1:07 PM Subject: Reg Table spaces and Rollback segments in MySQL 4.0.21 Hi ALL, We are using MySQL 4.0.21 with InnoDB. For creating the tablespace mentioned as innodb_data_file_path =3D ibdata1:10M:autoextend in my.cnf file. Here, is there a facility to know the table space name?. Shall we create multiple table spaces like the above in MySQL 4.0.21 and assign different tables to different table spaces?. in MySQL-4.0, there is just one tablespace. It consists of the ibdata files, that are thought of as catenated. Actually if we set autocommit=3D0, we are able to do the perform rollback and commit and is working as expected in different sessions. I think rollback segments are creating internally. Is there any facility to mention the rollback segment size?. Shall we create our own rollback segments in MYSQL 4.0.21? No need to create them. InnoDB creates the 'rollback segment' automatically in the ibdata files. PS: Whenever Oracle Database is created Rollback segments were created in the "System" tablespace". Also, have the option of creating more Rollback segments in the non system tablespace. Do we have the same facility in MySQL 4.0.21? In Oracle, rollback segments can become a bottleneck, but not in InnoDB. No need to create more of them. Please advise us for the solution. Thanks, Narasimha please address these general MySQL questions to [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reg Table spaces and Rollback segments in MySQL 4.0.21
Hi ALL, We are using MySQL 4.0.21 with InnoDB. For creating the tablespace mentioned as innodb_data_file_path = ibdata1:10M:autoextend in my.cnf file. Here, is there a facility to know the table space name?. Shall we create multiple table spaces like the above in MySQL 4.0.21 and assign different tables to different table spaces?. Actually if we set autocommit=0, we are able to do the perform rollback and commit and is working as expected in different sessions. I think rollback segments are creating internally. Is there any facility to mention the rollback segment size?. Shall we create our own rollback segments in MYSQL 4.0.21? PS: Whenever Oracle Database is created Rollback segments were created in the "System" tablespace". Also, have the option of creating more Rollback segments in the non system tablespace. Do we have the same facility in MySQL 4.0.21? Please advise us for the solution. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: commit or rollback?
At 11:42 +0100 10/18/04, Colm G. Connolly wrote: Hi all, I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */ SET AUTOCOMMIT=0; use db1; begin work; sql statement 1; sql statement 2; . . . sql statement n; At this point I'd like to say, in sql, if no errors then commit; else rollback end From what I read in the manual I can do one or the other (commit or rollback) but there didn't seem to be a way of conditionally doing one or the other of them. That's correct. You handle the logic of checking for errors and committing or rolling back using your programming language (or rather, in the MySQL API for your programming language). -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: commit or rollback?
Colm G. Connolly wrote: Hi all, I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */ SET AUTOCOMMIT=0; use db1; begin work; If you specify Begin or Start Transaction, set autommit=0; is optionnal. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: commit or rollback?
I'm relatively new to all of this but just about
finished setting up a transaction myself.
I'm doing something like this:
this is in php:( i also have functions set up for
"begin", "rollback" and "committ".
You should also set autocommitt to 0 .
Hope this helps!
Stuart
function run_query($sql)
{
$result = mysql_query($query);
if(!$result)
{
return false;
}else{
return true;
}
}
then:
begin();
$query = "INSERT INTO firsttable.//first query
$res1 = run_query($query);
$query = "INSERT INTO secondtable...//second query
$res2 = run_query($query);
$query = "INSERT INTO thirdtable//third query
$res3 = run_query($query);
if($res1 && $res2 && $res3) // If all results are true
{
commit();
echo "your insertions were successful";
}else{
echo mysql_errno($link)." :
".mysql_error($link)."\n";
rollback();
exit;
}
> Hi all,
>
> I'm working with tables stored by the InnoDB engine
> and would like to
> be able to commit only if there are no errors
> generated by a group of
> statements like this.
>
> /* -*- sql -*- */
> SET AUTOCOMMIT=0;
> use db1;
> begin work;
> sql statement 1;
> sql statement 2;
> .
> .
> .
> sql statement n;
>
> At this point I'd like to say, in sql,
> if no errors then
>commit;
> else
>rollback
> end
>
> From what I read in the manual I can do one or the
> other (commit or
> rollback) but there didn't seem to be a way of
> conditionally doing one
> or the other of them.
>
> Thanks in advance,
>
> --
> _\\|//_
> ( O-O )
>
---o00--(_)--00o--
> Colm G. Connolly| Tel :
> +353-1-716-2851
> Department of Computer Science | Fax :
> +353-1-269-7262
> University College Dublin (UCD) | Web :
> http://darwin.ucd.ie/
> Belfield, Dublin 4 | MSN :
> [EMAIL PROTECTED]
> Éire / Republic of Ireland |
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
commit or rollback?
Hi all, I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */ SET AUTOCOMMIT=0; use db1; begin work; sql statement 1; sql statement 2; . . . sql statement n; At this point I'd like to say, in sql, if no errors then commit; else rollback end From what I read in the manual I can do one or the other (commit or rollback) but there didn't seem to be a way of conditionally doing one or the other of them. Thanks in advance, -- _\\|//_ ( O-O ) ---o00--(_)--00o-- Colm G. Connolly| Tel : +353-1-716-2851 Department of Computer Science | Fax : +353-1-269-7262 University College Dublin (UCD) | Web : http://darwin.ucd.ie/ Belfield, Dublin 4 | MSN : [EMAIL PROTECTED] Éire / Republic of Ireland | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transaction question - no rollback needed?
See below - Original Message - From: "Jeremy Smith" <[EMAIL PROTECTED]> To: "[EMAIL PROTECTED] Mysql. Com" <[EMAIL PROTECTED]> Sent: Sunday, May 09, 2004 9:31 PM Subject: Transaction question - no rollback needed? > Does it make sense to use a transaction just for the row locking properties, > and then not needing to error check? > > I have a situation where I have seperate files that are being run very often > in realtime by many different users. One is calling a list of 12 football > players. The other is updating specific information about that player at a > given time. When the update takes place, occasionally the call for the 12 > players will only return 11 (presumably it is in the middle of an UPDATE). I don't understand why this would happen under any circumstance. Either your query should be blocked or it should read a snapshot of the data as it exists depending on your isolation level. I can't imagine a scenario where this would be good (desired) behavior by any RDBMS and it sounds like a problem. > So if I set autocommit = 0, begin work, perform the update, and then commit > will I in effect guarantee that the other read will wait until the update is > done before trying to find its 12 players? Yes and No. Using InnoDB, writers do not block readers, so they will still be able to read committed data, but they won't be able to update or delete it until your transaction commits. Lou > > I hope I didn't make that too confusing, and I appreciate any help that can > be offered. > > Thanks, > Jeremy > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transaction question - no rollback needed?
Does it make sense to use a transaction just for the row locking properties, and then not needing to error check? I have a situation where I have seperate files that are being run very often in realtime by many different users. One is calling a list of 12 football players. The other is updating specific information about that player at a given time. When the update takes place, occasionally the call for the 12 players will only return 11 (presumably it is in the middle of an UPDATE). So if I set autocommit = 0, begin work, perform the update, and then commit will I in effect guarantee that the other read will wait until the update is done before trying to find its 12 players? I hope I didn't make that too confusing, and I appreciate any help that can be offered. Thanks, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procs and Commit/Rollback Transactions
> Possibly veering off topic, but I have a strong urge to comment on this, and > shall! > > I am a M$ .NET developer (primarily ASP.NET with SQL Server), and have > recently embarked on a project at home, and wished to apply the same sort of > principles that I use at work - for example, keeping all 'system logic' > embedded within the database itself. > > I have begun prototyping using MySQL 5.0.0a-alpha on win32 (possibly the > most unstable combination you can imagine), and it is excellent. I have the > odd crash, or strange glitch (such as procedures not being recognized, > requiring a restart before they can be called), but this is fine - it's the > first alpha, after all! > > Now all I need to be truly content is views And stored procedures per database instead of global :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procs and Commit/Rollback Transactions
Possibly veering off topic, but I have a strong urge to comment on this, and shall! I am a M$ .NET developer (primarily ASP.NET with SQL Server), and have recently embarked on a project at home, and wished to apply the same sort of principles that I use at work - for example, keeping all 'system logic' embedded within the database itself. I have begun prototyping using MySQL 5.0.0a-alpha on win32 (possibly the most unstable combination you can imagine), and it is excellent. I have the odd crash, or strange glitch (such as procedures not being recognized, requiring a restart before they can be called), but this is fine - it's the first alpha, after all! Now all I need to be truly content is views Cheers, Matt -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: 20 March 2004 22:57 To: Laphan; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Stored Procs and Commit/Rollback Transactions Hi Laphan, (I'm sending this to the general list too, since this isn't Windows specific and more people will see it.) MySQL 5.0, which is an early Alpha, does now support stored procedures. http://www.mysql.com/doc/en/Stored_Procedures.html And MySQL also supports transactions with the InnoDB (most common) and BDB table types. MyISAM doesn't. Hope that helps. Matt - Original Message - From: "Laphan" Sent: Thursday, March 18, 2004 5:19 PM Subject: Stored Procs and Commit/Rollback Transactions > Hi All > > OK I admit it I'm a complete MSV (MySQL Virgin), so I might be asking some > very stupid questions!!! > > I'm used to SQL Server so I think I should have a basic understanding, but > I'm sure you'll tell me different!! > > Basically I just want to confirm that the latest release of MySQL doesn't > offer stored procs or commit/rollback functionality - right? > > How does a MySQL-er get round this? > > I'm wanting to develop my ASP/Cart with MySQL as the back-bone and I'm > trying to find out what the generic do's and dont's are when using this > collaboration. > > Any feedback would be very much appreciated. > > Rgds > > Laphan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procs and Commit/Rollback Transactions
Hi Laphan, (I'm sending this to the general list too, since this isn't Windows specific and more people will see it.) MySQL 5.0, which is an early Alpha, does now support stored procedures. http://www.mysql.com/doc/en/Stored_Procedures.html And MySQL also supports transactions with the InnoDB (most common) and BDB table types. MyISAM doesn't. Hope that helps. Matt - Original Message - From: "Laphan" Sent: Thursday, March 18, 2004 5:19 PM Subject: Stored Procs and Commit/Rollback Transactions > Hi All > > OK I admit it I'm a complete MSV (MySQL Virgin), so I might be asking some > very stupid questions!!! > > I'm used to SQL Server so I think I should have a basic understanding, but > I'm sure you'll tell me different!! > > Basically I just want to confirm that the latest release of MySQL doesn't > offer stored procs or commit/rollback functionality - right? > > How does a MySQL-er get round this? > > I'm wanting to develop my ASP/Cart with MySQL as the back-bone and I'm > trying to find out what the generic do's and dont's are when using this > collaboration. > > Any feedback would be very much appreciated. > > Rgds > > Laphan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB rollback
I just had a script fail unexpectedly and now I've got a couple hundred thousand undo entries rolling back. Yes, I realize that I should be committing smaller groups--my bad. I was really hoping to restart my server tonight to change some server parameters, but a rollback like this will take many, many hours (my only major pet peeve in InnoDB--rollbacks are WAY too slow). Can I shutdown the server during a big rollback, or will the shutdown wait for the rollback to finish before completely successfully? -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback
You can add a version field to each row. Then add a seperate table with info with a list of the versions and a flag for deleted. Queries would look for each record that has the highest version number thats not deleted. Having a lot undo/redo info can get kind of complicated, especialy with multiple end users playing with it and chains of dependant changes. If the info can be modeled as documents this is frequenetly done with CVS. On Fri, 2 Jan 2004 13:06:36 +0530, "karthikeyan.balasubramanian" <[EMAIL PROTECTED]> said: > Hi, > > I posted this question in MySQL mailing list and got no reply. > > The basic problem is that I have committed the transaction and then > replicated to another DB. Now I want to rollback the committed > transaction. > Is there a way to rollback to a particular point. This requirement is > very > similar to rolling back using save points. I guess an option would be to > backup database before changes and restore it if the user is not > satisfied > with the changes he has made. One transaction in my application would > affect > 6-8 tables with at least 50 - 100 records getting inserted/updated or > deleted. > > Please advice > > PS : Wish you all a very Happy New Year > > Karthikeyan B > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback
Am Fr, den 02.01.2004 schrieb karthikeyan.balasubramanian um 09:28: > Is there any alternative way to get back to the old state of the database? The only way I can think of is to dump the respective tables (e.g. every night) and re-import them when needed. Note that this can't be done by mysql automatically because - as Chris just said - COMMIT means to commit /now/ and irreversibly. First step: tell this to your users ;) Greetings Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback
Hi Chris, Thank you for you quick reply. Is there any alternative way to get back to the old state of the database? Looking forward for your response. Karthikeyan B - Original Message - From: "Chris" <[EMAIL PROTECTED]> To: "karthikeyan.balasubramanian" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, January 02, 2004 1:33 PM Subject: RE: Rollback > I'm fairly sure there is *no* way to do it. COMMIT says 'I want this data in > the database' not 'I think I want this data in the database' > > Chris > > -Original Message- > From: karthikeyan.balasubramanian > [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 01, 2004 11:37 PM > To: [EMAIL PROTECTED] > Subject: Rollback > > > Hi, > > I posted this question in MySQL mailing list and got no reply. > > The basic problem is that I have committed the transaction and then > replicated to another DB. Now I want to rollback the committed transaction. > Is there a way to rollback to a particular point. This requirement is very > similar to rolling back using save points. I guess an option would be to > backup database before changes and restore it if the user is not satisfied > with the changes he has made. One transaction in my application would affect > 6-8 tables with at least 50 - 100 records getting inserted/updated or > deleted. > > Please advice > > PS : Wish you all a very Happy New Year > > Karthikeyan B > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback
I'm fairly sure there is *no* way to do it. COMMIT says 'I want this data in the database' not 'I think I want this data in the database' Chris -Original Message- From: karthikeyan.balasubramanian [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 11:37 PM To: [EMAIL PROTECTED] Subject: Rollback Hi, I posted this question in MySQL mailing list and got no reply. The basic problem is that I have committed the transaction and then replicated to another DB. Now I want to rollback the committed transaction. Is there a way to rollback to a particular point. This requirement is very similar to rolling back using save points. I guess an option would be to backup database before changes and restore it if the user is not satisfied with the changes he has made. One transaction in my application would affect 6-8 tables with at least 50 - 100 records getting inserted/updated or deleted. Please advice PS : Wish you all a very Happy New Year Karthikeyan B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rollback
Hi, I posted this question in MySQL mailing list and got no reply. The basic problem is that I have committed the transaction and then replicated to another DB. Now I want to rollback the committed transaction. Is there a way to rollback to a particular point. This requirement is very similar to rolling back using save points. I guess an option would be to backup database before changes and restore it if the user is not satisfied with the changes he has made. One transaction in my application would affect 6-8 tables with at least 50 - 100 records getting inserted/updated or deleted. Please advice PS : Wish you all a very Happy New Year Karthikeyan B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rollback..
I run update on the database which by my mistake updated all rows. Is it possible somehow go back to previous state before the update? _ Have fun customizing MSN Messenger learn how here! http://www.msnmessenger-download.com/tracking/reach_customize -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Rollback
Hi Heikki, The basic problem is that I have committed the transaction and then replicated to another DB. Now I want to rollback the committed transaction. Is there a way to rollback to a particular point. This requirement is very similar to rolling back using save points. I guess an option would be to backup database before changes and restore it if the user is not satisfied with the changes he has made. One transaction in my application would affect 6-8 tables with at least 50 - 100 records getting inserted/updated or deleted. Please advice Karthikeyan B - Original Message - From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, December 23, 2003 12:36 AM Subject: Re: Replication Rollback > > Karthikeyan, > > replication poses no problem here. MySQL does not write to the binlog > transactions which are rolled back. Thus, a replication slave will never > perform the steps in a transaction which is rolled back in the master. > > Best regards, > > Heikki Tuuri > Innobase Oy > http://www.innodb.com > Foreign keys, transactions, and row level locking for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > tables > > Order MySQL technical support from https://order.mysql.com/ > > - Original Message - > From: ""karthikeyan.balasubramanian"" > <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Monday, December 22, 2003 7:37 PM > Subject: Replication Rollback > > > > Hello everybody. > > > > I have a clarification/solution to request. I am currently in the > > process of designing a web application with JBoss 3.2.2 and MySQL 4.0.16. > > The application is a data centric application with huge list of products > > (tens of thousands). Sets of products are grouped into Categories. These > > categories are maintained in a hierarchical fashion in the database. There > > is no restriction on the number of levels that this categories can go to . > > The requirements are to help an admin user manage product categories and > > also to be able to bifurcate a category. To cite an example, let us take a > > category of Toys which has 100 products beneath it. The admin user wants > to > > split the Toys category based on age group. he can split the category into > > any sub categories ( 3 Yrs - 8 Yrs, 9 Yrs - 15 Yrs, 16+). The admin user > > will now have to split the 100 products into these categories. He cannot > > leave any products in the Toys category. There are two risks associated > with > > this: > > > > a.. The user doesn't finish the multistep process. (handled by the use > of > > session variables) > > b.. The admin user needs to test the application (data changes) on a > > staging environment before he can confirm the changes to production. > > c.. The admin user needs to be able to roll back changes if he is not > > satisfied with the changes made. > > > > I was planning to use replication. This is described in detail at the > > url document(http://www.aspiresys.com/karthik/procs.pdf). The process > works > > fine as long as the data is correct. given the approach I would like to > know > > how I can roll the old transaction back. Also I want to know the best > method > > to achieve this. This is pretty important and any help would be highly > > appreciated. > > > > > > Thanks > > Karthikeyan B > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database rollback doesn't work
Monica,
I think there was no transaction support in MySQL-2.3.2.
You should use MySQL-4.0 and an InnoDB or BDB type table.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, December 19, 2003 6:30 PM
Subject: Database rollback doesn't work
> Hi!
>
> I have a simple webapp that allows users to register. The user is inserted
> in the DB and a confirmation mail is sent to the user.
> If the mail fails I'd like the DB to rollback the transaction, but it
> doesn't do it. The new user entry is kept in the DB.
> I'm using Tomcat 4.1 and mySQL 2.3.2.
>
> I include the code, log and server.xml.
>
> Thanks for your help.
>
> Monica
>
>
> ***CODE:
>
> Connection dbCon = null;
> boolean isSuccess = false;
> try {
> //inser user in db
> dbCon = new DBUtil().getDBConnection();
> dbCon.setAutoCommit(false);
> userDBPopulator.insertEntry(user, dbCon);
>
> //send mail to user
> [snip]
> isSuccess = true;
> }
> //rollback if fail
> catch (MailException e) {
> log.error("MailException " + e.getMessage(), e);
> log.debug("trying to rollback and close");
> try {
> dbCon.rollback();
> }
> catch (SQLException e1) {
> log.error("SQLException rollbacking " + e1.getMessage(),
e1);
> }
> try {
> dbCon.close();
> }
> catch (SQLException e1) {
> log.error("SQLException closing connection to DB " +
> e1.getMessage(), e1);
> }
> }
> catch (SQLException e) {
> log.error("SQLException " + e.getMessage(), e);
> try {
> dbCon.rollback();
> }
> catch (SQLException e1) {
> log.error("SQLException rollbacking " + e1.getMessage(),
e1);
> }
> try {
> dbCon.close();
> }
> catch (SQLException e1) {
> log.error("SQLException closing connection to DB " +
> e1.getMessage(), e1);
> }
> throw new ServletException(e);
> }
>
> //commit user entry
> if( isSuccess){
> log.debug("commiting");
> try {
> dbCon.commit();
> }
> catch (SQLException e1) {
> log.error("SQLException commiting " + e1.getMessage(),
e1);
> throw new ServletException("SQLException commiting " +
> e1.getMessage(), e1);
> }
> try {
> dbCon.close();
> }
> catch (SQLException e1) {
> log.error("SQLException closing connection to DB " +
> e1.getMessage(), e1);
> }
> }
>
> LOG:
>
> 2003-12-19 15:44:12,546 DEBUG [Thread-3] (RegisterServlet.java:200) -
> handle register request
> 2003-12-19 15:44:12,562 INFO [Thread-3] (UserDBReader.java:202) - No user
> found with name monica
> 2003-12-19 15:44:12,578 DEBUG [Thread-3] (MailHelper.java:88) - Sending
> mail to m
> 2003-12-19 15:44:12,625 ERROR [Thread-3] (MailHelper.java:121) - Messaging
> Exception: Sending failed
> 2003-12-19 15:44:12,625 ERROR [Thread-3] (MailHelper.java:124) - Next
> Messaging Exception: Invalid Addresses;
> 2003-12-19 15:44:12,640 DEBUG [Thread-3] (RegisterServlet.java:282) -
> trying to rollback and close
>
>
> ***SERVER.XML
>
> auth="Container"
> type="javax.sql.DataSource"/>
>
>
>
> factory
> org.apache.commons.dbcp.BasicDataSourceFactory
>
>
> maxActive
> 100
>
>
> maxIdle
> 30
>
>
> maxWait
> 1
>
>
> username
> ***
>
>
> password
>
>
>
> driverClassName
> com.mysql.jdbc.Driver
>
>
> url
> jdbc:mysql://myMachine.com/myDB?autoReconnect=true
>
>
> removeAbandoned
> true
>
>
> logAbandoned
> true
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Rollback
Karthikeyan, replication poses no problem here. MySQL does not write to the binlog transactions which are rolled back. Thus, a replication slave will never perform the steps in a transaction which is rolled back in the master. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""karthikeyan.balasubramanian"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, December 22, 2003 7:37 PM Subject: Replication Rollback > Hello everybody. > > I have a clarification/solution to request. I am currently in the > process of designing a web application with JBoss 3.2.2 and MySQL 4.0.16. > The application is a data centric application with huge list of products > (tens of thousands). Sets of products are grouped into Categories. These > categories are maintained in a hierarchical fashion in the database. There > is no restriction on the number of levels that this categories can go to. > The requirements are to help an admin user manage product categories and > also to be able to bifurcate a category. To cite an example, let us take a > category of Toys which has 100 products beneath it. The admin user wants to > split the Toys category based on age group. he can split the category into > any sub categories ( 3 Yrs - 8 Yrs, 9 Yrs - 15 Yrs, 16+). The admin user > will now have to split the 100 products into these categories. He cannot > leave any products in the Toys category. There are two risks associated with > this: > > a.. The user doesn't finish the multistep process. (handled by the use of > session variables) > b.. The admin user needs to test the application (data changes) on a > staging environment before he can confirm the changes to production. > c.. The admin user needs to be able to roll back changes if he is not > satisfied with the changes made. > > I was planning to use replication. This is described in detail at the > url document(http://www.aspiresys.com/karthik/procs.pdf). The process works > fine as long as the data is correct. given the approach I would like to know > how I can roll the old transaction back. Also I want to know the best method > to achieve this. This is pretty important and any help would be highly > appreciated. > > > Thanks > Karthikeyan B > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Rollback
Hello everybody. I have a clarification/solution to request. I am currently in the process of designing a web application with JBoss 3.2.2 and MySQL 4.0.16. The application is a data centric application with huge list of products (tens of thousands). Sets of products are grouped into Categories. These categories are maintained in a hierarchical fashion in the database. There is no restriction on the number of levels that this categories can go to. The requirements are to help an admin user manage product categories and also to be able to bifurcate a category. To cite an example, let us take a category of Toys which has 100 products beneath it. The admin user wants to split the Toys category based on age group. he can split the category into any sub categories ( 3 Yrs - 8 Yrs, 9 Yrs - 15 Yrs, 16+). The admin user will now have to split the 100 products into these categories. He cannot leave any products in the Toys category. There are two risks associated with this: a.. The user doesn't finish the multistep process. (handled by the use of session variables) b.. The admin user needs to test the application (data changes) on a staging environment before he can confirm the changes to production. c.. The admin user needs to be able to roll back changes if he is not satisfied with the changes made. I was planning to use replication. This is described in detail at the url document(http://www.aspiresys.com/karthik/procs.pdf). The process works fine as long as the data is correct. given the approach I would like to know how I can roll the old transaction back. Also I want to know the best method to achieve this. This is pretty important and any help would be highly appreciated. Thanks Karthikeyan B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database rollback doesn't work
Hi!
I have a simple webapp that allows users to register. The user is inserted
in the DB and a confirmation mail is sent to the user.
If the mail fails I'd like the DB to rollback the transaction, but it
doesn't do it. The new user entry is kept in the DB.
I'm using Tomcat 4.1 and mySQL 2.3.2.
I include the code, log and server.xml.
Thanks for your help.
Monica
***CODE:
Connection dbCon = null;
boolean isSuccess = false;
try {
//inser user in db
dbCon = new DBUtil().getDBConnection();
dbCon.setAutoCommit(false);
userDBPopulator.insertEntry(user, dbCon);
//send mail to user
[snip]
isSuccess = true;
}
//rollback if fail
catch (MailException e) {
log.error("MailException " + e.getMessage(), e);
log.debug("trying to rollback and close");
try {
dbCon.rollback();
}
catch (SQLException e1) {
log.error("SQLException rollbacking " + e1.getMessage(), e1);
}
try {
dbCon.close();
}
catch (SQLException e1) {
log.error("SQLException closing connection to DB " +
e1.getMessage(), e1);
}
}
catch (SQLException e) {
log.error("SQLException " + e.getMessage(), e);
try {
dbCon.rollback();
}
catch (SQLException e1) {
log.error("SQLException rollbacking " + e1.getMessage(), e1);
}
try {
dbCon.close();
}
catch (SQLException e1) {
log.error("SQLException closing connection to DB " +
e1.getMessage(), e1);
}
throw new ServletException(e);
}
//commit user entry
if( isSuccess){
log.debug("commiting");
try {
dbCon.commit();
}
catch (SQLException e1) {
log.error("SQLException commiting " + e1.getMessage(), e1);
throw new ServletException("SQLException commiting " +
e1.getMessage(), e1);
}
try {
dbCon.close();
}
catch (SQLException e1) {
log.error("SQLException closing connection to DB " +
e1.getMessage(), e1);
}
}
LOG:
2003-12-19 15:44:12,546 DEBUG [Thread-3] (RegisterServlet.java:200) -
handle register request
2003-12-19 15:44:12,562 INFO [Thread-3] (UserDBReader.java:202) - No user
found with name monica
2003-12-19 15:44:12,578 DEBUG [Thread-3] (MailHelper.java:88) - Sending
mail to m
2003-12-19 15:44:12,625 ERROR [Thread-3] (MailHelper.java:121) - Messaging
Exception: Sending failed
2003-12-19 15:44:12,625 ERROR [Thread-3] (MailHelper.java:124) - Next
Messaging Exception: Invalid Addresses;
2003-12-19 15:44:12,640 DEBUG [Thread-3] (RegisterServlet.java:282) -
trying to rollback and close
***SERVER.XML
factory
org.apache.commons.dbcp.BasicDataSourceFactory
maxActive
100
maxIdle
30
maxWait
1
username
***
password
driverClassName
com.mysql.jdbc.Driver
url
jdbc:mysql://myMachine.com/myDB?autoReconnect=true
removeAbandoned
true
logAbandoned
true
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rollback error
You start a transaction but you insert into a non-transactional table (probably MyISAM). Change your table type into InnoDB or DBD and it will work. Peter Sap - Original Message - From: "Victoria Reznichenko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 14, 2003 4:54 PM Subject: Re: rollback error > Fernando <[EMAIL PROTECTED]> wrote: > > > > In version 3.23.57 when i do a rollback i get this error message and the changes are not undone, why? > > > > This is what i typed: (NOTE: 'insert.sql' insert a row correctly in an InnoDB table) > > > > mysql> SET AUTOCOMMIT=0; > > Query OK, 0 rows affected (0.00 sec) > > > > mysql> BEGIN; > > Query OK, 0 rows affected (0.00 sec) > > > > mysql> \. /home/fernando/scripts/insert.sql > > Query OK, 1 row affected (0.00 sec) > > > > mysql> ROLLBACK; > > ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back > > Provide output of the SHOW CREATE TABLE command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rollback error
Fernando <[EMAIL PROTECTED]> wrote: > > In version 3.23.57 when i do a rollback i get this error message and the changes are > not undone, why? > > This is what i typed: (NOTE: 'insert.sql' insert a row correctly in an InnoDB table) > > mysql> SET AUTOCOMMIT=0; > Query OK, 0 rows affected (0.00 sec) > > mysql> BEGIN; > Query OK, 0 rows affected (0.00 sec) > > mysql> \. /home/fernando/scripts/insert.sql > Query OK, 1 row affected (0.00 sec) > > mysql> ROLLBACK; > ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back Provide output of the SHOW CREATE TABLE command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CRASH AND ROLLBACK SIMULATION
We used to have a fair amount of data in InnoDB. Had a few crashes from power failures at a crappy hosting provider. Everytime the InnoDB engine seemed to detect the crashes and read up its logfiles and recover. I've never tried any "manual" InnoDB recovery / data extraction. Are there methods? so far it seems to me that InnoDB manages itself after a crash!? innodb.com: "InnoDB tables have automatic crash recovery. You do not need to repair your tables if the operating system or the database server crashes, when there is no disk image corruption" Mike On Thursday 13 November 2003 17.29, Nils Valentin wrote: > Hi there, > > I am not sure I understood your question correctly. What exactly is it that > you want to test ? > > a) the recovery possibility in case of power down > b) the recovery possibility in case of client disconnection (network > interruption, timeout etc.) > c) Recovery possibilities in general accessing the data files directly and > indirectly using command line and/or GUI tools. > > In case a) mysql would recover the data files itself in most cases next > time when the server is started as long as the logfiles, datafiles, config > files are all there in the original positions (talking about InnoDB). > > b) If the network connection times out or client is diconnected than all > executed commands since the last commit,begin gets rolled back (will not be > applied) > > c) If the Innodb files are damaged so that the mysql server does not > startup than no client tool (command line or GUI) that uses the indirect > access method can access any data. > > I know that there are tools in the mysql package which access and repair > (My)ISAM tables directly (server doesnt need to run), but that isnt true > for the InnoDB tables I believe. I am not sure if InnoDB Hotbackup can > directly access the InnoDB tables. > > What I know is that there will be a book coming up in February from Paul > Dubois (Certification Study Guide) which is already described on Amazon. > > When it comes out I believe that it will be the best book on the market so > far, answering many of those and similar questions. I had the honour to > review one of those preprints, all I can say so far is "Very impressive", > you will see for yourself.. > > On Friday 14 November 2003 00:58, nm wrote: > > Do you know how to test a crash and a rollback? > > -- > kind regards > > Nils Valentin > Tokyo/Japan > > http://www.be-known-online.com/mysql/ -- Ongame E-Solutions AB Mikael Fridh Junior Systems Administrator Smedsgränd 3, 753 20 Uppsala, Sweden Mobile: +46 708 17 42 00 Office: +46 18 69 55 00 Fax: +46 18 69 44 11 e-mail: [EMAIL PROTECTED] http://www.ongame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CRASH AND ROLLBACK SIMULATION
Hi there, I am not sure I understood your question correctly. What exactly is it that you want to test ? a) the recovery possibility in case of power down b) the recovery possibility in case of client disconnection (network interruption, timeout etc.) c) Recovery possibilities in general accessing the data files directly and indirectly using command line and/or GUI tools. In case a) mysql would recover the data files itself in most cases next time when the server is started as long as the logfiles, datafiles, config files are all there in the original positions (talking about InnoDB). b) If the network connection times out or client is diconnected than all executed commands since the last commit,begin gets rolled back (will not be applied) c) If the Innodb files are damaged so that the mysql server does not startup than no client tool (command line or GUI) that uses the indirect access method can access any data. I know that there are tools in the mysql package which access and repair (My)ISAM tables directly (server doesnt need to run), but that isnt true for the InnoDB tables I believe. I am not sure if InnoDB Hotbackup can directly access the InnoDB tables. What I know is that there will be a book coming up in February from Paul Dubois (Certification Study Guide) which is already described on Amazon. When it comes out I believe that it will be the best book on the market so far, answering many of those and similar questions. I had the honour to review one of those preprints, all I can say so far is "Very impressive", you will see for yourself.. On Friday 14 November 2003 00:58, nm wrote: > Do you know how to test a crash and a rollback? -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CRASH AND ROLLBACK SIMULATION
Hi, > Do you know how to test a crash and a rollback? You mean a client app crashing on you? How about disabling/unplugging the network? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CRASH AND ROLLBACK SIMULATION
Do you know how to test a crash and a rollback? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rollback error
Hi In version 3.23.57 when i do a rollback i get this error message and the changes are not undone, why? This is what i typed: (NOTE: 'insert.sql' insert a row correctly in an InnoDB table) mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> \. /home/fernando/scripts/insert.sql Query OK, 1 row affected (0.00 sec) mysql> ROLLBACK; ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back http://webmail.wanadoo.es. Tu correo gratuito, rápido y en español -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on rollback segment equivalent
Fareeda, - Original Message - From: "fareeda" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, September 25, 2003 5:32 PM Subject: Question on rollback segment equivalent > What is the equivalent of rollback segments (from Oracle) on MySQL? > And if something is being used internally, can I change the size of > this? I do not want to run into problems like exceeded rollback size > or something like that. in InnoDB there is a 'rollback segment', which is the totality of the undo logs in the tablespace. But you do not need to tune it in any way, and it takes much less space than in Oracle. > Any pointer appreciated > Thanks > Fareeda Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do InnoDB rollback segments expand dynamically?
At 12:43 AM 9/9/2003, you wrote: Bill, - Original Message - From: ""Bill Todd"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, September 09, 2003 4:27 AM Subject: Do InnoDB rollback segments expand dynamically? > Using InnoDB with an autoextend tablespace, if I start a transaction that > results in many record versions, will the rollback segments grow dynamically > and force the tablespace to grow dynamically to provide the required room > for record versions in the rollback segments? yes. I have a question, too: if you are the Bill Todd who posts to the Borland newsgroups, what is the status of the new DBExpress driver for MySQL? The problem in old drivers was that they established a new connection for each individual SQL statement. Transactions and several other MySQL features did not work because of that. I saw some 4 weeks ago a Borland engineer mention that this is now fixed in a beta release of the driver. Is this so? Heikki, Most people have given up on DBExpress for MySQL because of bugs. If you want a MySQL component that works well really with Delphi, check out MySQL DAC 1.50 from CoreLabs http://www.crlab.com/. They have an eval that you can download. I've been using it for the past 3 months and I'm really impressed. They have a FetchRows property that allows the query to fetch as little at 25 rows at a time from a large query (you can specify how many rows to fetch at a time). This allows a million row query to open instantly. The documentation and support are also excellent. And of course it handles InnoDb tables. :-) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do InnoDB rollback segments expand dynamically?
Heikki, You can get the new driver at http://codecentral.borland.com/codecentral/ccweb.exe/author?authorid=163237 I have not tried it to see if the multiple connection problem is fixed. I am new to MySQL and have not tried to use it with dbExpress yet. Bill > -Original Message- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Monday, September 08, 2003 10:44 PM > To: [EMAIL PROTECTED] > Subject: Re: Do InnoDB rollback segments expand dynamically? > > > Bill, > > - Original Message - > From: ""Bill Todd"" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Tuesday, September 09, 2003 4:27 AM > Subject: Do InnoDB rollback segments expand dynamically? > > > > Using InnoDB with an autoextend tablespace, if I start a > transaction that > > results in many record versions, will the rollback segments grow > dynamically > > and force the tablespace to grow dynamically to provide the > required room > > for record versions in the rollback segments? > > yes. > > I have a question, too: if you are the Bill Todd who posts to > the Borland > newsgroups, what is the status of the new DBExpress driver > for MySQL? The > problem in old drivers was that they established a new > connection for each > individual SQL statement. Transactions and several other > MySQL features did > not work because of that. > > I saw some 4 weeks ago a Borland engineer mention that this > is now fixed in > a beta release of the driver. Is this so? > > > Bill > > Best regards, > > Heikki Tuuri > Innobase Oy > http://www.innodb.com > Foreign keys, transactions, and row level locking for MySQL > InnoDB Hot Backup - a hot backup tool for MySQL > > Order MySQL technical support from https://order.mysql.com/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do InnoDB rollback segments expand dynamically?
Bill, - Original Message - From: ""Bill Todd"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, September 09, 2003 4:27 AM Subject: Do InnoDB rollback segments expand dynamically? > Using InnoDB with an autoextend tablespace, if I start a transaction that > results in many record versions, will the rollback segments grow dynamically > and force the tablespace to grow dynamically to provide the required room > for record versions in the rollback segments? yes. I have a question, too: if you are the Bill Todd who posts to the Borland newsgroups, what is the status of the new DBExpress driver for MySQL? The problem in old drivers was that they established a new connection for each individual SQL statement. Transactions and several other MySQL features did not work because of that. I saw some 4 weeks ago a Borland engineer mention that this is now fixed in a beta release of the driver. Is this so? > Bill Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do InnoDB rollback segments expand dynamically?
Using InnoDB with an autoextend tablespace, if I start a transaction that results in many record versions, will the rollback segments grow dynamically and force the tablespace to grow dynamically to provide the required room for record versions in the rollback segments? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock in share mode/rollback question
Chris,
please send your messages to [EMAIL PROTECTED] The newsgroup
mailing.database.mysql is only a mirror.
- Original Message -
From: "Chris" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Tuesday, September 02, 2003 9:31 PM
Subject: lock in share mode/rollback question
> If I issue something like this for an innodb table, if the select
> returns no rows, do I need to roll back? I.e., is a rollback required
> to release any resources, even though no rows were locked?
>
> begin;
> select * from test where primary_key_column = 1 lock in share mode;
> ...
COMMIT and ROLLBACK release all InnoDB locks of a transaction. They do not
release MySQL's manual table level locks, which you set with the statement
LOCK TABLES ... {READ | WRITE}.
> Thanks,
> Chris
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rollback segment
Susan, you had posted your message to the newsgroup mailing.database.mysql. That is only a mirror of the mailing list [EMAIL PROTECTED], where you should send your postings for them to be readable for all. InnoDB has a data structure which is equivalent to the 'rollback segment' of Oracle. But, contrary to Oracle, you do not need to configure it in any way. InnoDB uses the rollback segment both to implement transaction rollback and read consistency. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL - Original Message - From: "Susan Lam" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, August 11, 2003 11:52 AM Subject: rollback segment > I am new to mysql. I am just wondering does mysql has rollback > segment? If there is, does it use for rollback only or also for read > consistency? If not, how do mysql maintain read consistency? > > Thanks, > Susan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Rollback in PHP within a website
Bruce, - Original Message - From: ""bruce"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, July 13, 2003 11:32 PM Subject: MySql Rollback in PHP within a website > Hey... > > I have a question. I need to be able to try to perform a database update, > but if it doesn't succeed, I need to be able to rollback the changes, and to > inform the user that the changes didn't succeed. > > I've looked at the MySql site, and can see somewhat how the Commit/RollBack > functions work. However, I'm not sure how to create the required PHP code to > implement this kind of process... > > Basically I need to: > > Create query... > Perform Update on the table(s) > Perform my check(s) > If the checks fail > alert user > rollback updates > else > success > > It's my understanding that I need to somehow set "START TRANSACTION" prior > to beginning the update. But I'm not sure how to do that within the PHP > code... Nor am I sure if there's something else I need to do... > > Any examples/hints/sample code will be appreciated. Thanks for any > assistance! can you simply execute in PHP BEGIN and ROLLBACK as normal SQL command strings? Like you would execute INSERT INTO ... It is better to send these commands to the mysqld server as normal SQL strings, because in old versions of MySQL middleware special 'commit' etc. function calls may not be implemented. Is it in PHP so that the mysqld connection is always closed if you move to another HTML page? When mysqld ends a connection it automatically rolls back the current uncommitted transaction. > Regards, > > Bruce > [EMAIL PROTECTED] > (925) 866-2790 Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Rollback in PHP within a website
Hey... I have a question. I need to be able to try to perform a database update, but if it doesn't succeed, I need to be able to rollback the changes, and to inform the user that the changes didn't succeed. I've looked at the MySql site, and can see somewhat how the Commit/RollBack functions work. However, I'm not sure how to create the required PHP code to implement this kind of process... Basically I need to: Create query... Perform Update on the table(s) Perform my check(s) If the checks fail alert user rollback updates else success It's my understanding that I need to somehow set "START TRANSACTION" prior to beginning the update. But I'm not sure how to do that within the PHP code... Nor am I sure if there's something else I need to do... Any examples/hints/sample code will be appreciated. Thanks for any assistance! Regards, Bruce [EMAIL PROTECTED] (925) 866-2790 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: rollback a table?
Yes, Back up your data every night. Then you will only have to perform a single simple restore instead of rebuilding from four months ago. I know that this sounds like a smart ass reply but I am serious. Disk space is cheap. CD's are cheap. Other media like DVDs are getting cheaper every day. Unless you have a monster database containing gigabytes of data the cost of a nightly archival should be far less than the cost of your time doing a four month restore of the data. Do the math and show your boss if he needs any convincing. The numbers should speak for themselves. John Griffin -Original Message- From: Mark [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:14 PM To: [EMAIL PROTECTED] Subject: rollback a table? Hi, Something bad happened the other day, a query hosed all the data in my table, but luckily I had an original dump of the table from 4 months ago and binlogs from then on. I had to load the original table into a separate db and then grep through the binlogs for queries to update it with, stopping at the one that hosed my data. Anyway, it was a project and it seems like there should be a better way. Is there? - 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: rollback a table?
Mark wrote: Hi, Something bad happened the other day, a query hosed all the data in my table, but luckily I had an original dump of the table from 4 months ago and binlogs from then on. I had to load the original table into a separate db and then grep through the binlogs for queries to update it with, stopping at the one that hosed my data. Anyway, it was a project and it seems like there should be a better way. Is there? Kinda. Firstly, I use the human-readable log, which is produced with the --log-update option of mysqld. Secondly, back up your data more often. Every night is good... Most likely using the --log-update option slows things down a little during inserts / updates, but it's so much easier to read than the binary log. I activate this backup script via crond every night: #/bin/sh DATE=`/bin/date +%d"-"%m"-"%y` cd /root/sql/backups for I in EnergyShop NUS ebills irm mysql sales Fuel do /usr/local/mysql/bin/mysqldump -v --opt $I > $I.dump -pMyPasswordGoesHere done /usr/local/mysql/bin/mysqladmin shutdown -pMyPasswordGoesHere /usr/bin/nice -n -10 /usr/local/mysql/bin/mysqld_safe --enable-locking --log-update --log-slow-queries --log-long-format & cd .. tar -zcvf backups_$DATE.tar.gz backups echo "Backup of MySQL databases complete!" -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - 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
