My personal ToDo list for MySQL

First of all the good news: there are a lot of great things to come in MySQL! I recently found Ronald Bradford's article about the MySQL Users Group Meeting in Brisbane where he met with Brian Aker, MySQL's Director of Architecture. They discussed the features of MySQL 5.1 and also the future of MySQL beyond 5.1 - you should really read that ... there's great stuff to come!

However, there's still a lot of other work to be done. After I wrote about foreign key dependencies and Beat Vontobel joined in to extend my example to automatically remove foreign key dependencies with MySQL 5.0, we found a few new nasty bugs. So I decided to create a list of bugs (some of which are contributed by Beat ;-)) that I personally wish to have solved, together with feature requests that I think would be very important and would MySQL make an even better product. They relate to MySQL 5.0.18 and most of them also to MySQL 5.1.5 (there would be some more bugs for MySQL 5.0.18, but as they will - according to the Change History - be fixed in 5.0.19, I don't mention them here). So here they are - first the bugs.

Second CALL to procedure crashes Server (Bug report 17204)

This is the most recent bug that Giuseppe Maxia, Beat Vontobel, Roland Bouman and I found when we experimented with the key dependency issue. Unfortunately, there are still occasions when the bug occurs and occasions where it's not reproducable, but we hope that we can figure out why it sometimes works and sometimes not. The issue is that there's a Stored Procedure that works if it's called for the first time, but when it's executed a second time, the server crashes.

View with check option can be updated to an invalid value (Bug report 16813)

You can create a view on an underlying table and enforce conditions using the WITH CHECK OPTION clause. This is supposed to emulate check constraints, however - while it works with INSERT statements, it's still possible to generate invalid values with the UPDATE command.

CREATE TABLE xxx SELECT function_call() ... doesn't work in some cases (Bug report 14150)

Sometimes you would wish to create a new table based on a query that includes a call to a user defined function. This operation fails with the error message: ERROR 1100 (HY000): Table 'xxx' was not locked with LOCK TABLES

mysqldump problem with triggers (Bug report 16878)

I found out that there are problems if you dump triggers and try to re-import that dump. I didn't get into this problem any closer, but I believe that this is related to the bug report mentioned above.

Dumping views with --add-create-table generates a second set of DROP DATABASE/CRETE DATABASE commands (Bug report 17201)

If you dump databases that include views and you use the --add-create-table option, you get a dump file which looks something like the following:

DROP DATABASE IF EXISTS database_name;
CREATE DATABASE database_name;

-- then the tables and its data are created

DROP DATABASE IF EXISTS database_name;
CREATE DATABASE database_name;

-- then the views are created

This makes the creation of the tables and their data worthless.

NOW() optimized away to constant in BETWEEN ... AND ... in views (Bug report 15950)

MySQL treats NOW() as a constant at the time of creating a view.

CONVERT_TZ() not allowed in all places in views (Bug report 15153)

This bug has been "fixed" before, but CONVERT_TZ() still breaks in some places inside views.

Table and server crash on ALTER TABLE (Bug report 17001)

This one is really, really bad: ALTER TABLE COMMENT='...' destroyed a table beyond any repair possibilities.

Here are some (potential) feature requests. Some have already been filed, but there are some where I still have to look if there are feature requests in the Bug Tracking system. In the cases where I know the feature request IDs, they are of course indicated.

Inside a stored procedure, DATABASE() returns the database where the stored procedure resides instead of from where the stored procedure is called (Bug report/Feature request 14642)

Maybe a set of new functions like CURRENT_DATABASE() could provide the desired functionality.

DELETEs and UPDATEs don't work with certain subqueries

It's not possible to use DELETE and UPDATE commands, if they use subqueries that refer to the deleted or updated query - like this:

DELETE FROM table_name WHERE id = (SELECT col FROM table_name);
UPDATE table_name SET col1 = (SELECT col2 FROM table_name);
UPDATE table_name SET col1 = value WHERE id = (SELECT col2 FROM table_name);

I think, MySQL should first check the subquery and then independently execute the outer query.

Missing features for triggers

There is currently no privilege set for triggers (they require the SUPER privilege to be created or dropped) and there are no commands such as SHOW CREATE TRIGGER, SHOW TRIGGER STATUS (however, SHOW TRIGGERS works) and DROP TRIGGER IF EXISTS. The same set of commands exist for stored procedures and user defined functions, so it would be intuitive if they would work equally with triggers.

Accept NEW.x as INOUT parameters to stored procedures from within triggers (Bug report/Feature request 14635)

NEW.x in triggers is assignable - so there's absolutely no reason to not accept it as an INOUT parameter.

INSTEAD OF TRIGGERS (Bug report/Feature request 16525)

This is a nice feature that's implemented in SQL Server which can trigger a set of statements (a procedure) without executing the original command.

Output of original view creation command (I know that a highly-asked-for feature request exists, unfortunately I don't have its ID available)

Currently, the SHOW CREATE VIEW command returns something like this:

DROP VIEW IF EXISTS `test`.`v_checktest`;
CREATE ALGORITHM=UNDEFINED DEFINER=`mpopp`@`localhost` SQL SECURITY DEFINER VIEW `v_checktest` AS select `checktest`.`id` AS `id`,`checktest`.`val` AS `val` from `checktest` where ((`checktest`.`val` >= 1) and (`checktest`.`val` <= 5)) WITH CASCADED CHECK OPTION;

instead of the original view creation command:

CREATE VIEW v_checktest AS
SELECT id, val FROM checktest
WHERE val >= 1 AND val <= 5
WITH CHECK OPTION

At the moment, there's no way to get the original view creation command directly out of MySQL (it's only available via the .frm file in the data directory)

