Skip to content

# Month: July 2010

## SQL trick: overcoming GROUP_CONCAT limitation in special cases

MySQL SQL July 21, 2010July 21, 2010

In Verifying GROUP_CONCAT limit without using variables, I have presented a test to verify if group_concat_max_len is sufficient for known limitations. I will follow the path where I assume I cannot control group_concat_max_len, not even in session scope, and show an SQL solution, dirty as it is, to overcome the GROUP_CONCAT limitation, under certain conditions.

Sheeri rightfully asks why I wouldn’t just set group_concat_max_len in session scope. The particular case I have is that I’m providing a VIEW definition. I’d like users to “install” that view, i.e. to CREATE it on their database. The VIEW does some logic, and uses GROUP_CONCAT to implement that logic.

Now, I have no control on the DBA or developer who created the view. The creation of the view has nothing to do with the group_concat_max_len setting on her database instance.

#### An example

OK, apologies aside. Using the sakila database, I execute:

>     mysql> SELECT GROUP_CONCAT(last_name) FROM actor \G
>     *************************** 1. row ***************************
>     GROUP_CONCAT(last_name): AKROYD,AKROYD,AKROYD,ALLEN,ALLEN,ALLEN,ASTAIRE,BACALL,BAILEY,BAILEY,BALE,BALL,BARRYMORE,BASINGER,BENING,BENING,BERGEN,BERGMAN,BERRY,BERRY,BERRY,BIRCH,BLOOM,BOLGER,BOLGER,BRIDGES,BRODY,BRODY,BULLOCK,CAGE,CAGE,CARREY,CHAPLIN,CHASE,CHASE,CLOSE,COSTNER,CRAWFORD,CRAWFORD,CRONYN,CRONYN,CROWE,CRUISE,CRUZ,DAMON,DAVIS,DAVIS,DAVIS,DAY-LEWIS,DEAN,DEAN,DEE,DEE,DEGENERES,DEGENERES,DEGENERES,DENCH,DENCH,DEPP,DEPP,DERN,DREYFUSS,DUKAKIS,DUKAKIS,DUNST,FAWCETT,FAWCETT,GABLE,GARLAND,GARLAND,GARLAND,GIBSON,GOLDBERG,GOODING,GOODING,GRANT,GUINESS,GUINESS,GUINESS,HACKMAN,HACKMAN,HARRIS,HARRIS,HARRIS,HAWKE,HESTON,HOFFMAN,HOFFMAN,HOFFMAN,HOPE,HOPKINS,HOPKINS,HOPKINS,HOPPER,HOPPER,HUDSON,HUNT,HURT,JACKMAN,JACKMAN,JOHANSSON,JOHANSSON,JOHANSSON,JOLIE,JOVOVICH,KEITEL,KEITEL,KEITEL,KILMER,KILMER,KILMER,KILMER,KILMER,LEIGH,LOLLOBRIGIDA,MALDEN,MANSFIELD,MARX,MCCONAUGHEY,MCCONAUGHEY,MCDORMAND,MCKELLEN,MCKELLEN,MCQUEEN,MCQUEEN,MIRANDA,MONROE,MONROE,MOSTEL,MOSTEL,NEESON,NEESON,NICHOLSON,NOLTE,NOLTE,NOLTE,NOLTE,OLIVIER,OLIVIER,PALTROW,PALTROW,P
>     1 row in set, 1 warning (0.00 sec)

>     mysql> SHOW WARNINGS;
>     +---------+------+--------------------------------------+
>     | Level   | Code | Message                              |
>     +---------+------+--------------------------------------+
>     | Warning | 1260 | 1 line(s) were cut by GROUP_CONCAT() |
>     +---------+------+--------------------------------------+
>     1 row in set (0.00 sec)

Continue reading » “SQL trick: overcoming GROUP_CONCAT limitation in special cases”

## mycheckpoint (rev. 170): improved custom queries; local charting; page/swap I/O monitoring; improved HTML reports

MySQL Graphs Linux Monitoring mycheckpoint Open Source July 16, 2010July 16, 2010

Revision 170 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision:

- Improved custom queries: lifting of limitations from previous, introductory revision; better HTML presentation
- Local, inline charting: no rendering of Google Charts, unless explicitly requested. All charts are now rendered locally using JavaScript. No data is now sent over the network.
- Page/Swap I/O monitoring: now monitoring for page ins and outs, swap ins and outs (Linux only).
- Improved HTML reports: several improvements on presentation (see sample, more follow).

#### Improved custom queries

Some limitations, introduced in revision 132, are now lifted. New features are introduced.

- There is now no limit to the number of custom queries (well, an INT limit).
- In fact, the data tables adjust themselves to the existing custom queries in the form of auto-deploy: once a new custom query is added or an old one removed, mycheckpoint will add or remove the relevant columns from the data tables.
- The chart_order column is now utilized: HTML reports which include custom query charts now order those charts according to chart_order values. This makes for nicer reports.
- The standard HTML brief report (SELECT html FROM sv_report_html_brief) now automatically includes all custom charts. The HTML brief report is the report one usually wants to look at: it provides with the latest 24 hours metrics for selected values. It now becomes a centralized place for all that is interesting in the past 24 hours.
- Custom queries are now allowed to return NULL, treated as a missing value. This is a bugfix from previous revisions.

#### Local charting

Motivation for local charting is clear: no one likes having their data being sent over the network. And no one likes Google to know about their DML values.

I’ve been playing around with quite a few charting solutions, and have gone into depths with two of them, adding and rewriting quite a lot of code. Eventually, I settled on my very own rendering. Here’s what I’ve seen & tested: Continue reading » “mycheckpoint (rev. 170): improved custom queries; local charting; page/swap I/O monitoring; improved HTML reports”

## Implicit casting you don’t want to see around

MySQL Data Types SQL July 7, 2010July 7, 2010

In Beware of implicit casting, I have outlined the dangers of implicit casting. Here’s a few more real-world examples I have tackled:

#### Number-String comparisons

Much like in programming languages, implicit casting is made to numbers when at least one of the arguments is a number. Thus:

>     mysql> SELECT 3 = '3.0';
>     +-----------+
>     | 3 = '3.0' |
>     +-----------+
>     |         1 |
>     +-----------+
>     1 row in set (0.00 sec)

>     mysql> SELECT '3' = '3.0';
>     +-------------+
>     | '3' = '3.0' |
>     +-------------+
>     |           0 |
>     +-------------+

The second query consists of pure strings comparison. It has no way to determine that number comparison should be made.

#### Direct DATE arithmetics

The first query seems to work, but is completely incorrect. The second explains why. The third is a total mess. Continue reading » “Implicit casting you don’t want to see around”

## Tales of the Trade #4: new home for the MySQL dolphins

MySQL Humor Planet July 5, 2010

## SQL: good comments conventions

Development MySQL Coding SQL Syntax July 1, 2010

I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code.

I list four major places where SQL comments are helpful. I’ll use the sakila database. It is originally scarcely commented; I’ll present it now enhanced with comments, to illustrate.

#### Table definitions

The CREATE TABLE statement allows for a comment, intended to describe the nature of the table:

>     CREATE TABLE `film_text` (
>      `film_id` smallint(6) NOT NULL,
>      `title` varchar(255) NOT NULL,
>      `description` text,
>      PRIMARY KEY (`film_id`),
>      FULLTEXT KEY `idx_title_description` (`title`,`description`)
>     ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Reflection of `film`, used for FULLTEXT search.'

It’s too bad the comment’s max length is 60 characters, though. However, it’s a very powerful field.

#### Column definitions

One may comment particular columns: Continue reading » “SQL: good comments conventions”