New features in and not in MySQL 5.1

As I read from Brian Aker, the beta freeze for MySQL 5.1 is right ahead. Time to reflect on the features that I know they will be in 5.1, features that I don't know if they're in 5.1, features that I wished they were in 5.1 and features that will very likely not be in MySQL 5.1. There are still some questions open for me - maybe somebody who knows more can answer some of them ;-).

New features that I know for sure are Partitioning, which allows to spread the logical representation of a table on multiple physical storage locations, based on conditions. Row based Replication means that data can be transfered from the master to the slaves row by row rather than by SQL statements (it will be adjustable, which method to choose). The Task Scheduler will allow (as the name says) to schedule tasks (one-time or recurring) to execute Stored Procedures. This will for example make it possible to plan clean up tasks (like removing invalid data). This feature might be extended to also allow setting automatical alerts on specific events. A big new feature will be Plugable Storage Engines. Developers will be able to write their own Storage Engine and plug it into MySQL - it could become very exciting which new Storage Engines will come up and provide great new possibilities. New features in MySQL Cluster will be Disk Based Storage, Global Replication and Variable Sized Attributes.

Another very interesting feature that I wrote a feature request for is logging into database tables. Currently, log files are written to the file system and I thought it would be nice if they could directly be written to MySQL tables, because this would make it much easier to analyze the log data.

There are features that I expected to be in MySQL 5.1, but I haven't heard about them for quite a while, so I hope, they haven't been postponed and there's still a chance to see them being implemented in 5.1. This begins with Check Constraints, a feature which I think is long overdue. Another very important feature would be privileges for trigger creation and deletion. At the moment, these actions require the SUPER privilege which should only be granted to database administrators, so there's no easy way to let non-privileged users create and delete their own triggers. Also Exception Handling in Stored Procedures is a feature that quite many people still miss.

Foreign Key Constraints in MyISAM tables seem to have been postponed for MySQL 5.2, as the MySQL manual says. FK constraints in MyISAM tables would be especially useful because it's not possible to enforce referential integrity and use fulltext indexes in the same table. And as I recently wrote, it would be useful to have a command that checks referential integrity, after foreign key checks have been set to off.

Another feature that's planned as far as I know, but probably not very soon, is to provide other languages for Stored Procedures, like C, Java, Perl, PHP, etc.

I would be greatful about any piece of more detailed information as I could provide here, so if you know more, please leave a comment or mail me at m.popp@db4free.net.

MySQL - PostgreSQL reactions

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/12/mysql-postgresql-reactions/.

Breaking referential integrity

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/12/breaking-referential-integrity/.

MySQL - PostgreSQL

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/12/mysql-postgresql/.

The first week on my new job

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/12/the-first-week-on-my-new-job/.

RTFM experiences

Andrew Gilfrin spoke right from my heart, telling about his "Read the ******* manual" experiences, as I unfortunately had to make quite similar experiences a while ago.

At that time, I started to look through some German speaking newsgroups and tried if I could help people. There were some other supporters around, but at most questions they didn't leave much more than those RTFM comments or other rather rude answers. I complained about that in those newsgroups and asked, why they give any answers at all, if they don't really want to help, why don't they just ignore it? The answer was once again not very polite and finally I was so frustrated that I gave up reading the German newsgroups.

A little later I had the luck that some English newsgroups have become available through my provider and I got an eye on those. Here, there's a very much more friendly atmosphere and I love to read through them and also give help, if I can. No comparison to the German newsgroups!

First of all, I'd like to mention Bill Karwin. This guy spends a lot of time to give great help to many people who are looking for support. He's always polite, very skilled and if nobody else can give good help, he probably can. I don't know him or had contact to him personally, but I found out that he is the kind of MySQL Community Member that MySQL needs!

Also in the MySQL forums, most people show very good manners. That's one of the big reasons why I found my way into the Community and preferably to the English speaking media, simply because people there were and are friendly and not stupidly competing against each other (who's the best?), but really trying to help and get out the best for both who need help and the supporters themselves (helping also means learning). As long as there are people who do this great work, I have no worries about MySQL and its Community, at all.

db4free.net CleanUp process

Since last weekend, the first clean up process of db4free.net is running. All users (except a few who I strongly expect that they will keep their database ;-)) have received an email with a link to the CleanUp web page, where they can leave a comment and decide, whether they would like to keep the database or rather have the database deleted.

This process should be running until the middle of January, then I will delete the users and databases from the people who have decided not to keep it and I will set the database accounts of those users inactive, who didn't tell me about keeping or deleting. So they have a chance to still get their database back if they really need it, otherwise I plan to delete those accounts by the end of January. That process should provide less overhead on the server and better performence for those users who use their database on a regular basis.

By now, from 2,342 users, 403 users will keep the database and 66 users want it to be deleted explicitly. For the other 1,873 users, there is no decision yet. However, I will keep on sending the clean up emails to those users who haven't told me yet, so everybody should have a fair chance to not have the account deleted unexpectedly.

The 17 % who want to keep the account (as far as they indicated it so far) don't seem like very much, but there are many very pleasant comments about db4free.net - there are people to who db4free.net has become an important provider for learning MySQL, for experimenting, testing migration scenarios and also for running active projects. There have been several positive statements about the performence and there really seem to be a lot of people who love db4free.net.

And that's a good feeling!

News about me & db4free.net

It's time to inform you about some great news about things that happened through the last few weeks that affect my personal career and possibly also the future of db4free.net and my role within the MySQL community.

