Monday, January 30, 2006

No MySQL forum category for migration from PostgreSQL to MySQL?

I never realized it until now, but there is no specific forum category for migration from PostgreSQL to MySQL at (only one for Other Migration).

Unfortunately, there might still be more people who migrate from MySQL to PostgreSQL than the other way around, but it should be one of MySQL's big goals to change that - and I believe that this is a realistic goal. The lack of a specific forum category has the potential to make people believe that this is meant to be too unrealistic - so I think, there should be one.

I also recently filed a feature request for MySQL MigrationToolkit to add functionality for migration from PostgreSQL to MySQL ( I know that it's no easy task to provide good migration functionality - but there's probably not much time to waste.

Another idea for a forum category may be to ask people what they miss about MySQL and why people migrate away from MySQL or choose another database system to migrate to than MySQL. Of course, not that many people who don't choose MySQL will come their to report about their reasons, but maybe some people will do - and their opinions could be very valuable for MySQL to find out, what the most important things are that are necessary to attract those potential users and customers.

Thursday, January 26, 2006

Joomla still doesn't work with SQL mode enabled

In October, very shortly before MySQL 5 became a Production Release, I tested Joomla and found out that there were a lot of problems if SQL mode is enabled. I reported the problem, but the developers didn't seem very interested and argued, MySQL was still a Release Candidate.

Now, 3 months have passed by and some new maintenance releases of Joomla were released in the meantime, so I thought it would be interested if the problems have been solved.

I installed Joomla 1.0.7 (using my local MySQL 5.0.18-max-nt installation with SQL mode "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION") and the first thing I tried was to create a new user.

And it still failed. First I thought, this could be an Opera problem (as I use Opera as default browser) and tried it with Internet Explorer. It still failed. So I set the SQL mode to '' (empty) and tried it again - suddenly it worked. So the evidence is clear - Joomla still doesn't work with SQL mode enabled.

The roadmap announces for Joomla 1.1: "MySQL 5 support (to be confirmed)" - but I ask to myself, what has yet to be confirmed? MySQL is the primary and at the moment only database system that's supported, and they question if it's necessary to support the latest Production Release? Or did I get something wrong?

However, it's an important task for everyone who develops and maintains a MySQL driven application to make it compatible with enabled SQL modes. And generally it's caused by sloppy development in the past, if an application doesn't work.

Unfortunately, people might tend to blame MySQL if an application does not work correctly (simply thinking, it has worked with MySQL 4.x, but does not work with MySQL 5.0 - so it must be MySQL's fault). Also uses SQL mode "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" and there have been some requests, why doesn't this or that application work. In most applications it's not that hard to change the SQL mode on a session basis - but it should really slowly come to an end that current versions of popular applications don't support enabled SQL mode.

The 'to enum or not to enum' question

I'd like to give you my thoughts about the ENUM data type, which has strongly been discussed in various blog articles recently.

Ronald Bradford showed a nice way how to set up a reference table that's combined with the original table via foreign key constraints. I think, he's right that this is a more proper way to restrict a field to a range of allowable values - read here:

However, I still believe, if you only have up to maybe 5 or 6 values that very likely never change, the ENUM data type does a great job. It's easy to handle and it does exactly what it's supposed to do.

But even though more than 65,000 different values are allowed in an ENUM field, I think it shouldn't be used for many different values - in that case it's probably a better choice to set up a reference table.

Emulating check constraints with views?

This blog post has moved. Please find it at:

Wednesday, January 25, 2006

A little look into the (near) future

In August, I've filed a Feature Request and suggested to allow logging into database tables additionally to writing log files to the file system. The development is in progress and it's told that the feature will probably be available in one of the earlier 5.1 releases.

We also know, that in MySQL 5.1.6 the Event Scheduling Feature will be available - read more about it here:

The combination of these two new features will give great new possibilities. The log data will be available more conveniently and it will also be very easy to filter those records that are of interest (with simple SQL statements). The Event Scheduling Feature can be run at regular intervals to filter out particular records, or to accumulate data - all possible kinds of things. This way it will be much easier and more comfortable for DBAs to find out, what's exactly going on on the MySQL server.

Emulating check constraints

This blog post has moved. Please find it at:

Is this a bug?

I was wondering if it would work to create a user, grant him all privileges on all databases (so make him a superuser) and then revoke all permissions from one particular database.

I knew that this would be quite a complicated process inside the mysql privilege database. All the global privileges would have to be set to 'N' and MySQL would have to insert a new record for each existing database (except the one where the privileges are revoked) to the db table to grant the privileges at database level instead of globally. So I tried it out:
mysql> CREATE USER testuser@localhost
-> IDENTIFIED BY 'abc123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.*
-> TO testuser@localhost
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL ON test.*
-> FROM testuser@localhost;
ERROR 1141 (42000): There is no such grant defined for
user 'testuser' on host 'localhost'

It's not that big surprise that it doesn't work, but what I would be interested in: should it work (according to the SQL standards)?

Creating log tables with triggers

This blog post has moved. Please find it at:

Tuesday, January 24, 2006


Today I took a closer look at XAMPP which seems to be a very interesting project. I even use it on a SuSE Linux 9.0 server, because I didn't want to go through the rather painful process of updating PHP to a current version (which required to resolve a lot of dependencies). There are some good things about it, but also some things that I don't like very much.

Generally I think that XAMPP is very much tailored for beginners to make it easy for them to set up a development environment. But I don't think that many advanced developers use it, because if you try to set it up for the needs of more advanced users, it's almost as much effort than installing each component manually. However, I believe that it would not be impossible to serve the needs of both groups - beginners and advances - by making a few more things customizable during the installation process.

MySQL is a product that shows perfectly that it's possible to keep things simple, but still to advance in a way that it doesn't affect users who like to keep it on a lower level. New (advanced) features are implemented, but the "old" features still work the way that people are used to. I think that this strategy would also work for XAMPP.

What I think of is, to implement options at the beginning of the installation process, where one could be "default" or "complete", where the installation process could remain basically as it is and a second option "custom", where a user could select, which components to install and select more advanced options, if desired.

Here are some things - basically about MySQL - that I didn't find so good in XAMPP's installation:
  • The InnoDB storage engine is by default disabled.

  • The structure of the mysql (privilege) database is still set to only handle old-passwords. It's required to run the mysql_fix_privileges script to update the structure.

  • With the Linux version of XAMPP, the user of the MySQL programs, the data directory etc. is set to root and also the server runs as root, which is a security risk.

  • IIRC (and I think that's even a MySQL issue instead of a XAMPP issue), there's by default an anonymous user. I don't think that anonymous users are necessary in most environments.

There are more MySQL settings that I used to change, but these are mainly personal adjustments.

But the most important thing that I would speak for is to make things more customizable, but to still offer a default install option to leave things unchanged for those users, who don't like to adjust settings.

If there are good reasons why those things are as they are - or if you have more ideas for XAMPP, please let me know. But maybe there are some points that can be discussed and eventually changed, so that XAMPP can become a product that attracts more advanced users, too.

Ideas for

This year I plan to create a new version of, which should be bigger and better.

One of my ideas is to create a support system where users can (if they want) mark a database as "supportable" and ask for help on query writing. I often have found questions in newsgroup how a query writing problem can be solved, but it's always harder to give help if there is no data that the query is based on. So with new, people should be able to upload their data into a supportable database and a group of MySQL experts could help them to solve the problem based on real data.

Another idea is to extend the possibility to specify IP addresses and hosts to allow access to the owner's database(s). At the moment, it's only possible to set one value for the host. In the future version I'd like to give the possibility to specify different hosts or IP addresses, each of which should be allowed to access the database.

Another idea comes from Giuseppe Maxia which is to provide an interface where users can insert their triggers - which can be activated by a script that is run by a cronjob. With MySQL 5.0, creating and dropping triggers require the SUPER privilege that cannot be granted to non administrators (due to security risks). This would be a great workaround for that problem. However, I hope that separate trigger privileges will be available in MySQL 5.1, so that should work without roundabouts in the future.

By the way - I'm also looking for sponsors for Don't worry, the service is absolutely not in danger! But there are a few expenses and on the other hand, there is space that could be used for advertising - preferably to MySQL or database related things. For EUR 50.00/month you can get a banner in the size of 468 x 60 in the head area, where you find the Google Ads at the moment and for the same amount, you can get banners of the same size on the start page and on the registration page, which are the 2 most frequently accessed pages of

So if you are interested in advertising at, please contact me ( and if you have ideas for the new, please email me too or leave a comment to this article ;-).

Monday, January 23, 2006

Calculating time spans and ... a bug?

It's quite a while (exactly, one week - or seven days) ago since I wrote my last article and I was wondering if that was the longest period that I didn't write anything here on my blog. And whooops - this gave me right the idea what I could write about.

I store all my blog articles together with the date in a local MySQL database. Even though the service at works fine, it always feels safer if there is a local copy of everything that I write, for the case if ...

So I extracted the ID and the dates, on which I wrote articles into a separate table which looks like this:
mysql> SELECT id, dt
-> FROM time_periods
-> LIMIT 10;
| id | dt |
| 1 | 2005-09-26 |
| 2 | 2005-09-27 |
| 3 | 2005-09-27 |
| 4 | 2005-09-27 |
| 5 | 2005-09-30 |
| 6 | 2005-09-30 |
| 7 | 2005-10-02 |
| 8 | 2005-10-03 |
| 9 | 2005-10-08 |
| 10 | 2005-10-12 |
10 rows in set (0.00 sec)

I have put a primary key on the id column and a non unique index on the dt column, as we have to reference this column to itself and if there would be thousands or millions of records, it would make the query execution a lot faster.

The query to calculate the days between each date and the day when the last article was written, is not that difficult:
mysql> SELECT a.dt, max(b.dt) as max_b,
-> datediff(a.dt, max(b.dt)) as diff
-> FROM time_periods a INNER JOIN time_periods b
-> ON b.dt <= a.dt AND !=
-> GROUP BY a.dt
-> LIMIT 10;
| dt | max_b | diff |
| 2005-09-27 | 2005-09-27 | 0 |
| 2005-09-30 | 2005-09-30 | 0 |
| 2005-10-02 | 2005-09-30 | 2 |
| 2005-10-03 | 2005-10-02 | 1 |
| 2005-10-08 | 2005-10-03 | 5 |
| 2005-10-12 | 2005-10-12 | 0 |
| 2005-10-14 | 2005-10-12 | 2 |
| 2005-10-20 | 2005-10-14 | 6 |
| 2005-10-24 | 2005-10-24 | 0 |
| 2005-10-25 | 2005-10-24 | 1 |
10 rows in set (0.01 sec)

The date column references to itself, based on the condition that the second date has to be less than or equal to the source date and the ID has to be different. To make sure that the first date isn't included (where there is no reference to an earlier written article), I have added that max_b should not be NULL.

However, I've found an interesting thing. If I change the query to
mysql> SELECT a.dt, max(b.dt) as max_b,
-> a.dt - max(b.dt) as diff
-> FROM time_periods a INNER JOIN time_periods b
-> ON b.dt <= a.dt AND !=
-> GROUP BY a.dt
-> LIMIT 10;

(I have eliminated the datediff function to directly subtract the second date from the original date), I get this result:
| dt | max_b | diff |
| 2005-09-27 | 2005-09-27 | 0 |
| 2005-09-30 | 2005-09-30 | 0 |
| 2005-10-02 | 2005-09-30 | 72 |
| 2005-10-03 | 2005-10-02 | 1 |
| 2005-10-08 | 2005-10-03 | 5 |
| 2005-10-12 | 2005-10-12 | 0 |
| 2005-10-14 | 2005-10-12 | 2 |
| 2005-10-20 | 2005-10-14 | 6 |
| 2005-10-24 | 2005-10-24 | 0 |
| 2005-10-25 | 2005-10-24 | 1 |
10 rows in set (0.02 sec)

Note the value of 72 in the third row.

Is it a bug? I guess not, because MySQL seems to calculate 20,051,002 - 20,050,930 which results in 72. However, it's something that somebody might not expect so I would at least call it a "Gotcha". But note that it's essential to use datediff here instead of simply subtracting the values, because the results are not the same!

Last but not least - how do I get the maximum number of days that I didn't write any article? Look here:
mysql> SELECT max(diff) FROM
-> (SELECT a.dt, max(b.dt) as max_b,
-> datediff(a.dt, max(b.dt)) as diff
-> FROM time_periods a INNER JOIN time_periods b
-> ON b.dt <= a.dt AND !=
-> GROUP BY a.dt
-> HAVING max_b IS NOT NULL) AS tmp;
| max(diff) |
| 7 |
1 row in set (0.01 sec)

I have put the original query into a derived table and asked for the maximum day of "differencies". Very straightforward and easy to see that seven days is really the maximum number of days that I didn't write anything ;-).

Monday, January 16, 2006

Everybody can post comments again ;-) ...

... as long as they are related to MySQL or database topics.

Unfortunately, there were often comments on my articles that were just spam and they had nothing to do with MySQL or databases at all. I always deleted them manually and one day I decided to only allow comments to people who are logged in.

But I received quite a lot of feedback from people who wanted to post relevant comments, but they didn't have an account and so couldn't answer. Now I have changed the setting back to its original state, because I'm quite curious about what people think about what I write and so, they should have a chance to tell me and the blog's readers :-).

Sunday, January 15, 2006

MySQL 5.0 Beta Certification Exams available

I have just discovered that the MySQL 5.0 Beta Certification Exams are available. The Developer Exams are available now, the Administrator Exams are scheduled to be available by the end of January.

The way to get MySQL 5.0 certified has changed a bit compared to the 4.x exams. There are 2 exams for each the Developer and Administrator Certifications. Only if one passes both exams, somebody can title himself either a CMDEV (Certified MySQL Developer) or CMDBA (Certified MySQL Database Administrator). There are no more prerequisites, so the exams can be taken in any order (which was not the case with the 4.x exams where the Core exam was a prerequisite for the Professional exam).

There are also upgrade exams for people who have already passed the 4.x exams. One exam is necessary to upgrade from 4.x Core to 5.0 CMDEV and one exam from 4.x Professional to 5.0 CMADM. Unfortunately, there is no more detailed information about which topics these exams will cover. It also seems that these exams are not yet available as I have logged into my Pearson VUE account and looked up for which exams I could register. I found that only exams 003-001 Certified MySQL Developer Exam Part I and 004-001 Certified MySQL Developer Part II were available, but no upgrade exams. I expect that more information about this is yet to come.

I successfully took both the Core and Professional Exams of MySQL 4.1 and I can truely recommand to get certified to everybody who plans to get good skills about MySQL. I learnt a lot of things that I probably wouldn't have learnt without taking the exams. There are many things which you probably think you don't need them, but when you come to learn about it you'll find out that it makes your work a lot easier.

To get detailed and up-to-date information, take a look at MySQL's certification page at If you plan to take the exams (and also, if you don't, it might be a good choice) I recommand that you get the MySQL 5.0 Certification Study Guide. If you use this book to learn about the relevant topics thoroughly, you have the best chances to pass your exams. CleanUp process (2)

