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.