Category Archives: PlanetMySQL

Blog posts to appear at PlanetMySQL

OurSQL Podcasts available at dev.mysql.com

Noticed the new navigation item in the MySQL Developer Zone?

Sarah Novotny and Sheeri K. Cabral have picked up the OurSQL Podcasts again which were on hiatus for a while (well, they have now been back again for a while as well). The Podcasts are available on several resources:

A nice extra to the implementation at dev.mysql.com is that it uses HTML5 audio, so provided that you are using a technically up-to-date browser, you can not only download the sound file of an episode, but play it right in the browser. No plugin required.

Enjoy!

New chance for crashed (port 3307) db4free.net server

One month ago I set up a new MySQL 5.5 db4free.net server instance, after the old instance started to keep crashing. Since then, the former user database server has run on port 3307 to give people a chance to rescue their data, while the new MySQL 5.5 instance started completely from scratch.

However, most of the time, the 3307 server was down and I doubt that many people had a chance to get a copy/backup of their data. Now I updated that server to MySQL 5.5 as well. It’s too early to tell whether or not that will increase people’s chances, but it may be worth to give it a new try.

db4free.net is back again, with MySQL 5.5

Disaster struck again. Well, it’s actually not really a disaster. db4free.net is a testing environment and people are not supposed to use it for any important data, or to use it in production. When after the last server update the main server instance started crashing, it was one of these circumstances which I can’t guarantee my users not to happen, and since both my budget and the server resources are low, I can’t afford setting up a backup system which allows me to restore everything just as a commercial database provider can (or should be able to) do.

Unfortunately I received a few emails (sorry that I couldn’t answer all of them directly) which made me believe that this fact isn’t obvious to all users. Even though the Conditions of Use pretty much explain that, not everybody obviously reads them. Which is why I added this text to both the registration form and to the registration email, so that it leaves no doubt that every db4free.net user is aware that this service is good for testing, but NOT for any data which is painful for the user to lose, or to not have available if the service goes down:

By registering for a db4free.net account you agree that:

  • db4free.net is a testing environment
  • db4free.net is not suitable for production
  • if you decide to use your db4free.net database in production despite the warnings, you do that at your own risk (very frequent backups are highly recommended)
  • data loss and outages can happen at any time (any complaints about that will likely be ignored)
  • the db4free.net team is not granting any warranty or liability of any kind
  • the db4free.net team reserves the right to delete databases and/or accounts at any time without notice

Sorry if I failed to make this clear enough in the past. Please note that these are no rule changes, but only a more thorough clarification.

OK, so now to the changes. After my InnoDB tablespace recovery attempts failed, it was clear that I need to set up a new server instance. Being a testing environment, it’s not really meaningful to use the now very mature MySQL 5.1 builds. So it’s time for an update, to MySQL 5.5, which is currently available as version 5.5.7, which is a Release Candidate (the last stage before becoming a Generally Available version).

Running a development version of course increases the chances of crashes and outages additionally, so it’s a good time to remember all users of the purpose of this service. This new server instance is fresh and new, no databases have been transferred and all users will have to register again to create a database.

What happens with the former 5.1 server instance? As it still starts up, I will keep it running until at least the end of November (unless anything else happens, the no-guarantee-rule keeps applying), on port 3307. So all former users can still access the database and try to rescue data they need. Please understand that I can’t offer any individual support for this. Please refer to the MySQL Reference Manual if you need assistance. You can also still use phpMyAdmin: go to http://www.db4free.net/phpMyAdmin/ and select “db4free.net:3307” from the Server Choice dropdown. However, please keep in mind that this server is damaged and will likely crash frequently, and take some time to come up again.

I am sorry for any inconvenience that the recent issues have caused. However, I am confident that this is taking the right steps to make all users aware what the purpose of db4free.net is and to set expectations right. And to all future db4free.net MySQL 5.5 users I would like to say: happy testing!

How to do user language/locale detection quickly without Zend Framework

Recently I wrote about detecting the preferred language or locale of a web site visitor, using Zend Framework.

Well, I have to start with one correction. In my last blog post about this topic I talked about the User Agent String, but since I wrote the article, I figured out that the User Agent String doesn’t play any role at all. Neither in the Zend Framework variant that I blogged about earlier, nor in today’s code. And that is good so, because both Internet Explorer and Mozilla Firefox will no longer add the browser language or locale to their User Agent Strings in the future. Other browsers are likely to follow, since there are efforts to make it harder to fingerprint users by their UA-Strings.

So here is my ZF-less variant:

$m = array();
$http_accept_language = 
   isset($_SERVER['HTTP_ACCEPT_LANGUAGE']) ? 
     $_SERVER['HTTP_ACCEPT_LANGUAGE'] : 
     "";