In the process of cleaning up unused database accounts, the first databases with their users have now been deleted.

During the last month I've sent out emails to all users asking them whether they would like to keep their account. Today I have deleted 966 database accounts, where people have either decided not to use it anymore, or where no data has been stored and the database hasn't been accessed within the last month.

Other 817 accounts, where the users didn't give me a decision, have been set inactive - they can still login but don't have access to their (still existing) database anymore. So there is still a final chance to get access again, if the database is really needed. Until the end of January I will also delete those accounts, where the user didn't ask for re-creation.

Before, there were 3,206 accounts from which 966 have been deleted and 817 are inactive - so there are now 2,240 accounts from which are 1,423 active.

So how can you verify the state of your database account? Try to login - if you can't login, your database account has probably been deleted. If you still can login, but you cannot access your database anymore, it's probably inactive. Or if you still can access your database (if you have visited the cleanup page on and told me to keep the database, this will be the case), everything's fine. Also if you have created your database account after 18th December 2005, it will still be there.

The reason for doing this is to provide more resources to people who use the database on a regular basis. In the future there will be more cleanup processes. Maybe the way how I do this will change, but I will always try to take care not to delete database accounts without giving users a fair chance to keep it, if that's what they want.

Saturday, January 14, 2006

mysqldump and mysqlcheck now work perfectly

