MYSQL :: Transaction Control

Querying For Transaction Totals And Last Transaction Date
I have a list of currency transactions made by users. I need to generate a list of users along with their transaction total (sum for each user) AND the date of their last transaction.

Sound doable?

MySQL 4.1

Data looks like this:

user, amount, date
==============
1, 50, 2003-11-23
2, 34, 2004-10-04
3, 45, 2005-08-30
3, 98, 2006-04-02
3, 76, 2000-02-03
2, 91, 2000-12-04
1, 11, 2003-11-05
3, 22, 2003-03-06
4, 34, 2006-03-07
5, 45, 2006-06-24

I figure I can group by userID but how do I get the date of the most current transaction?

Using the data above, the query would return:
1 (user) 61 (subtotal) 2003-11-23 (last transaction)
2 (user) 125 (subtotal) 2004-10-04 (last transaction)

Log Transaction?
Log Transaction

Does MySQL has Log Transaction

If not what will be the better way to make a backup of the database with log out the users.

Transaction And Two Tables
a little confused about transactionts on Mysql. I have such situation:
Two different tables, I need to add many rows to the first table and after those rows are added I need to add one row to the second table...

My question is how do I use transaction on two tables at the same time (no problems when dealing with just one table), because it's needed to success on all queries to both tables, if one fails, then rollback to starting point.

How To Define A Transaction?
I have a series of sql statements. I want them to be executed in one transaction.
How to do that?

Function Transaction
I'm working on a little framework based on SQL, i'm fighting with session becouse i'm need to check if the user is login or not and then if can see the resource or not, it's can be change for each resource. Code:

START TRANSACTION
After START TRANSACTION but before COMMIT, are intervening SQL transactions
visible to other threads or processes? I understand that failing to perform
a COMMIT, or explicitly performing a ROLLBACK, results in the transactions
occurring after START TRANSACTION being undone. But until and if the
transactions are undone, would queries to the database reveal those
transactions?

Transaction Processing
When doing an update in mysql in a multi-user session eg webpage/php/mysql , how do i have transaction locking on tables. As required data is being updated i don't want other users accessing it?

How To Implement A Transaction
I want to implement transaction mechanism.

Think about world document each change saved in a temporal workspace and at the end the user can press on save and the changes apply to the original document or close and the document will not be changed.

My client can do a lot of changes to the configuration we need to save this changes in a temporal workspace (in order to mange the relation between records ... and in order to prevent lost of his work in case that the client crash)
At the end (he can do the changes for a week) he can choose apply changes that will merge those changes to the configuration or cancel.

So, i need to implement here something similar to transaction (i can use transaction because the client can do changes for a week).

does any one have an idea on how transaction implemented in DB?

Transaction Logging
I am reading up on transaction logging, and am wondering whether MySQL's binary log is its "UNDO/REDO log"? I'm a beginner to all this, and not sure whether that's the correct interpretation of the MySQL binary log.

How does MySQL's transaction logging, or any transaction logging mechanism, work? I was reading up on general transaction logging principles but it was getting far too specific to SQL Server and other DBMSs, so I figured that I could come here and someone here might point me to some MySQL-specific resources on this, or perhaps help to explain how this all works.

e.g. Does the transaction log get rid of all transactions once committed, what's the difference in how UNDO and REDO logs work, are these logs stored in a more complex manner than just logging the plain text statement (I'd imagine so, because otherwise how would an undo operation work)?

In fact, I find it funny that I'm asking all the questions above because I'm still not quite clear on what the purpose of transaction logs is in the first place!

While it would be great if somebody could help clear up the confusion I'm having, it would also be good if you could point me to some MySQL-specific resources on this topic. I browsed through the MySQL documentation and did a web search, but either did not find any resources period, or did not find resources that "dumbed it down" enough for me.

Transaction Performace
Im going to develope a massive website that going to handle morethan 10,000 transactions perminute(my Assumption).
Is it possbile with MySQL Database to Handle 10,000 concurrent Users? Will MySQL take this much of load?

Row Locking In A Transaction
I've read that any selct statement issued in a Transaction will lock the resulting rows in the record set until the transaction is either COMMIT or ROLLBACK is issued. well what happens in the following situation.... A user workin in a content management system begins a transaction and within that transaction a SELECT statement is used on a table to populate a drop down menu with options. The drop down options are also used as menu options elsewhere on the site. If that statement selects all the rows in that table, does that mean that every row in that table will be unavailable to any other request until this user is done the transaction?

If this is the case, is there a way to NOT lock rows with certain statements in the middle of a transaction without ending the transaction?