preg_match_all('/([a-z]{2})(?:-[a-zA-Z]{2})?/', 
               $http_accept_language, 
               $m);

$pref_locale = array();
foreach($m[0] as $locale) {
    $pref_locale[] = $locale;
}

$pref_lang = array();
foreach($m[1] as $lang) {
    if (! in_array($lang, $pref_lang)) {
        $pref_lang[] = $lang;
    }
}

The array $pref_locale will contain all locales in order of the user’s preference, and $pref_lang will only contain the languages in order of the user’s preference. Other than my ZF code from last time, this allows to look up secondary, tertiary etc. choices of the user as well.

Here is an example. Lets assume, these are the user’s preference settings:

$_SERVER['HTTP_ACCEPT_LANGUAGE'] would contain:

en-us,en;q=0.9,de-at;q=0.7,de;q=0.6,es-es;q=0.4,es;q=0.3,ja;q=0.1.

After running my code, the $pref_locale array would contain this:

Array
(
    [0] => en-us
    [1] => en
    [2] => de-at
    [3] => de
    [4] => es-es
    [5] => es
    [6] => ja
)

and the $pref_lang array would contain

Array
(
    [0] => en
    [1] => de
    [2] => es
    [3] => ja
)

Still simple enough, and most importantly, still a better solution than what Google still does.

db4free.net’s 5th birthday

To my shame I must admit, I missed it. It happened on June 29, 2005 when db4free.net was first available to the public. At that time it was running MySQL 5.0.7 beta. Quite a lot has happened since then, MySQL 5.0 made its way up to 5.0.91 and the current MySQL GA version is 5.1.49, which is also the version db4free.net is running as of today. The first phpMyAdmin version that db4free.net was offering to provide easy access to the user’s databases was 2.6.3. Today I updated phpMyAdmin to 3.3.5.

Statistics are not necessarily 100 % accurate, but here is the best I can come up with. Since its launch, db4free.net had 528,900 visits. The ratio registrations per visits is at about 22 %, so more than every fifth visit ends in signing up for a new database. Which means, that about between 110,000 and 120,000 database accounts (meaning database and user) have ever been created. There have been some cleanups since then to make resources available to people who actively use their databases. Which is why the current number of databases is much lower, at slightly above 13,400.

Today I must (also) admit that the code behind db4free.net’s web application was initially quite poor and it stayed poor for quite long. Why it was poor from my today’s point of view is probably due to my learning process in these 5 years. This year I did a lot of cleanup on the code behind db4free.net’s web application and removed a few bottlenecks which often made the website painfully slow. Now it’s in a fairly reasonable state again, and I have some ideas in the back of my head how to further improve it. So db4free.net is far from its end of life. Quite the opposite is the case, it’s time to give it a new boost so that the balance after its 10th anniversary is even more impressive than today’s 5 year balance.

How to detect the user’s preferred language – smarter than Google

Sometimes I find Google annoying. Don’t get me wrong, I am not one of the many Google haters out there. They have done amazing things and especially since they have released WebM as Open Source and royalty free video format, they have earned a bonus in my attitude towards them. And even though Google Chrome is not my personal favorite, I have to admit that Chrome has brought a great momentum into the development of web browsers (and therefor the WWW as a whole) which users of any browser benefit from. Last but not least, I use a lot of Google’s services and they do their job quite well. So all in all, Google’s record from my point of view is pretty good.

But one thing really sucks. I use an English operating system. I use an English Firefox (locale en-US). All the language preferences in the Google profiles of my accounts are set to English. Nevertheless, I often get pages from Google delivered in German by default. How they do that is quite obvious, or lets say, there is only one explanation how they may do this: they identify my home country based on my IP address. There are services that provide the data and they are easy to implement, sometimes even for free, like MaxMind‘s GeoLite Country database. Also I am using these services and I believe there is nothing wrong in doing so, except for a few things – and one of them is to identify the assumed web page visitor’s language preferences based on his or her home country.

Thankfully, I am only a lightweight victim of this practice, because I speak the language which I get served based on their guesses. But how happy would a Japanese tourist on vacation in Austria be to get his pages delivered in German, only because he uses an Austrian IP address? There are many reasons why a person located in one country may not understand the language that is spoken there. I only have to drive about 20 kilometers to get into such a country (the Czech Republic). So to assume that a person’s preferred language is the language spoken by the majority of a country (what about minorities or countries that have more than one language?) is very wrong.

What are better solutions? As I mentioned, I use an English operating system and an English browser. Almost every browser sends a user agent string. My currently most frequently sent user agent string for example is “Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US; rv:1.9.2.7) Gecko/20100713 Firefox/3.6.7”. Every web server that receives a request from a user agent (usually a browser) can read this user agent string and process it accordingly.

