MYSQL :: Transaction Secure Code

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)

Access SQL Code > MySQL Code (INNER JOINs)
I've basically created the following tables / queries as part of my first mySQL project :

Candidates
---------------
Candidate_ID (autonumber)
Name (text)
...

1, Iain
2, Fi
3, Rob

Vacancies
--------------
Vacancy_ID (autonumber)
Vacancy (text)
...

1, Cartographer
2, Gardener
3, Occupational therapist
4, Web designer
5, Recruitment manager

Profiles
-----------
Profile_ID (autonumber)
Profile (text)
...

1, Map making
2, Web design
3, Gardening
4, Hand therapy
5, Recruitment
6, Aviation
7, Sport
8, Travel

VacancyProfiles
----------------------
Vacancy_ID (number)
Profile_ID (number)
...

1,1
1,2
4,2
2,3
3,4
5,5
5,6

CandidateProfiles
--------------------
Candidate_ID (number)
Vacancy_ID (number)
...

1,1
1,2
1,7
1,8
2,3
2,4
2,8
3,5
3,6
3,7

and from there created two queries

CandidatesQuery
--------------------

SELECT Candidates.Candidate_ID, Candidates.Name, Profiles.Profile_ID, Profiles.Profile
FROM Profiles INNER JOIN (Candidates INNER JOIN CandidateProfiles ON Candidates.Candidate_ID = CandidateProfiles.Candidate_ID) ON Profiles.Profile_ID = CandidateProfiles.ProfileID;

1, Iain, 1, Map making
1, Iain, 2, Web design
1, Iain, 7, Sport
1, Iain, 8, Travel
2, Fi, 3, Gardening
2, Fi, 4, Hand therapy
2, Fi, 8, Travel
3, Rob, 5, Recruitment
3, Rob, 6, Aviation
3, Rob, 7, Sport

and

Vacancies_Query
-------------------

SELECT Vacancies.Vacancy_ID, Vacancies.Vacancy, Profiles.Profile_ID, Profiles.Profile
FROM Profiles INNER JOIN (Vacancies INNER JOIN VacancyProfiles ON Vacancies.Vacancy_ID = VacancyProfiles.VacancyID) ON Profiles.Profile_ID = VacancyProfiles.ProfileID;
...

1, Cartographer, 1, Map making
1, Cartographer, 2, Web design
2, Gardener, 3, Gardening
3, Occupational therapist, 4, Hand therapy
4, Web designer, 2, Web design
5, Recruitment manager, 5, Recruitment
5, Recruitment manager, 6, Aviation

Trouble is, I come from an Access background, and I think the syntax of mySQL SQL is different to Access SQL - in the case here for the INNER JOINs - could anyone help out with the syntax for the queries above in mySQL friendly SQL, as I'm a bit unsure of what it should be.

Update Several Columns In A Row - Postal Code Or Zip Code
Ok - I have 125 records. They all have different postal codes. I have a column named "Region". I want to update all postal codes starting with "V" to region "WST".

How can I do this? I know how to update on MASS based on the column, but can I what I need to do?

Non Networked = Non Secure
mySQL looks good for a Network. BUT as a standalone it is not secure.Open anytable.MYD in a hex editor and you can see the data.So we can't sell our software to clients on wind98.

Believe it or not we have had a lot of success selling software with a proprietary database where the data is in 'unusual' format.
Clients are happy because casual snoopers can't see personal data,and we dont promise "strong" security.

Security through obscurity works!I wish mySQL had even a touch of obscurity in its MYD files.

Replication Secure
I'm about to jump into the world of mysql replication, but am wondering if the connection between database servers is secure seeing as how they will have to communicate across the internet.

How To Secure My Mysql Database From Others?
1) if somebody else knows my mysql database username and password ( username with all permissions ) can they copy my database even if they do not know my control panel password?

2) Suppose I have placed my database username and password in a .php file in my file manager not in the admin folder, is there any possibility for others to steal my username and password?

3) what steps should I take to secure my mysql database from hacking and all such unwanted dangers.

How To Store Secure Password
I want to store password in encrypted format(Non visible i.e *** ) in mysql tables?

Designing A Secure Database?
I'm working on a website/database deal, and right now I'm designing the
security model.

It's using LAMP(HP), so all the tricks are going to be through PHP.

First off, this will be behind a firewall with SSL encryption. We may also
setup some kind of VPN tunneling.I'll be ignoring other security details
that don't apply to the problem at hand, but please feel free to suggest.

I'm going to have users enter a username and password, with PHP emulating
the htaccess dialogue boxes. I would like to check the entered values in a
permissions table. This table looks like:

