Sunday, May 28, 2006

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.

Saturday, May 27, 2006

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

Tuesday, May 23, 2006

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.

Sunday, May 21, 2006

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!

Friday, May 19, 2006

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?

How do you think about certain product name extensions?

As you probably know, the MySQL server comes in two variations - a Community version and MySQL Network. I thought about how names like "Community" and "Network" and other descriptive name extensions from other products feel to me.

I never felt that the Community version is a less complete product than MySQL Network. It feels - and that's basically what it is - that MySQL Network provides additional features - mainly support. But MySQL - Community Edition is nevertheless still a complete product.

How's that with other products? Lets take a few examples.

SuSE Linux also offered two variations up to version 9.3 - there was a Personal Edition and a Professional Edition. The Personal Edition was never an option for me. It always felt like an incomplete product and it really missed many applications that I need. So it feels like: Personal: incomplete, Professional: the "real" SuSE Linux.

Let's take Fedora/RedHat Enterprise Linux. Do you feel like Fedora is an incomplete product? I don't. The term "Enterprise" does not give me the sense that the Non-Enterprise equivalent (the Community product Fedora) is less complete and not something "real".

The most extreme example I can think of is Microsoft Windows. And it's getting worse with Windows Vista. There will be

* Windows Vista Starter
* Windows Vista Home Basic
* Windows Vista Home Premium
* Windows Vista Business
* Windows Vista Enterprise
* Windows Vista Ultimate

Needless to say that the price raises with each edition and the "higher level" editions will probably be unaffordable to most private people.

I hate distinctions like that! I don't bother about Business/Enterprise/Ultimate, although I don't think that it makes sense to separate Enterprise targeted editions into three pieces. But how about Starter/Home Basic/Home Premium? That feels to me like "X for idiots" (Starter), "X for poor people" (Home Basic) and "X for wealthy people" (Home Premium). It's like making judgements about the customer. I looks like a bad marketing strategy to me (regardless of Microsoft's business success).

People usually associate product edition names with something and I personally think that some of them create bad feelings about the product (or even imply an insult of the customer) and some are neutral or positively associated.

What are your feelings about such extensions?

How db4free.net helps to improve MySQL

Today I had a perfect example to show you how db4free.net helps to find bugs - which are of course reported and consequently fixed. And sometimes, like this one, the bugs even result from a (although in this case not serious) bug in db4free.

A few days ago, a db4free.net user contacted me and told me that he could not connect to his database. He only saw information_schema and on the MySQL 5.1 server, he could see the show_log database, in which I provide access to a view where every user can see his individual slow queries (thanks to the new table logging feature).

I looked up his user and found out that he has entered an empty string for the host information (that's the db4free.net bug, because this should be prevented). I fixed the host info manually in the mysql schema and changed it to a % sign, but I forgot to update the tables_priv table where access to the show_log database was granted.

So he contacted me again and reported that he couldn't connect to the show_log database anymore. I issued a GRANT USER for his_username@'%' command - and the server crashed. I soon found out that this was because the host information was inconsistant between the user, db and tables_priv tables.

Here's how to repeat this bug:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21-max

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

mysql> CREATE USER testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE test2.tt (
-> id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)

mysql> GRANT ALL ON test1.* TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test2.tt TO testuser@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.tables_priv
-> SET host=''
-> WHERE user='testuser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR testuser@'%';
ERROR 2013 (HY000): Lost connection to MySQL server during query

You can also look up for this in the bug report.

This story is very typical for two reasons. One is that db4free.net hosts a huge variety of different users. They do things in different ways and sometimes (and this should not be considered bad) in unexpected ways. And you find most of the bugs, if you do unexpected things.

The second reason is that the bug itself was typical. Most of the bugs that I found so far can be split into two categories. The first category are of course bugs in new features (mainly in development releases) - that's just natural. Then I found quite a few bugs that result from complex privilege settings, including database or even table specific privileges (and probably even more if you define privileges on the column level, which I - to be honest - never did except for educational purposes when I learnt for my MySQL exams).

There would even be a third reason, why this bug is typical - but you can easily avoid this. I manually modified the mysql schema tables and flushed the privileges. If you do this and make a mistake so that you create inconsistant values (like I did), it's not unlikely that you stumble across a bug. However, if you can avoid to edit the mysql schema tables manually (and in almost all cases you can avoid it using the GRANT and REVOKE commands as well as CREATE USER and DROP USER), I'd highly recommand you to avoid it. It's a bad habit from me that I often do it anyway ;-).

I haven't counted the number of bugs that I found through db4free.net, but it were quite a few. Most of them of course in the development releases - in MySQL 5.1 and in MySQL 5.0 before it became GA and most of the bugs that I reported recently refer to the Event Scheduler - but that's probably because it's one of my favourite new MySQL 5.1 features that I played with more than with the other nice new things.

Thursday, May 18, 2006

Podcast with Brian Aker, Dana Gardner & Michael Baum

Here's an interesting Podcast about Troubleshooting with Brian Aker, Dana Gardner and Michael Baum:

http://www.splunk.com/index.php/articles/news/311?ac=MySQL06

Check it out ;-)!