Transaction Viewer
I'm writing an application that displays a list of transactions from a
mysql database. The problem I am having is I need the user to be able
to view the last x transactions but there needs to be an initial row
with the "balance forward" from the previous transactions.
Is there any mysql statement for this or class for this type of
feature or can point me in the right direction?

Controlling A Transaction
I have a question concerning general syntax of a Transaction block. My application is written in ColdFusion and my table in question is InnoDB.
Currently we have a non-auto-incrementing PK. My DBA instructed me this cannot change for some time.
So to retrieve the next available key to use, I run a SELECT:

CODESELECT (id + 1) AS id
FROM tradingPartners
ORDER BY id DESC

For How Long Transaction Can Be Open?
I want that my client will open a transaction for a non limit time.

Can i confiugre the MySQl to not close open transaction due to timeout?

Transaction Secure Code
When executing a specific query, I need to lock the db to ensure that no data is altered while the query executes.

My question is: Do I need to take a ROLLBACK on the db to release the lock, or is it enought just to set autocommit=1 again?

Simplified example of how I do it now:
SET AUTOCOMMIT=0
BEGIN
SELECT .......bla bla bla <execute some php code based on the result>
ROLLBACK
SET AUTOCOMMIT=1

Example of how I want it to be:
SET AUTOCOMMIT=0
BEGIN
SELECT .......bla bla bla <execute some php code based on the result>
SET AUTOCOMMIT=1

Are there any downsides by not using ROLLBACK in the second example? The database has not been altered in any way.

Timeout Transaction InnoDB
I have a application with invoice and invoice_items, but i need
used transaction to save records.

If only user save data this fine. But if two users save data same time, one
users save correctly and another user go to rollback directly,
if second user save data again this ok.

InnodBd Documentation have a timeOut transaction = 50 Secs.
But, the application save all records in about 2 secs.

The sintax is the next: Code:

MySQL 5.0 Transaction Refrerence
Does anybody know of a good user/reference manual that covers transactions. Specifically executing the transaction from Vb 6.0?

LAST_INSERT_ID() In START TRANSACTION
I have a transaction that runs a 3 insert statements.

The first inserts an record into the "patientsmain" table, generating a patient ID key (PATNR).

The second inserts into "patientdetail" table, this has a foreign key (also called PATNR), which I populate using LAST_INSERT_ID().

The 3rd insert goes into "patientpanel" table, it is similar to the 2nd INSERT. This table also has a PATNR foreign key but using LAST_INSERT_ID() here fails as it takes the ID generated by 2nd INSERT rather than the 1st INSERT.

How can I have the 3rd insert (or subsequent inserts) use the ID generated by my first insert? Code:

Transaction Management Support
if MySQL supports transaction management - in relation to rolling back a database to a previous consistent state?

Current Transaction Count
does anybody know of a way of determining the number of uncommitted/rolled back transactions in MySQL.

What Type Of Transaction [isolation Level] Do I Need?
Part of my web application needs to perform the following action:

User A UPDATES field "hits" in table X, increaseing the value of X +1.
User A then SELECTS the new value of field "hits" that they just increased.

If I have multiple concurrent users the following scenarios can occur:

User A: UPDATE field "hits" + 1 (ex, 49 to 50)
User B: UPDATE field "hits" + 1 (ex, now 50 to 51)
User A: SELECT val from field "hits"(should be 50, not 51!)
User B: SELECT val from field "hits" (should be 51)

What type of transaction or transaction isolation level do I need so that when 1 user updates the field "hits", they can then get the value of "hits" that THEY CHANGED IT TO??

I'm using an InnoDB table & mySQL.

Using Transaction In C# Lock Time Out Problem
I am using Innodb.I have grid and a list of item. Then I run a loop and based on it i will run an update statement which is all in block of transaction. A snippet of the code is as below .

MySqlConnection connectionCentral;
MySqlTransaction transactionCentral = null;
connectionCentral = new MySqlConnection("Address='localhost'Database='trial1'User Name='root'Password=?'");
connectionCentral.Open();
transactionCentral = connectionCentral .BeginTransaction();

for (int j = 0; j < gridReceiveTransfer.RowCount; j++)
{

String myUpdateQuery8 = "Update tblProduct " +
"Set tblProduct.branch1 = tblProduct.branch1 +" + Convert.ToInt32(this.gridReceiveTransfer[8, j].Value.ToString()) +
" Where tblProduct.productID=" + Convert.ToInt32(this.gridReceiveTransfer[0, j].Value.ToString());

MySqlCommand myCommandCentral1 = new MySqlCommand(myUpdateQuery8);
myCommandCentral1.Connection = connectionCentral;
myCommandCentral1.Transaction = transactionCentral;
myCommandCentral1.ExecuteNonQuery();

}

