More from the MySQL UC

So here are a few photos from me that I've taken at the Conference and around. So let's start with my hotel.

      

It was quite a cheap hotel and to be honest - not the very cleanest one. But I didn't spend much time there anyway, so why bother. But what's that against the litter that I could find on the streets and sidewalks (to not be misunderstood I'd like to add that none of the litter is from me ;-)).

      

Since I'm quite a heavy smoker, I was quite concerned about the smoking opportunities in California. But it was by far not that tough. Only during the flight I had to help myself with an inhalor, but finding places to smoke was not really hard in CA.

So here's my car that I rented for my 6 day stay in CA - it was an even bigger one than the one I've ordered (which wasn't available at the time when I picked it up, so I got a car one class higher). It had masses of buttons that I even didn't find out what they are good for, but most of them are not needed for driving anyway - so I even got quite used to it and felt quite comfortable (although
I usually don't drive cars with automatic transmission - another thing to get used
to).

      

   

It's a bit off-topic, but here's my own car - so you can compare a little bit ;-).

         



One of the bad things is that I hardly have any photos from the Conference and many are quite hazy. But there are many photos from other attendees of the Conference around - check out here:

http://sheeri.com/archives/48
http://www.flickr.com/photos/x180/tags/mysqluc06/
http://www.flickr.com/photos/x180/sets/72057594112748158
http://www.flickr.com/photos/byte/sets/72057594120596064/
http://jcole.us/gallery/uc2006

If you know more links, please let me know!

But here are two from me - one showing Sheeri during her MySQL Meetup BOF session and the second one shows Giuseppe Maxia receiving his Community Award - seconds before Roland Bouman and me had to face a huge surprise.

   

I like road and highway photos because they show a lot of the country and what people really see when they visit a specific place. Most photos were taken out of my car.

         

         

         

         

      

And last but (by far) not least there was the San Francisco sightseeing tour on April, 23rd together with Roland Bouman and Mike Kruckenberg.

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

Recent db4free.net downtimes

I know, there have been some problems with db4free.net during the week that I attended the MySQL Users Conference. Unfortunately, I didn't have a laptop with me and had no access to the Internet most of the time. I could find out on Tuesday (thanks to Sheeri who borrowed me her laptop!) that the MySQL 5.0 server was down and managed to start it up again. When I returned home Saturday evening, the MySQL 5.0 server was down again. Strangely enough, the MySQL 5.1.10 server which was built from the development source, seemed to work great all week long.

Today I have updated the MySQL 5.0 server to 5.0.21 and installed the lately released MySQL 5.1.9 server instead of the 5.1.10 development snapshot. I hope that the problems are solved now, but I'm back online now to keep an eye on the happenings on the server.

Thank you!

Thanks to everyone who's been involved in rewarding me with the MySQL Community Award "Writer of the Year 2006" and congratulations to my co-winners Giuseppe Maxia, Roland Bouman and Rasmus Lerdorf!

Here's that great prize (sorry for the quality and all the shades - I'm not a good photographer):



So many people have Oscars, Grammies and things like that on their chimneys - but how many have such a great trophy ;-)?

Returning home from the MySQL UC

Hey, this was an incredible week and it's hard to believe that it's over! Sorry that you didn't hear or read from me during this week (except of course the people who attended the Conference), but I was (with a few exceptions, thanks to Sheeri and Roland) offline this week and could not post any new articles in that time.

Every individual day was absolutely incredible. The trip was alright, it was my first big journey alone and I was concerned about many things that could possibly go wrong - from how to find my way through the airports (my route was Vienna - London - San Francisco), or since I rented a car, driving in a different country with some different rules and things like that. But it all worked very smoothly and there were no such problems at all ... and I gained a lot of new experiences about travelling. The flights were long and much of it was boring, but even there I had a few remarkable moments - for example when I managed to see Greenland's snow and ice landscape (absolutely beautiful). On my flight back home I had time to take a taxi into the city of London to get myself a cup of coffee at Harrods - this is also a nice thing on the side of my whole journey (although quite expensive since one taxi ride costed me 45 pounds).

But back to California - I arrived at my hotel (the Wellesley Inn at Steven Creek Blvd.) on Saturday afternoon. After checking in and having a shower, I planned to look up for the Convention Center, the location of the Users Conference. Just when I was about to leave, I got a call on my mobile phone from Roland Bouman, so we arranged that I pick him up to go there together. So we did - it wasn't hard to find the Convention Center, so we went in to look around and get ourselves a cup of coffee. Suddenly, Roland asked: isn't that Arjen Lentz over there? - and it was him, together with Jay Pipes and Colin Charles. It was followed by our first MySQL dinner.

Sunday was our sightseeing day. Originally I thought of going alone, but Roland told me that he arranged to go sightseeing with Mike Kruckenberg and I was delighted to join in. So we picked up Mike from the San Francisco Intl. Airport on Sunday morning and drove into town to take a ride with the famous San Francisco cable cars, see Golden Gate Bridge and more. Also watch out Roland's and Mike's articles about our tour - I'll soon also put some pictures online that I took.

In the evening, we returned to the Convention Center to register for the Conference. Here we met Beat Vontobel and Giuseppe for the first time - two more great bloggers that I spent most of the time at the Conference together. Of course we also finished this day with a dinner all together.

Monday was the tutorials day at the Conference. My first tutorial was "Secure Your PHP and MySQL Web Applications" with Laura Thomson. Even though I heard most of the things concerning PHP security before, it was great to call them back into memory. My second tutorial - "New Features of MySQL 5.0 and 5.1 In-depth" with Jan Kneschke - was not a good decision for me. The tutorial was not bad, but since I do a lot of work and testing with the latest versions, there was nothing new for me. If I could chose again, I would probably select the Data Warehousing tutorial.

In the evening, I joined the BOF session "MySQL's Graphical Tools - Current State and Future" by my compatriot Mike Zinner, who's the leader of the GUI developing team. It was a pleasure to meet another Austrian who has made a career with MySQL and he does great work - which was nothing new for the GUI tools MySQL Administrator, MySQL QueryBrowser and MySQL MigrationToolkit that I have used before, but I was very impressed by the possibilities that come with the latest tool in the GUI family: MySQL Workbench. If you haven't already done so, you should give it a try!

Tuesday was certainly the greatest day for me. There were these keynotes in the morning and I knew that I will see many of MySQL's most famous people like Monty Widenius, David Axmark or Kaj Arnö for the first time. Not only that - I met with Sheeri Kritzer who organizes those great MySQL meetings in Boston that I heard so much about and that really impressed me a lot (I hope, I can manage to be there one day). But the best surprise was yet to come. After the State of the Dolphin talks with Kay Arnö, Monty Widenius and David Axmark, the winners of the MySQL Community Awards were announced. Giuseppe and Roland were the winners for Contributor of the Year (Giuseppe) and Forum Leader of the Year (Roland) and I almost fell off my seat when I heard that I won the category "Writer of the Year"! That was completely unexpected and such a great honour - probably the most remarkable moment in my life (WOW!).

Sheeri wrote a great article covering these moments, including some photos that also show some of my most memorable moments - check it out!

I met a lot of people who told me, they were reading my articles and they knew db4free.net. It's such a great feeling to find out that I have a voice and it's heard and welcomed by so many people - thank you all and please continue reading my articles and using db4free.net! My great experiences at the Conference give me a lot of motivation to also continue my work and keep on improving these things - so stay tuned.

Back to the events at the Conference - in the following I'd like to give you a list of the other sessions and BOFs that I attended (I hope, I have memorized everything correctly):

Tuesday:

* The 5.1 Release, Brian Aker
* A Look at New and Cool Things in the World of PHP, Rasmus Lerdorf
* New Storage Engines in MySQL 4 and 5, Patrick Galbraith
* Performance Tuning Best Practices, Jay Pipes
* PHP Framework and the Eclipse PHP IDE, Andi Gutmans
* Higher Order MySQL, Giuseppe Maxia
* HackFest B: Creating New SHOW Commands, Brian Aker
* BOF: MySQL Meetup Groups, Sheeri Kritzer

