{"id":183,"date":"2011-03-17T08:53:45","date_gmt":"2011-03-17T06:53:45","guid":{"rendered":"\/\/www.mcgill.org.za\/stuff\/?p=183"},"modified":"2011-04-17T21:57:22","modified_gmt":"2011-04-17T19:57:22","slug":"floating-to-0-999","status":"publish","type":"post","link":"https:\/\/www.mcgill.org.za\/stuff\/archives\/183","title":{"rendered":"Floating to 0.999 on the MySQL cloud"},"content":{"rendered":"

This code:
\nREPLACE INTO loadtable (`hostname`,`load`) VALUES ('server1',2.093);<\/VALUE><\/code>
\ndidn’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:
\n
\n load host<\/b>
\n 0.309 desktop1
\n 0.201 desktop2
\n 0.172 desktop3
\n 0.999 server1
\n 0.999 server2
\n 0.999 server3
\n<\/code>
\nNo 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.<\/p>\n

This is how the table was created:
\nCREATE TABLE `domain_loadaverage` (
\n `hostname` varchar(48) NOT NULL COMMENT 'Name of host',
\n `load` float(3,3) default NULL COMMENT 'load average for the period',
\n PRIMARY KEY (`hostname`)
\n) ENGINE=InnoDB DEFAULT CHARSET=latin<\/code>
\nAnd that’s wrong. Float(3,3)<\/strong> means you get 3 significant digits, and they are all after the decimal point. Changing that to Float(7,3)<\/strong> makes it work a bit better (well, until the system load goes above 9999).<\/p>\n

So now it looks like this, and the data isn’t truncated:
\nCREATE TABLE `domain_loadaverage` (
\n `hostname` varchar(48) NOT NULL COMMENT 'Name of host',
\n `load` float(7,3) default NULL COMMENT 'load average for the period',
\n PRIMARY KEY (`hostname`)
\n) ENGINE=InnoDB DEFAULT CHARSET=latin<\/code>
\nI suppose it’s in the documentation somewhere.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[20,190,11],"class_list":["post-183","post","type-post","status-publish","format-standard","hentry","category-stuff","tag-mysql","tag-stuff","tag-stupidity"],"_links":{"self":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/comments?post=183"}],"version-history":[{"count":8,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/183\/revisions"}],"predecessor-version":[{"id":204,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/183\/revisions\/204"}],"wp:attachment":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/media?parent=183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/categories?post=183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/tags?post=183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}