This how i have actually code my code in C#. The error I get is "Lock wait timeout exceeded; try restarting transaction". I know why I get the error is sometime when the update is for the same productID within the same loop. So how can I overcome this problem where I want to keep all the updates within the loop so incase there is one error I want to rollback the whole thing.

Creating Transaction In Stored Procedures
I am working on a project at University using php, mysql and apache. The web application should should interract with the database system using stored procedures and transactions.

I know how to create stored procedures in mySQL but I am not sure how to implement the transactions into it.

The following php code needs to be stored procedure in mySQL:

<? php
$query = "SELECT * FROM item ORDER BY name";
@mysql_query("BEGIN");
$result=mysql_query($query, $this->db_connection);
if(!$result)
{
@mysql_query("ROLLBACK");
return $result;
}
else
{
@mysql_query("COMMIT");
return $result;
}
?>

Oledb Provider With Transaction Support?
i downloaded oledb provider from mysql.com, i use Delphi's ADO. but i found that this oledb provider does not support transaction.

is there any oledb provider that does support transaction?

Control Flow
I am having a hard time with Control Flows which is what i think I need to solve my current problem.

Below is a sample query dynamically built based on user screen selections. Notice that it attempts to JOIN 5 tables to fscenario.

My problem is that fscenario.`fsce_comp` may or may not be NULL.
Either way I need the record retrieved if all other criteria is met.

If fscenario.`fsce_comp` is not NULL I want to display the corresponding fcomponent.`fcom_name`.

If fscenario.`fsce_comp` is NULL, I still want to retrieve the record displaying nothing for the name.

Bad explanation, so I hope you follow?

Using the manual (http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html) I have tried several IF, CASE, IFNULL structures. They all return records where fscenario.`fsce_comp` is populated and exclude the records where fscenario.`fsce_comp` is NULL.

I guess I am not understanding how to implement the syntax......

Source Control
Does anyone know if there is a program (for windows) that can help us catalog our sprocs and table setups into source control?

MySQL DB Control
How can i manage my mysql db? Example: How can i learn db size? Phpmyadmin is not installed on my hosting company.

Remote Control
I have MySQL running on a desktop computer that I log into through logmein.com and this is not the best solution. How can I connect remotely to use the MySQL Command Line Client on my desktop so I don't have to wait so long for each line to appear? I should also point out that this computer is on my LAN, so I shouldn't have to configure the router.

One problem I am running into right now is that when I try to make a new table, I keep getting error 1064 (42000) and I'm not sure why because everything looks correct. Code:

Control Center
I started using Control Center as a GUI for MySQL. Making progress but I could do better if I had the help file. It is grayed out. Is there a place to locate this?

Revision Control
I've been searching the mysql forums and documentation for anwers to this
to no avail..Is there any facility for revision control within mysql? I need to be able to have
snapshots of a mysql database that users can modify, later merging those
modifications in with the main database, similar to branching in cvs.
Does something like this exist already? Is it doable by copying the database
and then logging edit transactions on the copy? What about using the storage
engine plugin for tracking modifications?

Control Center
I am new to using the Control Center-0.9.2-beta. My problem is that I
cannot seem to display more than 1000 rows in my result set when I know that
there is 1534 rows. I know that it is a setting somewhere, but is it in
MySql or in the Control Center somewhere. I am running this on a Windows
2000 machine.

Control Center
Getting the following message when I click on the MySQL shortcut on my desk
top:

Error Starting Program
The msvcrt.dll file is linked to missing export ntdll.dll:Rtlgetntversion
numbers.

A device attached to the system is not functioning.
Have gotten both Dll's and stuck em in the system and program directory...
nothing happening there.
Would like to find out what "Rtlgetntversion numbers" is all about.

Control Panel
i'm having some problems with my MySQL install. Firstlty when I installed MySQL I never got an icon of any kind within the control panel. I've been looking at tutorials of how to create a database and many of them use the control panel option as a starting point but I can't because MySQL is not present within the cPanel. Also when I click on the MySQLadmin.exe in the bin it does not open probably. It opens briefly- shows a black screen with decending text. It then dissappears completely after several seconds. It cannot be seen or opened in the tray

Control Max Time
Does anyone know of any parameters that can be set to control the maximum time limit mysql will allow a single query to run before timing out etc?
Rather than let badly written queries run for long periods I would like to just abort them if they exceed a set amount of time.
I know the php calling script should/could handle this with defensive coding etc. However, I'm not always in control of how other users write their scripts that interact with mysql.

Transaction Timeout For Data-base Mutex
I'de like to use transaction locking mechanism as a mutex. I've already succeeded making this work on pgsql.