Backups of your database data and database maintenance are essential things that everybody should do who runs database servers in production.

I used to do this with a little batch that looks like this:
mysqldump --add-drop-table --default-character-set=utf8 
--databases mysql -h localhost -u [my root user] -p
> "E:\mysql_dump\dump_mysql.sql"

mysqldump --add-drop-table --default-character-set=utf8
--all-databases -h localhost -u [my working user] -p
> "E:\mysql_dump\dump.sql"

mysqlcheck -u [my root user] -p --all-databases --analyze --optimize

This little batch creates two dump files - one with the mysql database which stores all the privilege data and a second dump file with the rest of the data (my working user has privileges for all databases except mysql). Finally, the mysqlcheck command optimizes my MyISAM tables and renews the index information of my tables to ensure that MySQL's optimizer can make his query desicions based on current index status data.

Until recently (at least 5.0.15) it wasn't possible to use the --all-database option for the mysqldump and mysqlcheck commands. mysqldump was stopped by some views and mysqldump also tried to optimize and analyze the tables in information_schema and failed on these. So I always had to specify all databases one by one (--databases [database1] [database2] ...) which required to maintain this list if I created or deleted databases on my server.

Now this requirement is over - I can use --all-databases and not care about the databases that I create or delete. Working with MySQL has again become a little bit more comfortable :-).

