MySQL Version >5.0

Ever wondered how would you get a list of business dates/Off dates from a specific date range in MySQL? here is a simple way to do so...

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`uGetBussinessDays`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `uGetBussinessDays`(in_sDate DATE, in_eDate DATE)
READS SQL DATA
BEGIN
DECLARE l_sDate DATE;
SET l_sDate = in_sDate;
DROP TABLE IF EXISTS _tblBussinessDays;
DROP TABLE IF EXISTS _tblHolidaysDays;
CREATE TEMPORARY TABLE _tblBussinessDays(BussinessDays date);
CREATE TEMPORARY TABLE _tblHolidaysDays(Offdays date);
IF ( in_sDate > in_eDate ) THEN
SELECT "Invalid dates supplied";
END IF;
WHILE l_sDate <= in_eDate DO

IF (DAYNAME(l_sDate) = 'Sunday' ) THEN
INSERT INTO _tblHolidaysDays VALUES(l_sDate);
ELSEIF ( …

I have been working recently with Matt Yonkovit to get Waffle Grid cloud enabled with Amazon Web Services (AWS).

An initial version of Waffle Grid Cream - Version 0.5 release is now available.

We have elected to create one AMI for now, that is ready to be configured as either a MySQL Server, a memcached server, or as in the following example both. For this first version, we have also not configured MySQL or memcache, but rather provide a virgin Waffle Grid ready server for developers to experiment and benchmark with.

Future releases will include custom AMI’s and the automated ability to register new memcached servers with the Waffle Grid enabled MySQL server.

Instance Creation

We assume you have created an …

Sorry for this being the latest wrap-up ever.

May was a fun month for us. We rolled out a ton of new features: the ability to add YouTube videos and polls to comments, stats in your time zone, the VideoPress upgrade (with HD!), post by email, new stats charts, comment search, improved comment …

Image by ToniVC via Flickr

Release models make a huge difference in the properties of the software delivered. I believe there is no single "ideal" model; what to choose depends on the code base, the group/community creating the code, the users/customers, the technology available (languages, CI tools, others)... What works for Hudson does not work for GlassFish nor for Solaris.

Tired of not watching the latest music videos on youtube like the way you used to before? Frustrated at the live performance or unplugged version of the song you wanted to hear (I actually like it that way, but anyway…)? Well do I have a solution for you!
China has their own version of youtube called YouKu.We all know for a long time now that in China, they copy everything and have very little or no respect for copyright laws. This sometimes upsets us (check out StackOverFlow vs CNprog), but here is a chance for us to get something back.
Go there and put in the search a name of a particularly famous/infamous blonde singer and see all the lost or missing music videos from youtube.
…

In MySQL, the tmpdir path is mainly used for disk-based sorts (if the sort_buffer_size is not enough) and disk-based temp tables. The latter cannot always be avoided even if you made tmp_table_size and max_heap_table_size quite large, since MEMORY tables don’t support TEXT/BLOB type columns, and also since you just really don’t want to run the risk of exceeding available memory by setting these things too large.

You can see how your server is doing with temporary tables, how many of those become disk tables, and also other created temporary files, by looking at SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;

So why might tmpfs be better? Well, not just any tmpfs, but specifically /dev/shm. Its …

I just finished adding pluggable protocol support to the Gearman job server, this will enable even more methods of submitting jobs into Gearman. If all the various Gearman APIs, MySQL UDFs, and Drizzle UDFs are not enough, it’s now fairly easy to write a module that takes over the socket I/O and parsing hooks to map any protocol into the job server. As an example module, I added basic HTTP protocol support:

> gearmand -r http &
[1] 29911
> ./examples/reverse_worker > /dev/null &
[2] 29928
> nc localhost 8080
POST /reverse HTTP/1.1
Content-Length: 12

Hello World!

HTTP/1.0 200 OK
X-Gearman-Job-Handle: H:lap:1
Content-Length: 12
Server: Gearman/0.8

!dlroW olleH

I’ve added a few headers for setting things like background, priority, and unique key. For example, if you want to run the above job in the background:

POST /reverse HTTP/1.1
Content-Length: 12
X-Gearman-Background: true

Hello World!

HTTP/1.0 200 OK …

I was curious about Google Fusion Tables, and gave it a try.
I uploaded the employees table from the employees test database, 16 MB of data, about 300,000 rows. Since the maximum limit per table is 100 MB, I expected interesting results.
However, one of my first tests, with aggregation was quite disappointing.
A simple group by gender was executed in about 30 seconds.

InnoDB on my laptop did a much better job:

select gender , count(*) from employees group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| M      |   179973 | 
| F      |   120051 |  …

One of the things I really like about Solaris (and OpenSolaris) is the Services Management Framework (SMF). And one of the things I like about my job is the opportunity to learn and work with new stuff. And, of course, the best way to learn something "non-lethal" is to try it yourself and do something with it.
So, how about using SMF for managing a MySQL Cluster setup? Something simple like two data nodes, one mgmt node, and one MySQL server node running on one OS instance? Sounds like a good exercise and, from a quick search, I couldn't find where it had been done before. Loaded up the latest release of OpenSolaris (2009.06) into a Virtual Box VM, downloaded the latest tar ball of …