Wednesday:

* Speeding-up Queries: New Features of the MySQL 5.0 Query Engine, Timour Katchaounov
* MySQL Migration Toolkit, Michael G. Zinner
* Web Performance and Scalability with MySQL, Ask Bjørn Hansen
* Tuning MySQL5 SQL and Stored Procedures, Guy Harrison
* MySQL Quiz show (with me participating and winning two books!)

Thursday:

* Plugging Your Applications into the MySQL Storage Engine Layer, Brian Aker
* Confession Time: Deadly Sins Using MySQL and PHP, Arjen Lentz
* MySQL GUI Tools for the Enterprise, Michael G. Zinner
* Jim Starkey's OLTP Storage Engine, Jim Starkey

Thursday ended with another MySQL dinner including Kaj Arnö, Arjen Lentz, Colin Charles, Jay Pipes and of course Beat, Roland and Giuseppe.

After checking out of Wellesley Inn and a little shopping tour with Roland, Giuseppe and Beat, I drove (joined by Roland) back to the airport to return my car and travel back home (which was a nice trip again with a short stay in London as I mentioned before - and a lot of rain when I arrived in Vienna *aargh*).

This is still not everything about the Conference - watch out, there's more to come.

And I don't want to finish this article without giving one more statement: this Community ROCKS! Not only from far away with mailing, writing articles and all the things we used to do. This Community also rocks meeting face to face! I met so many nice and lovely people - and it was like knowing Arjen, Beat, Colin, Frank, Giuseppe, Jay, the "Mike's" (Kruckenberg and Zinner), Roland and Sheeri (the people that I spent most time with in alphabetic order to not prioritize) for a long time.

Keep on doing your great work and see you all!

db4free.net offers MySQL 5.1.10

I know that it's a risky thing to run a MySQL version from the development source tree. But a lot of work has been done since MySQL 5.1.7 and I have experienced that the latest development version runs better than the most recent released version 5.1.7, so I dared to update MySQL 5.1 to 5.1.10-beta.

Maybe it's a little consolation for those who cannot participate at MySQL Users Conference, so you can in the meantime try all the new features at the very latest state of development. But be aware that there can be bugs that can usually not be found in a release version.

So take good care of db4free.net while I am trying to collect new experiences at the Conference, that I will certainly be able to use to make db4free.net even better :-).

The countdown is running

It's unbelievable how fast the last five months went by. In November, there was that huge surprise when I (together with Roland and Beat) won the MySQL 5 Contest, but it seemed very unreal and very far away.

Now, I'm 15 hours away from leaving towards the MySQL User Conference, and even though it still looks a little bit abstract, I am slowly starting to realize that the most exciting week of my life is right ahead of me. And it's a big challenge, too. It's the first time for me at all to leave Europe.

I'm really looking forward to meeting all of you! I used to have contact to many people by email and I have seen photographs (and from some even videos) from many of the attending people, but will I recognize you when you're standing in front of me in reality? It's a great thing to meet you face to face now!

So, see you all - I'm coming :-)!

My first live webinar experience

I registered for today's "What's new in MySQL 5.1 - Partitioning, Row-based Replication and more" live webinar which is a hot and very interesting topic. Well in fact, it was quite a short experience.

I wasn't aware that I get the sound through telephone (although it was mentioned in the confirmation email, which I didn't read so carefully - my fault) and that I need a fixed telephone line to get the corresponding sound for the webinar. So I followed the instructions, but I called the number on my mobile phone first. I heard from a tape voice that these kind of numbers (toll free 0800s) are not available via my mobile phone supplier, so I tried it on the fixed telephone line and it worked well. But there was another minor problem that made it impossible for me to watch and listen to the webinar simultanously - my fixed telephone is too far away from my PC.

However, that's not such a big deal as the whole webinar will be available for download in about a week. But the procedure to follow a live webinar looks a bit complicated to me - it would be more confortable to get the sound via Internet as well, but I guess that there are some technical issues that make this impossible with the software used.

As I said (to switch to a slightly different topic), MySQL 5.1 is a very hot topic and I have one MySQL 5.1.10 installation running (that's a few days old) and I saw a lot of progress going on. Unfortunately, I can't update from the development source at the moment because the compilation fails at a certain point. But I hope that a new MySQL 5.1 release will be available soon, because there are certainly many people who do not compile from source and they will probably also be excited to see the progress that has been made ;-).

Something broken at the development source tree?

I just tried to upgrade my MySQL 5.1 server from the development source tree. Unfortunately, with no success.

First I tried to get the latest updates via BitKeeper, but the links seem to be broken: http://mysql.bkbits.net:8080/mysql-5.0 and http://mysql.bkbits.net:8080/mysql-5.1-new.

So I decided to download the latest nightly snapshot and got me the file http://downloads.mysql.com/snapshots/mysql-5.1/mysql-5.1.10-beta-nightly-20060415.tar.gz. I extracted the file with "tar xvfz mysql-5.1.10-beta-nightly-20060415.tar.gz" (which worked without problems) and changed into the just created directory. From there, I tried to compile the source code:

suse:/MySQL_51/mysql-5.1.10-beta-nightly-20060415 # BUILD/compile-amd64-max
testing athlon64 ... ok
+++ gmake -k distclean
gmake: *** No rule to make target `distclean'.
+++ true
+++ /bin/rm -rf '*/.deps/*.P' config.cache storage/innobase/config.cache storage/bdb/build_unix/config.cache bdb/dist/autom4te.cache autom4te.cache innobase/autom4te.cache
+++ path=BUILD
+++ . BUILD/autorun.sh
++++ aclocal
/usr/share/aclocal/progsreiserfs.m4:13: warning: underquoted definition of AC_CHECK_LIBREISERFS
run info '(automake)Extending aclocal'
or see http://sources.redhat.com/automake/automake.html#Extending-aclocal
/usr/share/aclocal/pilot-link.m4:1: warning: underquoted definition of AC_PILOT_LINK_HOOK
/usr/share/aclocal/lib3ds.m4:4: warning: underquoted definition of AM_PATH_LIB3DS
++++ autoheader
++++ test -f /usr/bin/glibtoolize
++++ libtoolize --automake --force
++++ automake --add-missing --force
configure.in:713: required file `zlib/Makefile.in' not found
++++ die 'Can'\''t execute automake'
++++ echo 'Can'\''t execute automake'
Can't execute automake
++++ exit 1
suse:/MySQL_51/mysql-5.1.10-beta-nightly-20060415 #

Does somebody have an idea?

Alternative privilege system including ROLES and separate host table?

I recently talked about the idea of a different privilege system that separates the host information from the user name. Of course, it would break backwards compatibility and so it's almost impossible to change the current behaviour entirely. Also, Beat Vontobel has suggested that there are situations where it is desireable to have user and host information combined.

However, there might be a (not too complicated) solution. I'm thinking of a separate database that manages the user privileges according to the alternative system. Stored Procedures and Triggers could sync the information in the new database to the mysql schema database - and maybe also the other way around. This would require no changes in the MySQL server at all and every user could decide for himself, whether or not to use the alternative system. And finally, the users managed by the new system should not interfere with users that are managed by the mysql schema alone (in the traditional way) - so it could/should be possible to combine both systems.

As I thought about it, I came to the conclusion that it shouldn't be too tough to implement such an alternative system by a separate table using Stored Procedures and Triggers. At least, it looks like an interesting project.

Your thoughts about it are - as always - welcome!

A little (?) brain exercise

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/04/a-little-brain-exercise/.

User XYZ != User XYZ

It's nothing new to me and probably nothing new to most folks who work with MySQL on a regular basis that the username alone does not identify a MySQL user, but a combination of username@host.

But if you forget about it for a moment, you can easily begin to wonder about special behaviors. That just happened to me, so I just thought that I have discovered a very evil bug - but looking twice, it became quite clear to me why MySQL behaved this way.