The first great news is not really new, since you might already know that I am one of the three winners of the Grand Prize of the MySQL 5 contest. Time has begun to start preparing for travelling to Santa Clara next April and there's a great pleasant anticipation.

The second great news is that I have been invited to be added to the MySQL Writers Guild. It's a big honour to be listed among some great people (some of which are even authors of great books that I have learnt a lot from!).

Great news number three: yesterday I have passed the MySQL Professional Certification Exam *WOUHOOOO* (<- I promised that ;-))! I have now reached a big personal goal with being both a Zend (PHP) Certified Engineer and MySQL Certified Professional.

And last but not least - number four, this Monday (19th December) I will start on a new software and web developer job. The new job will involve PHP, MySQL, PostgreSQL, Java and maybe more. I will have to deal with large scale databases (probably both MySQL and PostgreSQL) and I'm very confident that this new job will give me a great opportunity to learn a lot of great new things on a much higher level that I have been used to.

I am sure that my involvement in the MySQL Community and YOUR help to get all that phantastic response and feedback helped me a lot to get this interesting new job. So I believe, you deserve a big THANK YOU for all of this!

I'm the living proof that a contribution within the MySQL Community can be very helpful in stepping forward in your personal career. But I wouldn't have had the chance to be active in the Community if there wasn't such a strong Community - so I know that I owe you people, who helped me to get such a great response, a lot.

How does that affect the future of db4free.net and my role within the Community? The "bad news" is that I will have to spend a lot of time the next weeks to get into my new job, so it's possible that you will hear a little less often from me than in the last weeks. But that doesn't mean that I'm out - far from it, I'm collecting new skills and advancing my old skills and I will bring them back into the Community and into db4free.net.

I have a lot of ideas for db4free.net (some of which came by other people, like the trigger implementation from Giuseppe Maxia) and other activities like writing an article about connecting to a MySQL database using .NET for Andrew Gilfrin's mysqldevelopment.com site. I'd like to apologize that I have to postpone some of the things that I planned to do, due to my new job. But postponed doesn't mean cancelled, so they are still on my to-do-list.

So be excited (just as I'm very excited) about what's coming up, because I think, 2006 will bring a lot of interesting new things from me & db4free.net :-).

Language specific MySQL.com website

This time, I have a little critisism, or let's say, suggestions for improvement, for the mysql.com website.

Since a few days ago, I find some contents on mysql.com in German (you know, I'm Austrian) that I used to get in English. In general, I think it's good to provide the website in the language of the country that the user comes from. Translating the website to other languages than English provides information to many more people, so it's of course a good thing.

But what I don't like so much about it is that I don't find an (easy?) way to switch the translated contents to English, if I want to. Even if I enter the website through the URL www.mysql.com, I get some contents only in German, where I had to enter www.mysql.de before to get German contents. Also that would not be so bad, but what I'd like to suggest is to provide buttons, or a drop down field, to make it possible to select the desired language.

In fact, there are some contents on mysql.com that I would rather like to see in English than in German, so I hope that it's possible to provide an easy way to make the language selectable to everybody (however, I do support the idea to set the default to the corresponding language of the visitor's country).

Simple or complex?

Mike Kruckenberg has written an article about the question "Is MySQL Simple or Complex (or both)?" Let me give you my thoughts about that.

I think, the process of putting new features into MySQL is like giving a construction worker a new toolbox that gives him a whole variety of choices to make his work easier. But it comes with the price that it requires more advanced skills to use them.

On the other hand, he has still the option to make his work the way he was used to. It's a new choice - to use the new tools, or to not use them.

That's the same with MySQL. If somebody started e.g. with MySQL 3.x, because he liked the fact that it works simple, he can still use MySQL 5 the same way he was used to. But now there is an option to also use more advanced features (stored procedures, user defined functions, triggers, ...).

MySQL (and MySQL 5 in particular) has great options to optimize a server to work with huge loads of data (as I recently could read, multiple Terabytes) with many millions of queries a day. It can serve the largest databases on earth. On the other hand, I know some people, who use MySQL to get their "little jobs" done, without the need for advanced features. Both groups do fine and feel comfortable with MySQL, so my answer to this questions would be a clear and straight: both!

The most important message shall be: there is an option to use advanced features, but anybody can still use it as the same simple database server that it was supposed to be in earlier versions.

TX isolation levels (4) How to produce an artificial deadlock

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/12/tx-isolation-levels-4-how-to-produce-an-artificial-deadlock/.

TX isolation levels (3) REPEATABLE READ, SERIALIZABLE

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/12/tx-isolation-levels-3-repeatable-read-serializable/.

Server statistics

In my article about data types statistics I was asked for further db4free.net statistics.

That's principally no problem. Of course I will take care of user's privacy and not hand out any user specific data, but as statistics usually don't relate to any specific user data, you can ask me for statistics about the server. It would be best to send me the queries, and if they are "common enough" that they don't give information about any single user, I will run them against the db4free server and return the results.

This gives me an idea to go even one step further. If people in the Community are interested, we can use status information to discuss server optimization on this concrete example. This could make the db4free.net service even better - and for me (as for anybody else who participates) it would be a chance to learn from suggestions that experienced MySQL experts can make to improve db4free's server performence.

db4free.net data type statistics

In response to Beat's article about "Discover "bad" use of ENUM and SET types using MySQL information_schema", here's db4free's data type statistic:

