Well, it’s Sunday night so I will put this down to being the weekend. The background to being caught out is a request I made to my local Users Group mailing list for some information on people’s environments because I wanted to some empirical data analysis without having any more knowledge of the systems.

In summary (without the surrounding fan-fare, I was seeking):

SELECT VERSION();
SHOW STATUS;
SHOW VARIABLES;  // Optional

I was however perplexed why my first data point analysis (Read/Write ratio) using the Status values Com_insert, Com_update, Com_delete and Com_select was not always giving me expected results. In particular, a number of server results showed 0 for values while I knew the results came from working MySQL environments.

So, sanity check with good friend Morgan and I get the response to answer the dilemma SHOW STATUS defaults to session …

It's three months ago since MySQL 5.1.11 was released and many things have changed since then. Just watch the Change Log for 5.1.12 - I believe it's one of the longest ever:

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html

But I've found another nice and interesting thing: watch this bug report. This says that it's now allowed to do many more things in Prepared Statements inside Stored Procedures:

SHOW BINLOG EVENTS
SHOW (MASTER | SLAVE) STATUS
SHOW (MASTER | BINARY) LOGS
SHOW (PROCEDURE | FUNCTION) CODE (parsable only in debug builds)
SHOW CREATE (PROCEDURE | FUNCTION | EVENT | TABLE | VIEW)
SHOW (AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS)
CHANGE MASTER
RESET (MASTER | SLAVE | QUERY CACHE) …

Got up and went walking this morning. This is probably going to be one of the last few good weekends here in Seattle, and I wanted to make sure that I got in some walking each day.

Came home to discover that Christine had been stung by a Yellow Jacket. Despite the full can of Yellow Jacket killer I sprayed into the nest last night, they seem to be quite alive. I am tempted to run to the store and get some of the instant foam I use for sealing insulation leaks. I wonder how well they could get out of their nest if it was filled full of instant insulation :)

So this afternoon has turned into "fetch her some ice", so I decided to code up a small project I've been meaning to write for a few years:

mysql> INSTALL PLUGIN memcache SONAME 'libmemcache_engine.so';
Query OK, 0 rows affected, 0 warnings (0.06 sec)

mysql> create table foo3 (k varchar(128), val varchar(300)) …

After directing a Devshed poster looking for a way to compute the number of months between two dates to the manual I decided to turn the solution posted in user comments by Isaac Shepard into a function, here it is:

1. DELIMITER $$
2. DROP FUNCTION IF EXISTS `test`.`months_between` $$
3. CREATE FUNCTION `test`.`months_between` (date_start DATE, date_end DATE) RETURNS INT
4. BEGIN
5. SELECT IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) > 0, (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) - (MID(date_end, 9, 2) < style="color: rgb(102, 204, 102);">(date_start, 9, 2)), IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) < …

Development of a more structured version is in progress. I just committed a set of changes to the repository that changes the current, rather monolithic, single-class design to what will be the first step of a more modular one.

Currently the generation of ALTER TABLE statements has been removed and the output format of the analysis is slightly different. But that is mere cosmetic.

What is much more important is that now the gathering of database schema information is based on a pluggable system, designed around an interface called IndexDescriptorProvider. Up to now I have just ported the MySQL 4 stuff that was already in the first version to this new architecture. Please feel free to have a look at it and tell me what you think.

Next thing I'll do is implement a provider based on the INFORMATION_SCHEMA database available in MySQL 5.x to see if I missed anything.

Go to …

I'm reading an interesting research paper (to be blogged later) that mentions a fact that I find pretty compelling:

In 2001 Debian included more than 55,000,000 lines of code, with an estimated (COCOMO) value of $1.9 billion.That's just Debian (and, as Wheeler notes, it grew to 230 million lines of code by 2005, putting its COCOMO price at $8 billion).

What number would we get to if we added MySQL (2M+ lines of code, I believe), JBoss, etc.? I can't even begin to come up with a rational number, but I'm guessing we're north of $50 billion. $50+ billion worth of free (as in cost and freedom) and open source software.

Priceless.

(Keep in mind that there's more to good software than code size. …

I'm reading a research paper [PDF] by Nicholas Economides (NYU) and Evangelos Katsamakas (Fordham) called "Linux vs. Windows: A comparison of application and platform innovation incentives for open source and proprietary software platforms." Long title, but the conclusion of the paper is relatively brief:

In our model, firms and developers invest to improve the quality of the platform or the application and expand the demand by users of these software products. When the operating system is proprietary, the platform provider and the application provider invest only in their own product to maximize their profit. When the operating system is open source, there is no platform provider firm, but the users invest in the platform to maximize their user surplus and their development reputation, which depends on the success of the platform measured by its …

best practices, database, internet, mysql, security, software, technology web

With recent posts by Frank Mash and Stewart Smith about password …

Back in May Dan Morgan was kind enough to invite me to do a guest lecture at the University of Washington about “Data Warehousing Basics.”  After having emailed these slides as a decent overview to a few customers lately, I realized they’d probably be useful online.  It is obviously a little light on content (their just slides) but they do provide some good “high level views” of dimensional modeling/DW/BI in general.  My employer, Pentaho, was generous enough to allow me time to build this presentation for the students at UW for which they, and I are grateful.  THANK YOU!

The online version: Univerisity of Washington Guest Lecture May 9
The PDF of …

Open source is democratic, open, real.

While I have a good sense for which Tech Tips would be useful, I’d also like to ask the community for what tips they’d like to see written up:

• Mondrian: Star Schema to OLAP cubes
  A very basic Star Schema with a Fact and Two Dimensions show how this is built into a Mondrian cube and how to built a “Pivot view” Pentaho report.
• Mondrian: Advanced MDX
  Sets, top, running totals, etc
• Kettle: Portable ETL
  Showing how to use paramater injection to make your Kettle solution (Jobs and Transforms) executable inside of Pentaho.
• Kettle: Custom rollups using Excel
  Showing how to build a dimension, reporting table, etc using a very easy to use interface for business users.
• Reporting: List of Values
  Show how to use the most unfortunately …