mysqldump improvements

Since the last few versions and especially since 5.0.23, the mysqldump command includes new and very important bug fixes.

I'd like to mention three of them that I was affected by:

Bug 16878 (fixed in 5.0.19, 5.1.8): Dump of triggers
Bug 17201 (fixed in 5.0.23, 5.1.12): mysqldump sometimes creates database twice
Bug 18462 (fixed in 5.0.23): mysqldump does not dump view structures correctly

New GUI Tool bundle available

You may have noticed the new link on dev.mysql.com to the MySQL GUI Tool Download.

This bundle includes new beta versions for MySQL Administrator, MySQL QueryBrowser, the MySQL MigrationToolkit and a new alpha version of MySQL Workbench. All these GUI products are supposed to be offered in one single package in the future.

To get all details, read Mike Zinner's Announcements in the forum:

http://forums.mysql.com/read.php?108,100559,100559#msg-100559
http://forums.mysql.com/read.php?108,100561,100561#msg-100561

Needless to say, Feedback and Bug Reports are very much appreciated ;-)!

Installed MySQL 5.2 today

You think I'm kidding? No way!

There is this web page (I blogged about it a few months ago) where you get an interface to BitKeeper to watch the development activities: http://mysql.bkbits.net:8080/mysql-5.1/index.html.

Just for fun I replaced 5.1 with 5.2 and - it worked. So I followed the instructions from the manual and only replaced 5.1 with 5.2 again. This way, I ended up with a MySQL 5.2.0-alpha installation:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.2.0-alpha

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

mysql>

I doubt that there are many (if any at all) differences to the current 5.1 development source - but it's nice to see that MySQL 5.2 is on the way.

And I'm really excited to hear about the new features that are planned for 5.2!

Welcome on board, Roland and all the best to Frank!

I have been knowing that Roland Bouman is about to join 
Carsten Pedersen's MySQL Certification team for quite a while, 
but now - as of 1st July - it's official and time to wish him all the best 
and much fun in his new job!

Also Frank Mash has recently moved to New York and started
a new job as MySQL DBA (I hope, I remember everything
correctly). Also my best wishes for him!

MySQL not only rocks as a database server, but also for great job
opportunities! And the best thing is - MySQL is currently hireing ...
so check out MySQL's job page - there might be the right job for you!

Sorting of numeric values mixed with alphanumeric values

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/06/sorting-of-numeric-values-mixed-with-alphanumeric-values/.

Driving to FrOSCon

I am ready to leave for the FrOSCon Conference, taking place on Saturday and Sunday in St. Augustin/Germany.

Here's the program that contains many MySQL related sessions.

I hope to meet many MySQL people there ;-).

Information_schema query taking more than 7 minutes

The biggest current problem that I know in the MySQL servers is the performence of information_schema. This is reported as bug 19588:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1829 to server version: 5.0.22-max-log

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

mysql> SELECT TABLE_SCHEMA,
    ->   sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
    -> FROM information_schema.TABLES
    -> GROUP BY TABLE_SCHEMA
    -> HAVING size_mb > 10
    -> ORDER BY size_mb DESC;

...

xxx rows in set (7 min 34.71 sec)

Even though this server hosts a lot of data - more than 7 minutes for this query is tough.

www.mysql.com higher PageRank than Google

Google offers a little Toolbar that provides additional information about the displayed website, including the PageRank value that indicates how "important" the website is in Google's eyes (and that's said to be used to calculate the relevance in Google searches).

Here are some values that I looked up:

