db4free.net blog

db4free.net blog has moved and what's new

2010-07-19T23:30:00.001+02:00

It was about time to switch to something new.

I finally have my new blog launched at www.mpopp.net which is something that I had planned for quite a while. This new blog is a replacement for my old blog at db4free.blogspot.com where people will find news and posts about db4free.net and MySQL. But since db4free.blogspot.com used to be specific to db4free.net and MySQL (implied by its name), it was not a good place to blog about various different topics as well, which I found increasingly limiting. So I decided for a new blog, run by Wordpress, which will allow me to categorize my blog posts to target people who are interested in specific topics.

Some of them will be:

db4free.net of course as well as

MySQL of course.

... so far, not much new and most posts which go into one category will go into the other as well.

Further categories that I'm thinking of are:

Astronomy (this is probably the topic that I missed a place to blog about the most),

Web and Web Development related things,

Guitar (which I'm playing) or

Music in general,

Politics (a PITA most of the time) and maybe even

日本語 - ehm, that's Japanese which I'm trying to learn and ...

who knows what else, this list is expandable without limits.

If you had my old blog bookmarked or had linked to it on your blog or site, please add or update to bookmark or link to my new blog. And if you haven't not done so far, why not do so now?

Welcome to my new home!

db4free.net outage

2010-03-22T18:54:00.002+01:00

Here is in brief what's going on.

Last Friday (CET), big hardware troubles started on the server which db4free.net runs on. The server was unavailable and I didn't manage to start it up again by myself. I files a support ticked to my server provider, and by Saturday afternoon, the server was accessible again. Everything seemed to look fine, but Sunday, shortly after midnight, the same troubles started again.

My server provider gave me a new server, and thankfully, managed to get the old server to start up again. I backed up the user databases and copied them to the new server, so no - or at least almost no - data should be lost.

It will take me some time to migrate the entire service to the new server, so please be patient. I also want to use this "opportunity" to start offering MySQL 5.5, so my plan is to make the old server available on a different port, and start the entire service over on a fresh and clean MySQL 5.5 instance. This will mean that users will have access to their data (probably for the next 2 months or so, until I shut down the MySQL 5.1 server), but in order to use the new server, everybody will have to register a new account again. These are my current plans, and I hope to have the new db4free.net fully up again by upcoming Friday (March 26).

Please apologize the inconveniences. However, it's again a good opportunity to remember that db4free.net is a testing service with no warranties and that users should keep backups of all data they really need!

What is happening with db4free.net and where is the Port 3307 server?

2010-01-24T07:31:00.002+01:00

db4free.net users may have noticed that they can't access the server on port 3307 anymore.

It isn't gone (yet), but soon will be. I plan to reduce the db4free.net service to a single server again which means that the 2nd server will be removed. So in order to make people notice that a change is going to happen, I moved the server to port 3308.

If you have data on this server which you want to keep, please move it to the port 3306 server until February 28, 2010. After then, the server will no longer be accessible.

Another change which is going to happen is that I will delete db4free.net accounts again which are not being used anymore. If you have - or will until end of February - accessed your port 3306 any time in the year 2010, you will be safe (unless you use up excessively much disk space or do other naughty things, so your account gets deleted for that reason). The purpose is to create more resources for those people who really use this service.

But there is more going on. Just a few minutes ago, db4free.net received its first major code update in a long time. I have to ashamedly admit, the code is a mess and I have made many bad (some very bad) design decisions when I first created the application (almost 5 years ago). The first set of changes (which went online right now) became necessary because parts of the code and libraries that I used would not work with PHP 5.3 anymore. So now, db4free.net is ready for PHP 5.3, but a lot more is required to be done. So I'm going to apply more code updates over time, some of which you may notice, but most (especially in the beginning) are probably under the hood so that they are not very noticeable. What should happen though is that at some points you should notice that the website will (hopefully) load significantly faster.

Another update which I plan in the first half of 2010 is to update the MySQL server to MySQL 5.5, the new development release. I am thinking about giving db4free.net a totally new start. So what is now the port 3306 server may also be moved to a different port, and a totally fresh MySQL 5.5 server will be available for your use. You may even have to register again, but (as with the port 3307/3308 server now) your data will be available for long enough to allow you to migrate it to the new server. Please remember that db4free.net is a testing environment and not a hosting service for productive data. Changes like these can happen, and will happen.

Also outages (most probably only for short periods of time) may become more frequent during the upcoming months, while I apply the updates. But when they are finished, db4free.net will be a much better service again, which may even allow feature additions. So rather than getting frustrated, please consider these outages as necessary to get improvements in the future. They will pay off in the end.

Until then, please apologize for the inconveniences they may cause.

Thanks,
Markus

PS: and please remember: backups, backups, backups for data which you need!

Firefox 3.5 and ... some dolphins

2009-06-30T19:54:00.002+02:00

Now here it is, the long awaited Firefox 3.5:

So, since there is Firefox 3.5 and since there are dolphins (mascot of MySQL of course), lets look for a relation between Firefox 3.5 and MySQL which not everybody necessarily knows yet.

Ever wanted to search through the MySQL documentation from your Search bar?

Fedora development repository including MySQL 5.1

2009-01-26T21:07:00.002+01:00

I just noticed that Fedora has updated their development repository to include MySQL 5.1:

Actually it's a pity that MySQL 5.1 didn't already make it into Fedora 10 since Fedora 10 was released on November 25, 2008, 2 days prior to MySQL 5.1.30. But it will most certainly be in Fedora 11.

Dolphins in the Sky

2009-01-21T20:18:00.003+01:00

This blog post has moved. Please find it at:

http://www.mpopp.net/2009/01/dolphins-in-the-sky/.

db4free.net running on Sun servers

2009-01-09T21:29:00.004+01:00

There's one thing which I believe I never mentioned so far, though it is certainly worth to be mentioned. db4free.net runs on a dedicated server at easyspeedy.com from Denmark, who use servers from Sun Microsystems and are a Certified Sun Partner.

I got reminded of this fact when I received an email from easyspeedy, wishing me a good new year, telling me about plans for 2009 and including the uptime statistics of 2008, which were as follows:

10 months of 100% uptime
1 month of 99.98% uptime
1 month of 99.97% uptime

Sure, uptime on db4free.net was a bit lower since these statistics only show how much the server has been up (and down) on their side.

Anyway, it's one more example where MySQL and Sun Microsystems come together in some sort of way.

Which browsers and OS do mysql.com visitors use?

2009-01-06T04:21:00.002+01:00

First of all, let me wish all of you a great year 2009! As an old year ends and a new year begins, it's a good time to do some statistics. One of the statistics that web developers are interested in is, which browsers and operating systems visitors use to access their websites. Let me share some of the interesting figures regarding the mysql.com website with you.

(click to enlarge and see more detail)

The spreadsheet contains data for December 2008 (another sheet for November 2008) in a pivot table style. In the columns you see the browsers and operating system and in the rows the continents and regions, with the shares in the respective cells. Fairly self-explanatory as I hope ;).