mysql> SELECT    DATA_TYPE,
    ->           COUNT(*) AS n,
    ->           ROUND(COUNT(*)
    ->                 / (SELECT COUNT(*) FROM information_schema.COLUMNS)
    ->                 * 100, 2
    ->           ) AS `%`
    -> FROM      information_schema.COLUMNS
    -> WHERE     TABLE_SCHEMA NOT LIKE 'mysql'
    -> GROUP BY  DATA_TYPE
    -> ORDER BY  n DESC;
+-----------------+-------+-------+
| DATA_TYPE       | n     | %     |
+-----------------+-------+-------+
| varchar         | 69993 | 28.29 |
| int             | 57613 | 23.29 |
| tinyint         | 40502 | 16.37 |
| mediumint       | 16413 |  6.63 |
| smallint        | 16292 |  6.58 |
| text            | 16011 |  6.47 |
| char            | 14142 |  5.72 |
| datetime        |  4297 |  1.74 |
| mediumtext      |  2059 |  0.83 |
| enum            |  1965 |  0.79 |
| decimal         |  1811 |  0.73 |
| bigint          |  1340 |  0.54 |
| tinytext        |   809 |  0.33 |
| date            |   802 |  0.32 |
| float           |   720 |  0.29 |
| blob            |   543 |  0.22 |
| longtext        |   481 |  0.19 |
| double          |   445 |  0.18 |
| timestamp       |   348 |  0.14 |
| mediumblob      |   166 |  0.07 |
| tinyblob        |   122 |  0.05 |
| time            |   116 |  0.05 |
| float unsigned  |   104 |  0.04 |
| varbinary       |    73 |  0.03 |
| set             |    35 |  0.01 |
| longblob        |    35 |  0.01 |
| double unsigned |    24 |  0.01 |
| bit             |     5 |  0.00 |
| binary          |     5 |  0.00 |
| year            |     3 |  0.00 |
+-----------------+-------+-------+

db4free.net offers MySQL 5.0 AND 5.1 databases

db4free.net has been extended to run two MySQL servers, one with MySQL 5.0 (currently 5.0.16-max) and one with MySQL 5.1.3-alpha-max.

All existing MySQL 5.0 user accounts have also been created on the MySQL 5.1 server and all new users will automatically receive one database on each server. However, technically the servers run individually, both have their own port (MySQL 5.0: 3306, MySQL 5.1: 3307), their own data directories, their own log files etc. But the user management, all the activities that can be performed through the website, will automatically run on both servers simultanously.

To access both servers, users can use phpMyAdmin which is available on the db4free.net website. Via a radio button, one can select which server to access. To access the 5.1 server through APIs, it's important to specify the port. For PHP, this can be done as follows:

$dbcon = mysql_connect("db4free.org:3307", "user", "password");
mysql_select_db("database", $dbcon);

...

mysql_close($dbcon);

or (using the mysqli extension):

$dbcon = new mysqli("db4free.org", "user", "password", 3307);

...

$dbcon->close();

If the MySQL monitor client is installed, the db4free database on the MySQL 5.1 server can be accessed like this:

shell> mysql -h db4free.org -P 3307 -u [username] -p[password] [database]

Of course, it's also possible to use other client software, like MySQL QueryBrowser.

db4free.net about to offer MySQL 5.1 databases as well!

This morning I learnt that the first MySQL 5.1 development version has been released to the public.

During the last days, I successfully experimented with installing multiple MySQL servers on one machine, so this gave me the perfect preparation to download MySQL 5.1 on the db4free.net server and install it independently of the current MySQL 5.0 version that's currently in use by about 2,000 users.

It has worked fine - MySQL 5.0 continues to operate just like before and the new MySQL 5.1 version will be available via port 3307. During the weekend, I will transfer the user accounts from the 5.0 server to the 5.1 server, so - everyone who already has a MySQL 5.0 account will soon be able to access his MySQL 5.1 database as well.

You will hear about more details, soon.

New links online

I have added a few links here in my weblog and at the Developer's page on db4free.net. This Developer's page was initially planned to end up as something like MySQLDevelopment.com - but as there is such a variety of great sites (what this page never could have competed with), I changed this plan to offer links to the best other resources, instead.

Many people who are familiar with the various resources about MySQL development will probably know most of the links, but for those who are not (yet), here are my personal recommandations:

• MySQLDevelopment.com (a great place to get started with the new features of MySQL 5!)


• Andrew Gilfrin's blog


• Roland Bouman's blog


• Beat Vontobel's blog


• Giuseppe Maxia's blog


• MySQL General Purpose Stored Routines Library


• Jay Pipes' blog

Besides the official resources at mysql.com and planetmysql.org (where many of the articles written in the mentioned weblogs can be found, too), these are the best sites about MySQL development that I know!

Thanks, congratulations and continue the good work!

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/11/thanks-congratulations-and-continue-the-good-work/.

PHP 5.1 released

Today (24th November), PHP version 5.1.0 has been released. Find a list of the new features, which include that PDO extension is now enabled by default, on the PHP website. The PDO extension provides a common interface to communicate to different database systems.

Also read the Changelog and the Upgrading Guide to get detailed information about what's new.

Many thanks ...

... to Shane Bester for finding a repeatable testcase for the bug report #14829, where I asked for help early this morning. The bug has now been acknowledged and it is likely that the bug will be fixed soon :-).

This showed me once more how great this Community works. If there is a problem that I can't solve myself, it really works to ask for help and get some assistance. That's one of the points that makes MySQL more attractive than many other products.