I have two computers, both with MySQL servers, both with users called mpopp - but each server has two of them - one is mpopp@localhost and the other is mpopp@10.0.0.1__ (both with the same permissions), so it allows me to access user mpopp from each of my PCs. I created a little stored procedure on one computer and then copied this procedure to the other one - together with the new DEFINER option that's new in MySQL 5.0.20. But I didn't think about it (and didn't look carefully) and created the new procedure with the option DEFINER=mpopp@'10.0.0.1__' (while being logged in as user mpopp@localhost).

It was quite a shock when I called "SHOW CREATE PROCEDURE" and there was no output for the definition - so it looked like my procedure was gone. Also in information_schema.ROUTINES I couldn't find the definition, although the procedure itself was listed.

It became clear when I logged in as root and finally found that the procedure definition was still there. I looked a bit more carefully and found the mpopp@'10.0.0.1__' user in the DEFINER column. Of course, you can only output the procedure definition from the user that created the procedure (or from a root user, obviously), and in that case, it was user mpopp@'10.0.0.1__' and not mpopp@localhost (even though this was the user that I was logged in when I created it).

That makes me think - wouldn't it be more intuitive if the user name alone would identify a user and the host definition would be separated by a 1 : n relation? So for example - there's a user mpopp with all the permissions and there's a separate table that does nothing more than to define from which hosts the user has access? I know, to change that would break backwards compatibility completely - but on the other hand, it might be less confusing. I would be curious what other people think about this.

Events work much better now

Today, I updated my development tree installation of MySQL 5.1 (which is already at 5.1.10-beta) and tested Event Scheduling again. In MySQL 5.1.7 there were still a lot of rough edges, but now a lot is much smoother.

Take for example this event:

DELIMITER //

DROP EVENT IF EXISTS copyGeneralLog //

CREATE EVENT copyGeneralLog
ON SCHEDULE EVERY 1 MINUTE
STARTS '2006-04-08 01:44:00'
ENDS '2006-04-08 01:50:00'
ON COMPLETION PRESERVE
ENABLE DO
BEGIN
  INSERT INTO test.general_log
    (event_time, user_host, thread_id, server_id,
     command_type, argument)
  SELECT event_time, user_host, thread_id, server_id,
         command_type, argument
    FROM general_log
    WHERE command_type='Connect';

  TRUNCATE TABLE general_log;

  SELECT 1, 2, 3;
END //

DELIMITER ;

In 5.1.7, it was not guaranteed that this event runs at every starting minute (with :00 in the seconds), this seems to work now. Also that the query "SELECT 1, 2, 3" (which actually doesn't make sense here, it's just for testing purposes) is now being logged is new - in 5.1.7, queries inside events haven't appeared in the general log.

And the TRUNCATE TABLE command also failed in 5.1.7 and works now. So there's a whole lot better now.

One thing is still a bit disturbing. Take the output of information_schema.EVENTS:

mysql> select * from information_schema.events\G
*************************** 1. row ***************************
 EVENT_CATALOG: NULL
  EVENT_SCHEMA: mysql
    EVENT_NAME: copyGeneralLog
       DEFINER: mpopp@localhost
    EVENT_BODY: ... [cut off]
    EVENT_TYPE: RECURRING
    EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: MINUTE
      SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
        STARTS: 2006-04-07 23:44:00
          ENDS: 2006-04-07 23:50:00
        STATUS: ENABLED
 ON_COMPLETION: PRESERVE
       CREATED: 2006-04-08 01:43:42
  LAST_ALTERED: 2006-04-08 01:43:42
 LAST_EXECUTED: 2006-04-07 23:46:01
 EVENT_COMMENT:
1 row in set (0.00 sec)

While CREATED and LAST_ALTERED are specified as of the local time zone, STARTS, ENDS and LAST_EXECUTED show the UTC time.

By the way, since last week, there are also nightly snapshots from MySQL 5.1.

The downside of information_schema

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/04/the-downside-of-information_schema/.

QueryBrowser 1.2.7?

I just started QueryBrowser on my SuSE 10.0 PC (the version that's installed by default) and wondered, what version it would be. Looked into Help/About and was extremely surprised to see 1.2.7 as version number.

The current Linux version of QueryBrowser as stated on dev.mysql.com is 1.1.18.

I guess that the version number of QueryBrowser's Help/About dialog is wrong, but does somebody know exactly, what happened here?

Woooohoooooo - the 21st century has just begun!

Maybe this message looks like a little late for you, but for me it's true. Today I finally got an xDSL internet connection, which starts a whole new epoch for me.

So far, I only had an ISDN connection with a maximum transfer rate of 7 - 8 KB/s. Every 8 hours, the line has been disconnected automatically, so no big downloads were possible - and even smaller downloads made it impossible to surf through the web comfortably. That was extremely limiting and there were many things that I could only dream of.

Now, these times are over! Today I have already downloaded more data than I usually did (or could) in a whole month before - I'm just installing SuSE Linux 10.1 Beta9 in a VMWare server (both just downloaded), and Ubuntu 5.10 is almost finished. Many more things are planned - also to take a close look at MaxDB, as it won't be a problem anymore to quickly download all the things that I need. Downloading new versions of MySQL when they come out? Will now be a laugh!

Besides, I'm listening to Internet Radio (through ITunes) without any interruptions (although the Ubuntu download is still running). Also the MySQL webinars are one of the new great possibilities. And ... and ... and ..... a list that doesn't stop.

And my iPod Nanos that I won at the MySQL 5 Contest get a whole new meaning as well ... it's now fun to surf around with ITunes. With ISDN, it was rather frustrating.

I also mentioned in my last article that I bought a new PC and I also got myself a new Java enabled mobile phone. That's another exciting thing, to take a look at J2ME, how to write Java applications for mobile appliances. I'm sure, MySQL will also be of great value in this context.

So it feels like when I was a little boy on my first Christmasses. But more than that, it's a big step from the past forward to reasonable up-to-date standards.

These are really bad news ...

... from Andrew that he doesn't find the time to maintain mysqldevelopment.com any longer and write MySQL related blog articles :-(.

It would be too bad to let mysqldevelopment.com die, so I hope that there's a way to keep it alive in some form or another. I know that it's a huge amount of work for a single person to keep such a site up to date, so maybe there's a way to integrate the content into MySQL Forge or to create a Wiki to make it easy to anybody who just has something interesting to contribute it. I would also integrate it into the db4free.net project, if there's no other way. Any rescue is better than letting it die completely.

I also haven't been very active in terms of MySQL recently, but nevertheless, there are a few great things that will make it easier for me to get involved more deeply than ever before. I just got a new PC running (an Athlon 64 3200+ with 2 GB RAM and two 250 GB hard disks - will be running SuSE Linux as primary OS) and in the upcoming week, a new xDSL connection will replace my current slow ISDN connection. I'm sure, that will make working much more fun and there will be new things that I can try out then that would be quite frustrating with an ISDN line.

My MySQLUC departure is just a month away

Time seems to be racing - in exactly one month I'll be on the way from Vienna via London to San Francisco and then to Santa Clara to attend the MySQL User Conference.

That's all extremely exciting for me. Actually, it's the first time in my life that I leave Europe. It will also be my first flight in 11 years (after leaving school, I travelled to Tenerife with my school mates - no big trip since then). It will be a great new experience.

I have rented a car in San Francisco for the whole duration of my stay together with a GPS system, so I will be able to drive around to see a bit of the location around San Francisco. I'll have a complete day for that until the Conference starts, and also a little time after the Conference. That's another exciting aspect of my trip.

But the most exciting thing will be to meet all the guys that I had contact to via emails, blog comments etc. - and last but not least, the MySQL management and developers. That still looks a bit unreal to me at the moment, but not far away from now, it's about to actually happen ;-).

Have you ever needed your mobile phone to connect to the Internet?

During a life with computers, you sometimes see very strange things - such as this:

I have a little server at home that connects to the Internet via ISDN, and all other PCs in the network get their Internet connection through this server, which was running SuSE Linux 9.0. Everything worked absolutely fine.

Last weekend, I re-installed this server and updated to SuSE Linux 10.0. I set up the hardware settings in the same way as with SuSE 9.0 and generally, it worked again - but not always. Sometimes, I couldn't connect to the Internet anymore (while sometimes, it still works), but I found out a funny solution how to work around this problem.

I have also set up a fax server with capisuite. So if I call the fax (e. g. with my mobile phone), I can connect again right afterwards. I don't know why, but it works.

Of course, this solution wouldn't be very satisfying for the long term. But xDSL is already ordered and so, one of the next days I should be able to through ISDN over board to get a 32 x faster Internet connection without any transfer limits :-). I'm pretty excited about this!

This will also give me a chance to do things that I didn't need to think about so far. For example, there are many great live web seminars from MySQL. ISDN would be too slow to watch them, but this will soon be no problem anymore. And there are many other great things that I can start to do (like downloading and testing different Linux distributions), that have been impossible or at least extremely slow.

Triggering emails

Today I received an interesting request from one of the db4free.net users: is it possible to trigger emails on particular events? So for example, if a record that fulfills certain conditions is being inserted, somebody should automatically receive an email.

As far as I know, it's not possible in MySQL, but in other RDBMS. So wouldn't this be a nice feature for e.g. MySQL 5.2?

The only solution that comes to my mind is rather tricky and not 100 % accurate (since it doesn't send the email immediately after change in the database happened). Somebody could write a little application that verifies the condition and if it does, it sends the email - so this app could be defined as a cronjob to be run at a regular basis. Not very elegant, indeed.

Are there better solutions for this?

What's the first thing that I see when I get up?

The answer is ... my PHP & MySQL Certificates.

     

The PHP Certificate has been hanging right over my bed for a bit longer, while my MySQL Professional Certificate had its place in my document case. But a place where I hardly see it cannot be a good place, so from now on, it has an honourable place on the wall, too ;-).

Five reasons

Here's one more that I stumbled over:

Five reasons why you should never use PostgreSQL -- ever

As the title suggests, it's mainly about PostgreSQL. However, a lot of it applies to MySQL, as well.

JDBC Metadata, MySQL, and Oracle Recipes

I just stumbled over a new book from Apress:

JDBC Metadata, MySQL, and Oracle Recipes: A Problem-Solution Approach

Could be interesting for some folks ;-).

Still no access to the original view definition

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/03/still-no-access-to-the-original-view-definition/.

How to calculate the sizes of your databases

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/03/how-to-calculate-the-sizes-of-your-databases/.

A little look ahead to MySQL 5.2

This topic seems to come quite early - MySQL 5.0 (as GA release) is not even half a year old and MySQL 5.1 has just turned into beta.

But I've heard that MySQL 5.1 will be quite a quick release, so MySQL 5.2 is maybe not that far away.

So it could be a good moment to start to talk about the features that could be in MySQL 5.2. Some plans have already been published:

http://dev.mysql.com/doc/refman/5.1/en/mysql-5-2-plans.html
http://blog.arabx.com.au/?p=111

(if you know more sources, please add them as comments ;-))