Usage shares on the mysql.com site generally favor browsers and operating systems that are more widely used by technically advanced people, compared to general global usage share statistics, like the probably most famous one at marketshare.hitslink.com. While the general global share for Mozilla Firefox is "only" 21.34 % as of December 2008, about 54 % of the visitors on mysql.com use Firefox. Visitors most likely to use Firefox come from Europe, Oceania and North America.

Just as the global share for Firefox is constantly increasing (from 16.80 % in Dec. 2007 to 21.34 % in Dec. 2008), the share for Firefox at mysql.com has increased from 48.96 % to 53.90 % during the same period.

Internet Explorer has always had a hard time, compared to global shares, so you find its share at mysql.com at only 33.62 %, 63.17 % of which use IE7 (making up 21.50 % of the total share) and 34.89 % of which use IE6 (making up 11.87 % of the total). For comparison: in December 2007, Internet Explorer found itself at 41.40 % (IE6 with 51.58 % still leading ahead of IE7 with 48.14 %, making up 21.35 and 19.93 % of the total). Internet Explorer has by far the strongest share in Asia, followed (with some distance) by Central America, South America and Africa.

So while Mozilla Firefox gained about 6 percentage points, Internet Explorer lost about 8.

Shares for Opera, Safari and - since September 2008 - Google Chrome are constantly between 3 and 4 %. There is hardly any trend that can be recognized as stable, so we'll have to wait and see what 2009 brings (sure will be exciting).

Also the shares of the operating systems are comparably stable (at least compared to the movements happening with IE and Firefox). Windows decreased its share (again from Dec. 2007 to Dec. 2008) slightly from 82.69 % to 80.83 % while MacOS increased from 6.44 % to 7.70 %. Linux has changed only from 10.41 % to 10.97 %. So there is a slow trend from Windows to MacOS. You may want to compare these figures with the global OS usage shares from marketshare.hitslink.com.

Windows is strongest in Asia and weakest in North America, Linux is strongest in Europe and weakest in Asia and MacOS is (little surprising) strongest in North America and weakest in South America.

Other than in the 90s and the early years of this decade, there is now a wide choice of web browsers across all the widely used operating systems and there has never been as much competition as today which results in noticeable quicker development. As the browsers of previous (browser) generations finally die out and recent browsers implement the W3C standards more and more completely, web developers will have an easier life to create rich internet applications with less pain, despite the higher number of web clients.