TX isolation levels (2) READ COMMITTED

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/11/tx-isolation-levels-2-read-committed/.

Call for help to make a bug repeatable

I have found a bug which disallows me to log in with a user, after I have created and deleted another user. After restarting the server, I can log in again as if there never was a problem. I have reported this bug to

http://bugs.mysql.com/bug.php?id=14829

I have tried to create a repeatable case and I made it to create a situation, where I could reproduce this bug on 3 different machines. Unfortunately, when Miguel Solorzano tries to repeat this, it still works - although we have already checked out that everything is completely identical.

So I'm asking for help to find out more details to reproduce this. If you have a Windows (2000 or XP) test server available that you can play around with, please try the following:

* Install MySQL 5.0.15, if possible to c:\mysql
* download and install this my.ini file
* download the dump file from dump_mysql.sql and put it to C:
Log in as root (without password) and delete the test database and the contents from the mysql.db table:

drop database test;
delete from mysql.db;
flush privileges;

Then do the following steps, starting from C:
C:\>mysql -u root mysql < dump_mysql.sql

C:\>mysqladmin -u root flush-privileges

C:\>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.15-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create user user@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> drop user user@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\>mysql -u mpopp

... if everything works "fine" (to say, you can reproduce the bug), you should get following message:

ERROR 1130 (HY000): #HY000Host 'localhost' is not allowed to connect to this
MySQL server

After restarting the server, you should be able to log in as user mpopp.

If you find any clue why this bug still doesn't occur on all installations (under the given conditions), please add a comment to the bug report. Please let me know, if you can or cannot reproduce it and mail me at m.popp@db4free.net. Thanks to all who help :-).

TX isolation levels (1) READ UNCOMMITTED

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/11/tx-isolation-levels-1-read-uncommitted/.

db4free optimizations

Today I did some server optimizations on db4free.net, which has recently welcomed its 1,500th user. As I already wrote earlier in my weblog, I'm currently preparing for the MySQL professional exam, and I could use the new skills from learning for this exams immediately for re-configuring the db4free.net server to offer better performence to its users. I enabled the query cache, extended the buffer limits, allowed more concurrent users and enlarged the max_allowed_packet value to make it possible to import larger dump files.

But there are more benefits from learning for MySQL certification exams than that. For example, before I started, I used to create tables and users, or change user's permissions with 3rd party tools like phpMyAdmin. I knew the fundamental statements how to do it with SQL, but I didn't know the details good enough to really use it. Today, I feel even more (!) comfortable to do it in MySQL monitor using SQL than with any graphical tool. So this proofs that the exams helped me a lot to become much better in writing SQL and to be independent of any 3rd party software.

Stay steady as one of the next articles in my weblog will be to show, how the different transaction isolation levels work in practice and how a deadlock can be created artificially. Very interesting stuff, indeed!

