There’s been an issue on Natural Load Testing (our fantastic web application load testing tool) for a while now, we’ve been referring to it as the Double Stacked Graph. Normally the graphs should look like this, kindly sharing the load testing results with the end user, hopefully being both easy to start with and powerful as usage grows.

Unfortunately, what we’ve been seeing occasionally is the dreaded Double Stack: Here you can see that there is two stacked red bars on top of each other. They represent the exact same space in time, but the data is being added to the graph twice for some reason.

Initially I expected that this was an issue with me handling the updates to the graph with ajax poorly. We had an issue with Where’s it Up for a while where results were duplicated. This was the result of asking the server for “new” results every second by passing the server the list of results already obtained. If a request took more than a second to complete, the following request (initiated while the first was outstanding) would be initiated with the same list of previously obtained results, so the server would obligingly return the same “new” results to both requests.

It turned out however that this wasn’t the problem at all, having learned from the Where’s it Up issue I’d coded those ajax requests more carefully. It turns out that the issue had to do with how I was communicating with MySQL. Each time the graph updates it asks for results with a timestamp newer than the newest data it has. The exact query is something like this:

The issue here turns out to be the fact that I'm quoting $minTime. Take a look at these results: mysql> CREATE TABLE `test_decimal` ( -> `job_id` varchar(32) NOT NULL, -> `ts` decimal(13,3) NOT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `test_decimal` VALUES -> ('9_0209_511673437c4dd', 1360425827.491), -> ('9_0209_511673437c4dd', 1360425828.491), -> ('9_0209_511673437c4dd', 1360425829.491), -> ('9_0209_511673437c4dd', 1360425830.492); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * from `test_decimal` WHERE `ts` > '1360425827.491' LIMIT 2; +----------------------+----------------+ | job_id | ts | +----------------------+----------------+ | 9_0209_511673437c4dd | 1360425827.491 | | 9_0209_511673437c4dd | 1360425828.491 | +----------------------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT * from `test_decimal` WHERE `ts` > 1360425827.491 LIMIT 2; +----------------------+----------------+ | job_id | ts | +----------------------+----------------+ | 9_0209_511673437c4dd | 1360425828.491 | | 9_0209_511673437c4dd | 1360425829.491 | +----------------------+----------------+ 2 rows in set (0.00 sec)

While using the greater than operator, I’m obtaining results that are equal if the term I’m comparing against is quoted. Without the quotes it performs as expected. This confused me greatly, I’ve been told countless times that I can quote my variables in MySQL to no ill effect, in fact I probably should if I’m not using paramaterized queries for increased security.

The crux of the issue will come down to MySQL’s Type Conversion rules. It must be converting the two values to float to handle the quoted comparison, though I may be at a loss to describe why they’re converted differently. By not quoting the decimal value I allow the comparison to occur between decimal values, and obtain the desired result.

Update

A few people have commented (either here or elsewhere) about MySQL versions. I just re-confirmed the test with the newest MySQL Debian would give me: mysql> SELECT * from `test_decimal` WHERE `ts` > '1360425827.491' LIMIT 2; +----------------------+----------------+ | job_id | ts | +----------------------+----------------+ | 9_0209_511673437c4dd | 1360425827.491 | | 9_0209_511673437c4dd | 1360425828.491 | +----------------------+----------------+ 2 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+-----------------------+ | Variable_name | Value | +-------------------------+-----------------------+ | protocol_version | 10 | | version | 5.1.66-0+squeeze1-log | | version_comment | (Debian) | | version_compile_machine | i486 | | version_compile_os | debian-linux-gnu | +-------------------------+-----------------------+ 5 rows in set (0.00 sec)

Or our Percona server (issue resolved): mysql> SELECT * from `test_decimal` WHERE `ts` > '1360425827.491' LIMIT 2; +----------------------+----------------+ | job_id | ts | +----------------------+----------------+ | 9_0209_511673437c4dd | 1360425828.491 | | 9_0209_511673437c4dd | 1360425829.491 | +----------------------+----------------+ 2 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------------------------+ | Variable_name | Value | +-------------------------+------------------------------------+ | innodb_version | 1.1.8-rel29.4 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.29-29.4-log | | version_comment | Percona Server (GPL), Release 29.4 | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------------+ 7 rows in set (0.00 sec)