Recently on a client site I had to fight the pain of having no way to confirm loss of data integrity when optimizing data types. Due to MySQL’s ability to perform silent conversion of data, when converting a number of columns we enabled sql_mode to catch any truncations as errors.

sql_mode=STRICT_ALL_TABLES

This ensured that should any data truncations occur, an error is thrown not a warning. The following shows an example case study for converting an INT to TINYINT UNSIGNED and shows that without sql_mode silent conversions occur.

mysql> drop schema if exists tmp;
Query OK, 25 rows affected (0.40 sec)

mysql> create schema tmp;
Query OK, 1 row affected (0.01 sec)

mysql> use tmp
Database changed
mysql> create table t1(i1 INT NULL);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values(1),(2),(3),(256),(65536),(NULL);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0 …

Tomorrow (Wednesday, May 20, 2009), Ulf Wendel and Andrey Hristov, engineers working in the Connector team of Sun|MySQL database group, will be presenting a webinar about the new MySQL Connector/C++. This connector, which uses much the JDBC API patterns, was recently made available as a GA release during the MySQL Conference and Expo.

Register from anywhere in the world and listen to Ulf and Andrey as they talk to you about Connector/C++ from somewhere in Germany!

Addendum: At this very moment, I'm attending this free Webinar. It is truly amazing how well-coordinated this is. So, if you couldn't get to this, try to make it to some other upcoming MySQL Webinar.

Tomorrow (Wednesday, May 20, 2009), Ulf Wendel and Andrey Hristov, engineers working in the Connector team of Sun|MySQL database group, will be presenting a webinar about the new MySQL Connector/C++. This connector, which uses much the JDBC API patterns, was recently made available as a GA release during the MySQL Conference and Expo.

Register from anywhere in the world and listen to Ulf and Andrey as they talk to you about Connector/C++ from somewhere in Germany!

Addendum: At this very moment, I'm attending this free Webinar. It is truly amazing how well-coordinated this is. So, if you couldn't get to this, try to make it to some other upcoming MySQL Webinar.

Sorry for the (relatively) short notice, but I will be talking at Sun’s CommunityOne conference in San Francisco on June 1st.

I’ll be talking about, and demonstrating, the DTrace probes we have put into MySQL in a joint presentation with Robert Lor who will be doing the same for Postgres.

CommunityOne West Badge

Our presentation is on the Monday afternoon.

Check out the CommunityOne West Conference Site for more details and registration.

Introduction

A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.

Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.

Solutions Suggested By Others

DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);

DELETE a FROM a INNER JOIN b on a.id=b.id;

DELETE FROM a WHERE id IN (SELECT id FROM b)

The Problem With Suggested Solutions

Solutions above are all fine if the tables are quite small and the …

On my last count, there are now 20+ Sun Contributor Agreement (SCA) signatories whose names appear on the master list and who are interested in contributing to MySQL. Only this week, three new members signed the SCA.

These 22+ signatories have all been added since mid-February when we launched the new, Sun-compliant SCA signing process. Before the end of 2009, if we proceed at this same rate, MySQL SCA signatories list should grow to about 70 to 90 contributors.

In the meantime, contributions from many of these contributors have already been accepted and integrated. (I had earlier pointed to Armin Schöffmann's contribution as a simple example of how all this works.)

Some people continue to …

On my last count, there are now 20+ Sun Contributor Agreement (SCA) signatories whose names appear on the master list and who are interested in contributing to MySQL. Only this week, three new members signed the SCA.

These 22+ signatories have all been added since mid-February when we launched the new, Sun-compliant SCA signing process. Before the end of 2009, if we proceed at this same rate, MySQL SCA signatories list should grow to about 70 to 90 contributors.

In the meantime, contributions from many of these contributors have already been accepted and integrated. (I had earlier pointed to Armin Schöffmann's contribution as a simple example of how all this works.)

Some people continue to …

On my last count, there are now 20+ Sun Contributor Agreement (SCA) signatories whose names appear on the master list and who are interested in contributing to MySQL. Only this week, three new members signed the SCA.

These 22+ signatories have all been added since mid-February when we launched the new, Sun-compliant SCA signing process. Before the end of 2009, if we proceed at this same rate, MySQL SCA signatories list should grow to about 70 to 90 contributors.

In the meantime, contributions from many of these contributors have already been accepted and integrated. (I had earlier pointed to Armin Schöffmann's contribution as a simple example of how all this works.)

Some people continue to …

I’ve just come across the following post on new 5.1 and 6.0 replication features which was quite interesting.

A few features mentioned such as:

• pluggable replication architecture
• semi-synchronous replication
• time-delayed replication
• scriptable replication

sound like things what are going the right way. For those of still using 5.0 we’ll have to wait for quite a long time before we will actually be able to use them. At least some of this gives us an incentive to upgrade.

No mention yet of N:1 replication, or making the replication process separate from the main mysqld process. The latter is much easier now and would only require minimal hooks in a plugin at each send of the replication path. However, these are things that can come in the …

MySQL has deep roots in open-source software development communities and code contributions to MySQL keep flowing in, being reviewed and integrated into the MySQL. 

During our BoF at the MySQL Conference and Expo, Lenz Grimmer talked about our work to make MySQL (even more) contributor friendly, with some more focused effort starting on February 1, 20091.

The desire to contribute code to MySQL remains as strong as ever as evidenced by this year's MySQL Conference and Expo, where I had an opportunity to speak with some contributors and partners who wish to contribute to MySQL. Of course, there are a lot of strong and varying opinions in this area.

As I said above, code has been contributed and absorbed in MySQL (according to either the MySQL …