But now back to db4free. As already mentioned, db4free has hit the 1,500 users mark. Time for some statistics. As of today, there are 1,575 users from 60 different countries. The website has been visited 9,415 times from people from 91 different countries. Most visitors (and also users) come from Germany, followed by China, USA, Taiwan, Poland, Austria and Hongkong (as it's easy to see, db4free is very popular in the Far East region). After Germany (339), most users come from Poland (259), China (192), Taiwan (166), Hongkong (160) and the USA (98).

Information schema allowed me to get some more interesting statistical data. The 1,575 users have created 22,445 tables, from which are 21,894 MyISAM tables, 470 InnoDB tables, 80 MEMORY tables and (as db4free is offering the Max version of MySQL 5.0.15) 1 BerkeleyDB table. There are 13 views, 23 Stored Procedures and 28 User Defined Functions.

Using a SP to split up a table

Lets assume that we have one big table with forum posts (called forum_posts) from quite a long period. We want to split this big table up in several smaller tables for every month. The date is stored in a datetime column called dt.

Here's a stored procedure that splits the table up in smaller tables called fp_YYMM which are identical in its structure as the original table. It uses a cursor to walk through every month, create the new table (with a CREATE TABLE ... LIKE statement) and copying the correct data into the archive tables. The original table will persist.

The procedure also uses prepared statements to dynamically build up the SQL statements for creating the new tables and copying the contents.

DELIMITER //

DROP PROCEDURE IF EXISTS splitUpTables //
CREATE PROCEDURE splitUpTables ()
BEGIN
  declare finished int default 0;
  declare month char(7);
  declare month2 char(6);
  declare dfrom date;
  declare dto date;

  declare curCreate cursor for 
     SELECT distinct date_format(dt, '%Y-%m') as month 
     FROM forum_ posts order by date_format(dt, '%Y-%m');
  declare continue handler for not found set finished := 1;

  open curCreate;

  fetch curCreate into month;

  while finished = 0 do

    select concat(substring(month, 1, 4), substring(month, 6, 2)) 
       into month2;

    select month2;

    set @sql := concat('create table if not exists fp_', month2, 
                       ' like forum_posts ');
    prepare pCreate from @sql;
    execute pCreate;

    set dfrom := cast(concat(month, '-01') AS date);
    select date_sub(date_add(dfrom, interval 1 month), interval 1 day) 
       into dto;

    set @sql1 := concat('truncate table fb_', month2);
    set @sql2 := concat('insert into fp_', month2, 
       ' select * from forum_posts where dt between \'', 
       dfrom, '\' and \'', dto, '\' order by dt');

    prepare pTruncate from @sql1;
    execute pTruncate;
    prepare pInsert from @sql2;
    execute pInsert;

    fetch curCreate into month;

  end while;

  close curCreate;

END //

DELIMITER ;

Calculate uniqueness of text fields for setting indexes

Indexing long text fields can be more consuming than helpful. Indexes have to be maintained by the server. So if you have an indexed 255 character long varchar field, it probably means more work for the server than the index can help you.

Fortunately, it's easy to limit the index length to a specified number of characters. We can do this with e.g. a KEY(field(15)) clause when adding the index with a CREATE or ALTER TABLE or CREATE INDEX statement. In this case, only the first 15 characters would be indexed.

The question is - how long should the indexed length be? As a rule of thumb we could say, we should choose a length that's just enough to make most of the indexed contents unique. Maybe we won't reach 100 % of uniqueness, but that's not necessary. It will probably be too little to index only the first one or two characters, because they will be the same for many records. So the next question is - what's a good value?

To make the decision a little easier, we can calculate the uniqueness with following formula:

SELECT count(distinct left(field_name, indexed_length)) / 
   count(*) * 100 FROM table_name;

The higher the value, the more unique the indexed contents are.

So I thought to myself, why not put this into a stored procedure where we pass the table name, the column name and two boundaries from which value to which other value we'd like to see the percentage of uniqueness. Here's how I did it: I created a temporary table inside the procedure, prepared the insert into [temporary table] select ... statement and queried the values inside a while loop from the lower bound to the upper bound. Here it is:

DELIMITER $$

DROP PROCEDURE IF EXISTS getUniqueness $$
CREATE PROCEDURE getUniqueness (IN _table VARCHAR(255),
   IN _column VARCHAR(255), IN _noFrom INT, IN _noTo INT)
BEGIN

  create temporary table tt_uniqueness
     (noChar int unsigned not null, uniqueness decimal(10,2) not null);

  set @sql := concat('insert into tt_uniqueness ',
    'select ?, cast(count(distinct left(',
    _column,
    ', ?)) / count(*) * 100 as decimal(10,2)) from ',
    _table);

  prepare pSql from @sql;

  set @count := _noFrom;

  while @count <= _noTo do

    execute pSql using @count, @count;

    set @count := @count + 1;

  end while;

  select * from tt_uniqueness;

  drop temporary table if exists tt_uniqueness;

END$$

DELIMITER ;

Finally, lets take a look how it works. I try it on the phpbb_posts_text table from a phpBB database which holds 340 entries:

mysql> explain phpbb_posts_text;
+--------------+-----------------------+------+-----+---------+-------+
| Field        | Type                  | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+-------+
| post_id      | mediumint(8) unsigned | NO   | PRI | 0       |       |
| bbcode_uid   | varchar(10)           | NO   |     |         |       |
| post_subject | varchar(60)           | YES  |     | NULL    |       |
| post_text    | text                  | YES  |     | NULL    |       |
+--------------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select count(*) from phpbb_posts_text;
+----------+
| count(*) |
+----------+
|      340 |
+----------+
1 row in set (0.00 sec)

mysql> call getUniqueness('phpbb_posts_text', 'post_text', 1, 10);
+--------+------------+
| noChar | uniqueness |
+--------+------------+
|      1 |       7.94 |
|      2 |      27.65 |
|      3 |      41.47 |
|      4 |      51.18 |
|      5 |      59.41 |
|      6 |      67.94 |
|      7 |      71.47 |
|      8 |      78.82 |
|      9 |      84.12 |
|     10 |      85.00 |
+--------+------------+
10 rows in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql>

So we can see, how the uniqueness rises as we choose longer index lengths. If we for example decide that we want to have a uniqueness of 80 % for the index, it would be fine to select 9 characters as the index length.

ApacheLog2MySQL

I have written a little Java program that parses an Apache Log file and stores its content in a MySQL database.

The program is published under the General Public License and can be downloaded from http://apachelog2mysql.mpopp.net/ApacheLog2MySQL.jar. You can download the source code at http://apachelog2mysql.mpopp.net/ApacheLog2MySQL.zip and find the ReadMe file at http://apachelog2mysql.mpopp.net/readme.txt. In this ReadMe file you will find all details about the limitations and the features that are planned in the future.

You can also find all the information on my website at http://www.mpopp.net/apachelog2mysql.php.

The program runs at all platforms that have the Java Runtime Environment 1.5.0 or higher installed. It can be started from the console with following command:

java -jar ApacheLog2MySQL.jar [filename] [host] [user]
   [password] [database]

The program is very basic at the moment, but I plan to extend its features, so it might become a great little tool to analyze the Apache log. Please refer to the ReadMe file to get more detailed information about where the program is still limited and which features I plan to implement.

Feel free to try the program, modify it, give me feedback and let me know your thoughts about this little tool.

Compiling PHP 5 with mysql and mysqli

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/11/compiling-php-5-with-mysql-and-mysqli/.

My first look at 5.1 ...

It was a great thing when MySQL 5.0 came out, but what's next? Well, of course, the stabilization process of MySQL 5.0 continues, but the work for the next release - 5.1 - has begun quite a while ago.

So, up to now I've always been using the official binaries from MySQL. That's the best thing to do for running MySQL servers in production. But why not take a look a bit further to see what's going on beyond the public releases?

So I looked into MySQL's documentation to read about Installing from the Development Source Tree. I did all the points step by step and now I've ended up with my first MySQL 5.1 server:



These are very interesting days in terms of MySQL - two weeks ago the first Production Release of MySQL 5.0 and now my first personal MySQL 5.1 installation. It's always very exciting to have a first look at a new milestone.

Got number two ...

Today I have made my second step towards my goal of getting a row of IT certificates. After passing the MySQL Core Certificate three weeks ago, I passed the Zend PHP Certification today.

For me, today's exam was harder than the MySQL exam. One reason is certainly that I love to do things with MySQL and I do much more with MySQL than my actual work requires. So I have collected more experiences with MySQL than with PHP, which I use to do my work, but not so much for experimenting.

The second reason - the MySQL Study Guide covers the topics that will be on the exam very exactly. Therefore, it's also much bigger than the PHP Certification Study Guide. The latter also indicates what you will find on the exam, but don't expect that every little detail is mentioned in the PHP Study Guide. Comparing them, learning with the MySQL Study Guide was much easier for me. Finally, both exams might require quite the same amount of knowledge - I haven't found a big difference on this.

Well, now it's time to get myself ready for the MySQL Professional Exam. It might take some time, it's certainly not easy and even some good people haven't taken the exam at the first time - so I'll try to learn the material very closely. When it's done, you'll hear a loud 'wouhoooo' from me :-).