www.mysql.com: 9/10
www.planetmysql.org: 8/10
www.google.com: 8/10
www.microsoft.com: 9/10
www.yahoo.com: 9/10
www.oracle.com: 9/10
www.postgresql.org: 8/10
www.amazon.com: no value
www.phpmyadmin.net: 8/10
www.php.net: 9/10
www.wikipedia.org: no value
www.novell.com: 8/10
www.redhat.com: 8/10
www.orf.at (Austria's national TV broadcast station): 7/10
www.db4free.net: 6/10
www.freesql.org: 5/10 (but currently no content)
www.freemysql.net: 4/10
www.mpopp.net: 4/10

There's only one website that I've found with a value of 10/10: www.apple.com.

However, it's great to see that the relevance of MySQL's website is among the highest of all websites of the world.

Knoppix 5.0.1 available

Today I have downloaded the brand new Knoppix 5.0.1 DVD and played a bit around with it.

It looks quite nice, although some of the packages are very up to date and others are quite old. MySQL comes with 5.0.21, so there's probably no distribution with a more recent MySQL version at the moment.

PostgreSQL is included with version 8.0.4, while the current version is 8.1.4 (and the latest version of the 8.0 tree is 8.0.8). Apache comes with versions 1.3.34 and 2.0.55, but only Apache 1.3.34 is configured to start with PHP and that only with PHP 4.4.2, which is the most disappointing aspect that I found.

Multiple SP call crash to be fixed in MySQL 5.0.23

There was this bug that I wrote about earlier which caused certain Stored Procedures to crash when they were executed more often than once.

I noticed in the bug report and in the change log that this bug will be fixed in MySQL 5.0.23.

Goodbye anger, hello fun

I just saw a Microsoft commercial video clip using the slogan

"Better and more simple Visual Basic - goodbye anger, hello fun"

What do they want to tell us? Does that mean, the former version of Visual Studio/Visual Basic caused anger? Do they worsen their old product to make advertising for the new one?

My next thought would be - how will they advertise when the next version comes out? Will they again say, that the now current release causes anger or something similar?

I don't think that's good advertising.

How I work

I think it was Brian Aker who got this "How I work" series started and it's a pleasure for me to join in and tell you something about how I work.

Actually, it's only half a month since I've been working for the web development team of MySQL, so some things might still be subject to change. But most things are very likely fixed, so here they are ...

My working PC is an Athlon AMD64 3200+ with 2 GBs RAM and two 250 GB hard drives. Currently it's running SuSE Linux 10.0, preferably with KDE and I'm using the ext3 file system. However, I consider switching over to Fedora not too far from now (maybe in early October, when Fedora Core 6 is released).

Formerly I worked most of the time with Windows, but delegated some server tasks (file server, print server, web server, database server, ...) to Linux - which always used to be SuSE, so I'm still most familiar with this distribution. I used to do a lot with YaST (SuSE's configuration tool), but since I started my job with MySQL and with it started to extensively use Linux, I'm doing much more on the command line and therefore become more independant of GUI tools. More about that later.

My email client is currently "Kontakt", one of KDE's standard email, contact and scheduling applications. I'm not yet sure if I hold on to this, since there are some issues that don't work like I'd like it to (however, I didn't spend much time with this application - so maybe it's because of me ;-)).

For development, I currently use kvim, but though I often used the vi editor to make modifications on files, I'm not very sure if I'll like it for more complicated development tasks. Maybe I'll look around if I find a good PHP plugin for Eclipse (which I preferably used for Java development so far - one of the best IDEs, I guess). If you can recommand something like this, please let me know!

My preferred web browser is Opera. The big advantage compared to Firefox is (in my opinion) that I don't need to install plugins to get everything I need. It's very comfortable to work with!

But of course, I also need different browsers, and some browsers require different operating systems - therefore I use VMWare Server. Unfortunately, I don't have a Mac available yet, but this might also change ;-).

As I already mentioned - I do a lot at the command line now. One of my favourites is

find -name '*' -exec -q [regexp] {} \; -print

which allows me to find all files in the current directory (including subdirectories) where a certain regex pattern occurs. This is extremely helpful mostly now at the beginning of my web developer job to find the code sections that I'm looking for.

Another useful thing I've learned recently is to use the tar compression command not only for decompressing (I actually used that for a while), but also for compressing files and whole directory structures.

And finally, I learned a lot about Subversion. Actually, I have used CVS (and for a short time also Subversion) before, but on quite a low level - so this is also an important improvement.

And of course - I'm learning more and more every day, which is one of the most pleasant aspects of my job.

My working hours are mostly in the evening and during the night, which provides several advantages. First of all, my colleagues live in different parts of the world, so it's easiest to catch them at these times and second, I'm a night person. I used to sleep in the morning (so, right now is an exception - it's currently 10:20 a.m., that's when I'm usually deeply asleep) and can do some other things during the afternoon (and do little job tasks in-between) - to dedicate myself to the job starting from the late afternoon or early evening, mostly until 3 or 4 o'clock in the morning. Another big advantage is that during the evening and night, it's very calm and there's no danger of being disturbed by anyone ;-).

Did I forget something important?

FrOSCon Conference in St. Augustin/Germany from 24th to 25th June

I'm looking forward to visiting the FrOSCon Conference in St. Augustin/Germany from 24th to 25th June and to meeting some fellow MySQL Community members and colleagues.

The MySQL related events are:

* MySQL Administration - Backup and Security Strategies on Linux by Lenz Grimmer

* MySQL Cluster: an introduction - A journey into High Availability by Geert Vanderkelen

* Pivot tables in MySQL 5 - creating cross tabulations with MySQL 5 stored routines by Giuseppe Maxia

* The MySQL Business Model - Where and How we Thrive by Lenz Grimmer

... and of course there are many more events that are related to MySQL indirectly (like PHP, Java, Typo3, ...).

Filling table with prime numbers

First of all many thanks to Dean Swift, Carsten Pedersen, Kai Voigt and Kristian Köhntopp for providing me with this example and allowing me to blog about it.

