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.