What's wrong with MySQL QueryBrowser?

First I'd like to say - I absolutely like the way that MySQL QueryBrowser is supposed to work! All the windows can very easily be arranged so that everything's in sight (one one screen) that's necessary to write complex queries - I can e.g. show the results of a query in one screen, open the field lists of the tables that I need, so this way, everything's available that I need to write big queries (including complex joins) without the need to switch between different screens. I haven't found another programm (neither for MySQL, nor for PostgreSQL) that makes the work of query writing more comfortable!

However, since version 1.1.15, evil bugs have found their way into QueryBrowser. For some time, it wasn't possible to drag and drop a table name into the result area to immeditely show the contents of a table. In one version, it wasn't possible to open the window to write a Stored Procedure or User Defined Function. And in another version (I think, it was 1.1.16) it wasn't possible to switch between tabs without losing the query that's in the other tab.

These were really annoying bugs, but they seem to have been solved in the most recent release, 1.1.19.

But there are still other nasty things. If you mark a query, it becomes invisible (Bug no. 15825). And I found that inactive menu items are completely invisible (Bug no. 16507). They should of course be greyed out, but invisible?

I also found another problem (I only tested 1.1.19 for a short amount of time, since I soon found out that there are still too many problems to update) that sent me an "out of memory" error and again, it was impossible to drag and drop a table into the result set. Unfortunately I haven't found a way to repeat this on a test case, so I didn't have sufficient information about this to report this bug. But it shows that there are more problems in MySQL QueryBrowser.