Maybe, someone could create a new forum where people can discuss what they would like to see inside MySQL 5.2. I'm sure, many people inside the MySQL Community (me included) have their favourite features.

My current MySQL UC plan (still subject to change)

I had a tough time to choose from all those phantastic tutorials and sessions at the MySQL User Conference. Now I have a list of my top favourites, which I'd like to show you.

However, this is of course a very personal choice with very personal decisions, so I wouldn't like to call this recommandation or anything like this. Everybody is different, has different topics of interest and different expertises - so it's quite obvious that everybody would make different choices.

You can see how tough the choice was if you look at the "other favourites". It makes my heart bleed that so many interesting sessions (and of course, the tutorials, too) take place at the same time and it's not possible to watch all of them.

So here it is:

2006-04-24 08:30:00 - 2006-04-24 12:00:00

Secure Your PHP and MySQL Web Applications
(Laura Thomson, Senior Web Developer, OmniTI)

2006-04-24 13:30:00 - 2006-04-24 17:00:00

New Features of MySQL 5.0 and 5.1 In-depth (Partitioning, Updateable Views, Triggers, Stored Procedures)
(Jan Kneschke, Senior Developer, MySQLNetwork)

2006-04-25 00:00:00 - 2006-04-25 00:00:00

GPL v3 Jam Session


2006-04-25 08:20:00 - 2006-04-25 08:30:00

Welcome!
(Arjen Lentz, MySQL AB)

2006-04-25 08:30:00 - 2006-04-25 09:15:00

State of the Dolphin
(Kaj Arnö, MySQL AB, David Axmark, MySQL AB, Michael (Monty) Widenius, MySQL AB)

2006-04-25 09:15:00 - 2006-04-25 10:00:00

Bootstrapping: Starting an Open Source Business With Almost No Money!
(Greg Gianforte, RightNow Technologies)

2006-04-25 11:00:00 - 2006-04-25 11:45:00

The 5.1 Release
(Brian Aker, MySQLAB)

Other favourites:

The Java Advantage in MySQL
(Philip Antoniades, MySQL AB)

Creating .Net Applications with Linux and MySQL
(Matt Lord, MySQL AB)

2006-04-25 11:50:00 - 2006-04-25 12:35:00

A Look at New and Cool Things in the World of PHP
(Rasmus Lerdorf, PHP)

Other favourites:

Migrating a Large System to MySQL
(Ananth Reddy, MyPoints.com)

MySQL and Windows: Playing Well Together
(Reggie Burnett, MySQL AB)

2006-04-25 14:00:00 - 2006-04-25 14:45:00

MySQL In-Depth for DBAs
(Robin Schumacher, MySQL AB)

2006-04-25 14:50:00 - 2006-04-25 15:35:00

Performance Tuning Best Practices
(Jay Pipes)

Other favourites:

A Day in a MySQL Windows DBA
(Gerardo Narvaja, MySQL)

2006-04-25 16:30:00 - 2006-04-25 17:15:00

PHP Framework and the Eclipse PHP IDE
(Andi Gutmans, Zend Technologies, Inc.)

Other favourites:

Embedding MySQL
(Jim Winstead, MySQL, Inc.)

Panel: Scale Out
(Brian Aker, MySQLAB, Dorion Carroll, Technorati, Jeremy Cole, Yahoo! Inc., Brad Fitzpatrick, LiveJournal.com)

MySQL 5 Applied: The Sakila Sample Database
(Mike Hillyer, MySQL AB)

MySQL Partitioning
(Mikael Ronström, MySQL AB)

Advanced User-Defined Functions in MySQL 5
(John David Duncan, MySQL)

2006-04-25 17:20:00 - 2006-04-25 18:05:00

Higher Order MySQL
(Giuseppe Maxia, Stardata.it)

Other favourites:

MySQL GUI Tools for the Enterprise
(Michael G. Zinner, MySQL AB)

