Glom is an interesting graphical database front-end I’ve been meaning to try out for some time. Someone asked about graphical database front-ends on the #mysql IRC channel recently, and that prompted me to install Glom and learn how to use it. My overall impressions? It lands squarely in the middle of its target audience’s needs, but still has a quirk here and there. With a bit of polish it will be a fine product, and it’s already a winner over Microsoft Access and Filemaker, two similar programs with which you might be familiar.

Yesterday, Webyog announced that they have open sourced the very popular SQLyog database management tool. I've known about this good news for quite some time and have been itching to spread the good news. Whenever a company decides to make the leap from closed-source to open-source products, that is good news to me. It's also something that shouldn't be cast off as an easy decision for a company to make.

Rohit Nadhani, co-founder of Webyog, contacted me a few months ago and we talked about their plans to open-source a community edition of SQLyog. He's a bright guy. Very bright. But he shares the same concerns that any business owner has when pondering an entrance into the open source market. It's a frightening thing to "let go" of the business and development model you have used for years. I applaud him and Webyog for their efforts and their gumption over the last few months.

I did …

So often it seems that I am seeing someone struggling to find out what is wrong with their PHP script. They see the following error message:

Warning: mysql_fetch_assoc():
supplied argument is not a valid MySQL result resource
in /var/www/htdocs/somefile.php on line 18

and have no clue what this means. The line number has no bearing on where the real problem is, and there may be a whole cascade of these messages. Let's assume the code looks something like this:

mysql_connect ("server", "user", "password");
mysql_select_db ("dbname");
$result = mysql_query ("SELECT something FROM mytable");
while ($row = mysql_fetch_assoc ($result))
{
...
}

Where did the error occur? The answer is it could be any number of places. Let's make a list:

1. The server name is wrong.
2. MySQL is not running on the server
3. The script is being run on a host that is not …

You know, sometimes, when things start rolling, they really start to snowball. Such is the case with the newly-formed Community Doxygen Project. Yesterday, I wrote about some things that I've been working on recently. One of which was an effort to introduce doxygen into the MySQL source code documentation arena.

Since then, three community members — David Shrewsbury, Ronald Bradford, and Frank Mash — have stepped up to the challenge and become part of the Community Doxygen Project team. So, what will this team be doing over the next few months? Well, we will be attempting to pinch, pull, and push the MySQL source code into a consistent, Javadoc comment …

Those who were present at the 1st Belgian MySQL UG might have overheard that I had spend the better part of tuesday , fighting with an unstable MySQL cluster, ndb nodes dying with no reason whatsoever and no traces in the MySQL logfiles.

I found the issue wednesday evening, it wasn't even MySQL related.

Aug 30 12:30:30 DB-A kernel: __alloc_pages: 0-order allocation failed (gfp=0x1f0/0)
 Aug 30 12:30:30 DB-A last message repeated 6 times
 Aug 30 12:30:43 DB-A kernel: __alloc_pages: 0-order allocation failed (gfp=0x1d2/0)
 Aug 30 12:30:44 DB-A kernel: __alloc_pages: 0-order allocation failed (gfp=0x1f0/0)
 Aug 30 12:30:44 DB-A kernel: __alloc_pages: 0-order allocation failed (gfp=0x1f0/0)
 Aug 30 12:30:44 DB-A kernel: __alloc_pages: 0-order allocation failed (gfp=0x1d2/0)
 Aug 30 12:30:44 DB-A kernel: __alloc_pages: 0-order allocation failed (gfp=0xf0/0)
 Aug 30 12:30:45 DB-A kernel: __alloc_pages: 0-order allocation failed (gfp=0x1d2/0)
 Aug 30 …

Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause of MySQL Performance problems.
Here is what you need to know about ORDER BY ... LIMIT optimization to avoid these problems

ORDER BY with LIMIT is most common use of ORDER BY in interactive applications with large data sets being sorted. On many web sites you will fine top tags, recently registered users etc - which would often require ORDER BY with LIMIT in the back end. In general this type of ORDER BY looks like: SELECT ..... WHERE [conditions] ORDER BY [sort] LIMIT N,M

Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, so it is important for it to use index - in this case index range scan will be started and query execution stopped as soon as soon as required amount of rows generated.

…

A lot of time, I hear discussions about which reporting tool is the easiest to use for certain special tasks. Most of the time, I just ignore these “threads” because it’s not my cup of tea as a developer of ETL solutions.
However, it has to be said, often the solution to complex reporting requirements is to be found in ETL.
When you find yourself struggling with complex reports that need any of the following:

• compare different records
• aggregate beyond simple sums and averages.
• report on a non-existing records (report 0 sales, etc)
• …

Well, in those cases you need ETL.

Let’s take for example the case of the reporting on non-existing sales: how can you report that there has been 0 sales for a certain product during a certain week? Well, you can create an aggregate table in your ETL that contains the following:

• Dimensions
  …

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 …