Kai Voigt

Having a smart class this week in Amsterdam, I needed to make up some additional exercises. One was inspired by Tobias. Here it goes.

Among all European non-capital cites where English is not official, which is the one with the highest number of English speaking people?

Solution:

SELECT City.Population*CountryLanguage.Percentage AS Nmb,City.Name
FROM City JOIN Country ON City.Country = Country.Code
JOIN CountryLanguage ON Country.Code = CountryLanguage.Country
WHERE Country.Capital <> City.Id
AND Continent = 'Europe'
AND CountryLanguage.IsOfficial = 'F'
AND CountryLanguage.Language='English'
ORDER BY Nmb DESC LIMIT 1;

It's been a while that I spoke at a larger conference, this time at the Open Source Database Conference 2005 in Frankfurt.

Besides meeting a lot of collegues and other attendees of the OSDB Conf as well of the PHP International Conf, I presented the new features in MySQL 5.1

I put a copy of the slides on my website. Cheers!

You will need the World Database to work on this question.

Some solutions follow. If you need any faster way, please leave it as a comment.

SELECT Country.Name
FROM Country
JOIN CountryLanguage
ON Country.Code=CountryLanguage.Country
WHERE Language='French'
AND Country NOT IN (SELECT Country FROM CountryLanguage WHERE Language='English')

SELECT DISTINCT Country.Name
FROM Country
JOIN CountryLanguage AS cl1 ON Code = Country
LEFT JOIN CountryLanguage AS cl2 ON cl1.Country = cl2.Country
AND cl2.Language = 'English'
WHERE cl1.Language = 'French'
AND cl2.Language IS NULL

You all know this number puzzle called Sudoku. You get small applications to create and to solve them. It's like the tetris of the 21. century.

I'm sure there is software written in C, Perl, Lisp, Assembler, Java to implement all those. But as you know, I work for a database company which delivered the new and great 5.0 version almost in time.

Our favourite language is SQL and we are able to write stored procedures now which enable us to port almost any existing software into plain SQL!

Per-Erik Martin, a collegue of mine, once wrote a Sodoku solver in C and now ported it to SQL. Enjoy!

Second Munich week in a row, Cluster class. New training room, flaky network cables and evil SuSE firewalls. Well, we survived somehow. Here are some open questions.

Static or Dynamic Binaries on Linux, when to use which one?

On modern Linux systems, the shipped glibc comes with a new POSIX threads library (NPTL) which should work just fine in most cases. Use the dynamic binaries then.

The static binaries include a patched version of the glibc library that is able to create up to 4096 threads. If the default of 1024 threads is not enough for you, use the static binaries.

How to load balance the layer of mysqld servers in a cluster?

Use some load balancing solution, like Linux Virtual Server or some hardware based product. If you have a lot of small queries that a simple round robin system will do fine. Otherwise, you have to build your own metrics.

Will "ALL RESTART" in nbd_mgm restart all data nodes in the right order?

No, the data nodes will be shutdown all together and restarted then. I.e, you will have some time where the cluster is not available.

To perform a rolling update, run " RESTART" for each data node. Make sure that the node is back in the cluster and continue untill all data nodes have been restarted.

This is the first article about questions I get during MySQL classes. These classes are usually very interactive and people ask a lot of questions. A frequent answer is "Try it!", e.g. "How many columns can a MyISAM table hold?"

But some questions are more advanced and sometimes even the trainer doesn't know the answer. This is nothing bad because even Monty will not know everything. Trainers try to get answers to those difficult questions by reading the online documentation or by asking collegues on IRC or by mail.

The latest class of mine was Using and Managing MySQL, the most popular MySQL class about SQL and adminstrative tasks.

Here we go.

TRUNCATE TABLE does reset the auto_increment counter on MyISAM tables, but not on InnoDB tables?

This is actually a bug in InnoDB and will be fixed eventually.

I'm running with --safe-updates but I cannot delete rows with "... WHERE keycol LIKE '%'", why?

This options will only let you delete or update rows when you use a WHERE clause that uses an index. Example: We have the following table.

CREATE TABLE su (id VARCHAR(100) PRIMARY KEY NOT NULL, name VARCHAR(100))

Then have a look at the following two queries.

mysql> DELETE FROM su WHERE id LIKE '%foo';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

mysql> DELETE FROM su WHERE id LIKE 'foo%';
Query OK, 0 rows affected (0.00 sec)

Will OPTIMIZE TABLE lock the table?

Yes, according to the docs.

There's a three pages article on MySQL Cluster n the latest edition of the German iX magazine. It's about reasons for using MySQL Cluster and it contains a simple configuration example derived from the online documentation. It's not covering much details like backup, system requirements and more complex crash scenarios. To learn more about this, order the Cluster class

Enjoy reading and please give feedback. Thanks.

There are of course various ways to prepare for MySQL certification. Some ideas come to mind.

• Take a 4 weeks vacation with the Study Guide and read, read, read
  
• Try to bribe the certification team (unfortunately just one guy, 1.96m tall)
  
• Pray to god or do some other voodoo
  
• ...
  

I passed Core exam and failed Pro exam on my first attempt and did the Pro exam again during the MySQL Consulting Bootcamp in Munich a couple of weeks ago. I'm still waiting for the results, wish me luck.

Soon, there will by MySQL 5.0 (release candidate is out!) with a lot of new features. And of course, new certification exams need to be created. I'm in the lucky/unfortunate/painful/... situation to help writing items for the new exams.

Writing questions and answers yourself is a great training to get to know all the little nasty details deep inside MySQL. So, this is possibly one of the best ways to prepare for an exam. It would of course be more than embarassing to fail on your own questions