The algo is simple: I open a connection, set lock timeout to whatever i want, start the transaction and try to update a dummy row or create a dummy table (P/semTake of mutex), check if I got the mutex token (affected rows > 0 on update, no error on create table) (if not exit), execute my critical job and finally close the data-base connection causing a roll-back on update or create table (V/semGive of mutex).

with MySQL, I haven't found anything to modify the lock timeout. Does someone know?

Mysql Equivalent Of Transaction Count Of SQL Server
Is there an equivalent in mysql for Transaction count variable of Sql
server ?

Deadlock Found When Trying To Get Lock, Try Restarting Transaction.
Im getting the following SQLException during an insert or a delete or an update statement. "Deadlock found when trying to get lock; try restarting transaction message from server: lock wait timeout exceeded; try restarting transaction".

I have a jsp page which does an insert, i have another jsp page which has a delete query that deletes a particular row, and then tries to insert (using an id,which is a primary key), then there is an update statement. In both these pages, i sometimes get the above mentioned exception. When the Tomcat server is restarted, i do not get the exception.

Mysql Equivalent Of Transaction Count Of SQL Server
Is there an equivalent in mysql for Transaction count variable of Sql
server?

MySql Control Center
I am new to using the Control Center-0.9.2-beta. My problem is that I
cannot seem to display more than 1000 rows in my result set when I know that
there is 1534 rows. I know that it is a setting somewhere, but is it in
MySql or in the Control Center somewhere. I am running this on a Windows
2000 machine.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw

Mysql Control Center
I'm trying to get the GUI to connect to a remote mysql database from my
client machine. What is the proper syntax for this in the GUI? I have
tried everything with no luck: using the ip of the host, using the complete
dns hostname of the host, using root as the user, using root@hostname as the
user. I keep getting access denied for every attempt. I'm sure I am using
the correct password.

MySQL Control Center
I can access my MySQL database with MySQL Control Center from a computer with a direct connection to the internet, but I can't do it from my other computer on a network. Is there a way to use MySQL Command Center through a network/router? I'm unable to find any connection settings options in the program.

Start Control Center
Getting the following message when I click on the MySQL shortcut on my desk
top:

Error Starting Program
The msvcrt.dll file is linked to missing export ntdll.dll:Rtlgetntversion
numbers.

A device attached to the system is not functioning.

Have gotten both Dll's and stuck em in the system and program directory...
nothing happening there.
Would like to find out what "Rtlgetntversion numbers" is all about.

Retaining Control From MyODBC
I'm developing a system with an Excel front-end that uses ADODB and MyODBC to fire off instructions to a (local machine) MySQL server. As I step through my VBA code, it sends an instruction to MySQL (e.g. gconMySQL.Execute "DROP TABLE IF EXISTS MyData;" ), waits for the instruction to be executed, before returning control to the code and moving on to the next VBA statement. All well and good; this works fine for the majority of the processing.

However there is some initialisation of the MySQL database, particularly loading some data tables into MEMORY, that takes up to half a minute to complete. I'd rather fire off the instruction(s) to MySQL to do this so that MySQL initialises invisibly in the background, whilst the user continues playing around with the front-end themselves.

MySQL Control Center
I have installed MySQL Database Server 4.0 and MySQL Control Center, but i can't still use MySQL CC to create new database or other feature.

MySQL Control Center
I am trying to install MySQL Control Center for the first time (the latest
version). I am using Windows XP. When I try running the setup routine, I
get through all the prompts and when it starts to install, it gets stuck at
the 'Initializing... ' screen. And it does not go any further. I cannot
even cancel. the operation.

Mysql Control Center
Quote: ERROR 1109: Unknown table '1' in information_schema
Quote: [stuff] ERROR 1146: Table 'cdcol.1' doesn't exist
Quote: [stuff] ERROR 1146: Table 'mysql.1' doesn't exist
Quote: [stuff] ERROR 1146: Table 'webauth.1' doesn't exist

I get that error when ever i try to access any tables in mysql control centre. The error is almost the same, except for the database name. It does this even on a clean install.
Im using xampp

MySQL Control Center
how can i declare a Vendor-Specific
attribute in MySQL Control center. In the regular radius i declare it
well.
I check the radtest and its write : radclient:WARNING: Malformed
RADIUS packet from host 10.0.1.16: Vendor specific attributes do not
exactly fill Vendor-Specific

Transaction Control

I am a recent user in mysql and I'd like to know if I need control the transactions in java when I am using mysql. I am asking it because I have read the mysql already control the transactions automatically, that is, it automatically control the commit and roolback commands.