Recently I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking and the process list was filling up with queries in Locked status.

My first thought was that the customer had deleted from the table, which leaves "holes" in the middle of it and prevents concurrent inserts. (You can configure the server to permit concurrent inserts even when there are holes, but it's disabled by default.) However, that turned out not to be the cause; the table was only inserted into (and selected from). Instead, the blocked statements were because of INSERT... SELECT statements that were running against the table, selecting data from it and inserting into another table.

Let's look at what happens here: suppose you have two tables tbl1 and tbl2 and concurrent inserts into tbl2 are running fine. If you now run the following query,

…

One of the more recent additions to the SiteCrafting CMS arsenal is a comprehensive error logger, tracking all PHP and MySQL errors (by default... other error types can be created on a case by case basis) that occur in new sites we build. Errors are stored in our own intranet system with a timestamp, error body and a site ID (assigned to each client at a different stage of our project workflow). The table looks something like this:+----+---------------------+---------------+---------+| id | logTime             | text          | project |+----+---------------------+---------------+---------+|  2 | 2008-05-14 14:42:15 | A PHP Error   |       1 ||  3 | 2008-05-14 14:42:26 | A PHP Error   |       1 ||  4 | 2008-05-14 14:42:34 | A PHP Error   …

The 451 Group’s Commercial Adoption of Open Source (CAOS) Research Service now has a podcast (iTunes or RSS feed).

A month or so ago, I was having a conversation with The 451 Group’s Vice President of Research Services, Simon Carruthers, about ways to expand the offerings of the CAOS Research Service. The CAOS Research Service includes aspects that are public (such as this blog), but the majority of the work that we do is accessible only to our paying clients, namely our research articles and reports, as well as our advisory service.

We made the decision to add a biweekly conference call …

I had an interesting discussion with Marten Mickos at JavaOne last week that I've been meaning to blog about. I was disappointed that MySQL decided to put encryption and compression backup into MySQL Server (GPL license), versus including those features only in MySQL Enterprise (commercial license). Most of you will recall the outrage from "the community" that began when MySQL considered adding these enterprisey features only inside of MySQL Enterprise. I wanted to discuss this situation with Marten. I do not believe that Support and/or Monitoring around an OSS product are viable long term value propositions that will convince users... READ MORE

As you probably know, or at least heard, we are currently porting Workbench to Linux. Generally speaking the porting process is split in several stages. The first one is to compile non-GUI Back-End which represents about 80% of the total application code. The next stage is to ensure that unit-tests are run correctly for the ported stuff. The third is to create user interface and to bind it to the back-end/core. After that we will have alpha version of Workbench for Linux.
Regarding tests, actually a portion of unit-tests are already passed. These are 121 of 122 going well. At the moment we are working on non-GUI back-end, and core part is compiled and run, so now the modules and plugins are in progress. I must admit that process of porting is pretty smooth, most of the code has already been prepared with Linux/OS X ports in mind. I will be posting our progress on the porting efforts frequently, please keep checking our blog.

Very nice comparision of SQL Server, MySQL and PostgreSQL servers.MySQL DBA & Programming Blog by Mark Schoonover

Very nice comparision of SQL Server, MySQL and PostgreSQL servers.MySQL DBA & Programming Blog by Mark Schoonover

As promised, after individual presentations at last week's CommunityOne I brought together the community leaders of three of the top GNU/Linux distros (Zonker Brockmeier, OpenSUSE; Jono Bacon, Ubuntu; Karsten Wade, Fedora), threw in Glynn Foster of OpenSolaris and moderated a no-holds-barred panel.  (It took them three hours to clean up the blood afterwards!!)

Although the panel itself wasn't recorded, immediately after it concluded, the five of us headed to the make-shift podcast studio we had set up at the event …

MySQL has a handy feature, that allows you to turn an INSERT into an UPDATE if a unique or primary key duplication is detected:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

A common usage pattern for this is “lazy initialization“ of a row in a database, which is exactly what my team was using it for yesterday to solve a problem in the backend for version 2.0 of the MySQL Enterprise Monitor. However, we ran into an issue where Hibernate would throw an exception complaining that when the INSERT was turned into an UPDATE, it couldn‘t retrieve the generated primary key value (we are using auto increments on this particular table, as it‘s not a high insertion-rate table).

To understand why this happens, you have to know a little bit about how Statement.getGeneratedKeys() works with MySQL‘s JDBC driver. …

In the Columbus, Ohio, airport right now waiting to board my plane to Salt Lake City. Tonight, I will be speaking on Join-Fu at the PLUG (Provo Linux User Group). Ryan Simpkins asked that I speak "at a very advanced level" and so that's what I will be doing! We'll see how the PLUG members handle my new set of slides on Intermediate Join-Fu that I have been working on...

I will post the slides a little later today after the PLUG meeting. I'm really looking forward to meeting Ryan and the other folks from PLUG and getting a short tour of Omniture, a large web analytics company that uses MySQL (no, it's not Overture. Totally different. One of those is now extinct.)