2009 is certainly going to be an exciting year in regard to web browsers. How will Internet Explorer 8 affect the usage shares? Firefox 3.1 is on its way, about to be released around the same time as Internet Explorer 8. Maybe even Firefox 3.2 may make it to a final release in 2009. Opera will release version 10, Safari 4 is under development and there is certainly more to come from Google Chrome. I can't wait to see what all these numbers look like when 2009 is over.

db4free.net again offers latest MySQL 6.0 (compiled from source)

2008-07-02T22:20:00.001+02:00

The db4free.net's MySQL 6.0 server is at the very latest state again. I have compiled the server from source, so you can test the very latest of MySQL development.

Unfortunately there were some issues with the former version (6.0.4) and I didn't manage to simply upgrade the server as I used to. After upgrading, the server didn't start up with the old data directory and dumping database by database to re-import it into the new version resulted in frequent crashes. After multiple failures to do a straight update I decided to install a fresh and empty new 6.0 server.

Now the most important question for you if you used the 6.0 server: where did my data go?

I left the old server up and running, but configured it to port 3308. This means, if you had data in your MySQL 6.0 database, you can still access it - here's how, if you use the default MySQL client:

mysql -h db4free.net -P 3308 -u [your_username] -p[your_password] [your_database]

I am also going to configure phpMyAdmin to allow you accessing the old MySQL server using it.

If you want to keep your data on the 6.0 server, please migrate it to the new one (running on port 3307 as usual) as soon as possible. I will keep the old server (running 6.0.4) running for about a month.

Have fun experimenting with the very latest MySQL 6.0!

Never miss a new product release again

2008-06-03T21:52:00.000+02:00

Would you like to be notified when a new MySQL product is being released? Check this out:

(find this in the left sidebar at dev.mysql.com)

... or you can click right here to subscribe to this new RSS feed.

New RSS feeds for MySQL Web Seminars

2008-06-03T18:15:00.001+02:00

We (the MySQL Web team) have now made it easier for you to keep track of upcoming MySQL Live Web Seminars as well as MySQL OnDemand Webinars - check out the new RSS Feeds:

Have you noticed the change on www.mysql.com?

BTW, did you see the RSS icon next to MySQL Training? This RSS feed is not as new as the ones for the Web Seminars. But they are nevertheless cool because they inform you immediately if there is a new training course in your country.

Stay tuned - there's more to come!

Integration of Frameworks for Rapid Web Development

2008-04-09T19:30:00.000+02:00

Are you interested in how to integrate the Zend Framework, Smarty and the Yahoo! UI Library into an infrastructure that makes it comfortable to develop professional web applications? Do you want to know how this infrastrucure is going to form a solid foundation for the MySQL website? And see what the future basement of db4free.net is going to look like?

And are you going to attend the MySQL Users Conference next week?

Then this is a presentation that you don't want to miss:

Integration of Frameworks for Rapid Web Development
Markus Popp (MySQL), Duleepa Wijayawardhana (MySQL)
3:05pm - 3:50pm Wednesday, 04/16/2008
PHP and MySQL
Ballroom D

"Dups" and I are looking forward to meeting you!

db4free.net now offers MySQL 5.1 and MySQL 6.0

2008-03-31T23:42:00.000+02:00

