Apparently, there’s been an outstanding bug for nearly two years for MySQL to add support for millisecond storage in DATETIME and TIME data types.

A microseconds part is allowable in temporal values in some contexts, such as in literal values, and in the arguments to or return values from some temporal functions. Microseconds are specified as a trailing .uuuuuu part in the value. Example:

…

However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.

What’s this about? I have to admit that I’ve known about this problem for about the same amount of time (probably three years).

At Rojo we used BIGINTs as timestamps which provided millisecond …

Following my article Everything fails, Monitor Everything, and some inquiries, I’ve made some small modifications to my initially hourly script. This script is still a quick and dirty trial of what I’m wanting to develop, but in true Guy Kawasaki terms “5. Don?t worry, be crappy”. It works for now, and enables me to determine what works and what doesn’t.

My goals are Data Collection, Data Analysis and Data Presentation. This is the start of Data Collection. So now I get the following files:

• os.vmstat.070524.122054.log
• os.ps.070524.122054.log
• mysql.innodbstatus.070524.122054.log
• mysql.processlist.070524.122054.log
• mysql.status.070524.122054.log
• mysql.tablestatus.070524.122054.log
• mysql.tablestatus.vertical.070524.122054.log
• mysql.variables.070524.122054.log
…

mysqlreport v3.2 has been released. Mark Leith’s post, Aggregating SHOW STATUS Output, reminded me that a user had asked me a few months ago to do this for mysqlreport. I forgot until Mark’s post.

I have finished implementing this feature with mysqlreport. The new version has three new command line options: –relative (-r), –report-count (-c), and –detach. All of these are explained in the doc. This feature required substantial alterations to the script, so please tell me if anything doesn’t work.

To proceed from one truth to another, and connect distant propositions by regular consequences, is the great prerogative of man --Samuel Johnson

The simple perl script proof of concept demonstrated here, performs data type validation on the provided data file. The script creates an output file with bad data attributes substituted by default values. The data type specification and default values are read from a specification file. A database table can be easily substituted for the specification file to make this process more robust. This functionality is provided out of the box by the commercial ETL tools.

#!/usr/local/bin/perl -w
use strict;
#
# http://www.dwoptimize.com/2007/05/data-type-validation-using-regular.html
# jag.singh@dwoptimize.com
# Version 0.3
#   feedback incorporated …

Here are some useful firefox keyboard shorcuts for urls:


Ctrl + enter : to complete a .com url . You can type "google" in the url bar and then ctrl + enter to complete the "www.google.com".

Ctrl + shift + enter : to complete a .org url . You can type "mysql" in the url bar and then ctrl + shift + enter to complete the "www.mysql.org".

F6: to select or move the cursor to the address in the url bar.

Ctrl + k : to move the cursor to the search box.

In a previous post, Thumper: Putting Blastwave on ZFS, I quickly saw some information and jumped to completely the wrong conclusion. In the comments, Boyd kindly pointed out that I should probably investigate it a little more thoroughly. So I have. Just to recap, effectively I am trying to install software, with pkgadd onto a ZFS filesystem. The full filesystem is 17 terabytes, and still has 17TB available. The steps I followed were:

zfs create zpool1/software
zfs create zpool1/software/blastwave
zfs set mountpoint=/opt/csw zpool1/software/blastwave
pkgadd -d http://www.blastwave.org/pkg_get.pkg

And the errors I was receiving were along the lines of:

pkgadd: ERROR: unable to create package object </opt/csw/bin>.
    pathname does not exist
    pathname does not exist
    unable to fix attributes …

Well I have to say, Skype is a piece of crap. Between the abysmal sound quality/compression artifacting and the compatibility issues with my Gentoo install we had a nightmare trying to communicate, eventually resorting to text chat.

I think we clarified a few ideas during this session, mostly spec-related but with one or two interesting ideas cropping up.

One notable thought was local auditing; how do we watch local administrators' queries? We can't capture packets from their session since they're working on the server itself so what can we do? This may involve a whole other branch of code that allows us to send local server events to the auditing server for storage and flagging. I'm not entirely sure how we're supposed to sit between the user and the mysql server, especially if the user is a domain/local administrator and has some determination not to get caught!

Another interesting thought was rules; …

Welcome to the MySQL UDF Repository! The MySQL UDF repository aims to offer an organized collection of open source MySQL user defined functions.

There are numerous MySQL UDFs available in the public domain. However, most UDFs appear standalone. Often, binaries are not included: only the source code is available. In many cases, it is not straightforward to compile binaries for different platforms. Likewise, documentation and installation instructions are either scarce or missing. Also, there is little consistency with regard to naming conventions, error handling and error reporting.

The MySQL UDF Repository tries to improve that situation.
GoalsHere's a list of things the MySQL UDF Repository aims to offer:

• 
  
…

Novell and the EFF team up on software patent reform. Sun and Redflag collaborate on OpenOffice. Microsoft focues on interoperability for online identity management. (and more)

Novell and the Electronic Frontier Foundation Team Up to Reform Software Patents, Novell (Press Release)

Sun and Redflag Chinese 2000 to Collaborate on OpenOffice.org Projects, Sun Microsystems (Press Release)

Microsoft Focuses on Interoperability for the Identity Metasystem, Microsoft (Press Release)

…

Inserting user-defined DTrace probes into MySQL source code is very useful to help user identify the performance problems in the application level and the database server, In addition, the cost of the USDT probe is basically neglectable. Each probes inserted into the src can be enabled by adding the code like:

 If (PROVIDER_PROBE_ENABLED()

{

                PROVIDER_PROBE(arg0,…);

}

The steps to add DTrace probes into MySQL is very straightforward.

Step 1: Figure out what probes are needed to insert into the source code

This is the difficult part that requires you understand the MySQL implementation details. Generally, it is good to insert probes to clarify the DB response time distribution …