What can we read in mine, amongst other information? en-US. So the locale tells a web server that it’s a browser (a Firefox 3.6.7) in American English. Now guess what my preferred language will be? Is it likely that I use a browser in a language which I don’t want? Is it likely that a tourist in a different country uses a browser in a language that he or she doesn’t want? It’s certainly possible (like if there is a terminal in the hotel room or an Internet Café), but nowadays most people have their laptops, notebooks, smart phones etc. And their browser language is still their preferred one, regardless where they are currently located. So the browser language serves as a much better criterion to assume a web site visitor’s preferred language.

Alright, no big rocket science so far, and to be honest, there is no big rocket science to come. At one point, when I was annoyed about Google’s failure to identify correctly which language to deliver me, I wondered, how hard would it be to implement the (in my humble opinion) better solution, using the web browser’s locale. The answer is far from being rocket science. The solution can be as easy as this:

First, get yourself a recent copy of the Zend Framework and install it so you make its libraries accessible to your code. You can add an __autoload() function, something like this:

function __autoload($classname) {
    if (substr($classname, 0, 4) == "Zend") {
        $classname = str_replace("_", "/", $classname);
        require_once $classname . '.php';
    }
}

or even easier, use the Zend Framework AutoLoader:

require_once 'Zend/Loader/Autoloader.php';

Zend_Loader_Autoloader::getInstance();

Everything we need to identify the browser language and locale can be found inside Zend_Locale.

The constructor of the Zend_Locale class allows to set the desired language or locale. In addition to that, there are three predefined constants named Zend_Locale::BROWSER, Zend_Locale::ENVIRONMENT and Zend_Locale::FRAMEWORK. According to the Zend_Locale introduction manual page they do the following:

Zend_Locale::FRAMEWORK – “When Zend Framework has a standardized way of specifying component defaults (planned, but not yet available), then using this constant during instantiation will give preference to choosing a locale based on these defaults. If no matching locale can be found, then preference is given to ENVIRONMENT and lastly BROWSER.”

Zend_Locale::ENVIRONMENT – “PHP publishes the host server’s locale via the PHP internal function setlocale(). If no matching locale can be found, then preference is given to FRAMEWORK and lastly BROWSER.”

Zend_Locale::BROWSER – “The user’s Web browser provides information with each request, which is published by PHP in the global variable HTTP_ACCEPT_LANGUAGE. if no matching locale can be found, then preference is given to ENVIRONMENT and lastly FRAMEWORK.”

And this is what we want!

Lets get to the code:

$zend_locale = new Zend_Locale(Zend_Locale::BROWSER);

// returns en for English, de for German etc.
$browser_language = $zend_locale->getLanguage();

// returns en-US for American English, en-GB for British English etc.
$browser_locale = $zend_locale->toString();

Three lines of code to get all the information we need. And how to make a choice which language to serve based on that? Lets assume we have a site in English, French and German. French browsers should get French, German browsers should get German and everybody else should get English:

$site_language = "en";
switch ($browser_language) {
    case "de" :
        $site_language = "de";
        break;
    case "fr" :
        $site_language = "fr";
}

And that’s it. That’s all Google would have to do to make me happy. I assume, their IP address based identification code requires more lines, and is still a poorer solution in my opinion. There are additional benefits to this solution. Want to display date and time according to the locale? Here is what to do:

$zend_date = Zend_Date::now();

$date_time = $zend_date->get(Zend_Date::DATETIME_FULL, $zend_locale);

print $date_time;

My default browser now shows me “Friday, July 23, 2010 2:14:58 AM Europe/Vienna”. If I fire up a German browser, the same page delivers me “Freitag, 23. Juli 2010 02:17:11 Europe/Vienna” and there may be answers like “vendredi 23 juillet 2010 02:17:56 Europe/Vienna” (French), “viernes 23 de julio de 2010 02:18:29 Europe/Vienna” (Spanish), “pátek, 23. července 2010 02:18:53 Europe/Vienna” (Czech) or “2010年7月23日金曜日2時19分17秒 Europe/Vienna” (Japanese) as well. It’s pretty cool how the Zend Framework can make such tasks very simple ;).

Where is a solution like mine being used in the wild? I don’t know where as of the time of this writing, but I know where very soon. The MySQL website will soon launch new localized content, delivered to people who we think really want to see these languages. We try very hard not to annoy people by enforcing a language upon them which they would not like to choose.

And remember: to make good guesses is good. The best solution however is to provide choice! If you make assumptions and even if the assumptions are well-founded, always leave people the choice to select what they want. There may be situations which you are not thinking of and because of some unusual circumstances, somebody may still want to choose differently than you would think.

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:

… 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:

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

Welcome to my new home!