I have several double precision columns in a table and a select on a server running 5.1.63 would return a number like 12.0. I upgraded to the latest Percona 5.5 (5.5.27-28.0 at the time), same hardware, and the select now returns 12.00000001. Why would this be the case? I understand floating point cannot represent certain numbers, but would expect the stored value to be represented the same way on both distributions.
No announcement yet.
double precision column returning different values after upgrade to 5.5Page Title Module
Move Remove Collapse
double precision column returning different values after upgrade to 5.5Tags: None
Hi, I have tried to test the same scenario locally but I didn't get any differences. Can you provide some more details to troubleshoot it? like, any one table description which contains double precision columns, before and after upgrade and the steps which you followed to upgrade DB from 5.1 to 5.5.
Btw: You can get more information about versions changes here.
It did not occur on all rows, just a handful. I'd like to isolate this to a few rows if possible, but the table is quite large. Do you have any suggestions as far as creating a table in an isolated schema with just the rows that caused me issues? Then I can upload a sample db if able to reproduce.
The upgrade was nothing more than booting the server on the latest version, no data conversion scripts were run. The underlying data did not change, and rolling back to the older version yields "correct" results.
I did see the linked guide before posting, but unless I misunderstood it, had to do with string to fp conversion. Since I was working with previously stored data, I thought it did not apply.
Databases can not be upgraded by only booting the server with the latest mysql version (specially for major version upgrade like 5.1 to 5.5) . You must run mysql_upgrade utility on the server just after upgrade the mysql version for correcting all the differences between the versions. Can you run mysql_upgrade on the server and check? I guess, you will not get above differences.