What *Not* to Include in Your DB Schema, and Why
(Danny O'Brien, Need To Know)

MySQL Performance: 5.0 vs 4.1
(Peter Zaitsev, MySQL, Inc.)

Synchronizing MySQL-based Applications with Mobile Devices
(Fabrizio Capobianco, Funambol)

2006-04-25 18:00:00 - 2006-04-25 19:30:00

Exhibitor Reception


2006-04-25 20:00:00 - 2006-04-25 21:30:00

HackFest B: Creating New SHOW Commands
(Brian Aker, MySQLAB)

Other favourites:

HackFest A: Creating New UDFs
(Hartmut Holzgraefe, MySQL AB, Jeremy Cole, Yahoo! Inc.)

2006-04-26 08:30:00 - 2006-04-26 09:15:00

State of MySQL AB
(Mårten Mickos, MySQL AB)

2006-04-26 09:15:00 - 2006-04-26 10:00:00

O'Reilly Radar
(Tim O'Reilly, O'Reilly Media, Inc.)

2006-04-26 10:00:00 - 2006-04-26 10:30:00

Open Source & Linux at HP: Community Development, Customer Success
(Christine Martino, Hewlett-Packard)

2006-04-26 11:00:00 - 2006-04-26 11:45:00

Use Cases for Replication and Cluster in MySQL
(Brian Aker, MySQLAB)

Other favourites:

What Do You Mean There's No Backup?
(Michael Kruckenberg, OpenAir, Jay Pipes)

MySQL Network: MySQL Expertise, Across the Enterprise
(Andy Bang, MySQL, Rob Young, MySQL Network)

Speeding-up Queries: New Features of the MySQL 5.0 Query Engine
(Timour Katchaounov, MySQL)

Open Source Business Intelligence
(Barry Klawans, JasperSoft)

2006-04-26 11:50:00 - 2006-04-26 12:35:00

Wikipedia: The Cheap and Explosive Scaling with LAMP
(Domas Mituzas, MySQL and Wikimedia Foundation, Brion Vibber, Wikipedia)

Other favourites:

Security by Example (or, "Stupidity and the Real World")
(Kristian Köhntopp, MySQL AB)

Speeding-up Queries: New Features of the MySQL 5.0 Query Engine (Cont'd.)
(Timour Katchaounov, MySQL)

2006-04-26 14:00:00 - 2006-04-26 14:45:00

MaxDB: The Professional Database
(Ashwath Kakhandiki, Roland Mallmann, SAP)

Other favourites:

High Performance Interconnect Solution for MySQL Cluster
(Einar Rustad)

2006-04-26 14:50:00 - 2006-04-26 15:35:00

MySQL Migration Toolkit
(John Bradford, MySQL, Michael G. Zinner, MySQL AB)

Other favourites:

MaxDB: Easy Admin & Cool Features
(Roland Mallmann, SAP, Ashwath Kakhandiki)

MySQL Partitioning, Part 2 (Advanced)
(Philip Antoniades, MySQL AB)

Connector/ODBC 5.0 Features and Enhancements
(Mark D. Matthews, MySQL AB)

2006-04-26 16:30:00 - 2006-04-26 17:15:00

Web Performance and Scalability with MySQL
(Ask Bjørn Hansen, Develooper.com)

Other favourites:

InnoDB: Features and Latest Enhancements
(Speaker: To Be Determined)

FeedBurner: Scalable Web Applications Using MySQL and Java
(Joe Kottke, FeedBurner)

2006-04-26 17:20:00 - 2006-04-26 18:05:00

InnoDB: Designing and Configuring for Best Performance
(Speaker: To Be Determined)

Other favourites:

Tuning MySQL5 SQL and Stored Procedures
(Guy Harrison, Quest Software)

Using Embedded MySQL as a High Performance Alternative to Traditional Filesystems
(George Schlossnagle, OmniTI Computer Consulting, Inc.)

2006-04-26 20:00:00 - 2006-04-26 21:30:00

MySQL Quiz Show


2006-04-27 08:30:00 - 2006-04-27 09:15:00

The Ubuntu Project: Improving Collaboration in the Free Software World
(Mark Shuttleworth, Ubuntu / Canonical Ltd)

2006-04-27 09:15:00 - 2006-04-27 10:30:00

Wikipedia, Open Source, and Knowledge Communities
(Mitchell Kapor, Open Source Applications Foundation (OSAF))

2006-04-27 10:30:00 - 2006-04-27 11:15:00

Plugging Your Applications into the MySQL Storage Engine Layer
(Brian Aker, MySQLAB)

2006-04-27 11:20:00 - 2006-04-27 12:05:00

Confession Time: Deadly Sins Using MySQL and PHP
(Arjen Lentz, MySQL AB)

Other favourites:

Dynamic SQL in Stored Procedures
(Konstantin Osipov, MySQL AB)

MySQL GIS: Overview and Tools
(Jeremy Cole, Yahoo! Inc.)

2006-04-27 13:30:00 - 2006-04-27 14:15:00

Extending MySQL Made Easy: Plugin API (FULLTEXT parsers, Storage Engines, and More)
(Sergei Golubchik, MySQL AB)

Other favourites:

JJSF, AJAX, MySQL, and Component-based Web Development
(Fima Katz, Exadel, Inc.)

MySQL Performance Landscape
(Peter Zaitsev, MySQL, Inc.)

Demands On a Storage Engine for Planning and Budgeting
(Juergen Faisst, Thinking Networks AG)

2006-04-27 14:20:00 - 2006-04-27 15:05:00

Ruby on Rails
(Speaker: To Be Determined)

Other favourites:

Growing Your Business; Strategizing for the Future
(Laura Merling, SDForum)

From Green Screen to Web Screen
(Hans Zaunere, New York PHP, Chris Hendry)

2006-04-27 15:30:00 - 2006-04-27 16:15:00

MySQL Movie Magic
(Milton Ngan, Weta Digital Ltd)

How to get a MySQL copy from the development source?

You might know that you can install a MySQL copy from the development source tree and that it's perfectly documented in the MySQL manual.

However, many people might not have tried it for themselves, maybe assuming that it's very complicated. But I can tell you, it's easier, as it looks - so maybe this little article can encourage you to give it a try ;-).

First you need a Linux PC (maybe the new gratis VMWare can help you to install Linux without losing your current installation). I have (quite an old) test PC with a Pentium processor (x86 architecture) with SuSE Linux 10.

First I created a directory for the MySQL 5.1 development source installation. I used /MySQL51 - you can put it anywhere you like, of course.

I also assume that you try it on a PC where security isn't such a big issue. So we do all our work with user 'root' - although you should normally secure your MySQL installation as it's described here.

So you start with:

mkdir /MySQL51
cd /MySQL51
sh bk-client.shar
cd bk_client-1.1
make all
PATH=$PWD:$PATH
cd ..

So you have installed your BitKeeper client and included the BitKeeper directory into your path variable. You should know be in your /MySQL51 directory again.

Here you issue the command to download the latest MySQL 5.1 development version:

sfioball -r+ bk://mysql.bkbits.net/mysql-5.1-new mysql-5.1

Dependent on your bandwith, this can take quite an amount of time.

If the download is finished, take a look into the BUILD directoy - here you find some pre-defined compilation scripts. I normally use the compile-pentium-max script (of course you have to take the one that suits to your architecture), but you can also use a debug script to be able to see the debug output when you do your testings (chasing bugs and stuff ;-)).

So (from your /MySQL51 directory) execute the following commands:

cd mysql51
BUILD/compile-pentium-max (or BUILD/compile-pentium-debug-max)

This again can take quite a while, depending on the speed of your system.

Then you can configure your new MySQL server, as described in the Unix Post Installation chapter in the manual.