Upgrading from older versions to MySQL 5.0

I sometimes read the posts in the MySQL Newsgroup (mailing.database.mysql) and take a look if there are any questions where I can help other MySQL users. That's a good think for many purposes: I can help people, I can learn new things for myself and last but not least - there are often questions that give me interesting material for this weblog. Such as this:

There was a question, how to update to the latest MySQL 5.0 version - not only from 4.1 (which is described perfectly in the MySQL manual), but also from older versions like 3.23. Here's how I would do it:

* make a dump of all your data except the mysql database

* make a separate dump of the mysql database

* uninstall this old MySQL version

* install the latest MySQL version from scratch

* create a separate database for the previously dumped mysql database (e.g.
mysql_old) and restore this dump

* sync the old mysql database with the new one (this might require some
manual work, because the structures will be slightly different)

* when the new mysql database is OK, restore the dump of the data

If you have a chance, try to install the new version on a separate machine,
before you uninstall the old MySQL version to make sure that everythink works.

Selecting data by user in views

I have found an interesting request in the MySQL newsgroup asking, how data can be selected as of a user column in a table. Only the user who is stored in a record should be able to access and alter this row.

So this is a perfect example to demonstrate, how this can be accomplished by a view.

First I created a table called tt in the database test and filled it with sample data:

create table tt (
id int unsigned not null auto_increment primary key,
charvalue char(1) not null,
intvalue int unsigned not null,
allowed_user char(6) not null);

