db4free.net blog has moved and what's new

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

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?

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

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

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

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

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?

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

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!

Current activities and plans

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!

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



Stay tuned - more enhancements are to come!