It has been pretty quiet about db4free.net for some time, but now there are news. db4free.net now offers MySQL 6.0 database accounts - in addition to the old (and I'm tempted to even say tested) MySQL 5.1 ones.

So - everybody who registers gets 2 databases, one on the MySQL 5.1 server and one on the MySQL 6.0 server, which runs at port 3307. Connections can be made using the MySQL client running the command


mysql -h db4free.net -P 3307 -u [username] -p[password]

or using phpMyAdmin, right from the db4free.net website.

Users who have set their accounts to allow remote connections can also use tools like the MySQL GUI Tools or MySQL Workbench.

Curious about what MySQL 6.0 has new to offer? Check out http://forge.mysql.com/wiki/Falcon_Feature_Preview.

Your feedback about MySQL 6.0 is much appreciated. Please send them to mysql-6.0-feedback@db4free.net or post in the db4free.net forum.

Have fun and enjoy diving into the new MySQL 6.0!

init_connect system variable

2007-08-31T14:26:00.000+02:00

Even after working with MySQL for quite a long time, I sometimes stumble over great things that I didn't know. It happened to me again when I met Beat Vontobel (together with other MySQL folks like Giuseppe, Roland, Lenz, Susanne) at the FrosCon Conference.

What I learned about was the init_connect system variable. It allows to trigger a SQL statement (or Stored Procedure) whenever a new user that doesn't have SUPER privileges connects to the server. There's no need for me to explain this in large detail, since Beat already did this in one of his blog articles.

Why is it so useful for me? To keep db4free.net free from database accounts that aren't in use anymore, it's important to log the connects. So far I tried to do it with the general_log - but this of course also logs all the queries from the users ... which produces a huge amount of data that I don't really need for this purpose. Of course there are ways to filter that out, but since resources are limited, every way to avoid unnecessary data from the very beginning is of big help for me.

And since I never heard about it before - even though it is so useful - maybe there are others like me who could make use of it, but just haven't heard about it.

Early registration for MySQL UC ends this week (March 14th)!

2007-03-12T10:30:00.000+01:00

Don't miss this date! March 14th is the last date to get a $200 discount for a MySQL Users Conference registration.

Watch out for the Tutorials, Sessions, Speakers and Events and take the opportunity to Get MySQL Certified during the Conference!

I'm looking forward to meeting you!

New db4free.net statistics

2007-01-02T17:33:00.000+01:00

At the end of November, I activated Google Analytics to db4free.net, so now I have the data for a whole month which already shows some interesting facts about where the visitors come from, which browsers and operating system they use etc.

Very interesting is the Geo Map Overlay:

This is based on 5,736 visitors. The total number of pageviews is 21,854.

2,831 visitors used the Internet Explorer - 2,210 of them version 6.0 and 601 used version 7.0, 20 used an older version.

2,303 visitors used Firefox - 1,454 used Firefox 2.0 and 742 used Firefox 1.5 (and the rest older versions).

434 visitors used Opera.

The use of operating systems splits up as follows: 5,402 visitors use Windows (4,929 of them Windows XP), 235 use Linux and 85 MacOS.

1,014 new users registered for a new account in December, which means that in average every 5th to 6th visitor creates a new account.

And last but not least - the number of total db4free.net users as of now according to my own statistics is 5,966 and the total number of visitors since its beginning is 73,182.

New MySQL related HowTo available

2007-01-02T17:28:00.000+01:00

I just found this new HowTo at www.howtoforge.org:

http://www.howtoforge.org/secure_mysql_connection_ssh_tunnel

It describes how to set up a secure tunnel between your MySQL Server and a locally running MySQL Administrator using Putty. I haven't tried it out myself, but I strongly assume that this works for all the other GUI tools as well.

Running Stored Procedures on MySQL 4.x

2006-11-23T01:55:00.001+01:00

This blog post has moved. Please find it at:

http://www.mpopp.net/2006/11/running-stored-procedures-on-mysql-4-x/.

www.howtoforge.org

2006-10-27T22:14:00.000+02:00

Many of you might know HowToForge, but for those who don't - there's a bunch of excellent and interesting tutorials relating to Linux and OpenSource.

There are also many about MySQL or using MySQL - here are some examples:

How To Set Up Database Replication In MySQL

MySQL Master Master Replication

How To Set Up A Load-Balanced MySQL Cluster

Running A MySQL-Based DNS Server: MyDNS

Virtual Users And Domains With Postfix, Courier And MySQL (Fedora Core 5)

Virtual Users And Domains With Postfix, Courier And MySQL (+ SMTP-AUTH, Quota, SpamAssassin, ClamAV)

... and more.

There's definitely a lot of stuff to play with.

Official German MySQL 5.1 Reference Manual to be released this month

2006-10-07T01:03:00.000+02:00

All German speaking folks will have a chance to get a printed German MySQL 5.1 Reference Manual which covering 1,312 pages for a price of EUR 59.95 (hey, most books with that amount of pages cost much more than that).

It is about to be released this month and can be pre-ordered at amazon.de.

I pre-ordered my copy today. Sometimes it's nice to leave the PC for a while and dig into a good book, but still use the time to improve skills. There are still some topics about MySQL that I'm not as familiar with as I wished and this will be good opportunity to remove some gaps in my knowledge.

New Lists of Certified Candidates online

2006-10-03T01:49:00.000+02:00

The Lists of MySQL Certified Candidates now also include the new Certifications for MySQL 5.0 - Certified MySQL 5.0 Developer and Certified MySQL 5.0 DBA:

http://www.mysql.com/training/certification/candidates.php

Also new is that you can now find some statistic figures. You can see how many have passed each individual Certification and on the detail page you also find how many people have passed this particular Certification in each specified country. So you can for example easily say: I'm one of eight Certified MySQL 5.0 DBAs in Germany.

db4free.net now exclusively runs MySQL 5.1

2006-09-24T06:57:00.000+02:00

It's past 7 o'clock in the morning, but this night has been a productive one (hey - no bad thinking please ;-)).

