This is the 182nd edition of Log Buffer, the weekly review of database blogs. Make sure to read the whole edition so you do not miss where to submit your SQL limerick!

This week started out with me posting about International Women’s Day, and has me personally attending Confoo (Montreal) which is an excellent conference I hope to return to next year. I learned a lot from confoo, especially the blending nosql and sql session I attended.

This week was also the Hotsos Symposium. …

Continuing on from preparation in our MySQL latin1 to utf8 migration let us first understand where MySQL uses character sets. MySQL defines the character set at 4 different levels for the structure of data.

• Instance
• Schema
• Table
• Column

In MySQL 5.1, the default character set is latin1. If not specified, this is what you will get. For example.

mysql> create table test1(c1 varchar(10) not null);
mysql> show create table test1\G
Create Table: CREATE TABLE `test1` (
  `c1` varchar(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

If you want all tables in your instance to always be a default of utf8, you can changed the server variable character_set_server. This can be set dynamically.

mysql> set global …

Before undertaking such migration the first step is a lesson in understanding more about how latin1 and utf8 work and interact in MySQL. latin1 in a common and historical character set used in MySQL. utf8 (first available in MySQL Version 4.1) is an encoding supporting multiple bytes and is the system default in MySQL 5.0

• latin1 is a single byte character set.
• utf8 is a 1-3 byte character set depending on the size of the character. NOTE: MySQL utf8 does not support the RFC 3629 4 byte sequences

MySQL variables

MySQL has a number of different system variables to consider, the following is the default representation in MySQL 5.1

mysql> show global variables like '%char%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value …

We've got another round of free Windows-centric webinars coming up tomorrow:

Migrating from SQL Server to MySQL (North America)...covering the fundamentals of why and how to migrate, plus an apples to apples feature comparison. Register here

Windows Server 2008 Failover Clustering with MySQL (EMEA)...Mike Frank will run through step by step how to set up a development environment with minimal cost/effort that implements Windows Clustering and MySQL. Register here

Finally, check out LiveTime's presentation next week where they will demonstrate the methodology and toolset they use to perform free SQL Server to MySQL migrations for their customers. Register …

Join us for a free webinar tomorrow where we will present the fundamentals of migrating a sample application from Microsoft Access to a MySQL back end. We will be giving the presentation twice, at 14:00 Central for EMEA and 10:00 Pacific for North America. To register, visit here.

A while back I was at a seminar on migrating database applications to MySQL. A lot of the attendees were Oracle users. Based on their questions, comments and conversations during lunch, I made the following list of things Oracle users need to know about migrating to MySQL. Most of these are “gotchas” that would be contraindications or require some thought about a workaround.

Note: this is not meant to be MySQL-bashing. Some of these limitations are going to be fixed in future versions of MySQL, but they generally apply to current GA version 5.1. Some things are possible to achieve by choosing one particular way to use the server, at the exclusion of other things (e.g. Cluster rules out foreign keys, spatial data types rules out transactions). I am posting this list at the request of some Oracle DBAs who asked me to produce it for them.

1. Subqueries are poorly optimized.
2. Complex queries are a weak …

I just finished reading the recently released 2nd Edition of High Performance MySQL by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy Zawodny, Arjen Lentz & Derek Balling.  I’ve posted a review here on Amazon.

Wow, that’s quite a list of authors, but when you look at the material, you see why.  This book is a very indepth look at the MySQL server.  Intended for the intermediate to advanced DBAs and developers who want to know the inner workings of the server, as well as how to use many of it’s advanced features.

For instance the chapter on replication was quite good.  Given that you probably setup replication in five minutes, and are wondering weeks or months later why it’s not working, this chapter will give you …

“So, I suppose MySQL’s main competitor is Oracle?” is a frequent question I get asked by the press. “Well, we don’t really compete heads-on with other databases. We co-exist! Just as an example: Over a third of respondents in an Oracle User Group survey said they also use MySQL”, I answer.

The reporter then continues “But everyone has a main competitor. Don’t you plan for people to migrate from Oracle to MySQL?”. I continue with “Not really. Migrations do happen, but not all that often. MySQL tends to be used in new applications.”

“But surely you must have some competitive atmosphere, or equivalent feelings towards Oracle.” The reporter never gives up. “Don’t you at least internally joke about your relationship with Oracle?”.

And that’s where I will now have a new answer for whichever reporter nexts goes …

First up, I want to say, I’m truly impressed with Brazil. One day I will visit this amazing place, and spread the good word of open source with projects that are close to my heart: MySQL, OpenOffice.org, Fedora, and in due time, a lot more. This is a live-blog, from a most interesting talk, at JavaOne 2008. As I wrote on Twitter, “Brazil, simply impresses me. Their use of open source in government, makes me think that the rest of the world has a lot to learn from them”.

Free and Open Source Software: Use and Production by the Brazilian Government
Rogerio Santana <rogerio.santanna@planejamento.gov.br> +55 61 313 1400, Logistics and Information Technology Secretariat
Planning, Budget and Management Ministry
Brazilian Government

Households with Internet access: 70% in the US4k household income range. …

I took the opportunity today evening to get myself upgraded (from 4.5.3_GA_733) to the latest (5.0.0_GA_1869) open source version of Zimbra - ZCS 5.0 GA. The database migration took about the longest, mainly due to some schema changes. Lots of starts and stops to the database. Its now running MySQL 5.0.45 Community.

What prompted the upgrade? A few days ago, I got a bunch of new packages, and rebooted the server (new kernel). To my dismay, Zimbra started to have issues - amavisd wouldn’t start. This meant that there was a large amount of mail, sitting in the queue, not being delivered. Things you don’t normally check for, immediately, anyway.

Turns out Compress::Zlib was too old. Well, not the system provided Compress::Zlib, but the Zimbra provided Compress::Zlib. Kind of annoying when there …