insert into tt (charvalue, intvalue, allowed_user) 
values ('a', 560, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('b', 931, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('c', 661, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('d', 246, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('e', 452, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('f', 130, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('g', 341, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('h', 847, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('i', 417, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('j', 151, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('k', 11, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('l', 66, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('m', 375, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('n', 412, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('o', 358, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('p', 78, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('q', 463, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('r', 773, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('s', 165, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('t', 740, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('u', 875, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('v', 793, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('w', 677, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('x', 133, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('y', 994, 'user_d');
insert into tt (charvalue, intvalue, allowed_user) 
values ('z', 479, 'user_e');

The column allowed_user specifies, which user should be allowed to access this row.

The next step is to wrap this table into a view (called tt_select) and limit it to be accessible for only the user who is logged in:

create view tt_select as
select * from tt
where allowed_user = substring(user(), 1, locate('@', user()) - 1);

The substring and locate functions cut off the host from the user name, so e.g. user@localhost becomes user (if the limit should apply to both user and host, you can also store the complete user@host information in the table and query 'where allowed_user = user()').

Finally I created the user accounts and granted the privileges exclusively for the view (not for the table):

GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_a'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_b'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_c'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_d'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_e'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_f'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_g'@'%' IDENTIFIED BY 'abc123';

That's it :-). Lets test it using the MySQL console client, assuming that the user 'user_g' logs in:

E:\>mysql -u user_g -pabc123 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 181 to server version: 5.0.15-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tt_select      |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      375 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> update tt_select set intvalue=584 where id=13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      584 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> delete from tt_select where id=13;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
4 rows in set (0.02 sec)

mysql> insert into tt_select values (13, 'm', 375, 'user_g');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      375 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> select * from tt;
ERROR 1142 (42000): SELECT command denied to user 
'user_g'@'localhost' for table 'tt'
mysql>

As we can see - it works fine!

Information schema diagram

Roland Bouman has created a perfectly organized diagram about the information schema tables:

http://www.xcdsql.org/Misc/MySQL_INFORMATION_SCHEMA.html

Roland's diagram has been awarded in MySQL's beta challenge (congratulations for this prize!).

Information schema has been introduced in MySQL 5 and provides all kinds of information about the database objects.

db4free.net offers MySQL 5.0.15-Max

db4free.net has been updated to MySQL 5.0.15-Max (this enhanced variant offers some additional Storage Engines like BerkeleyDB and Archive). This way, db4free.net is probably the first public MySQL provider of the world that offers a MySQL 5 Production Release.

I hope, many - also commercial - providers will follow soon!

Congratulations

... to MySQL AB for the new "baby" 5.0 and many thanks to everyone who has been involved in the process of making MySQL 5.0 a great product. Keep on the good work!

Web apps ready for MySQL 5?

I think there is no doubt that MySQL 5 is ready for web applications. But I'd like to put this question around and ask, whether the most popular web applications are ready for MySQL 5.

Well, this question is easy to answer, as long as MySQL 5 in not executed with any strict mode options. The behavior is similar to that of MySQL 4.x and so there should be no problems.

However, I have tested some popular web applications like Mambo/Joomla, phpBB and Typo3 with db4free.net, which uses the sql-mode:

"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION"

On Windows, this sql-mode is the default.

I'd like to start with Mambo. There is quite an old problem when multi byte character sets are used. Trying to install Mambo into a database using utf8, the creation of the tables fails here:

CREATE TABLE `mos_core_acl_aro` (
  `aro_id` int(11) NOT NULL auto_increment,
  `section_value` varchar(240) NOT NULL default '0',
  `value` varchar(240) NOT NULL default '',
  `order_value` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `hidden` int(11) NOT NULL default '0',
  PRIMARY KEY  (`aro_id`),
  UNIQUE KEY `section_value_value_aro` (`section_value`,`value`),
  UNIQUE KEY `mos_gacl_section_value_value_aro` (`section_value`,`value`),
  KEY `hidden_aro` (`hidden`),
  KEY `mos_gacl_hidden_aro` (`hidden`)
) TYPE=MyISAM

The reason is quite simple: the index length is limited to 1,000 bytes in MySQL. For single byte character sets, this is a legal statement, because the sums of the indexed column lengths are still less than 1,000 bytes. However, using utf8 or any other multi byte character set, this limit is exceeded. This problem isn't new in MySQL 5, it has also occured in MySQL 4.1.

In Joomla 1.0.3, this problem has been solved - I have been able to install Joomla 1.0.3 without any problems. But also Joomla is not free of MySQL 5 related problems. Creating a new user account, this account is created, but doesn't show up in the user management backend.

I also have found problems trying to log in at the frontend - it simply didn't work. Searching inside the code, I found the source of the problem. They tried to insert a quoted value into a tinyint field - something that's also invalid in strict mode.

The next web app that I've tested was phpBB. It already failed during the installation at this set of queries (as you can see, I've used the German version of phpBB):

CREATE TABLE phpbb_posts_text (
   post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   bbcode_uid char(10) NOT NULL,
   post_subject char(60),
   post_text text,
   PRIMARY KEY (post_id)
);

INSERT INTO phpbb_posts_text (post_id, post_subject, post_text) VALUES 
(1, NULL, 'Das ist ein Beispiel-Beitrag. Du kannst ihn löschen, wenn Du 
willst, denn so wie es aussieht, hat die Installation geklappt!

Hier noch ein paar wichtige Informationen zu phpBB

Weitere Informationen zu phpBB und seiner Benutzung und Administration 
findest Du auf phpBB.de unter der URL - http://www.phpbb.de/doku/doku.php -.

Wenn Du Fragen hast oder Probleme mit einer Funktion, schau im Forum unter 
der URL - http://www.phpbb.de/index.php - nach.

Da die meisten Fragen schonmal gestellt und beantwortet wurde, nutze bitte 
die Suchfunktion unter der URL - http://www.phpbb.de/search.php - bevor 
Du Deine Frage ins Forum schreibst.

Erweiterungen (Mods) zu phpBB findest Du unter der URL - 
http://www.phpbb.de/moddb/ -.

So, und nun viel Spaß mit phpBB ;-)');

The reason for this can also be found easily. There is no default value for the column bbcode_uid, but no value has been provided for this column in the insert statement.

Without strict mode, MySQL uses a system defined default value (in this case, an empty string), but in strict mode, a value must be provided for any field without a default value.

The third application that I've tested was Typo3. With Typo3, no errors occured during setting up the tables, so this Content Management System could possibly work with MySQL 5 without errors.

I have reported the errors that I've found to Joomla and phpBB and I hope that they solve these problems soon. There's always the danger that MySQL 5 has to take the blame for bad written queries in the web applications, because the users often don't know what the real source of the problem is. So I think that another important job to support MySQL 5 is to make developers of these web applications aware of those problems, so that they can fix them as soon as possible until they all work well with MySQL 5, also.

Florian Müller vs. Bono

It's a tough decision, really! The singer of U2, one of the greatest bands (also see my profile to see, who I mean by that ;-)) who takes every opportunity to speak against poverty and discrimination in the world would of course have been a great candidate to me.

But the fight against software patents is so extremly important to developers and almost everybody involved with software in any way, makes me still believe that Florian Müller is the better choice at the 'Vote for the Europeans of the year'. Even though it wasn't easy not to vote for Bono - I gave my vote to Florian (even though I don't know much about himself, I believe that the subject is so important that it had to be done).

I think, there's also a strong political statement behind the vote for Florian Müller and the NoSoftwarePatents project (without supporting any political party in particular), whether we support freedom and liberty or whether we want to be dictated by a few giant enterprises.

And for U2 - I'll instead buy the new DVD (where I had the great opportunity to see one of their shows on the Vertigo Tour!), so everybody has his benefits :-).

New manuals for each version

There's a split up of the MySQL manuals to one for each version, including MySQL 4.1, 5.0 AND 5.1 (!).

They can be accessed at http://dev.mysql.com/doc/refman/4.1/en/index.html, http://dev.mysql.com/doc/refman/5.0/en/index.html and http://dev.mysql.com/doc/refman/5.1/en/index.html.

Very interesting is the Change History on MySQL 5.1 which you can find at http://dev.mysql.com/doc/refman/5.1/en/news.html. It seems, the release of the first 5.1 alpha version is not far ahead.

What a great week ...

... in terms of MySQL :-))). On Friday I passed the MySQL Core Exam (on my first try) and today I was announced as the winner of the 2nd IPod on the MySQL 5.0 contest (http://www.planetmysql.org/kaj/?p=21.

That's even greater if you know that I'm just a little self-employed developer, starting just 2 1/2 years ago with no big company behind me. I once used to believe that there is some software on the market and you can use it - but you have to take it like it is and if there's something you'd think that could be improved, you have to live with it. MySQL convinced me that I was wrong. When the first alpha versions of MySQL 5 came out, I was curious about it and I started testing - I wrote bug reports when I found that something wasn't working the way I expected and also made recommandations how I thought that features could work better or which features I was missing. And I was surprised to see how quickly developers reacted on my input, cleared bugs and discussed about suggestions - and I saw that even I little guy can make a contribution to improve a product that's used by some million users around the world. That's one of the things that I love about MySQL - a great community of a product that's great and still trying to become better and better and a product that comes with a license that's fair to users with a good balance between freedom and a commercial approach (http://www.mysql.com/network/compare.html) - giving every user the choice to choose what's best for him.

How many people love to use MySQL was easy to see when I together with two friends of mine started the db4free.net project at the end of June. Even without any budget for advertising, there are now more than 1,100 users from 53 countries from all continents. db4free provides the very latest version of MySQL 5.0 and is always being updated immediately when a new version is released. There are plans to also install a second MySQL server with MySQL 5.1 soon after its first alpha release and to give users the option whether to create a database account on the MySQL 5.0 server or on the 5.1 server.

So there's still a lot of great things to come and a lot of work to do. I'd like to thank everyone who's involved in making MySQL a great product and ask everyone to continue doing great work. I'm looking forward to seeing the first MySQL 5.0 Production Release and I'm very curious about the MySQL 5.1 version :-).

MySQL 5 certifications

There were a few days of silence in my weblog - but for a good reason. Yesterday I've passed my MySQL Core Exam - and there are plans for more :-). My goal is to end up with the new Developer and Administrator Certificates for MySQL 5.0, as well as some other certificates (PHP, Java).

As it happens, I also received my new MySQL 5.0 Study Guide yesterday. I've read a lot about the MySQL certifications on the MySQL website and in the forum, so I'd like to take this opportunity to sum up the most important details that I've learned about the ways to get certified.

There are two certification levels based on MySQL 4.1 that are currently offered - there's a Core Exam which covers the topics that a developer needs to know and a Professional Exam which is mostly about administration of a MySQL server. The Core Certificate is a requirement that someone can take the Professional Exam.

I've read that the new MySQL 5 certificates will be available about 2 or 3 months after MySQL 5 becomes a Production Release. There will be 2 exams for each of the Developer Exam and the Administration exam. As soon as somebody has successfully taken both exams for either the Developer Exam or the Administration Exam, he will be entitled as CMDEV which is a 'Certified MySQL Developer' or a CMADM which is a 'Certified MySQL Administrator'. At the beginning, the 5.0 exams will be offered as Beta exams - probably with a price discount. Therefore, it might take some time until the candidate knows whether he has passed or failed on the exam.

There will also be upgrade exams from the 4.x exams to the 5.x exams, which might also come at a discount price. I don't know all the details about them, but as far as I know at the moment, there might be one upgrade exam from Core Certificate to CMDEV and another from Professional Certificate to CMADM. For the MySQL 5 exams, it will not be necessary to take the exams in any particular order, as it was required for the 4.x exams.

So if I take my personal example (having already passed the Core exam and planning for both new 5.0 exams), there are 2 ways to achieve this goal. I can either take the Professional Exam and then upgrade both exams to the new 5.0 exams, or I can wait until the 5.0 exams are out and then take the 2 Administration exams and upgrade my Core Certificate to the new Developer Certificate. Both ways end up with 3 more exams.

The most important benefit for me is the following. Even if you know a lot about the technices (e.g. MySQL, but also others) and have good skills on these, it's still very hard to proof your skills to other people - mainly to people who are not developers themselves. It's very hard to convince your customers, but also employers, if you apply for a new job, that you have good skills. There are many competitors around and there are many who pretend that they are good on their job, even though they are only good in selling themselves. Often, these guys get a job more easily than somebody who has better skills.

But with certificates, there's a big advantage against those people who are good in selling, but not as good in developing.

Addition to my Locate IP addresses article

This refers to my article Locate IP addresses and to Arjen's article Storing an IP address in a database table to show you, how the function getCountry can be written more efficiently:

DELIMITER $$

DROP FUNCTION IF EXISTS `getCountry`$$
CREATE FUNCTION `getCountry`(pIp CHAR(16)) RETURNS varchar(100)
BEGIN
  DECLARE _ip_value INT UNSIGNED;
  DECLARE _country VARCHAR(100);

  SELECT INET_ATON(pIp) INTO _ip_value;

  SELECT b.country
    FROM ip_ranges a INNER JOIN ip_countries b
    ON a.code = b.code
    WHERE ip_from <= _ip_value AND
      ip_to >= _ip_value
    INTO _country;

  RETURN _country;

END$$

DELIMITER ;

Locate IP addresses

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/10/locate-ip-addresses/.

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.

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/.

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.