I have updated db4free.net to the latest MySQL 5.1 development source version (5.1.12-beta) and discontinued the 5.0 server. The "former" 5.1 server which ran on port 3307 is still alive, but no new accounts are being created there anymore. During the next days I plan to make the databases that run on the port 3307 server accessible on the main 5.1 server (with standard port 3306) to have everything together on one server - and let the port 3307 server die.

Before the update, most users have only used the 5.0 server, but only a little number of users did anything on the 5.1 server. My intention is to test 5.1 more intensively under a higher load and I hope that this also helps to contribute to the development of MySQL 5.1 by finding bugs or other flaws on a production system. I'm also thinking of setting up a replication slave server (instead of the second production server) to do regular backups from there - and of course for testing and finding bugs.

Running MySQL 5.1 offers new possibilities for the users and for me as administrator. The users can now create events and triggers (it was not possible for users to create triggers on the 5.0 server since this would have required SUPER privileges). I want to make use of new logging capabilities, events and new information_schema views to set up a monitoring system - all done by the server. The new MySQL 5.1 features make it much more comfortable for me to keep track of what's happening on the server. This will also be used to clean up unused accounts and to find misused accounts more easily and more quickly.

So this is a great improvement for the users as well as for me - and hopefully also for the MySQL company and products.

New information_schema views

2006-09-23T20:46:00.000+02:00

As you might have recognized already, I love to take a look into the Change logs from time to time and hope to find some goodies there. Here's an especially nice one in the Change log of 5.1.12:

INFORMATION_SCHEMA contains new tables, GLOBAL_STATUS, SESSION_STATUS, GLOBAL_VARIABLES, and SESSION_VARIABLES, that correspond to the output from the SHOW {GLOBAL|SESSION} STATUS and SHOW {GLOBAL|SESSION} VARIABLES statements.

This was reason enough for me to compile MySQL 5.1 from source and take a look:

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

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

mysql> use information_schema
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
27 rows in set (0.00 sec)

So what can you do with it? Certainly a lot. You want to know when your server has been restarted the last time? Here's what to do:

mysql> SELECT (NOW() - INTERVAL VARIABLE_VALUE SECOND) 
   AS server_start_time
    -> FROM information_schema.GLOBAL_STATUS
    -> WHERE VARIABLE_NAME = 'UPTIME';
+---------------------+
| server_start_time   |
+---------------------+
| 2006-09-23 20:38:05 |
+---------------------+
1 row in set (0.00 sec)

There are other nice things you can do. You can create a monitoring table and an event to copy the values of all Com% status variables into the monitoring table regularily, like so:

SELECT NOW() AS ts, 
   VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME LIKE 'Com%'

And that's certainly not everything you can do ;-).

db4free.net runs dev source version again (5.1.12)

2006-08-27T05:18:00.000+02:00

It's three months ago since MySQL 5.1.11 was released and many things have changed since then. Just watch the Change Log for 5.1.12 - I believe it's one of the longest ever:

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html

But I've found another nice and interesting thing: watch this bug report. This says that it's now allowed to do many more things in Prepared Statements inside Stored Procedures:

SHOW BINLOG EVENTS
SHOW (MASTER | SLAVE) STATUS
SHOW (MASTER | BINARY) LOGS
SHOW (PROCEDURE | FUNCTION) CODE (parsable only in debug builds)
SHOW CREATE (PROCEDURE | FUNCTION | EVENT | TABLE | VIEW)
SHOW (AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS)
CHANGE MASTER
RESET (MASTER | SLAVE | QUERY CACHE)
SLAVE (START | STOP)
CHECKSUM (TABLE | TABLES)
INSTALL PLUGIN
UNINSTALL PLUGIN
CACHE INDEX
LOAD INDEX INTO CACHE
GRANT
REVOKE
KILL
(CREATE | RENAME | DROP) DATABASE
(CREATE | RENAME | DROP) USER
FLUSH (TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES |
LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES)

Ain't that nice? This gives a lot more flexibility to dynamic SQL! (I know some guys who will have a lot of fun with that ;-))

This was reason enough for me to update the MySQL 5.1 server (on Port 3307) on db4free.net to this latest Development Source version. So you can try out all the nice enhancements that have been introduced in the last three months.

Enjoy!

MySQL Playground for Download

2006-08-21T21:10:00.000+02:00

I have now uploaded my MySQL Playground and you can get it from

http://www.db4free.org/mysql-playground.tar.gz

The size of the file is 1 GB, so please take into account that it might take some time to download the file.

After download, just extract it to your "Virtual machines" directory:

tar xvfz mysql-playground.tar.gz

... and load it into your VMWare Server (should work for VMWare Workstation, VMWare Player too, I guess). I have assigned 768 MB RAM to this machine, so depending on your total amount of memory available you might have to change this.