username | passwordhash | ipaddress | permission

All users will have a static IP, so they have to match the
username-password-ipaddress combination. I'm storing the passwords as an MD5
hash in case someone breaks in and reads the table.

So what MySQL user does PHP log onto as the database as, in order to read
the permissions table? (After that, we just go with the logged user's
permission). If I make a special user that just has permission to read the
permission table, do I have to store that user's password plaintext in a php
script somewhere, thus adding a security risk if someone were to get a hold
of that password?

Making Secure Database
how would i go about making my database as secure as possible so users information is kept as safe as possible....

at moment i am using md5 encryption for their passwords... does any1 recommend anything else ?? and also in general to keep my data as safe as possible..... will web hosts deal with keeping mysql database secure or should i do sumting against it?

C API Secure Client Connections
Any idea where can I get a copy of the libmysql dll with ssl enabled?

Secure Backup On Win32 Systems?
mostly for unix platforms.

On win32, what is the best way to run a secure mysqldump so that my
password doesn't have to live in a script somewhere?

I'm using perl scripts to kick off my backup commands.

How Do I Enable Query Logging? Is It Secure?
How do I enable query logging? I am using version 4.1.22-standard

From Test Environment To Secure Commercial Use.
I'm relatively new in the database administration field. Uptil now I've only tried MySQL together with PHP in a test environment. I would appreciate if someone could answer a couple of question, I have regarding commercial use and a secure professional configuration.

Uptil now I've used XAMPP as my installation on my own computer on localhost. Now we'll install the same software package on a server. I guess XAMPP and PHPmyAdmin is not a professional setup right?

Questions:
1. What is the best recommended and professional setup for commercial use?
2. What is the best GUI for the database?
3. What is Frontend and Backend?
4. Backup setup? Just if anyone have some recomendation.

I've built a web based dynamic Content Management System in PHP and MySQL for internal use. Using the localhost as the developer platform. I've never before built something like this for commercial use.

5. How do best implement this system on our companys server, so we all can use the system?

Secure Backup On Win32 Systems?
i know items have been posted before, mostly for unix platforms.

On win32, what is the best way to run a secure mysqldump so that my
password doesn't have to live in a script somewhere?

I'm using perl scripts to kick off my backup commands.

Java Code Or Pseudo Code For "Outer Join"
I want to implement the "outer join" functionality in Java. Can
somebody explain the pseudo code for the same. OR what needs to be done
to extend the hash-join Java code of equijoin.

Mysql Connection - Plain Text Or Secure
Are mysql connections over the internet plain text or are they encrypted.
If not encrypted is there a way to encrypt them?

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?

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.

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?

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?

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?

Aggregated Variable Only Work With Transaction In Query Browser?
I want to select rows and use user variable to generate an incremental id for the result. For example, I want the following query

select @id:=(@id+1) as id from file_list limit 5

to return 5 rows with values 1 to 5. However, the result is ....

Start Transaction Blocks Rows Affected Return Value
I'm calling an update stored procedure from ASP.NET code via the .NET Connector v5.1.4.0, and Microsoft's Data Application Block v3.1.0.0

The actual code calls the procedure via the application block's ExecuteNonQuery method, which is supposed to return the 'rows affected' by the procedure code.

Everything works fine, but if I wrap the mysql code in a Start Transaction; and a Commit; then ExecuteNonQuery returns 0 rows affected, eventhough that's not true.

If I comment out the transaction statements then ExecuteNonQuery returns the actual number of rows affected.

Error : Lock Wait Timeout Exceeded; Try Restarting Transaction
i am trying to create a java application using mysql but if i try to create multiple connections, i keep on getting this exception given below. I have read about the TransactionDeadLockDetectionTimeOut option but i am neither able to find config.ini nor i am running the cluster for which this option applies. Code:

My Mysqld (server) Drops Connection, Transaction And Lock After 15 Sec Of Inactivity
I wrote similar question earlier but maybe it was unclear that why is post this question.

Used server/os version
Mysqld 5.0.22 - win 2000 sp3 installed as service

Used Clients:
Mysql.exe default cmd-line client - v 14.12 Distrib 5.0.22
QueryBrowser - gui client - v 1.1.20

To simplify my problem:
I'm unable to use mysql transactions and locks (for update statement) because my server drops connection on every 15 seconds of inactivity despite to what value is set on params in my.ini file or via set variable_name=value statement. This inactivity is cased by user behavior on my binary win32 application. He starts transaction and set row-level lock by opening edit form and it may take some time before commit or rollback can be send to server (usually more than 15 sec).

More info is available on referenced posts: