Floating to 0.999 on the MySQL cloud

This code:
REPLACE INTO loadtable (`hostname`,`load`) VALUES ('server1',2.093);
didn’t work. It worked in my testing from my local machine, but I ended up with this when I ran it from a real machine:

load host
0.309 desktop1
0.201 desktop2
0.172 desktop3
0.999 server1
0.999 server2
0.999 server3

No matter what the actual load on the machine was, the value recorded was set to 0.999. This was really distressing: I was using the load (a moving average) to adjust a moving average (to get a longer term moving average). What happens when you try to move a moving average and it doesn’t? It made me emotional: it was moving.

This is how the table was created:
CREATE TABLE `domain_loadaverage` (
`hostname` varchar(48) NOT NULL COMMENT 'Name of host',
`load` float(3,3) default NULL COMMENT 'load average for the period',
PRIMARY KEY (`hostname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin

And that’s wrong. Float(3,3) means you get 3 significant digits, and they are all after the decimal point. Changing that to Float(7,3) makes it work a bit better (well, until the system load goes above 9999).

So now it looks like this, and the data isn’t truncated:
CREATE TABLE `domain_loadaverage` (
`hostname` varchar(48) NOT NULL COMMENT 'Name of host',
`load` float(7,3) default NULL COMMENT 'load average for the period',
PRIMARY KEY (`hostname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin

I suppose it’s in the documentation somewhere.

This entry was posted in Stuff and tagged , , . Bookmark the permalink.