This origins from a stored procedure exercise that a group of students did which ended up in an optimization competition. It's about a table that should be filled with prime numbers - up to a pre-defined bound - by a stored procedure.

So here's the basic solution:

mysql> DELIMITER //
mysql> CREATE DATABASE sieve //
Query OK, 1 row affected (0.00 sec)

mysql> USE sieve //
Database changed
mysql> CREATE TABLE sieve (
     ->   id INT PRIMARY KEY
     -> ) //
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE PROCEDURE sieve (max INT)
     -> BEGIN
     ->   DECLARE l0 INT;
     ->   DECLARE l1 INT;
     ->   TRUNCATE sieve;
     ->   SET l0=2;
     ->   WHILE l0
     ->     INSERT INTO sieve (id) VALUES (l0);
     ->     SET l0=l0+1;
     ->   END WHILE;
     ->   SET l0=2;
     ->   WHILE l0
     ->     SET l1=l0*2; # delete from first multiple
     ->     WHILE l1
     ->       DELETE FROM sieve WHERE id=l1;
     ->       SET l1=l1+l0;
     ->     END WHILE;
     ->     SET l0=l0+1;
     ->   END WHILE;
     -> END //
Query OK, 0 rows affected (0.00 sec)

Here's some further information, if you want to play with optimizing this stored procedure:

Our colleague, Philippe Campos, suggested removing the inner loop and replacing it with a modulo operator. (DELETE FROM sieve WHERE (id%l0)=0 AND id>l0) This increased speed. He then suggested batch inserts. This made it much faster. A student suggested batch insert of odd numbers to the memory storage engine. The former is cunning and the latter opens much scope for optimization beyond the algorithm of the stored procedure.

By what ratio can you improve the basic implementation? Do indexes help or hinder?


So what's your best solution?

Enjoy!

From Oracle via MS SQL Server up to PostgreSQL or MySQL

This morning I browsed through a training course book (from one of the largest Austrian training providers) and found the description for a SQL course which I think sounds really nice. Translated to English, it says about this:

"You will learn to know dialect independant SQL, which can be used in almost all database systems without major changes - from Oracle via MS SQL Server up to PostgreSQL or MySQL."

I really like the way how they've set the priorities :-).

Started to use replication

It's been a long time that I've been using MySQL, but it has just happened now that I made use of replication in production.

What's the reason for this? Well, I have a working machine (currently with SuSE Linux 10.0) and a private machine (with Windows), both running the latest production release of MySQL 5.0. On my working machine, I've set up a Wiki. I used to make regular backups on my private machine and wanted to backup my Wiki database, too.

There are certainly serveral solutions for this, but the solution that I preferred was to replicate the Wiki database to my private machine to simply backup it together with my other databases there.

Here's how I did it (not very difficult - and not at all with the help of Jay's and Mike's Pro MySQL 5 book ;-)):

First I added the following lines to the my.cnf file of the master (which is the working machine):

log-bin
binlog-do-db=wikidb (wikidb is the name of the database)

and there should also be a line

server-id=1 (which might already be there). Then restart the MySQL server.

Then I accessed MySQL using my root user to add a slave_user:

CREATE USER slave_user@'%' IDENTIFIED BY 'xxxxxx';
GRANT REPLICATION SLAVE ON *.* TO slave_user@'%';

(xxxxxx is of course your password and you can limit the host information of the user more strictly than just %.)

Then flush the tables, apply a read lock and output the replication master information:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
            File: master-bin.000002
        Position: 98
    Binlog_Do_DB: wikidb,wikidb
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Note the position number (here 98), since you will need it to configure the slave.

Keep the lock and open another client to make a dump of the database (make sure you add --lock-tables=false, because otherwise mysqldump might try to apply another lock and you end up waiting forever):

mysqldump --databases wikidb --lock-tables=false > wikidb.sql

... and import the database to the slave:

mysql -h [slave host] < wikidb.sql

This makes sure that you have exactly the same data on your slave and nobody can modify any data on the master in the meantime.

Then change to your slave and enter the following lines to your my.cnf there:

server-id=2
master-host=[your master ip address]
master-user=slave_user
master-password=xxxxxx
master-port=3306

Then access your slave host and enter the following lines (according to the SHOW MASTER STATUS output on your master):

mysql> CHANGE MASTER TO
MASTER_HOST='[your master ip address]',
MASTER_USER='[slave_user]',
MASTER_PASSWORD='[your slave_user password]',
MASTER_LOG_FILE='master-bin.000002',
MASTER_LOG_POS=98;

START SLAVE;

... and that's it - now your replication system should be running. Try to modify data on your master server and the change should immediately be visable on your slave as well.

You can also try this:

on your master:

mysql> show master status\G
*************************** 1. row ***************************
            File: suse-bin.000005
        Position: 196
    Binlog_Do_DB: wikidb,wikidb