The virtual machine runs SuSE Linux 10.1 (32-bit). The root password is simply "root" and there's a normal user named "user" with password "user". The same for the three MySQL instances (MySQL 5.1.11 each): user "root" - password "root", user "user" - password "user". There's also a replication slave user names "slave" with password "slave". The MySQL servers are installed into directory /MySQL_51/server-x, x being 1, 2 and 3. They use port 3306 for the first server, 3307 for the second server and 3308 for the third server. The database "replication_db" is set up multi-master replication, so whatever you do inside this database on one of the servers should also happen on the other two servers.

Please tell me if I have forgotten some important piece of information.

Feel free to play around with it and if you create other nice setups out of it, feel free to publish your versions.

MySQL GUI tools bundle replaces individual GUI tools

2006-08-18T18:23:00.000+02:00

You may have noticed that you don't find any links for MySQL Administrator, MySQL QueryBrowser, MySQL MigrationToolkit and MySQL Workbench on dev.mysql.com anymore.

All these tools are now available in the new MySQL GUI Tools bundle and you can download versions for Windows, Linux and MacOS.

My new MySQL playground

2006-08-16T19:22:00.000+02:00

The VMWare Server allows to do great things that you usually wouldn't like to do on your working machines, mostly because of the danger to damage something that can take a lot of time to recover. And it's also a great thing to play with advanced MySQL issues.

So I installed myself a "MySQL playground" - a VMWare machine running SuSE Linux 10.1 - and installed three MySQL 5.1.11 servers (placed in different directories and assigned the ports 3306, 3307 and 3308). The first thing that I played with was to set up a multimaster replication example, based on Giuseppe Maxia's article: Advanced MySQL Replication Techniques (I planned to do this for quite a while and now I finally did it!).

Giuseppe's article is great and makes this a very straight-forward task. The only thing I'd like to mention (because I overlooked it and lost a bit of time) is that if you run more than two servers, you absolutely need to add the log-slave-updates option to the [mysqld] section of your my.cnf files. Otherwise your actions will be replicated only to the next slave, but since this one wouldn't write these actions to the binlog, it would not be replicated to the other servers as well.

Just try it out - get yourself a virtual machine, install a few MySQL instances and play around with them. This can be a lot of fun and of course very instructive!

I'm not sure whether there are any legal issues distributing VMWare machines (I don't think so, but the VMWare server is proprietary software so I'm not completely sure)? If there aren't any I would be happy to offer my playground for download.

A new idea?

2006-08-10T18:15:00.000+02:00

Since we are currently speaking a lot about new ideas, maybe there is one.

MySQL provides a lot of Live Web Seminars being offered as On-Demand Webinars a few days later. This is a great offer - it provides presentations from experts that everybody can join in and watch or download as soon as they are provided on the On-Demand Webinar pages - all for free.

During the Users Conference, there were some "HackFests" where developers showed how to extend functionality in code right in front of the attendees.

Maybe this could be combined with Webinars? There are many topics that can be presented perfectly from code, from the command line or from the desktop, better than from conventional presentations. Some examples?

* How to set up a replication environment
* How to set up MySQL cluster
* How to use the MySQL Test tools
* How to create backups, showing different options
* Performance tuning
* How to get debugging information for bug reports
* How to enable plugins
* and so on, and so forth, ...

Personally, I need easy ways to get into a feature or a technique. Once I jumped over the first barriers, it's not that difficult anymore to hook in and get involved more deeply. But the most difficult part is the beginning and the lower the barriers to get started are, the easier it gets to enhance the skills.

This includes many benefits for MySQL, too. When people get an easy way to learn how to use such things, they will use it which will in return provide better feedback for MySQL.

Maybe there are technical issues that make it difficult to present Webinars like this at the moment - but hey ... this should not be a reason not to think about it ;-).

Supporting ideas and being productive

2006-08-10T17:49:00.000+02:00

Hey, that's a great topic and I very much like Zack's article and the responses (thanks Roland, Mats ... did I forget someone?).

Some time before I joined MySQL I worked for a local company (which is the biggest company in the region where I live) and I could perfectly see how it should not be. There were (and still are) very strong hierachies and if you are in the low areas of the hierachy structure, the management only wanted you to "work on something". The last thing they wanted was that employees speak up and shake on the structures of the company. No criticism was appreciated, employees should just do their daily work and even if they do it in a very inefficient way - that's perfectly fine.

