The latest from the MySQL community

MySQL pre version 5.0 was very lax in it’s management of valid data. It was easy for data integrity to be abused if you knew how. The most common examples were truncations and silent conversions that if not understood could provide a serious data integrity issue.

In version 5.0, the introduction of SQL_MODE solved this problem. We will look at one example of how SQL_MODE can be enabled to provided improved data integrity.

You want to store the individual RGB (red/green/blue) decimal values of colors in a table. Each of these has a range from 0 to 255. You read that you can store 255 values in a TINYINT Integer data type, so you create a table like:

DROP TABLE IF EXISTS color_to_decimal;
CREATE TABLE color_to_decimal(
name VARCHAR(20) NOT NULL PRIMARY KEY,
red    TINYINT NOT NULL,
green TINYINT NOT NULL,
blue   TINYINT NOT NULL); …

Sun continues to take a performance pounding, and the rumors of replacements, layoffs and revamps are beyond swirling and now perpetuating skepticism of the company. It strikes me as odd that Sun, which has embraced open source and is also the defacto leading corporate open source software contributor, is continually dogged by doubts about its transitions and tenures despite well-respected technology and participation in open source. Part of this lies in the company’s continuing dichotomy in strategy — a reference to tepid support for Linux and continued preference for and focus on Solaris. This is a large part of Sun’s ‘handicap,’ IMHO when it comes to Linux and open …

Sun Microsystem’s (NASDAQ:JAVA) hit a low this week of $8.71. There was a stronger rally and a close at $9.16 today. The financial times reports Sun Micro chief sees rays of hope, and Bloomberg Sun Rises After Fourth-Quarter Profit Tops Estimates.

I cashed out in March at $16.32, so that’s like a 50% drop in share price. I was lucky having been at MySQL long enough to have options to vest. Newer employees are not that lucky. I certainly hope MySQL Sun Employees get the Q4 weighted bonuses. (A structure I didn’t believe compensated with the old bonus structure).

I have been following more closely since Matt Asay’s comments …

I have been getting a lot of comments and errata from people who seem to be mistakenly buying the first edition and believing it’s the second edition. A lot of the blame for this probably rests with Amazon, who did not distinguish between the two editions at all until the editor and I (among others) leaned on them persistently for about 6 weeks. I think some people are buying the second edition and getting the first edition.

I’ve even spoken to people in person who said “yeah, I’ve been reading it” and I give them a copy of the second edition to hold in their hands, and they go “whoa, that is like twice the size. I don’t have this edition at all.”

If you have any question at all, just look at the front cover. If you have the second edition, you will see it clearly in the upper right-hand corner of the cover, as shown in this picture:

I feel like a Microsoft Anti-Piracy Minion “educating” you about how to …

Percona has a strong team of MySQL developers and consultants on board, so we decided to prepare MySQL releases with our patches and third-party patches which we think are very useful. We actually use these internally and for our customers.

Current releases include:

• microslow patch (enables microsecond resolution in slow logs)
• execution plan (show info about query execution in slow log)
• InnoDB statistic (show extended InnoDB usage during query execution in slow log)
• Google's patches with user statistics, and statistics about tables and indexes usage

You can find more info about some of our patches here:
http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-patch-installation-walk-through/
Some features were …

Its worth noting that MySQL will have a big presence at OSCON 2008. All this, thanks to Sun, as Sun’s a fairly big sponsor (Platinum Sponsor), and we’ve got a humongous booth!

There are booth talks, that you must attend! They range from getting started with the MySQL Community, MySQL 5.1, MySQL Workbench, MySQL Cluster, and many more. Expect to see the schedule up at the booth (and yes I know, there are a whole heap of already interesting talks in the program, but a lot of the talks at the booth are also scheduled during breaks).

Today, I saw the ad copy, which reads “Our Booth is Your Booth”. “We have whiteboards, tables, electrical outlets and fresh coffee. Come to converse, share ideas, participate or simply to vegetate.”

Seriously, …

I have been getting a lot of comments and errata from people who seem to be mistakenly buying the first edition and believing it’s the second edition. A lot of the blame for this probably rests with Amazon, who did not distinguish between the two editions at all until the editor and I (among others) leaned on them persistently for about 6 weeks. I think some people are buying the second edition and getting the first edition.

One of our developers ran into the strangest error a few days ago, which neither the MySQL manual nor Google searching could find any information on. When he called certain stored procedures in a certain order, he got "Error 153 returned from storage engine." It's been reduced to a very simple test case, and a bug report has been filed. Here's the test case:

DROP TABLE IF EXISTS foo;
CREATE TABLE `foo` (
`a` int(11) NOT NULL auto_increment,
PRIMARY KEY  (`a`)
) ENGINE=InnoDB;

begin;
insert into foo values();
savepoint a1;
insert into foo values();
savepoint a2;
insert into foo values();
savepoint a1;
insert into foo values();
savepoint a2;
show warnings;
rollback;

The very last "savepoint" generates the warning "Got error 153 from storage engine". Any other MySQL'ers use savepoints and …

Part of updating the MySQL Certifications is trying out all the new features that have appeared with 5.1 and 6.0. Currently I am working with 5.1.24-rc on a Mac and 6.0 on Ubuntu and looking for items to add to the exams. The Event Scheduler is sure to become one of those Swiss Army Knife tools that we will wonder how we ever accomplished anything without.

I usually experiment with very simple tasks.

CREATE EVENT x1_event ON SCHEDULE AT '2008-07-16 14:01:01' DO INSERT INTO x1 VALUES (9,'nine');

SHOW EVENTS did exactly that. And just after 14:01, I found the new row in my x1 table.

So now I am thinking of a dozen think I used to do with cron(1) or at(1) that I could do with the event scheduler.

Warning: There are ramifications with backups and the event scheduler that I will not go into details here. But for now do not try to run your backups with event scheduler. The …