Binlog_Ignore_DB:
1 row in set (0.00 sec)

on your slave:

mysql> show slave status\G
*************************** 1. row ***************************
...
            Master_Log_File: suse-bin.000005
        Read_Master_Log_Pos: 196
...
1 row in set (0.00 sec)

File on master and Master_Log_File on slave as well as Position on master and Read_Master_Log_Pos on slave should always match.

You can find more information about replication as already mentioned in Jay Pipes' and Mike Kruckenberg's Pro MySQL 5 book, in the MySQL manual and if you want to do extremely fancy stuff, refer to Giuseppe Maxia's Advanced MySQL Replication Techniques, where you can learn how to set up a master/master-replication system with MySQL.

Check out this Podcast Episode

Pro MySQL 5 from Jay Pipes and Mike Kruckenberg is definitely one of the best advanced MySQL books around.

dbazine.com has published a Podcast Episode with an interview with Jay and Mike where they talk about themselves and - of course - MySQL:

http://www.dbazine.com/podcasts/podcast-kruckenberg

MySQL Upgrade Certification exams available

I just checked out my account at www.pearsonvue.com and found out that the MySQL Upgrade Certification exams from 4.x to 5.0 are available.

Guess, I'll have to start learning again.

Congratulation to the Finns and the Swedes

MySQL is a Finnish/Swedish founded company, so it's nice to congratulate both countries to this successful weekend!

On Saturday, Finland won the Eurovision Song Contest. That's fantastic - finally, Europe woke up and let a Hard Rock song ("Hard Rock Halleluja" from Lordi) win!

To explain the Eurovision Song Contest to Non-European readers, I'd like to quote Kaj Arnö: "that’s a yearly classic event going on since tens of years, which everybody watches but nobody admits watching.". That's usually true - but this years winning song literally ROCKS!

Today, Sweden won the Ice Hockey World Championships. It's the first time that the Olympic Champion wins the World Championships in the same year.

Happy celebrations!

A good example of what is not freedom

I love the spirit of freedom that comes with Open Source - that's one important thing that I like about MySQL. This is about an extreme example of the opposite.

I like the US band called Live (for those who don't know, that's really the name of the band - very disadvantageous if you try to google for that band) - they make great Rock music. I had the opportunity to see this band live in 1999 and I have several CDs from the band.

Recently they have issued a new CD called "Songs From Black Mountain" which comes in a normal CD edition and a DualDisc Edition, which is double sided - on one side a CD and on the other side a DVD with videos from the songs. I pre-listened the songs in iTunes, liked them and decided to buy the DualDisc from Amazon.

Fine - you probably might already guess what's the issue. The CD is copy protected and not only that - if I play the CD on my PC, there are several short interruptions in the songs and the sound quality is extremely bad. Also the DVD is copy protected, but at least it's possible to play the songs in good quality from the DVD. I didn't find any indication on Amazon's product page about the copy protection, which should actually enable me to return the disk.

Why does the copy protection bother me? Very simple - I used to make MP3 files out of my CDs to play them on my PC without the need to insert the disk, copy them to my iPod, create CDs for my car - all reasonable things that I think I should be allowed to do with a CD that I bought for good money.

I'm not really sure about what I will do with the disk. Should I return it? If I return it, should I buy the songs from iTunes, burn them on CD and rip the CD to get MP3 files out of it? That's another barrier, because files from iTunes are not compatible with many devices and applications. Should I return the disk and protest against all this stupidity by not buying the disk (although I like the music)? Should I keep the CD and try to crack the songs (and waste precious time that I could spend doing useful things like writing blog articles ;-))? Is it at all legal? Nobody's sure about that, because on one hand there's the right to create backup copies, on the other hand it's illegal to skip copy protection ... that's absolutely ambiguous. Will I end up in jail for just wanting to listen to a few songs? Can you really be sure that this is as ridiculous as it sounds?

It's extremely annoying that the music industry punishes honest customers - and makes people who would legally buy stuff (if it came without the limitations) get the songs from illegal sources. I don't mind to pay money for good music - that's just fair. I also think that there's nothing bad about sharing songs with friends (even though I don't think that's legal). But I think that most people would spend a reasonable amount of money for good music - and private sharing certainly helps to learn about new music and if those people who like the songs go out and buy the CD and visit concerts, it will be no harm to anybody (my personal opinion, obviously not the opinion of the music industry). How about those who share this material in a commercial and criminal way? They won't be stopped by copy protection anyway.

This all really looks to me like the maximum amount of possible stupidity!

Let's get one stop further ... can I be sued for using the word stupidity in the same context as "music industry" in a public article?

Will the click on the Submit button mean the end of my life in freedom?

Am I a criminal for having thoughts like that?

Is that Freedom?