To upgrade the development source installation, change to the /MySQL51/bk_client-1.1 directory and add again the path to your PATH variable (unless the path variable isn't still in there):

PATH=$PWD:$PATH

Then change to /MySQL/mysql-5.1 and download the changes:

update bk://mysql.bkbits.net/mysql-5.1-new

At the end, run the compile-pentium-max script (or whatever script you used) to re-compile the upgraded installation.

Another call for help on a bug report

A few months ago, I already asked for help to make a bug reproducable and it worked great, so I'd like to try it again, to find out, why a bug occurs on some occasions, but not on others.

It's about Bug report 17204. It is about stored procedures that execute successfully when they are executed for the first time, but executing them again leads to server crashes. This happened on both MySQL 5.0.18 and 5.1.7 in both Windows and Linux.

If you have any hints, please add your comments to the bug report.

Smart moves by MySQL AB

Recently, Oracle stole a little the show on the database market by acquiring Innobase and Sleepycat, who are producers of two storage engines (InnoDB and BerkeleyDB) that are in use by MySQL.

Now there seems to be a clear response. MySQL has hired former Firebird developer Jim Starkey and MySQL has a new CTO: Taneli Otala - read Kaj Arnö's interview with Taneli.

The final statement on this interview declares a clear goal: "Definitely. I want to be part of the team building the world's best database!". The times when MySQL targeted itself mainly to drive small and medium web based applications are definitely over!

The fact that MySQL is hireing new developers is just another indication that underlines that MySQL is moving forward towards the "big competitors".

By the way - have you figured out the new features of MySQL 5.1?

There's also a new page at db4free.net that gives a little introduction to the new features of MySQL 5.1 with the intention, to encourage db4free.net's users to test the new features and help to get MySQL 5.1 free of bugs.

The PROCEDURE ANALYSE option

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/03/the-procedure-analyse-option/.

db4free.net Crash report

Last night happened what I've always been afraid of. It started when I tried to update PostgreSQL to the latest version, something that I'm not very familiar with (so to be honest - it was completely my own fault). I also have to admit that I'm not a Linux guru - I like Linux and I can work with it quite comfortably, but when it comes to problems that I didn't expect, I sometimes end up trying and messing things up instead of really keeping everything under control.

First I tried to update the server with the .tar.gz file and followed the instructions from the PostgreSQL documentation. The installation process seemed to work fine, but when I thought, the installation was done, I didn't manage to start the server with the new version.

Next I downloaded the .rpm file from the SuSE 10.1 development repository. That's where the real problems started to arise - there were dependencies over and over and I tried to solve them one by one until I found out, that more things were broken than repaired. Finally I gave up upgrading PostgreSQL - MySQL still worked and was completely unaffected by my actions. But the disaster was finished when I realized that I couldn't connect via SSH anymore.

So I had no other choice than to start the re-installation from the customer interface of my server provider. It took several hours until the installation was finished with a completely fresh server (unfortunately, still a SuSE 9.0). Nevertheless, the rest was easy - MySQL was quickly set up again (the 5.0 server through an .rpm binary, the 5.1 server through the .tar.gz file). I was clever enough to backup the user's data shortly before the server crashed, so I could easily recover both MySQL servers with hardly any data loss (how great would it be if I had a second server with a replication system ;-)). So in the end, not much damage was done, except a few hours of downtime and for the moment there's no more PostgreSQL server.

My server provider informed me that very soon (probably next week) they will provide a new SuSE 10.0 image. So probably, I'll soon make an update, but in contrast to the situation last night, I'll be prepared well and everything should run quite quickly and smoothly. And maybe, I'll also get the PostgreSQL server up running again with at least an 8.0 version.

That's really tough!

I recently registered myself for the MySQL User Conference from 24th to 27th April in Santa Clara, California.

Now I try to make a plan which sessions to attend. That's an extremely tough task! There are so many interesting sessions, held my many people who I'm really excited to meet - so it's really hard to decide which ones to drop.

I really need a few clones of myself!

Free VMWare server to expire in July?

Maybe it's not a typical article for a MySQL blog, but since VMWare might be an interesting product for people who do testings on different platforms, I feel that it's not completely wrong here.

Today I've installed the new free (like beer) VMWare server for Linux. Installation worked fine, but as I looked into the "About VMWare" dialog, I found a line that the license expires 13th July 2006.

Is there a time limitation in the free VMWare server?

To separate or to not separate

First I have to correct myself. In my last blog article I wrote about a bug (17564) that MySQL remains database privileges after a database has been renamed. This behaviour is described in the manual, so it's no bug.

However, this raises an interesting question. Should data definition statements also affect the privilege settings? If you for example drop a database or a table, the privileges still remain for this object. Or if you rename a database or a table, the privileges will not be set to the new name. Should the definition statements and the privilege settings be separated, or handled together?

I would be interested, what other people think about this, what SQL standard suggests and how other RDBMS handle this. Is this the default behaviour among the RDBMS, or is it a Gotcha?

Could it be a good idea to add an option to the data definition statements, something like (just an idea) PRESERVE PRIVILEGES or MODIFY PRIVILEGES?

I would really appreciate your thoughts.

Take care with RENAME DATABASE

MySQL 5.1.7 introduces a new RENAME DATABASE command. Today, I've updated my BitKeeper MySQL 5.1 version, so I had the opportunity to already test this command. I found two bugs, which will definitely be in 5.1.7, as the development tree has already switched to 5.1.8:

Bug 17564: the RENAME DATABASE command doesn't change the privileges, so privileges might be lost, when the database name is changed.

Bug 17565: all events that were stored in the database will be lost after the database is renamed.

Outdated versions on dev.mysql.com

I'm a guy who very often watches if there are new versions of MySQL related programs (the server, the client programs and the APIs). There are good ways to quickly receive notifications if new versions come out, the best might be the Announcement Mailing List.

The dev.mysql.com page also lists the most current versions of the relevant products - or lets better say - it should list the most current versions. Unfortunately, it often takes quite a long time until this list is updated, when a new version comes out - for example:

MySQL MigrationToolkit is listed as 1.0.22 beta, although version 1.0.23 RC is out (also 1.0.22 had RC in its file name).

MySQL Administrator is listed as version 1.1.7, but the current Windows version is 1.1.8, while the current Linux version is 1.1.6.

MySQL QueryBrowser is listed as version 1.1.18, which is right for the Linux version. However, for Windows, there's already a 1.1.20.

MySQL Connector/J is listed as 5.0.0-alpha on the dev.mysql.com page, while the file name indicates that it's a beta version.

MySQL Connector/MXJ is listed as 1.0.10 (GA), although the higher version 1.1.6 would also be a GA version, which I think might have a higher kind of priority than 1.0.10.

From my point of view, these are little blemishes, but for someone who's new to MySQL, this could easily be irritating.

New mysql_upgrade program

I just found two interesting entries in the Changelogs for MySQL 5.0.19 and 5.1.7:

Added the mysql_upgrade program that checks all tables for incompatibilities with the current version of MySQL Server and repairs them if necessary. This program should be run for each MySQL upgrade (rather than mysql_fix_privilege_tables).

Added the FOR UPGRADE option for the CHECK TABLE statement. This option checks whether tables are incompatible with the current version of MySQL Server. Also added the --check-upgrade to mysqlcheck that invokes CHECK TABLE with the FOR UPGRADE option.

This seems to be a very useful program, as the table versions change from time to time and it's not always easy for users to keep track of these table versions and to ensure that they are up-to-date (unless you always dump and restore all tables on each upgrade).

I don't know the details about this new program (actually, I don't know more than what the Changelog says), but I guess that with the new program and the new option for the CHECK TABLE command, you can automatically upgrade the table's versions and also ensure that the tables in the mysql schema are correct.

Running this program will probably also reduce the danger of being affected from some bugs, as there might be bugs that only occur when the table versions are outdated.

What has happened to the Open Source Database Consortium?

These days, everybody's talking about Oracle's attempts to buy MySQL and their previous purchases of Innobase and Sleepycat.

