While there are some major annoyances with transactions in MySQL like causing an implicit commit when doing DDL statements, there are also slightly more subtle annoyances. A user just stumbled over a little annoyance in MySQL transaction handling and autoincrement. If you generate a new ID inside a transaction and rollback, the autoincrement counter is not decremented.

Of course I know that autoincrement is just about generating new unique id's and I should not worry about gaps and that for most people the value range for INT is more than they will ever need, not to mention BIGINT (though BIGINT really sucks in PHP as its larger than the natively supported value range for integers in PHP). However this issue goes to show again that a ROLLBACK is not the magic undo-wand that people often expect.

mysql> create table autoinc (sequence int auto_increment primary key) engine=innodb;
Query OK, 0 rows affected (0.59 sec)

mysql> …

Two days ago we added the University of Illinois/NCSA Open Source License to the list of licenses in the MySQL FLOSS license exception. In my opinion exception is actually a badly chosen word - extension would probably be more sufficient here as it actually extends the scope of the MySQL client libraries license. From the introductionary text on top of the document:

We want specified Free/Libre and Open Source Software ("FLOSS") applications to be able to use specified GPL-licensed MySQL client libraries (the "Program") despite the fact that not all FLOSS licenses are compatible with version 2 of the GNU General Public License (the "GPL").

In other words, the license exception allows the distribution of a derived work including the MySQL Client …

Two days ago we added the University of Illinois/NCSA Open Source License to the list of licenses in the MySQL FLOSS license exception. In my opinion exception is actually a badly chosen word - extension would probably be more sufficient here as it actually extends the scope of the MySQL client libraries license. From the introductionary text on top of the document:

We want specified Free/Libre and Open Source Software ("FLOSS") applications to be able to use specified GPL-licensed MySQL client libraries (the "Program") despite the fact that not all FLOSS licenses are compatible with version 2 of the GNU General Public License (the "GPL").

In other words, the license exception allows the distribution of a derived work including the MySQL Client …

database, declarative, procedural, SQL transactions

Developers* seem to think of a transaction as a block of code. A try/catch block seems analogous at first glance — try to do a series of actions, and if one fails, roll them all back, else commit. So many people trip up and try to write code within a transaction to avoid deadlock or race conditions.

This is the source of the problems with the commands that perform an implicit COMMIT (that links to the 4.1 and under …

database, silent failure transactions

At the July MySQL User Group Meeting, Jim Starkey wondered aloud, “What happens when I COMMIT on a memory table?” I wrote the question down, to research it later.

The obvious answer is “COMMIT on a non-transactional table does nothing.”

Tonight I was thinking about this, and I realized I do not actually COMMIT “on a table.”

The manual page at: …

Do you know how to best allocate the data nodes to different computers to get maximum availability? Or do you know how to do it, but are curious about why it should be done in that particular way? I just read an internal discussion at MySQL and realized that people might be interested in this.

Assumptions
Lets assume that we have a MySQL Cluster with twelve data nodes (ndb processes) and two replicas (copies) of each data fragment. This means that there are twelve ndbd processes that store the data and that each piece of information (fragment) exists in two copies.

Then assume that six fragments of data is distributed on the data nodes as follows:

• Data nodes D1 and D2 store the same information (fragment F1),
• D3 and D4 store F2,
• D5 and D6 store F3,
• D7 and D8 store …

The latest issue of (IN)SECURE magazine, a freely available digital security magazine discussing some of the hottest information security topics, is featuring a reprint of my introduction to MySQL Sandbox.

I came across this opinion piece by Paul Barton, an attorney at Field Fisher Waterhouse LLP today. I wish he would have attended the Open Source Business Conference before writing his piece. (OSBC includes, among other things, two full days of legal education on open source.) He could have saved himself the embarrassment of misinformation. (I won't call it malpractice. :-)

(Btw, I am an attorney. I don't play one on TV.) (Unfortunately.)

First off, Paul is clearly talking about "in the wild" open source, whereas most enterprise open source adoption is of commercial open source (Red Hat, MySQL, JBoss, etc. etc.). It's true that Red Hat doesn't own the code (or most of it, anyway) that it ships, but this is emphatically not true of virtually every other piece of commercial open source …

As part of my job at Pentaho, I occasionally get to help build solutions using the Pentaho BI platform and the solution building toolset, Design Studio, Report Wizard and Designer. I really enjoy that time, in part because I miss my developer days, but also because using the software we build makes me feel like I can connect and relate more to our community. The end product is I can do my job better, because I know the project and the software better.

I recently started creating some reports against our case tracking data, which lives in a JIRA database. JIRA is a phenomenal issue tracker, but I just can't get the reports and data analysis out of it that I really need. And that's OK, that's what we have the Pentaho platform for. One thing I came across that I thought would be good to explain a bit is the difference between "Save As" and "Publish" in our report …

Anyone who has heard me speak or give a webinar knows I repeatedly harp on a single concept that comes up time and time again when discussing software architecture, performance tuning, database development, or really anything technical. That is, there are few real absolutes in the art of software development.

I know that one of the most annoying answers to any question is "it depends". But, unfortunately, this is an accurate answer for many questions that have the form "What is the best way to do...".

A question that was asked during Tuesday's webinar on Tagging and Folksonomy schema choices was the following:

Could you tell us the best practices for managing summary or statistic data in the database?

I responded online that I would write a blog entry with answers to that question, and I that blog article is currently underway and should be available soon. However, I want to point out that there really …