db4free.net runs dev source version again (5.1.12)

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

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

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

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?

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

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

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

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

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