The worst thing you could do was to make your work more efficient to get yourself a little time for breaks or for other (even educational) activities. So for example - if you stupidly entered numbers into an Excel spreadsheet for 4 hours, it was perfectly OK. If you found a way to automatically import the same data in half an hour and you allowed yourself a little coffee break so that everything together summed up to let's say 45 minutes - you were labeled lazy (speaking of coffee, cigarette or whatever breaks - I even consider them important to get your head free again and keep a distanced look at your work, so many of the best ideas arise during breaks!). The management just wanted to see you sit in front of your PC and do something that's somehow related to your work, but never do anything else. Needless to say that everyone was frustrated, everybody was working just to get paid and hardly anyone had ambitions to make things better (and those who did soon ended up as candidates to be fired).

Such conservative views are the worst idea killers. Work is done most efficiently if you like what you do and if you can identify with your work and your company. That doesn't even mean that you have to be the best skilled expert on your job. If you like what you do and if you like what your company does, you will look for good solutions and try to give the best you can. By looking for the best solutions, this individual will learn and become better skilled anyway. But if somebody isn't motivated to look for good solutions, there's no chance to learn from finding new ideas.

Being productive doesn't work without a certain amount of freedom and it definitely requires a pleasant atmosphere in your daily working life.

MySQL tries to find new employees who are already dedicated to MySQL's products and databasing in general, providing the best chances that new employees are motivated to create improvement. Most employees work from their home office which gives an employee the possibility to set up the working environment according to personal needs. Working times are more flexible - people work best at different hours, so everyone can find the working times that suit him/her best (e.g., I'm a completely night person, I could not find a company that allows me to work during night somewhere around my place). And being an Open Source company which represents open mindedness by definition makes pretty much sure that ideologies that I found in my former company don't have a chance.

Just see this - no manager from my former company would never have written or said something like Zack in his article.

I have seen two very different ways and it's easy to see which one works much better ;-).

PHP 5 Certification exam available soon

2006-08-09T23:56:00.000+02:00

A new and updated version of the Zend PHP Certification is on the way and will be available starting August, 31st:

Zend PHP 5 Certification Page

It's a complete rewrite, but the exam will still (as with the PHP 4 exam) cover 70 questions in 90 minutes. A new Study Guide will be available starting September and current PHP 4 certified people will get a discount for the PHP 5 exam.

To get detailed information, you can also listen to the Webcast Recordings at the phparch.com website.

And for those who prefer taking a MySQL 5 certification, there's also information available for you :-).

Too many connections

2006-08-09T18:44:00.000+02:00

Here's another good rule about using MySQL in your application:

Never let your application access the MySQL server with a user that has SUPER privileges (at least unless there's a VERY good reason to do it).

There may be more reasons for that but one is that it might stop your root user from accessing the server if all allowed connections are being used (the "Too many connections" error).

Here's what the manual says:

"mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected."

But if your application user also has SUPER privileges, this might have used that one extra connection that should be reserved for that purpose.

A good application starts with good database design

2006-08-04T00:22:00.000+02:00

... and most important - good use of SQL!

That doesn't sound surprising, I guess.

Recently, Giuseppe Maxia provided a good hint (An ugly query should be a warning bell) how to get an indicator that something is wrong with your query. I would like to add a hint to help you find out that something is wrong with the way you use your database inside your application. Just ask yourself the following question:

How much of my client side code is there just to get the data that I got from the database into the form that I need in my application?

Your answer should be: very little.

In most cases, SQL provides the capabilities to get out of the database exactly what you need. Needless to say that it's most important to normalize your data. It's not a bad sign if you end up with complex queries and joins that bind together several tables (just take care that your queries don't look like Giuseppe's negative example ;-)). Place the correct indexes to make sure that these queries still perform well when there are thousands and millions of records in the tables. There are many rules about good database design and they exist for a good reason.

But the message of my words shall be: don't waste hundreds of client side code lines to do what one SQL query can do!

Current activities and plans

2006-07-24T03:52:00.000+02:00