As I already mentioned earlier, I hope that Oracle will never gain influence on how MySQL evolves. MySQL is on its way to become a high end database system and so might - at least in some business areas - be a serious competitor of Oracle. What really turns my stomach upside down is the thought that MySQL could (in some people's minds, hopefully never in reality) become a lower-end alternative of something. MySQL should make it's way independently and never let any proprietary competitor gain any influence in any way.

However, what I would appreciate very much would be more co-operation among the Open Source database producers. A while ago, people from the major Open Source database systems have met to form the Open Source Database Consortium - that was in October 2005.

OK, that's not that long ago, but I hope that the ambitions to co-operate aren't over again. It was told that a website will be created at www.osdbconsortium.org. There's nothing to see except a "Just a web page" note. I haven't heard any news about this since October - I believe, it would be nice if MySQL, PostgreSQL, Firebird etc. could do some things together. That would certainly be more welcoming than any deal with a proprietary database vendor.

InnoDB and Berkeley DB under Oracle's control?

As a "little developer", I don't really know what's exactly going on behind the scenes - business stuff is not so my big thing.

However, I feel that it would be good for MySQL to have its own full featured storage engine(s) (with foreign key constraints, ACID transaction and all that stuff) under its control. Maybe there are no risks for the short term (I don't know), but it would still be better for MySQL's reputation to be completely independent from any competitor and from anybody else.

Insecure users will more likely migrate to another database systems than those who know what the future will bring. And people who now use other database systems will more likely not migrate to MySQL, if they can't anticipate how data will be stored in the future. So I would see an own and independent full featured storage engines as one of the very highest priorities for MySQL's future - not only from the technical point of view, but also for MySQL's reputation.

My first EVENT and table logging tests

I've been very excited to test the new EVENT and table logging features, so I downloaded and installed MySQL 5.1 from the development source tree to get the very latest development version - 5.1.7-beta (no alpha anymore!).

The first thing you have to do to test the features is to enable event handling and logging (I also enabled the slow-query-log) in the my.cnf file - like this:

[mysqld]
log
log-slow-queries
event-scheduler=1

First I needed a table - I created quite a simple one:

DROP TABLE IF EXISTS `test`.`tt`;
CREATE TABLE  `test`.`tt` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `d` int(10) unsigned NOT NULL,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP 
    on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The event should insert a random value into column d every 5 seconds. With Trudy Pelzer's documentation it was quite easy to do that:

delimiter //

DROP EVENT IF EXISTS insertIntoTt //

CREATE EVENT insertIntoTt
ON SCHEDULE EVERY 5 SECOND
STARTS current_timestamp + INTERVAL 5 SECOND
ON COMPLETION PRESERVE
DISABLE
DO
BEGIN
  INSERT INTO test.tt (d) VALUES (floor(rand() * 20));
END //

delimiter ;

There's just one mistake that I found: the documentation names the options to enable and disable the event as ENABLED and DISABLED, wheras I found out that it works with ENABLE and DISABLE.

So lets start the event:

E:\mysql_dump>mysql -h 10.2.4.1 -P 3307
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 419 to server version: 5.1.7-beta-log

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

mysql> use test
Database changed
mysql> ALTER EVENT insertIntoTt ENABLE;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT current_time(), count(*) FROM tt;
+----------------+----------+
| current_time() | count(*) |
+----------------+----------+
| 19:26:14       |       14 |
+----------------+----------+
1 row in set (0.00 sec)

mysql> SELECT current_time(), count(*) FROM tt;
+----------------+----------+
| current_time() | count(*) |
+----------------+----------+
| 19:26:22       |       16 |
+----------------+----------+
1 row in set (0.00 sec)

mysql> SELECT current_time(), count(*) FROM tt;
+----------------+----------+
| current_time() | count(*) |
+----------------+----------+
| 19:26:30       |       17 |
+----------------+----------+
1 row in set (0.02 sec)

Currently there's a bug in the Event Scheduler which prevents commands from inside an event from being logged in the general log. However, the mysql.general_log table shows the log data like we were used to from the general_log stored in the file system:

mysql> SELECT * FROM mysql.general_log
    -> ORDER BY event_time
    -> LIMIT 10\G
*************************** 1. row ***************************
  event_time: 2006-02-13 18:52:42
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 114
   server_id: 0
command_type: Quit
    argument:
*************************** 2. row ***************************
  event_time: 2006-02-13 18:53:19
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 115
   server_id: 0
command_type: Connect
    argument: mpopp@10.2.1.1 on
*************************** 3. row ***************************
  event_time: 2006-02-13 18:53:23
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 115
   server_id: 0
command_type: Query
    argument: SELECT DATABASE()
*************************** 4. row ***************************
  event_time: 2006-02-13 18:53:23
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 115
   server_id: 0
command_type: Init DB
    argument: test
*************************** 5. row ***************************
  event_time: 2006-02-13 18:53:27
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 115
   server_id: 0
command_type: Query
    argument: DROP EVENT IF EXISTS insertIntoTt
*************************** 6. row ***************************
  event_time: 2006-02-13 18:53:27
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 115
   server_id: 0
command_type: Query
    argument: CREATE EVENT insertIntoTt
ON SCHEDULE EVERY 5 SECOND
STARTS current_timestamp + INTERVAL 5 SECOND
ON COMPLETION PRESERVE
DISABLE
DO
BEGIN
  INSERT INTO test.tt (d) VALUES (floor(rand() * 20));
END
*************************** 7. row ***************************
  event_time: 2006-02-13 18:54:01
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 116
   server_id: 0
command_type: Connect
    argument: mpopp@10.2.1.1 on test
*************************** 8. row ***************************
  event_time: 2006-02-13 18:54:01
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 116
   server_id: 0
command_type: Query
    argument: SET SESSION interactive_timeout=1000000
*************************** 9. row ***************************
  event_time: 2006-02-13 18:54:01
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 116
   server_id: 0
command_type: Query
    argument: SELECT @@sql_mode
*************************** 10. row ***************************
  event_time: 2006-02-13 18:54:01
   user_host: mpopp[mpopp] @  [10.2.1.1]
   thread_id: 116
   server_id: 0
command_type: Query
    argument: SET SESSION sql_mode=''
10 rows in set (0.00 sec)

Nice stuff, indeed :-).

Is there a bug?

I've just downloaded and installed the new Sakila Sample Database.

Then I wanted to create a dump file to transfer it to my MySQL 5.1 installation on the same machine. Here's what I did:

E:\mysql_dump>mysqldump --triggers=false sakila > sakila.sql
Enter password: *******

E:\mysql_dump>mysql -P 3307 sakila < sakila.sql
Enter password: *******
ERROR 1005 (HY000) at line 392: Can't create table 
  'sakila.payment' (errno: 150)

E:\mysql_dump>perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Here are my default settings for mysqldump:

all                               TRUE
all-databases                     FALSE
add-drop-database                 FALSE
add-drop-table                    TRUE
add-locks                         TRUE
allow-keywords                    FALSE
character-sets-dir                (No default value)
comments                          TRUE
compatible                        (No default value)
compact                           FALSE
complete-insert                   FALSE
compress                          FALSE
create-options                    TRUE
databases                         FALSE
default-character-set             utf8
delayed-insert                    FALSE
delete-master-logs                FALSE
disable-keys                      TRUE
extended-insert                   TRUE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
first-slave                       FALSE
flush-logs                        FALSE
force                             FALSE
hex-blob                          FALSE
host                              (No default value)
insert-ignore                     FALSE
lines-terminated-by               (No default value)
lock-all-tables                   FALSE
lock-tables                       TRUE
master-data                       0
max_allowed_packet                25165824
net_buffer_length                 1047551
no-autocommit                     FALSE
no-create-db                      FALSE
no-create-info                    FALSE
no-data                           FALSE
order-by-primary                  FALSE
port                              3306
quick                             TRUE
quote-names                       TRUE
routines                          FALSE
set-charset                       TRUE
shared-memory-base-name           (No default value)
single-transaction                FALSE
socket                            (No default value)
tab                               (No default value)
triggers                          TRUE
tz-utc                            TRUE
user                              mpopp
verbose                           FALSE
where                             (No default value)

Line 392, where the error occurs, starts the CREATE TABLE statement for the payment table:

CREATE TABLE `payment` (
  `payment_id` smallint(5) unsigned NOT NULL auto_increment,
  `customer_id` smallint(5) unsigned NOT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `rental_id` int(11) default NULL,
  `amount` decimal(5,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP 
    on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`payment_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `fk_payment_rental` (`rental_id`),
  CONSTRAINT `fk_payment_customer` 
    FOREIGN KEY (`customer_id`) 
    REFERENCES `customer` (`customer_id`) 
    ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_rental` 
    FOREIGN KEY (`rental_id`) 
    REFERENCES `rental` (`rental_id`) 
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_staff` 
    FOREIGN KEY (`staff_id`) 
    REFERENCES `staff` (`staff_id`) 
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This looks perfectly right to me - also the foreign key creations. FOREIGN_KEY_CHECKS is set to 0, so it shouldn't be a problem that some of the referenced tables don't exist, yet.

The complete dump file can be downloaded from here.

Anybody have a clue?

"Entirely uninterested in fancy features like stored procedures, triggers, and the like"

Here's my response to David Heinemeier Hansson's statement about being "entirely uninterested in fancy features like stored procedures, triggers, and the like" and Frank Mash's article about this issue.

MySQL simply *needs* features like Stored Procedures, User Defined Functions, Triggers etc. to be taken serious on the database market! So long has MySQL been criticized for not providing them - now as they have been introduced, it would be stupid to criticize MySQL *for* providing them.

MySQL has a very good chance to get as advanced as any other database system without losing it's simplicity. I don't think that performence will suffer a lot in the long run, because performence optimizations will certainly go on forever and will certainly overcome the speed decreases that may come from implementing new features. On the other hand, the other database systems will hardly ever have a chance to become as convenient to work with as MySQL (of course, people will always like what they are used to - if somebody has always worked with another product, he'll be more comfortable with this than with another - but for new users, MySQL is probably less painful to start with than the others).

The great thing is - people can still work with MySQL 5.0 the way that they were used to with earlier versions. They can decide whether to use the new features, or not. And that's exactly the right way for MySQL! Continuing to get as advanced as possible without breaking downward compatibility - where possible (and useful).

However, it is very important that MySQL also makes migration from other database systems as simple as possible. MySQL plans e.g. to introduce new languages for SPs - this would be a good chance to introduce languages that are compatible to other database systems to make it easier to migrate from them to MySQL.

I certainly wouldn't like to go without the new features anymore and if MySQL wouldn't have chosen to introduce them, I would have had to consider very seriously to move completely to e.g. PostgreSQL. In fact, the introduction of these new features was even my prime motivation to get involved more deeply with MySQL and to create the db4free.net project. Probably, it wouldn't have happened if MySQL hadn't chosen to implement them.

MySQL is on the right track - and I hope (and I'm very confident) that MySQL continues to stay there. Also MySQL 5.1 makes further important steps towards the right direction :-).

Trigger privileges in MySQL 5.1.6

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/02/trigger-privileges-in-mysql-5-1-6/.

Logging into database tables available in MySQL 5.1.6

I just received an email notification stating that the "Logging into database table" feature as of my Feature Request 12304 has been pushed into MySQL 5.1.6.

If I got that right, there will be two new tables in the mysql schema - "slow_log" and "general_log". You will have the choice whether to store log information in the file system, like you were used to - or to store them inside these new tables.

Together with the new Event Scheduling Feature (which can perfectly play together with the new logging feature - as I already wrote about in an earlier article), MySQL 5.1.6 will provide very interesting new features - I'm very much looking forward to testing them.

Wanna know what's going on in the development trees?

There are perfect places to look at - watch here:

MySQL 5.1 (http://mysql.bkbits.net:8080/mysql-5.1-new)

10 Changesets in the last day
35 Changesets in the last two days
73 Changesets in the last three days
102 Changesets in the last four days
128 Changesets in the last week
309 Changesets in the last two weeks
447 Changesets in the last three weeks
612 Changesets in the last four weeks
992 Changesets in the last eight weeks
1505 Changesets in the last twelve weeks
3410 Changesets in the last six months
5331 Changesets in the last nine months
7492 Changesets in the last year
13858 Changesets in the last two years
17219 Changesets in the last three years

MySQL 5.0 (http://mysql.bkbits.net:8080/mysql-5.0)

8 Changesets in the last day
15 Changesets in the last two days
22 Changesets in the last three days
34 Changesets in the last four days
43 Changesets in the last week
92 Changesets in the last two weeks
139 Changesets in the last three weeks
219 Changesets in the last four weeks
353 Changesets in the last eight weeks
671 Changesets in the last twelve weeks
2415 Changesets in the last six months
4172 Changesets in the last nine months
6272 Changesets in the last year
12625 Changesets in the last two years
15986 Changesets in the last three years

MySQL 4.1 (http://mysql.bkbits.net:8080/mysql-4.1)

1 Changesets in the last two days
2 Changesets in the last three days
5 Changesets in the last four days
7 Changesets in the last week
16 Changesets in the last two weeks
31 Changesets in the last three weeks
56 Changesets in the last four weeks
87 Changesets in the last eight weeks
150 Changesets in the last twelve weeks
668 Changesets in the last six months
1222 Changesets in the last nine months
1970 Changesets in the last year
6531 Changesets in the last two years
9653 Changesets in the last three years

MySQL 4.0 (http://mysql.bkbits.net:8080/mysql-4.0)

1 Changesets in the last three weeks
3 Changesets in the last four weeks
6 Changesets in the last eight weeks
12 Changesets in the last twelve weeks
61 Changesets in the last six months
104 Changesets in the last nine months
195 Changesets in the last year
958 Changesets in the last two years
2302 Changesets in the last three years

Another interesting place to look at is always the Snapshot page.

More about my ToDo article

Thanks for all the great feedback on my "My personal ToDo list for MySQL" article. I hope that developers read this article and all its comments and try to solve these issues as soon as possible. There are some great new suggestions from Roland Bouman and Giuseppe Maxia which add perfectly to the bugs and feature requests that I wrote about.

There's another idea for a feature request (which I think is also already filed): DATETIME and TIMESTAMP could support milliseconds - that's also something that I found was criticized by people who migrated from other database systems.

Generally, people who try to migrate from other database systems to MySQL might be good suggestors for features or what could be done better - so it's always a good thing to listen to what they have to say.

Even though most of the usual criticisms have become outdated, there are still several points where things could still be done better - often watched from other database systems, or sometimes because features don't work the way that a newbie or somebody who migrates to MySQL would expect to.

But still the very most important thing is to keep MySQL free of major bugs - nothing can do more harm to the reputation of a database system than a bug that destroys data. The harm can hold on much longer (sometimes year) as until the bug is fixed - people and companies migrate away from this product and always consider it unreliable. They might publicly tell about their problems which might make other people sceptical. So they also might choose another product which they think is safer, what might not be true anymore at the time they here about a damage that happened maybe years ago.

Unfortunately, it won't be possible to prevent evil bugs all the times. Where humans work, mistakes happen.

But there's a lot that we - the Community - can do. We can make the developers aware of such bugs and what we think is not yet perfect. This gives them the chance to fix those issues to keep the danger as low as possible that such damage happens too often.

Updated QueryBrowser again

A while ago I complained about a range of very evil bugs inside the Windows version of MySQL QueryBrowser: http://db4free.blogspot.com/2006/01/whats-wrong-with-mysql-querybrowser.html

Now, version 1.1.20 has been released. After skipping (but still tested, at least for a short amount of time) five versions in a row (still used 1.1.14), I found out that the "killer bugs" that I knew about are now removed, so I gave 1.1.20 a try and updated MySQL QueryBrowser to its most current version again.

It's still too short to really say that this version is now free of heavy bugs, but it looks well so far and I'm confident that at least the times of skipping versions are over again.

MySQL products CD/DVD?

Ronald Bradford's article about Testing/Trialing new MySQL Releases (an excellent idea!) made me think of another idea which could help MySQL to promote its products.

There are still many people without fast speed access to the internet to download files with many MBs of size. And there might be many people who would be interested in testing MySQL, but they would still need a little impulse to really get into it.

So my idea would be to sell CDs, or even better DVDs with all the free products around MySQL - maybe at net costs. This could include (for all common platforms)

* the current GA releases of MySQL server
* the current development releases of MySQL
* the client tools like QueryBrowser, Administrator, MigrationToolkit, etc.
* all the API drivers like JDBC, .NET, PHP etc.
* the current MaxDB release
* free 3rd party tools (like phpMyAdmin, Apache, PHP, ...)
* the manuals
* tutorials
* training videos (speeches at Conferences, ...)
* newsletters
* maybe even a 'best of' from blog articles

and so on (to keep it short - everything around MySQL that's available for free).

This CD/DVD could be renewed for example every six months. Maybe it would even be possible to attach it to Computer Magazines or give free copies to big companies, where people could redistribute their copy and get some of their employees in tough with MySQL (which would not only be a deal for the IT departments, but also for other office clerks who still abuse Excel as a database - maybe special tutorials could be tailored especially for this target group ;-)). There would be a lot of great opportunities, I think!

Even I - although I'm one of the guys who keeps his MySQL products much more up-to-date than on a half-a-year basis - would seriously consider buying this CD/DVD to have everything packed up together nicely.