Error handling in stored procedures/user defined functions/triggers

I would appreciate enhanced error handling capabilities in stored procedures, user defined functions and triggers, as they exist in other database servers.

Stored procedures don't accept parameters inside a LIMIT clause (Bug report/Feature request 11918)

Parameters can be used on many places in queries inside a stored procedure, but unfortunately not in a LIMIT clause. This would for example be useful for paging.

Check constraints

MySQL does currently not provide check constraints, like this:

CREATE TABLE tt (
d1 INT UNSIGNED NOT NULL,
d2 VARCHAR(200) NOT NULL,
email VARCHAR(200) NOT NULL,
CHECK(d1 >= 1 AND d1 <= 5),
CHECK(d2 like 'A%'),
CHECK(email REGEXP '^[a-z0-9_\.-]+@[a-z0-9_-]+\.[a-z0-9_\.-]+$'));

This feature is implemented in most other database systems.

Unclear error message with foreign key constraints (Bug report/Feature request 16290)

If you create a foreign key constraint which references to a column that's not
indexed, you get the error message:

ERROR 1005 (HY000): Can't create table '.\test\b.frm' (errno: 150)

If you don't know that referenced columns have to be indexed, it can leave you searching for a long time.

DROP DATABASE remains the privileges for the database unchanged

If you drop a database, all users remain their privileges for that database. If another user later creates a database with the same name, there might still be privileges for users of which the creator doesn't even know that they exist (could this even be considered as bug?).

Error messages from stored routines should name their source (Bug report/Feature request 17034)

Current error reporting doesn't indicate in which module it occured.

CHECKSUM TABLE should be a function instead of a command (Bug report/Feature request 17009)

It's not possible to use the result of CHECKSUM TABLE in SQL.

It's impossible to use cursors on dynamic SQL (Bug report/Feature request 17153)

MySQL should allow cursors on dynamic SQL (prepared statements).

And last but certainly not least - since Oracle has bought Innobase and with it the rights for the InnoDB storage engine, MySQL should enforce implementing foreign key constraints, ACID transactions and all the advanced features of InnoDB into storage engines that are completely under MySQL's control. This way, a full featured storage engine could even become the default storage engine (or should I say, the 'default default storage engine' since the default storage engine can be changed) of MySQL.

This has become quite a long list and certainly it's not yet complete as there will be issues that I don't know about (so feel free to add your suggestions and 'favourite feature requests'). But don't get me wrong: this should not tell you that MySQL is a bad product! Giuseppe, Beat, Roland and I do a lot of work with MySQL, that's why we might find bugs that are deep inside MySQL (that often only occur in very special situations that even we have to try hard to reproduce) that most users probably will never have to deal with.

However, I believe that these are very important issues and together with the new features that we can expect (mentioned at the beginning of this article), this would bring me much closer to my dream of the perfect database system.

Foreign key dependencies

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/02/foreign-key-dependencies/.

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 http://forums.mysql.com/ (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 (http://bugs.mysql.com/bug.php?id=16508). 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.

Book by Karl Fogel - Producing Open Source Software

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/01/book-by-karl-fogel-producing-open-source-software/.

How to deal with repeating date intervals?

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/01/how-to-deal-with-repeating-date-intervals/.

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 db4free.net 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: http://blog.arabx.com.au/?p=87.

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:

http://www.mpopp.net/2006/01/emulating-check-constraints-with-views/.

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: http://dev.mysql.com/tech-resources/articles/event-feature.html

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:

http://www.mpopp.net/2006/01/emulating-check-constraints/.

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
    -> WITH GRANT OPTION;
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:

http://www.mpopp.net/2006/01/creating-log-tables-with-triggers/.

XAMPP

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 db4free.net?

This year I plan to create a new version of db4free.net, 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 db4free.net 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 db4free.net. 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 db4free.net.

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

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 blogger.com 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 a.id != b.id
    -> GROUP BY a.dt
    -> HAVING max_b IS NOT NULL
    -> 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 a.id != b.id
    -> GROUP BY a.dt
    -> HAVING max_b IS NOT NULL
    -> 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 a.id != b.id
    -> 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 ;-).

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 :-).

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 http://www.mysql.com/training/certification/50/. 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.

db4free.net CleanUp process (2)

In the process of cleaning up unused db4free.net 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 db4free.net 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.

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.

mysqldump's tz-utc trap

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/01/mysqldumps-tz-utc-trap/.

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
    -> FROM b INNER JOIN
    ->   (SELECT a.row, max(b.d) as max_d
    ->    FROM a INNER JOIN b
    ->    ON a.id = b.id 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:

http://www.mpopp.net/2006/01/index-information-from-information_schema/.

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 http://pgfoundry.org/projects/mysqlcompat/.

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.

Hail to GROUP_CONCAT!

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/01/hail-to-group_concat/.

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 :-)!

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