Thursday, July 31, 2008

MySQL Hero-Function of the Month

MySQL has amazed me twice today. Once in a bad way and once in a good way.


The good news first: Today we have a new found Hero-Function from MySQL. (I have wanted this for soem time. Never found it until now. MySQL just has too much to offer!)

I have had to work with weekly reports quite a bit lately. Getting missing reports can be quite tricky and I feel I have gotten good at pulling data that isn't there. Finding the data that isn't in the database is hard to do. One of my previous reports was doing great until it case across a situation where a user submitted two reports in the same week. The main display is fine, but the linear report was broken - it was usign a GROUP_CONCAT() on the seven criteria for the weekly report. One of which had two records. The group_concat then had an extra item in it's delimited list. This effectively threw a wrench in the geers of the linear report.

Solution - get only the latest of each type of criteria. This also can be difficult without slowing down the query with sub-queries to get the max record in week X of type Y for project Z. Yes a pain in the rear.

Accomplishing the solution requires the new-found hero-function (Thank you once again to the genius folks at MySQL!)
STR_TO_DATE(CONCAT(YEARWEEK(NOW()),'Monday'), '%X%V %W')

This STR_TO_DATE(CONCAT(YEARWEEK(NOW()),'Monday'), '%X%V %W') gets any day of the given week without fail, without date_add minus the current day of the week + 1, or any other drawn out manual solution (of which there are probably many). I think this is by far the simplest, shortest and fastest. (I love it when those three get together!)



Now the bad news. (Not really all that bad. It's all good in the end [usually].)

Today, my favorite MySQL Hero-Function, the GROUP_CONCAT() function, failed me. It was concating some strings and it truncated the results. This not only cot the text short for a single item, but the remainder of the list was missing. Being that there were only 5 of the expected 7 items in the delimited string, it broke the report. (Yes, same report mention above.)

This was harder to find the solution to, but easier to fix. (No change of code or queries.)

Solution: In the my.ini file, add a line

My situation was slightly different from the reference. I first rummaged around in the MySQL GUI Administrator for the settings, found it, and set it.
It wanted a single number. It was defaulted to '1'. Like the reference above, I wanted to set it to '8'.
I set that and then checked the my.ini file which then had:

#The maximum length of the result of function  group_concat.
group_concat_max_len=8k

I restarted MySQL and the error was fixed on the report. I was pleased.


Just another fun day working with MySQL. Thanks guys!