Friday, September 30, 2005

1,000th user at db4free.net

db4free.net has just welcomed its 1,000th visitor! This project went online on 29th of June this year.

db4free.net is - as far as I know - the first and only database provider that offer's MySQL 5 databases for free. The server currently runs MySQL 5.0.13-rc-Max. So it also gives the opportunity to test storage engines like BerkeleyDB, Archive or Federated which are not available in the standard packages.

This project gives interested people a chance to test new features of MySQL 5 without the need to run a server at their own. I hope, people get used and comfortable with these new feature. Many users will probably move their databases to a commercial Internet Service Provider after testing at db4free.net, so they might probably prefer an ISP running MySQL 5 rather than an ISP running the older versions. Maybe that's a little contribution to accelerate the ISP's decision to update to MySQL 5.

More details about the project can be found at http://www.db4free.net/index.php?content=port_faq

I finally did it!

Today I did it - I've updated my MySQL installation on my production system to MySQL 5, to be more concrete, to MySQL 5.0.13-rc-nt-max.

That was not what I initially planned. I thought, I'd wait until MySQL becomes a Production Release, but everything works fine, I did a lot of things on my testing environments and I didn't find any bugs. So I asked to myself, why wait ;-)?

It was quite an easy move, even easier as from 4.0 to 4.1. From 4.0 to 4.1 I had to try hard to find the character set and collation settings that work best for me (as German speaking I need umlauts). But now from 4.1 to 5.0, everything was just smooth.

I imported my mysql privilege database from my 4.1 server to a test server with 5.0. I copied MySQL 5.0's mysql database and truncated the data from the db, tables_priv and user tables. Then I made SQL dumps from these tables from the mysql 4.1 database with extended inserts, but without the table structure. I only had to import this dump into my cleaned up mysql 5.0 privilege database.

Finally, I manually changed the privilege settings for the new privileges that exist in MySQL 5.0. But that was quite easy to do with simple SQL strings.

A 'flush privileges' finished my short piece of work.

Tuesday, September 27, 2005

INSERT ... ON DUPLICATE KEY UPDATE

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/09/insert-on-duplicate-key-update/.

ATM transfer simulation

This example tries to simulate an ATM transfer system using MySQL Stored Procedures. We will also use one view and a cursor in this example.

We use three tables. One table will hold the account information, the name of the account holder, the account type and the limit of the account. We will have to take care that the balance of the account doesn't fall below this limit. The second table will store the balance of the account. This table enforces a 1:1 relation to the first table with the account information.