Joined the MySQL web development team

Yesterday I have officially joined the MySQL web development team. During the Users Conference three weeks ago I have talked with Eric Braswell about joining the team and just after the Conference finished, he promised me the job offer. This was the perfect ending of a Users Conference that has also started incredibly perfect when I was awarded with the MySQL Community Award "Writer of the Year 2006". The last month was just absolutely fantastic!

It's such a great feeling to work for a company that I can fully identify with. I love the MySQL database server and I want MySQL to be the best database server of the world. I love the spirit of freedom that comes with Open Source and to work with people from all over the world who have the same goal in mind: providing the best, but still free database server of the world. I love to use English (which is not my native language) as working language and I love to learn new things. Even though I live in quite a remote area with no big city in a radius of more than 100 km, I can be part of a "huge world wide thing". Many other people of my age in this area have moved to Vienna or other urban areas to find good jobs. I got a great job that I can do at home - what more can I want? I will get the opportunity to travel and see new parts of the world. And I can arrange my working hours to work very much at night, which is the most convenient working time for me (which gives me the chance to do other things while most people work). There are many great things about working for MySQL!

However, the job will definitely not be easy and will include many big challenges. It's a high responsibility to provide a good service and there will be many technical issues that will be new for me. But of course it's also a perfect opportunity to learn a lot - and I'll do my best to get a good job done!

Friday, May 12, 2006

MySQL 5.1.10 is on the way

I found out that the MySQL 5.1 development source version has changed to 5.1.11, so MySQL 5.1.10 will probably be released very soon.

This is especially interesting for two reasons. First, MySQL 5.1.9 had a major bug in the Event scheduler, which will be fixed in 5.1.10:

"Executing a CREATE EVENT statement could cause 100% CPU usage. (Bug#19170)"

Second, MySQL 5.1.10 looks like the first full featured MySQL 5.1 version for Windows. It will include the CSV storage engine which will make it possible to write the general_log and slow_log into database tables:

"Logging to the mysql.general_log and mysql.slow_log tables did not work for Windows builds because the CSV storage engine was unavailable. The CSV engine now is enabled in Windows builds. (Bug#17368))"

Also the mysql_upgrade command will be available for Non-Unix-OS, too:

"The mysql_upgrade command has been converted from a shell script to a C program, so it is available on non-Unix systems such as Windows. This program should be run for each MySQL upgrade."

To see all the changes, take a look into the Change history. 5.1.10 really looks like a very interesting release.

How reliable are checksums?

Or should I ask, how reliable are copy commands?

Okay, this doesn't relate to MySQL directly, but one should know that also MySQL provides md5 checksums for all downloads where you can easily verify if your downloaded file is really identical to the file on MySQL's server. Just type in "md5sum [filename]" and compare the displayed code to the code shown on the website - if they are identical, the file is also identical to its very last bit.

Last night I have downloaded the new SuSE Linux 10.1. I compared the md5 sums and they were absolutely identical to the codes provided at the download source:

sputnik:~/SuSE # md5sum SUSE-Linux-10.1-GM-x86_64-CD*
60cf3e9b090326c39334a72c66145ad2 SUSE-Linux-10.1-GM-x86_64-CD1.iso
15ea8db1b388fadce8e8ca853bb05901 SUSE-Linux-10.1-GM-x86_64-CD2.iso
0bdc464910785936a322f2519ca49da3 SUSE-Linux-10.1-GM-x86_64-CD3.iso
20884a4c45d406ae0c38fb0bc3ee7de8 SUSE-Linux-10.1-GM-x86_64-CD4.iso
5538cba168f0b32b9562f73a40977405 SUSE-Linux-10.1-GM-x86_64-CD5.iso