I still use version 1.1.14 because as far as I discovered, this was the last version that works without major bugs. The latest version will be the fifth version in a row (I've tested them all) that I will skip - I hope that I'll soon be able to update MySQL QueryBrowser, too. I used to work with the most recent versions of MySQL's products and almost all the times, I did fine with this strategy. I hope that the need to skip versions will stay very rare exceptions.

MySQL 5.1 evolves

MySQL has announced two very useful new features for versions 5.1.5 and 5.1.6.

5.1.5 will get new XML capabilities and 5.1.6 will be able to schedule event tasks. That will be very helpful to clean up outdated data, for example - a task that had to be done on the client side so far, but now it can be delegated to the database server.

I still wonder if MySQL 5.1 will introduce check constraints, as this is still another thing that still has to be done on the client side, although it would be useful to transfer this to the database server.

Another thing that should abolutely be in 5.1 are separate privileges for trigger creation and deletion.

If these both features, together with the features that have already been announced, will make it into 5.1, I'm sure that MySQL 5.1 will become a very important new release that brings it a big further step forward to compete with the biggest commercial database systems.

Sunday, January 08, 2006

Saturday, January 07, 2006

A little join problem

Today I found a question in a newsgroup asking for help with creating a join. This join should take a date out of table a and find the record in table b which has a date less than that of table a, but still closest to that date. The tables should be joined through the id column.

Here's my example data (I've changed it a bit from the original request):
mysql> SELECT * FROM a;
| row | id | d |
| 1 | 15 | 2005-12-25 |
| 2 | 20 | 2005-12-29 |
| 3 | 25 | 2006-01-12 |
| 4 | 30 | 2006-01-15 |
4 rows in set (0.00 sec)