It's very important for me to keep closely in touch with MySQL's products and all the fun stuff that comes with working with databases which is also a good way to keep in memory what the background of my job and our company is. A job is much more fun if you know and use the products that the company you work for produces instead of only doing it to get paid (that's something I also know well from local companies I worked for earlier - that makes a huge difference). Because of this, I never want to lose the fun side that comes with Community activities which involves to sometimes simply play and experiment with various things that come to my mind and write about that.

Unfortunately I found a little less time recently for Community related activities than the months before, but there are plans which I hope will bring that back to what it used to be. During the next months I plan to reorganize my working environment and my PC infrastructure which involves setting up a new office. My current office is quite a mess and now during the summer heat it gets unpleasantly hot here, so I'll move downstairs where a nice, quite large room is free and where it doesn't heat up as much as upstairs, so I can setup my new working place exactly according to my needs which will certainly raise my productivity for both my job as well as my Community work.

The reorganization of my PCs includes to start using MySQL 5.1 as my production system (besides moving many things that still run under Windows to Linux) - which will certainly bring up new topics to blog about. The same is planned for db4free.net - until autumn I'd like to move everything to one single MySQL 5.1 server (probably set up a second server as replication slave to provide better security) - so there will be a large amount of new 5.1 users who will contribute in testing 5.1 in production and hopefully provide valuable feedback to us.

So there's definitely a lot to come ;-).

Forum navigation now available!

2006-07-22T02:47:00.000+02:00

Many people have complained that the new MySQL Forum misses an appropriate navigation. Now it's back:

Stay tuned - more enhancements are to come!

mysqldump improvements

2006-07-12T19:31:00.000+02:00

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

2006-07-07T03:20:00.000+02:00

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

2006-07-03T15:08:00.000+02:00

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!

2006-07-01T01:41:00.000+02:00

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

2006-06-29T02:39:00.001+02:00

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

2006-06-23T06:37:00.000+02:00

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

2006-06-11T05:47:00.000+02:00

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

2006-06-11T02:12:00.000+02:00

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

2006-06-06T04:15:00.000+02:00

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

2006-06-03T19:21:00.000+02:00

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

2006-06-03T18:59:00.000+02:00

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

2006-06-01T09:35:00.000+02:00

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

2006-06-01T08:59:00.000+02:00

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

2006-06-01T08:40:00.000+02:00

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

2006-06-01T08:33:00.000+02:00

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

2006-05-28T21:02:00.000+02:00

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

2006-05-27T03:02:00.000+02:00

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

2006-05-23T18:45:00.000+02:00

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

2006-05-21T23:22:00.000+02:00

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

2006-05-19T02:30:00.000+02:00

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?

2006-05-19T01:38:00.000+02:00

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

2006-05-19T00:39:00.000+02:00

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.

Podcast with Brian Aker, Dana Gardner & Michael Baum

2006-05-18T02:00:00.000+02:00

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

2006-05-18T01:09:00.000+02:00

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!

MySQL 5.1.10 is on the way

2006-05-12T11:14:00.000+02:00

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?

2006-05-12T10:58:00.000+02:00

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.

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

2006-05-07T02:01:00.000+02:00

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

2006-05-07T01:43:00.000+02:00

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?

Unable to build MySQL 5.1 dev version from source

2006-05-05T13:22:00.000+02:00

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

2006-05-05T12:28:00.000+02:00

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!

mysql_upgrade to run on all systems from 5.0.22 & 5.1.10

2006-05-02T18:53:00.000+02:00

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?

2006-05-02T07:44:00.000+02:00

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?

More from the MySQL UC

2006-05-01T23:25:00.000+02:00

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

2006-05-01T03:01:00.000+02:00

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!

2006-05-01T02:44:00.000+02:00

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

2006-04-30T23:01:00.000+02:00

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

2006-04-21T15:11:00.000+02:00

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

2006-04-21T12:04:00.000+02:00

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

2006-04-19T11:46:00.000+02:00

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?

2006-04-15T21:24:00.000+02:00

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?

2006-04-15T19:45:00.000+02:00

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

2006-04-12T23:54:00.001+02:00

This blog post has moved. Please find it at:

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

User XYZ != User XYZ

2006-04-12T21:17:00.000+02:00

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

2006-04-08T01:48:00.000+02:00

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

2006-04-07T20:57:00.001+02:00

This blog post has moved. Please find it at:

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

QueryBrowser 1.2.7?

2006-04-03T22:29:00.000+02:00

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!

2006-04-03T22:08:00.000+02:00

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

2006-03-31T20:27:00.000+02:00

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

2006-03-22T11:58:00.000+01:00

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?

2006-03-22T10:45:00.000+01:00

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

2006-03-22T10:38:00.000+01:00

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?

2006-03-21T17:27:00.000+01:00

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

2006-03-14T21:08:00.000+01:00

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

2006-03-14T18:54:00.000+01:00

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

2006-03-12T02:48:00.001+01:00

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

2006-03-12T01:42:00.001+01:00

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

2006-03-11T23:07:00.000+01:00

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)

2006-03-11T22:41:00.000+01:00

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?

2006-03-11T19:41:00.000+01:00

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

2006-03-05T04:00:00.000+01:00

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

2006-03-04T20:56:00.000+01:00

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

2006-03-01T15:58:00.001+01:00

This blog post has moved. Please find it at:

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

db4free.net Crash report

2006-02-26T22:06:00.000+01:00

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!

2006-02-24T03:10:00.000+01:00

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?

2006-02-24T03:00:00.000+01:00

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

2006-02-20T22:47:00.000+01:00

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.