(compare with http://download.opensuse.org/distribution/SL-10.1/iso/MD5SUMS)

Then I used the scp command to transfer the iso files to another PC (from which I want to burn the CDs). After copying, I checked the md5 sums again and got following output:

mpopp@suse:~/SuSE_10_1> md5sum *
449c04a969166e49aa20efbe7be0a6a2 SUSE-Linux-10.1-GM-x86_64-CD1.iso
15ea8db1b388fadce8e8ca853bb05901 SUSE-Linux-10.1-GM-x86_64-CD2.iso
a828021a47c6af08c699469234ba4587 SUSE-Linux-10.1-GM-x86_64-CD3.iso
20884a4c45d406ae0c38fb0bc3ee7de8 SUSE-Linux-10.1-GM-x86_64-CD4.iso
59e3ebb1c397b923216dcca55acf7127 SUSE-Linux-10.1-GM-x86_64-CD5.iso


So, the checksums for CDs 2 and 4 are still identical, but all the others have changed. It took me two more attempts to get identical codes for CDs 3 and 5 and I'm still trying to get a correct checksum for CD 1 (after copying the file for at least 4 times, I got different checksums after each attempt). So this looks very strange to me.

However, I also have a downloaded Fedora Core 5 DVD where the md5 checksum did not match the code that was provided on the download source (which was of course the official Fedora download page). But the DVD works without problems and I have found no errors at all.

Sunday, May 07, 2006

db4free.net's MySQL 5.0 server is not able to execute SELECT * FROM INFORMATION_SCHEMA.TABLES anymore

I wrote about a similar issue recently when I tried to update db4free.net's phpMyAdmin to 2.8.0.3 and couldn't do so because the latest phpMyAdmin versions refer to information_schema instead of SHOW commands. Now there's another problem that shows that queries on information_schema perform much too slow on large servers - I can't execute a simple SELECT * FROM INFORMATION_SCHEMA.TABLES query on db4free.net's MySQL 5.0 server anymore. After running for quite a long time, it fails in a "ERROR 2013 (HY000): Lost connection to MySQL server during query".

I guess that many MySQL DBAs must have similar problems. Now I've filed an official bug report with Severity S1.

Patch of bug 17204 still pending

There has been that nasty issue when the MySQL server (both 5.0 and 5.1) crashed when certain Stored Procedures were executed several times. Giuseppe Maxia, Beat Vontobel, Roland Bouman and I experimented with a Stored Procedure that should retrieve foreign key dependencies out of information_schema - that's when we found this issue and reported it as bug 17204 - that was in February.

Now there's a patch pending for this bug since March, 29th, but it still hasn't found its way into the source and the bug still exists in MySQL versions 5.0.21 and 5.1.9.

I'm afraid that this issue has somehow been forgotten (I also asked in the bug report recently and got no answer) - does anybody know more about it?

Friday, May 05, 2006

Unable to build MySQL 5.1 dev version from source

For a few weeks now I haven't been able to compile the most current MySQL 5.1 dev versions from the source anymore. My system is an AMD64 with SuSE 10.0 - and I tried it in two ways.

The first way is to get the current source code from BitKeeper. BitKeeper is installed on my system, so I get the current version with the "sfioball -r+ bk://mysql.bkbits.net/mysql-5.1-new mysql-5.1" command (which works fine). I change into the directory mysql-5.1 and enter "BUILD/compile-amd64-max" - that's the way I used to do it and before the problems started to occur, it always worked fine.

But now, after quite a while I end up with the following errors:

ccache gcc -DDBUG_OFF -Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mtune=athlon64 -O3 -fno-omit-frame-pointer -g -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o ndb_mgmd MgmtSrvr.o MgmtSrvrGeneralSignalHandling.o main.o Services.o convertStrToInt.o SignalQueue.o MgmtSrvrConfig.o ConfigInfo.o InitConfigFileParser.o Config.o ../../../../storage/ndb/src/mgmclient/CommandInterpreter.o ../../../../storage/ndb/src/.libs/libndbclient.a -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread -lpthread ../../../../dbug/libdbug.a ../../../../mysys/libmysys.a ../../../../strings/libmystrings.a ../../../../cmd-line-utils/readline/libreadline.a -lncurses -lpthread -lcrypt -lnsl -lm -lpthread
../../../../storage/ndb/src/.libs/libndbclient.a(NdbDictionaryImpl.o):(.debug_loc+0x921): undefined reference to `.LVL119'
../../../../storage/ndb/src/.libs/libndbclient.a(NdbDictionaryImpl.o):(.debug_loc+0x92c): undefined reference to `.LVL119'
collect2: ld returned 1 exit status
gmake[5]: *** [ndb_mgmd] Error 1
gmake[5]: Leaving directory `/MySQL_51/mysql-5.1/storage/ndb/src/mgmsrv'
gmake[4]: *** [all-recursive] Error 1
gmake[4]: Leaving directory `/MySQL_51/mysql-5.1/storage/ndb/src'
gmake[3]: *** [all-recursive] Error 1
gmake[3]: Leaving directory `/MySQL_51/mysql-5.1/storage/ndb'
gmake[2]: *** [all-recursive] Error 1
gmake[2]: Leaving directory `/MySQL_51/mysql-5.1/storage'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/MySQL_51/mysql-5.1'
gmake: *** [all] Error 2
suse:/MySQL_51/mysql-5.1 #


The second way is to download the nightly snapshot (the tar.gz file) and extract it to a new directory - and then again execute "BUILD/compile-amd64-max". This time, it doesn't take long until compilation fails:

suse:/MySQL_51/mysql-5.1.10-beta-nightly-20060505 # 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:813: 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-20060505 #


My simple question is - does anyone have a clue what's wrong here?

New information_schema view

I often take a look into the Changelogs to see if I can find something interesting new - and often I do. Today I found this entry in the changelog for MySQL 5.1.10:

"Added the REFERENTIAL_CONSTRAINTS table to INFORMATION_SCHEMA. It provides information about foreign keys."

It seems (or lets say, I hope) that information_schema will now also provide information like "ON UPDATE CASCADE" or "ON DELETE CASCADE". I think (and I heard of other people who also think this way) that every CREATE statement (be it a table, a view, a stored procedure, an event or whatever) should be re-creatable from the content that's provided by information_schema. So this would be one step further to achieve this goal.

Unfortunately, the queries against information_schema are often very slow (mainly, if there's a lot of data in the databases), but this issue has also been discussed during the MySQL Users Conference and as far as I know, the developers are currently working to provide better performance. I wish them luck and success!

Tuesday, May 02, 2006

mysql_upgrade to run on all systems from 5.0.22 & 5.1.10

Just found an interesting note in the Changelog. The mysql_upgrade command has been converted from a shell script to an executable binary and therefore be available not only on Unix systems, but also on other operating systems like Windows. This will be starting from MySQL 5.0.22 and 5.1.10.

Event scheduler out of control?

I tried to test (once more) the event scheduler of MySQL 5.1.9 on Windows and experienced a very strange behaviour:
CREATE TABLE  `tt` (
`id` int(10) unsigned NOT NULL auto_increment,
`d` int(10) unsigned NOT NULL,
`ts` timestamp NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER //

DROP EVENT insertIntoTT //

CREATE EVENT insertIntoTT
ON SCHEDULE EVERY 5 SECOND
STARTS current_timestamp + INTERVAL 5 SECOND
DO
BEGIN
insert into tt (d) values (floor(rand() * 100));
END //

DELIMITER ;

After a while, I found this content in table tt:
mysql> select * from tt;
+-----+----+---------------------+
| id | d | ts |
+-----+----+---------------------+
| 1 | 83 | 2006-05-02 07:40:20 |
| 2 | 35 | 2006-05-02 07:40:25 |
| 3 | 66 | 2006-05-02 07:40:25 |
| 4 | 68 | 2006-05-02 07:40:25 |
| 5 | 82 | 2006-05-02 07:40:25 |
| 6 | 50 | 2006-05-02 07:40:25 |
| 7 | 44 | 2006-05-02 07:40:30 |
| 8 | 14 | 2006-05-02 07:40:30 |
| 9 | 79 | 2006-05-02 07:40:30 |
| 10 | 94 | 2006-05-02 07:40:30 |
| 11 | 75 | 2006-05-02 07:40:30 |
| 12 | 35 | 2006-05-02 07:40:30 |
| 13 | 90 | 2006-05-02 07:40:30 |
| 14 | 88 | 2006-05-02 07:40:30 |
| 15 | 13 | 2006-05-02 07:40:30 |
| 16 | 44 | 2006-05-02 07:40:30 |
| 17 | 21 | 2006-05-02 07:40:30 |
| 18 | 16 | 2006-05-02 07:40:30 |
| 19 | 56 | 2006-05-02 07:40:30 |
| 20 | 76 | 2006-05-02 07:40:30 |
| 21 | 54 | 2006-05-02 07:40:30 |
| 22 | 81 | 2006-05-02 07:40:30 |
| 23 | 88 | 2006-05-02 07:40:30 |
| 24 | 38 | 2006-05-02 07:40:30 |
| 25 | 66 | 2006-05-02 07:40:30 |
| 26 | 59 | 2006-05-02 07:40:30 |
| 27 | 39 | 2006-05-02 07:40:30 |
| 28 | 60 | 2006-05-02 07:40:30 |
| 29 | 25 | 2006-05-02 07:40:30 |
| 30 | 89 | 2006-05-02 07:40:30 |
| 31 | 9 | 2006-05-02 07:40:30 |
| 32 | 19 | 2006-05-02 07:40:30 |
| 33 | 10 | 2006-05-02 07:40:30 |
| 34 | 36 | 2006-05-02 07:40:30 |
| 35 | 90 | 2006-05-02 07:40:30 |
| 36 | 84 | 2006-05-02 07:40:30 |
| 37 | 93 | 2006-05-02 07:40:30 |
| 38 | 55 | 2006-05-02 07:40:30 |
| 39 | 36 | 2006-05-02 07:40:30 |
| 40 | 58 | 2006-05-02 07:40:31 |
| 41 | 23 | 2006-05-02 07:40:35 |
| 42 | 81 | 2006-05-02 07:40:35 |
| 43 | 80 | 2006-05-02 07:40:35 |
| 44 | 97 | 2006-05-02 07:40:35 |
| 45 | 88 | 2006-05-02 07:40:35 |
| 46 | 90 | 2006-05-02 07:40:35 |
| 47 | 26 | 2006-05-02 07:40:35 |
| 48 | 5 | 2006-05-02 07:40:35 |
| 49 | 88 | 2006-05-02 07:40:35 |
| 50 | 66 | 2006-05-02 07:40:35 |
| 51 | 8 | 2006-05-02 07:40:35 |
| 52 | 83 | 2006-05-02 07:40:35 |
| 53 | 33 | 2006-05-02 07:40:35 |
| 54 | 59 | 2006-05-02 07:40:35 |
| 55 | 38 | 2006-05-02 07:40:35 |
| 56 | 54 | 2006-05-02 07:40:35 |
| 57 | 99 | 2006-05-02 07:40:35 |
| 58 | 76 | 2006-05-02 07:40:35 |
| 59 | 22 | 2006-05-02 07:40:35 |
| 60 | 27 | 2006-05-02 07:40:35 |
| 61 | 8 | 2006-05-02 07:40:35 |
| 62 | 1 | 2006-05-02 07:40:35 |
| 63 | 25 | 2006-05-02 07:40:35 |
| 64 | 62 | 2006-05-02 07:40:35 |
| 65 | 78 | 2006-05-02 07:40:35 |
| 66 | 45 | 2006-05-02 07:40:35 |
| 67 | 35 | 2006-05-02 07:40:35 |
| 68 | 80 | 2006-05-02 07:40:35 |
| 69 | 36 | 2006-05-02 07:40:35 |
| 70 | 80 | 2006-05-02 07:40:35 |
| 71 | 38 | 2006-05-02 07:40:35 |
| 72 | 89 | 2006-05-02 07:40:35 |
| 73 | 73 | 2006-05-02 07:40:35 |
| 74 | 39 | 2006-05-02 07:40:35 |
| 75 | 19 | 2006-05-02 07:40:35 |
| 76 | 19 | 2006-05-02 07:40:36 |
| 77 | 81 | 2006-05-02 07:40:36 |
| 78 | 88 | 2006-05-02 07:40:40 |
| 79 | 42 | 2006-05-02 07:40:40 |
| 80 | 84 | 2006-05-02 07:40:40 |
| 81 | 38 | 2006-05-02 07:40:40 |
| 82 | 81 | 2006-05-02 07:40:40 |
| 83 | 30 | 2006-05-02 07:40:40 |
| 84 | 50 | 2006-05-02 07:40:40 |
| 85 | 2 | 2006-05-02 07:40:40 |
| 86 | 1 | 2006-05-02 07:40:40 |
| 87 | 42 | 2006-05-02 07:40:40 |
| 88 | 48 | 2006-05-02 07:40:40 |
| 89 | 57 | 2006-05-02 07:40:40 |
| 90 | 81 | 2006-05-02 07:40:40 |
| 91 | 78 | 2006-05-02 07:40:40 |
| 92 | 87 | 2006-05-02 07:40:40 |
| 93 | 42 | 2006-05-02 07:40:40 |
| 94 | 91 | 2006-05-02 07:40:40 |
| 95 | 70 | 2006-05-02 07:40:40 |
| 96 | 22 | 2006-05-02 07:40:40 |
| 97 | 38 | 2006-05-02 07:40:40 |
| 98 | 69 | 2006-05-02 07:40:40 |
| 99 | 70 | 2006-05-02 07:40:40 |
| 100 | 88 | 2006-05-02 07:40:40 |
| 101 | 71 | 2006-05-02 07:40:40 |
| 102 | 31 | 2006-05-02 07:40:40 |
| 103 | 85 | 2006-05-02 07:40:40 |
| 104 | 76 | 2006-05-02 07:40:40 |
| 105 | 63 | 2006-05-02 07:40:40 |
| 106 | 30 | 2006-05-02 07:40:40 |
| 107 | 3 | 2006-05-02 07:40:40 |
| 108 | 68 | 2006-05-02 07:40:40 |
| 109 | 72 | 2006-05-02 07:40:40 |
| 110 | 97 | 2006-05-02 07:40:40 |
| 111 | 13 | 2006-05-02 07:40:40 |
| 112 | 15 | 2006-05-02 07:40:40 |
| 113 | 78 | 2006-05-02 07:40:40 |
| 114 | 86 | 2006-05-02 07:40:40 |
| 115 | 36 | 2006-05-02 07:40:41 |
| 116 | 66 | 2006-05-02 07:40:45 |
| 117 | 61 | 2006-05-02 07:40:45 |
| 118 | 51 | 2006-05-02 07:40:45 |
| 119 | 15 | 2006-05-02 07:40:45 |
| 120 | 61 | 2006-05-02 07:40:45 |
| 121 | 2 | 2006-05-02 07:40:45 |
| 122 | 68 | 2006-05-02 07:40:45 |
| 123 | 78 | 2006-05-02 07:40:45 |
| 124 | 25 | 2006-05-02 07:40:45 |
| 125 | 35 | 2006-05-02 07:40:45 |
| 126 | 42 | 2006-05-02 07:40:45 |
| 127 | 47 | 2006-05-02 07:40:45 |
| 128 | 51 | 2006-05-02 07:40:45 |
| 129 | 56 | 2006-05-02 07:40:45 |
| 130 | 70 | 2006-05-02 07:40:45 |
| 131 | 21 | 2006-05-02 07:40:45 |
| 132 | 39 | 2006-05-02 07:40:45 |
| 133 | 75 | 2006-05-02 07:40:45 |
| 134 | 99 | 2006-05-02 07:40:45 |
| 135 | 12 | 2006-05-02 07:40:45 |
| 136 | 5 | 2006-05-02 07:40:45 |
| 137 | 31 | 2006-05-02 07:40:45 |
| 138 | 82 | 2006-05-02 07:40:45 |
| 139 | 58 | 2006-05-02 07:40:45 |
| 140 | 85 | 2006-05-02 07:40:45 |
| 141 | 93 | 2006-05-02 07:40:45 |
| 142 | 53 | 2006-05-02 07:40:45 |
| 143 | 25 | 2006-05-02 07:40:45 |
| 144 | 11 | 2006-05-02 07:40:45 |
| 145 | 19 | 2006-05-02 07:40:45 |
| 146 | 6 | 2006-05-02 07:40:45 |
| 147 | 14 | 2006-05-02 07:40:45 |
| 148 | 93 | 2006-05-02 07:40:45 |
| 149 | 64 | 2006-05-02 07:40:45 |
| 150 | 83 | 2006-05-02 07:40:45 |
| 151 | 65 | 2006-05-02 07:40:45 |
| 152 | 17 | 2006-05-02 07:40:45 |
| 153 | 35 | 2006-05-02 07:40:46 |
| 154 | 63 | 2006-05-02 07:40:50 |
| 155 | 51 | 2006-05-02 07:40:50 |
+-----+----+---------------------+
155 rows in set (0.00 sec)

Instead of inserting one row every 5 seconds, MySQL inserted 155 rows in 30 seconds. Can anybody else experience something like this?

Monday, May 01, 2006

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.

mysqluc_hotel01sn.jpg   mysqluc_hotel02sn.jpg   mysqluc_hotel03sn.jpg

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

mysqluc_dirty01sn.jpg   mysqluc_dirty02sn.jpg   mysqluc_dirty03sn.jpg

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

mysqluc_car01sn.jpg   mysqluc_car02sn.jpg   mysqluc_car03sn.jpg

mysqluc_car04sn.jpg   mysqluc_car05sn.jpg

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

mysqluc_car06sn.jpg   mysqluc_car07sn.jpg   mysqluc_car08sn.jpg   

mysqluc_car09sn.jpg

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.

mysqluc_conf01sn.jpg   mysqluc_conf02sn.jpg

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.

mysqluc_road01sn.jpg   mysqluc_road02sn.jpg   mysqluc_road03sn.jpg   

mysqluc_road04sn.jpg   mysqluc_road05sn.jpg   mysqluc_road06sn.jpg   

mysqluc_road07sn.jpg   mysqluc_road08sn.jpg   mysqluc_road09sn.jpg   

mysqluc_road10sn.jpg   mysqluc_road11sn.jpg   mysqluc_road12sn.jpg   

mysqluc_road13sn.jpg   mysqluc_road14sn.jpg   

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

mysqluc_sf01sn.jpg   mysqluc_sf02sn.jpg   mysqluc_sf03sn.jpg   

mysqluc_sf04sn.jpg   mysqluc_sf05sn.jpg   mysqluc_sf06sn.jpg   

mysqluc_sf07sn.jpg   mysqluc_sf08sn.jpg   mysqluc_sf09sn.jpg   

mysqluc_sf10sn.jpg   mysqluc_sf11sn.jpg   mysqluc_sf12sn.jpg   

mysqluc_sf13sn.jpg   mysqluc_sf14sn.jpg   mysqluc_sf15sn.jpg   

mysqluc_sf16sn.jpg   mysqluc_sf17sn.jpg   mysqluc_sf18sn.jpg   

mysqluc_sf19sn.jpg   mysqluc_sf20sn.jpg   mysqluc_sf21sn.jpg   

mysqluc_sf22sn.jpg   mysqluc_sf23sn.jpg   mysqluc_sf24sn.jpg   

mysqluc_sf25sn.jpg   mysqluc_sf26sn.jpg   mysqluc_sf27sn.jpg   

mysqluc_sf28sn.jpg   mysqluc_sf29sn.jpg   mysqluc_sf30sn.jpg   

mysqluc_sf31sn.jpg   mysqluc_sf32sn.jpg   mysqluc_sf33sn.jpg   

mysqluc_sf34sn.jpg   mysqluc_sf35sn.jpg   mysqluc_sf36sn.jpg   

mysqluc_sf37sn.jpg   mysqluc_sf38sn.jpg   mysqluc_sf39sn.jpg   

mysqluc_sf40sn.jpg   mysqluc_sf41sn.jpg   mysqluc_sf42sn.jpg   

mysqluc_sf43sn.jpg   mysqluc_sf44sn.jpg   mysqluc_sf45sn.jpg   

mysqluc_sf46sn.jpg   mysqluc_sf47sn.jpg   mysqluc_sf48sn.jpg   

mysqluc_sf49sn.jpg   mysqluc_sf50sn.jpg   mysqluc_sf51sn.jpg   

mysqluc_sf52sn.jpg