mysql> SELECT * FROM b;
| row | id | d |
| 1 | 15 | 2005-12-19 |
| 2 | 15 | 2005-12-21 |
| 3 | 15 | 2005-12-27 |
| 4 | 20 | 2005-12-11 |
| 5 | 20 | 2005-12-30 |
| 6 | 20 | 2006-01-09 |
| 7 | 25 | 2005-12-31 |
| 8 | 25 | 2006-01-02 |
| 9 | 25 | 2006-01-12 |
| 10 | 25 | 2006-01-16 |
| 11 | 30 | 2006-01-01 |
| 12 | 30 | 2006-01-08 |
| 13 | 30 | 2006-01-13 |
13 rows in set (0.00 sec)

So for example row 1 from table a should refer to row 2 from table b, because the 21st December is closest, but still less, to 25th December; row 2 from table a should refer to row 4 a.s.o.

For a real MySQL prof this shouldn't be much of a problem, but I like it, because it includes a derived table that's included in a join. This is my solution to this problem:
mysql> SELECT c.row as row_a,
-> b.row as row_b,
-> c.max_d
-> (SELECT a.row, max(b.d) as max_d
-> ON = AND b.d < a.d
-> GROUP BY a.row) AS c
-> ON b.d = c.max_d;
| row_a | row_b | max_d |
| 1 | 2 | 2005-12-21 |
| 2 | 4 | 2005-12-11 |
| 3 | 8 | 2006-01-02 |
| 4 | 13 | 2006-01-13 |
4 rows in set (0.00 sec)

If the current date should be included, the only thing that's to be changed would be to alter the < sign to a <= operator. A little but fine query that shows the power of SQL.

Index information from information_schema

This blog post has moved. Please find it at:

Tuesday, January 03, 2006

PostgreSQL's MySQL compatibility functions

Yesterday I complained about PostgreSQL's lack of the GROUP_CONCAT function, today I received a mail from Christopher Kings-Lynne with the solution: the mysqlcompat functions, which can be found at

Besides an implementation of GROUP_CONCAT (not very equal to MySQL's implementation, but similar), there are many other functions that we are used to from MySQL. I installed the functions and tested the GROUP_CONCAT function and it allowed me to solve my problem without any pain. So for everyone who's used to work with MySQL and has to do some work with PostgreSQL, it's good to know that this collection of functions (in total there are 158 functions, including all the different overloading variants) exists.

Monday, January 02, 2006

Sunday, January 01, 2006

Happy and successful new year!

I wish everybody envolved with MySQL: developers, employees, the management and of course everybody in the MySQL community a great and successful new year 2006!

It will be exciting to see what the new year will bring - I guess, a lot of great things again. So keep on rocking everybody :-)!