The latest from the MySQL community

You’ve probably seen the MySQL Reference Manual page “What’s New in MySQL 6.0″ and so you’ve seen this description of a new feature: “Support for extended comments for tables, columns, and indexes.” Here’s what “extended” means.

You can do a little more with COMMENTs in ALTER or CREATE statements. The easy way to see the difference is by running this statement in both MySQL 5.1 and MySQL 6.0:

SELECT table_schema, table_name, column_name, character_maximum_length

FROM information_schema.columns

WHERE column_name LIKE '%comment';

From that, you’ll see that the differences are:

TABLE_NAME     COLUMN_NAME      CHARACTER_MAXIMUM_LENGTH CHARACTER_MAXIMUM_LENGTH

                                in MySQL 5.1             in MySQL 6.0

COLUMNS        COLUMN_COMMENT   255                      1024

PARTITIONS …

It's nice to be able to make a difference.

Since David Axmark and I started to work on MySQL we also took a strong stand against software patents. MySQL AB have been sponsoring several efforts to prevent software patents in Europa.

Now David and I are continuing to do this outside of MySQL AB. We just gave an economic sponsorship to the Patent Lens, who recently lost their main sponsor, so that it can continue it's important work in making the patents system more accessible.

Here is a short description of Patent Lens from it's founder, Richard A Jefferson:


We are working to generalize the Patent Lens as the informatics platform of the Initiative for Open Innovation, which aspires to render worldwide patent systems …

I hadn’t run a race since 2003, but I have improved upon my amount of running and thought it would be good to see whether it would show in my race time. It did!

Thumbs up after a half marathon finished in record time

“From a fat bastard to a running dynamo” was what a fellow member of the MySQL Running Club SMSed me when I had proudly announced my personal record of 1:46:05 (as measured “on the safe side” by my own watch; the organisers timed me at 01:45:58) on the Münchner Stadtlauf half marathon in Munich today.

Full of energy before the race

While I may never have fully qualified for the attribute “fat”, I certainly was very bad at sports at school. My average speed …

Dear reader, this is a challenge. How’s your MySQL prowess? You know about LIMIT: it cuts off the results at the specified number.

mysql&gts; select actor_id from sakila.actor where actor_id % 5 = 0 limit 5;
+----------+
| actor_id |
+----------+
|        5 | 
|       10 | 
|       15 | 
|       20 | 
|       25 | 
+----------+
5 rows in set (0.00 sec)

But that query actually accessed 25 rows. What if I want to say “return up to 5 rows, but don’t read any more than 20 rows to find them?”

Right now I’ve got the following:

mysql> select actor_id, @rows
    -> from actor, (select @rows := 0) as x where
    ->    ((@rows := @rows + 1) <= 20)
    ->    and actor_id % 5 = 0 
    -> limit 5;
+----------+-------+
| actor_id | @rows |
+----------+-------+
|        5 | 5     | 
|       10 | 10    | 
|       15 | 15    | 
|       20 | 20    | 
+----------+-------+
4 rows in set (0.00 sec)

The …

A customer of ours was in this particular situation. They had a very decent hardware load balancer for their webservers with capacity to spare. So they ended up load balancing the mysql instances through the same device and using a piece of software I've written called mylbhelper.

A few days back I read the Workbench Team’s blog and was curious about the printing capabilities of MySQL Workbench Community edition. As we already know by now, it only allows you to print a single page. I needed to review a customer query which had several tables and some complicated relationships, so I decided to take Workbench Community for a spin (I already knew the Standard edition from my previous job) and tested the following steps:

1. imported the database after doing a mysqldump -d (Workbench Community can’t connect to the database to get the schema directly).
2. had Workbench rearrange the diagram.
3. followed the Workbench Team’s blog instructions to spread it among more pages, since it ended really cramped.
4. created a PDF file, which was barely readable on paper.

Here’s where the heavy …

The book is done now, right? What’s next?

Don’t tell my wife this, but a book is never done.

Right now I’m proofreading the printed copy. I proofread PDF after PDF during production, but some problems will always slip through and make it to paper. I’m finding quite a few little mistakes. For example, at one point we refer to TPC as TCP three times in a row. Oops.

These problems will be corrected in the next printing. Please notify me if you find any errors yourself, and I’ll add them to the list of things to fix! Also let me know if you find things that should just be “fixed” in general. For example, the layout and page-breaking on pages 364 and 365 is totally confusing — it’s hard to tell which figures are associated with which text.

I’m not offering rewards like Donald Knuth, sorry…

I will place a list of errata on the official …

According to Johans wishes I write down my concerns about MySQL (Cluster) replication. These items are things I run again and again into it with customers:

• SQL-nodes are still loosing too easy connection to cluster after data node or management node restart (which leads into gaps, see next point). Automatic fail over or reconnection is just a dream (maybe it works in about 90% of the cases at least).
• Gaps: Whenever I do a cluster reconfiguration (should not be necessary too often, see loosing connection above) or a mysqld restart I get a gap (these are the planned ones, see also automatic channel fail over). Then we have the not planned ones...
  I cannot understand, why we are not able to keep at least a certain amount of traffic in a binlog-injector-buffer to avoid the majority of these gaps. This could be something like a Round-Robin buffer which …

According to Johans wishes I write down my concerns about MySQL (Cluster) replication. These items are things I run again and again into it with customers:

• SQL-nodes are still loosing too easy connection to cluster after data node or management node restart (which leads into gaps, see next point). Automatic fail over or reconnection is just a dream (maybe it works in about 90% of the cases at least)..
  

• Gaps: Whenever I do a cluster reconfiguration (should not be necessary too often, see loosing connection above) or a mysqld restart I get a gap (these are the planned ones, see also automatic channel fail over). Then we have the not planned ones...
  I cannot understand, why we are not able to keep at least a certain amount of traffic in a binlog-injector-buffer to avoid the majority of these gaps. This could be something like a Round-Robin buffer which stores all …

Found or rediscovered a bunch of nice software this week

• Spreed.com web-based meetings, with shared whiteboarding, application sharing, video, and soon mindmaps.  Why is it noteworthy? They explicitly support linux, even for whiteboarding and app sharing. The whiteboard is geared toward making notes on top of a presentation, and participants can easily generate a PDF of any page. The linux screen sharing client is written in python, and the mind maps (coming soon) promise to be compatible with FreeMind.
• Coccinella instant messaging client with shared whiteboards. What I really want is OmniGraffle for Linux, with the ability to share drawings with someone on the other side of the world and both edit in realtime. Inkscape’s Inkboard feature would be even better, if it worked, but it …