On public demand I added a list with my upcoming projects/tasks in the next couple of weeks. You'll find the list on the right hand side of this website.

Feel free to get in touch with me if you're in the same city at the same time as I am.

Note that my schedules might change at almost every time. I learned not to plan more than 2 or 3 weeks ahead.

They did it! The Kiel football team, the Baltic Hurricanes won the championship of the northern second German football league. Now, they have the chance to get into the major league of German football. They need to fight against the Düsseldorf Panther, the worst team of the northern major league.

But winning the championship was a hard piece of work. The Canes had to beat the leading team, the Berlin Rebels by at least 21 points as Berlin won their home game 20:0 some weeks ago.

Some exciting touchdowns at the end of the first and second half made the dream come true and at the end, Kiel won 32:10. Of course, the Berlin team and coaches were very upset, but they were good losers. I'm sure they will follow into the major league next year.

This is a short note from the Cafe TEBA in Cologne, Hamburger Straße 12. I met here with Gowry yesterday to see him again after many years. We had a plenty of beers, a tasty water pipe and delicious Egypt food.

So, if you ever come to Cologne, visit this place. You'll find nice people and can listen to easy music.

To go there, take the S-Bahn to Hansaring and you're right on target. Check the routing details on the website.

Cheers

Of course, you all know the OpenBC system. You register for free and submit your business data, like the name of your company, your main focuses, how to contact you, what you're looking for, etc.

Then you look for people you know or want to know and make "friends" with them. OpenBC calls it "contacts".

I added contact after contact since I joined OpenBC in 2004. The more you are involved in consulting projects the easier it is to fill you contact list with people.

Today, Sebastian Bergmann, invited me to be on this contacts' list. Of course, I accepted.

"Good news, everyone". It's now public news that Kris will join MySQL in a couple of months. Jan and I asked him a while ago if he'd like to start working for MySQL as a consultant. After some interviews, the contracts were signed and I'm looking forward to work with Kris again.

This makes four old people from the good old SH Online and NetUSE times to work for MySQL. Actually, we're also all in the same deparment, Professional Services. Together with Kris, Jan and Ulf, we started our MySQL/PHP knowledge by developing early ecommerce projects and intranet applications. After going separate paths in different companies, it's good to have the old crew in one team again.

I wonder if we'll find other people to join the "fantastic 4". Tatjana, Boris, are you reading?

I've been lazy for the past few weeks, either because of lack of internet access or because of too much to do. This is just a short summary of the rest of my California trip.

My first weekend in San Francisco ended with a great Sunday when Carol picked me up at my hotel to drive to Marin County. We had a nice walk in Sausalitos and watched the 1000+ years old redwood trees. After seeing the beach and the Pacific Ocean at Cliff point, we had late lunch in a Korean BBQ restaurant. Then I attended some other Baseball game, again the Giants lost...

The following week, I attended Sarah's "Using and Managing MySQL" class in Cupertino. She did great and I got a lot of ideas for my upcoming classes. The following weekend was spent in Carmel, Pebble Beach, Monterey and another night in San Francisco. My plane back to Germany took me to Frankfurt. The following days, I attended Jan's "MySQL Cluster" class. Again, a lot of new input for my brain.

Finally, after one day of MySQL Cluster consulting with Hartmut, I made my way home to Kiel. Time to gain some new energy before the next trip is taking me to the next locations, probably Cologne.

Sightseeing was on schedule for Saturday. I got up early in the morning and bought a 3-day-ticket for muni buses and cable cars. I started at Powell station and took the cable car up to Fisherman's Wharf. The ride on the cable car was fun, lots of laughters with the other tourists while going up and down through the streets.

I had a good view to Alcatraz from the pier. I decided to have a late breakfast and got some clam chowder in a bread bowl, very tasty. I met Raol and Rocky from Canada there who where on their way to Los Angeles. Very pleasant to get in touch with people that easily. The fog had disappeared and I continued my explorations towards Pier 39 where I could resist to buy SF merchandising. Lots of tourists around and lots of things to buy and eat.

Walking in the sun made me thirsty, so I was happy to find to find the Hard Rock Cafe right at Pier 39. I was setting next to Randall from Wales who was visiting family in Berkeley and we had some Anchor Steam, a local San Francisco beer. I liked it and my prejudices against American beer almost vanished. We decided to meet again later in town as I wanted to continue my tourist route.

I headed east towards Coit Tower and had to climb a million steps to get up to Telegraph Hill. But it was worth it. After taking the elevator to the top of the tower, I was rewarded with a great view all around San Francisco. Sun was shining and I took lots of pictures. I wonder if I will be able to glue them all together to get some panorama picture.

Then, I went down and made my way all along Lombard Street to finally arrive at the most crookedest street. I saw it on pictures and on TV before, but it looks much more weird in reality. I went the street all up, another exhausting activity to get to Hyde Street. There, I enjoyed the view back over Lombard Street to Coit Tower and I was proud to have walked all the way.

Then I got lazy and took the Cable Car to Chinatown. It was great to walk Grant Avenue and peek into the small streets, feeling like I left the western hemisphere and arrived in some other part of the world. A lot of small stores selling strange things, cute restaurants and of course some tourists traps. After having dinner in Chinatown, I walked around the financial district a bit as it started to get dark already.

I returned to the hotel, dropping all my stuff I was carrying around and was picked up by Randall. Again, the Gold Dust Lounge at Powell Street was the destination for some more Anchor Steam beer. Short after midnight, I returned home as I dated with Carol from MySQL on Sunday for some more long range sightseeing.

Pictures of this day and others will eventually appear in a gallery section on my homepage.