Here are the SQL commands for creating the tables and filling them with sample data:
CREATE TABLE  atm_accounts (
`acc_id` int(11) NOT NULL auto_increment,
`acc_owner` varchar(50) NOT NULL,
`acc_name` varchar(50) NOT NULL,
`acc_limit` decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (`acc_id`),
KEY `acc_limit` (`acc_limit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO atm_accounts VALUES (1, 'abc', 'check', -10000.00);
INSERT INTO atm_accounts VALUES (2, 'abc', 'saving', 0.00);
INSERT INTO atm_accounts VALUES (3, 'def', 'check', -5000.00);
INSERT INTO atm_accounts VALUES (4, 'def', 'saving', 0.00);
INSERT INTO atm_accounts VALUES (5, 'ghi', 'check', -3000.00);
INSERT INTO atm_accounts VALUES (6, 'ghi', 'saving', 0.00);
INSERT INTO atm_accounts VALUES (7, 'jkl', 'check', -12000.00);
INSERT INTO atm_accounts VALUES (8, 'jkl', 'saving', 0.00);

CREATE TABLE atm_balances (
`acc_id` int(11) NOT NULL,
`balance` decimal(10,2) NOT NULL,
PRIMARY KEY (`acc_id`),
CONSTRAINT `atm_balances_ibfk_1` FOREIGN KEY (`acc_id`)
REFERENCES `atm_accounts` (`acc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO atm_balances VALUES (1, 2000.00);
INSERT INTO atm_balances VALUES (2, 300.00);
INSERT INTO atm_balances VALUES (3, -2800.00);
INSERT INTO atm_balances VALUES (4, 4700.00);
INSERT INTO atm_balances VALUES (5, 3500.00);
INSERT INTO atm_balances VALUES (6, 900.00);
INSERT INTO atm_balances VALUES (7, 18000.00);
INSERT INTO atm_balances VALUES (8, 14300.00);

We could have store all of this data in a single table as well. Instead, we create a view to tie the tables together and make accessing the data easier:
CREATE VIEW v_accounts AS
select a.acc_id, a.acc_owner, a.acc_name, a.acc_limit, b.balance
from atm_accounts a inner join atm_balances b
on a.acc_id = b.acc_id
order by a.acc_id;

Now we can access the view v_accounts as if the data would have been stored in a single table.

The third table will be for logging. We want to keep track of all successful transactions. We will use this table to write another stored procedure, which makes it possible to undo transactions that have been successfully finished before.

In this table, we store not only the account ids (from both the debited account and credited account), but also the balances of the accounts before and after the transactions. Here's the structure of this table:
CREATE TABLE  atm_log (
`id` int(11) NOT NULL auto_increment,
`account_from` int(11) NOT NULL,
`account_to` int(11) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`acc_from_balance_before` decimal(10,2) NOT NULL,
`acc_from_balance_after` decimal(10,2) NOT NULL,
`acc_to_balance_before` decimal(10,2) NOT NULL,
`acc_to_balance_after` decimal(10,2) NOT NULL,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP
on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `account_from` (`account_from`),
KEY `account_to` (`account_to`),
CONSTRAINT `atm_log_ibfk_1` FOREIGN KEY (`account_from`)
REFERENCES `atm_accounts` (`acc_id`),
CONSTRAINT `atm_log_ibfk_2` FOREIGN KEY (`account_to`)
REFERENCES `atm_accounts` (`acc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Well, the tables are done - we're ready to enter the most interesting part of this example: the stored procedures.

The first procedure will be the one to make the transfer between two accounts - we call it "xfer". Of course, we need parameters for the debited account and the credited account as well as the amount that we want to transfer. We add another parameter, that we call _withlimit (I'm using underscores to sign my variables to separate them from column names). A value of 1 will indicate that the account limits should be enforced, that's what we usually have to pass into the procedure. We need this, because in the undoTrans procedure that we'll write later, we will not enforce that limit, so we'll pass a value of 2 within the undoTrans procedure. The last parameter is an output parameter where we can store error messages (or a success message, if everything worked fine).

First we have to do some checks inside this procedure. First we verify the value of the _withlimit parameter which has to be 1 or 2. Then we verify that both (the debited and the credited) accounts exist and that they are not equal to each other. If _withlimit is set to 1, we finally verify, whether the transfer would cause the balance of the debited account to fall below the account limit, before we start the transaction, assuming that no error has occured doing these checks.

In the declaration part of the procedure, there's also an exit handler for the case that a SQL exception occurs. This declaration causes a rollback to be made, wherever this error happens. This ensures that the procedure can never succeed if any SQL error happens anywhere in the procedure.

Within the transaction, first the amount is deducted from the debited account. If not exactly one row has changed, an error variable (_errorvar) is set to indicate that something went wrong. Then the same happens with the credited account, of course we increase the balance of this account - then we check again. Finally we make an insertion into the logging table and check once more, whether exactly one row has been affected.

With this error variable we can decide whether to commit or to rollback this transaction. Finally we output the error message variable (_error) and the procedure is finished. Here's the code:
DELIMITER $$

DROP PROCEDURE IF EXISTS xfer$$
CREATE PROCEDURE xfer(IN _accfrom INT, IN _accto INT,
IN _amount DECIMAL(10, 2),
IN _withlimit INT, OUT _error VARCHAR(255))
SQL SECURITY INVOKER
BEGIN
declare _acc_balance_from_before INT default 0;
declare _acc_balance_from_after INT default 0;
declare _acc_balance_to_before INT default 0;
declare _acc_balance_to_after INT default 0;
declare _idcount INT default 0;
declare _acc_limit DECIMAL(10, 2) default 0;
declare _rowcount INT default -1; -- temp variable for rowcount
declare _errorvar INT default 0; -- 0 = no error, else = error
declare exit handler for SQLEXCEPTION rollback;

set _error := 'SQL error';

if _withlimit != 1 and _withlimit != 2 then
set _errorvar := 1;
set _error := '_withlimit must be 1 or 2!';
end if;

if _accfrom = _accto then -- accounts are identical
set _errorvar := 2;
set _error := 'Accounts are identical!';
end if;

select count(*) as cnt from atm_accounts where acc_id = _accfrom
into _idcount;

if _idcount = 0 and _errorvar = 0 then
-- source account does not exist
set _errorvar := 3;
set _error := 'Source account does not exist!';
end if;

select count(*) as cnt from atm_accounts where acc_id = _accto
into _idcount;

if _idcount = 0 and _errorvar = 0 then
-- target account does not exist
set _errorvar := 4;
set _error := 'Target account does not exist!';
end if;

if _withlimit = 1 then
select balance
from v_accounts
where acc_id = _accfrom
into _acc_balance_from_before;

set _acc_balance_from_after :=
_acc_balance_from_before - _amount;

select acc_limit
from v_accounts
where acc_id = _accfrom
into _acc_limit;

select balance
from v_accounts
where acc_id = _accto
into _acc_balance_to_before;

set _acc_balance_to_after := _acc_balance_to_before + _amount;

if _acc_limit > _acc_balance_from_after and _errorvar = 0 then
-- balance below limit
set _errorvar := 5;
set _error := 'Transaction failed - Balance of source account
would fall below limit';
end if;
end if;

-- START TRANSACTION
if _errorvar = 0 then

start transaction;

update atm_balances set balance = balance - _amount
where acc_id = _accfrom;

select row_count() into _rowcount;

if _rowcount != 1 then -- SQL error
set _errorvar := 6;
end if;

update atm_balances set balance = balance + _amount
where acc_id = _accto;

select row_count() into _rowcount;

if _rowcount != 1 and _errorvar = 0 then -- SQL error
set _errorvar := 7;
end if;
insert into atm_log (account_from, account_to, amount,
acc_from_balance_before, acc_from_balance_after,
acc_to_balance_before, acc_to_balance_after) values
(_accfrom, _accto, _amount,
_acc_balance_from_before, _acc_balance_from_after,
_acc_balance_to_before, _acc_balance_to_after);

select row_count() into _rowcount;

if _rowcount != 1 and _errorvar = 0 then -- SQL error
set _errorvar := 8;
end if;

if _errorvar = 0 then
commit;
set _error := 'Transaction successful';
elseif _errorvar = 6 then
rollback;
set _error := 'Error deducting money from source account!';
elseif _errorvar = 7 then
rollback;
set _error := 'Error adding money to target account!';
elseif _errorvar = 8 then
rollback;
set _error := 'Error writing transaction to log!';
end if;
end if;
-- TRANSACTION FINISHED

select _error as Status;

END$$

DELIMITER ;

I have used SQL INVOKER as security option for these procedures. This option requires that the user who calls this procedure has all privileges (including the EXECUTE privilege) for all operation within this procedure. If we had used SQL DEFINER instead, the user would only need the EXECUTE privilege.

Here's an example how to use this procedure:
mysql> call xfer(3, 1, 500, 1, @a);
+------------------------+
| Status |
+------------------------+
| Transaction successful |
+------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> select @a;
+------------------------+
| @a |
+------------------------+
| Transaction successful |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from v_accounts;
+--------+-----------+----------+-----------+----------+
| acc_id | acc_owner | acc_name | acc_limit | balance |
+--------+-----------+----------+-----------+----------+
| 1 | abc | check | -10000.00 | 2500.00 |
| 2 | abc | saving | 0.00 | 300.00 |
| 3 | def | check | -5000.00 | -3300.00 |
| 4 | def | saving | 0.00 | 4700.00 |
| 5 | ghi | check | -3000.00 | 3500.00 |
| 6 | ghi | saving | 0.00 | 900.00 |
| 7 | jkl | check | -12000.00 | 18000.00 |
| 8 | jkl | saving | 0.00 | 14300.00 |
+--------+-----------+----------+-----------+----------+
8 rows in set (0.00 sec)

mysql>

Finally we write the procedure to undo all transactions between two given dates. Actually, we don't really undo them, instead we exchange debited and credited accounts of all procedures and perform the transaction again. The only difference is that we don't check the account limits this time, to make sure, all transactions are being executed with exchanged accounts.

Therefore, we declare a cursor that selects all transactions from the log between the "from date" and the "to date" which we pass as parameters. The order will be the timestamp (column ts) in descending order. As the cursor iterates through all rows, this procedure calls the xfer procedure, passing the parameter 2 for the _withlimit variable. Here's the code and an example how to use it:

DELIMITER $$

DROP PROCEDURE IF EXISTS undoTrans$$
CREATE PROCEDURE undoTrans(IN _datefrom DATETIME,
IN _dateto DATETIME,
OUT _error VARCHAR(255))
SQL SECURITY INVOKER
BEGIN
declare finished INT default 0;
declare accfrom INT;
declare accto INT;
declare amt DECIMAL(10, 2);
declare cur_trans cursor for
select account_to as account_from, account_from as account_to,
amount
from atm_log where ts between _datefrom and _dateto
order by ts desc;

declare continue handler for not found set finished := 1;
declare exit handler for sqlexception rollback;

open cur_trans;

cur_loop: loop
fetch cur_trans into accfrom, accto, amt;

if finished = 1 then
leave cur_loop;
end if;

call xfer(accfrom, accto, amt, 2, @a);

end loop;


close cur_trans;

END$$

DELIMITER ;

Voilà - here's the example:
mysql> call undoTrans('2005-09-22', '2005-09-30', @a);
+------------------------+
| Status |
+------------------------+
| Transaction successful |
+------------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select * from v_accounts;
+--------+-----------+----------+-----------+----------+
| acc_id | acc_owner | acc_name | acc_limit | balance |
+--------+-----------+----------+-----------+----------+
| 1 | abc | check | -10000.00 | 2000.00 |
| 2 | abc | saving | 0.00 | 300.00 |
| 3 | def | check | -5000.00 | -2800.00 |
| 4 | def | saving | 0.00 | 4700.00 |
| 5 | ghi | check | -3000.00 | 3500.00 |
| 6 | ghi | saving | 0.00 | 900.00 |
| 7 | jkl | check | -12000.00 | 18000.00 |
| 8 | jkl | saving | 0.00 | 14300.00 |
+--------+-----------+----------+-----------+----------+
8 rows in set (0.00 sec)

mysql> select account_from, account_to, amount from atm_log;
+--------------+------------+--------+
| account_from | account_to | amount |
+--------------+------------+--------+
| 3 | 1 | 500.00 |
| 1 | 3 | 500.00 |
+--------------+------------+--------+
2 rows in set (0.00 sec)

mysql>

Enjoy ;-)!

Menu management with stored procedures (without Nested Set Model)

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/09/menu-management-with-stored-procedures-without-nested-set-model/.

Monday, September 26, 2005

About me and this blog

Hi folks,

let me first introduce myself. I'm a 29 year old self employed software and web developer with a focus on database applications, living in the north of Austria in a region called "Waldviertel", best translated as "wood quarter".

In June this year, me and two friends and colleagues of mine, Natascha and Jürgen, started a project called "db4free.net". We rented a server, created a web application and set up a MySQL 5 database server, to give people MySQL 5 database accounts for free. Now, three months later, we are about to welcome our 1,000th user.

The reason for doing all this is that MySQL is a great OpenSource product that's simple to work with on one hand, but still very powerful on the other. It's available for all common platforms and for most (even commercial) cases you can use it for free. Only if an application using MySQL is being distributed with another licence than MySQL's own GPL, a commercial licence is required.

MySQL 5 which has just entered Release Candidate stage introduces a whole bunch of new features. Some people might say that they are nothing new - other database systems have implemented things like Stored Procedured, Functions, Views, Triggers a.s.o. a long time ago and MySQL has been far behind them. Of course, that's a fact I can't deny. But still, MySQL has remained the world's most popular database server - for a good reason. There's a strong community behind this product and you are never alone if you need assistance in solving a problem.

Now, what is this blog all about? OpenSource and being a part of an OpenSource product's community (somebody described a MySQL community member as "someone who does work on MySQL related things beyond the call of duty") is very much about giving. I have been working with MySQL a lot and I did a lot of testing with the new features of MySQL 5, mainly with stored procedures. My contribution shall be to share solutions to common problems that I have solved with MySQL (mostly MySQL 5) with other people. In this blog, you will find many useful examples that you can easily adopt to your own needs or simply use them to learn about MySQL's techniques.

Of course, this is not the only blog about this topic. There are two other great blogs related to MySQL 5 that I recommand you to visit: Andrew Gilfrin's blog and Roland Bouman's blog. Also take a look at Andrew's MySQLDevelopment.com site. At these sites, together with this one, you'll find a great starting point to dig into the depths of MySQL 5.

If you need even more information about MySQL 5, get the book Pro MySQL 5 from Michael Kruckenberg and Jay Pipes.