MySQL Bugs: #12462: Corruption of data insertion, namely truncating of floating point values

Bug #12462	Corruption of data insertion, namely truncating of floating point values
Submitted:	9 Aug 2005 14:49	Modified:	24 Aug 2005 7:22
Reporter:	Vince Barwinski	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.13	OS:	Linux (Linux)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
While I was in Poland, a friend of mine transferred my website from an American server, to an Australian server. Now, everything at first seemed in order. But then I noticed, some operations on my website that required the use of floating point number data were giving what I could see as utterly implausible results. I wondered at first if my PHP scripts had been corrupted, but soon I found that some of my MySQL tables with floating point number data were corrupted.

For example, in my location_table, cities with a longitude greater than 100 degrees East or West, eg Sydney (about 151 degrees East) or Perth (about 115 degrees East) had both been recorded, along with many other cities, as only 100 degrees east. In other words, they had been truncated/rounded off to 100. Other cities with a longitude value less than 100 were OK, and any NON-FLOATING POINT data was accurately recorded, eg INT, VARCHAR and so on. The trend was the same on all my other tables.

So, I decided to drop all these corrupted tables, and use my mysqldump backup to reinsert all the data, but still the same NON-FLOATING POINT data bug was still occuring, namely, any floating point values above 100, were being truncated or rounded off to 100. In the end, the only thing I could do to fix the problem, was to labouriously redefine all my floating point values from say double(8,6) to double(12,6). This seemed to mostly fix the problem. My webserver for barwinski.net uses MySQL version 4.1.13 ----- AND I SUSPECT IT WAS COMPILED. Why?

Well, a couple of weeks ago, after returning to Australia, during an upgrade of my own computer to Fedora Core 4, I decided to also try an upgrade to MySQL 4.1.13. I COMPILED it.

Now, on my own local computer, I have a copy of my website, running on MySQL/Apache/PHP, just like on my website. Lo and behold, in exactly the same MySQL tables, I had the exactly the same floating point data corruption: all floating point values above 100, were being truncated or rounded off to 100. Any update queries I ran to correct the data did nothing, just like on my website. In the end, I uninstalled the Compiled MySQL version 4.1.13, and replaced it with my old binary:

MySQL(mysql-standard-4.0.20-pc-linux-i686.tar.gz)

PROBLEM SOLVED!

------Something of course, I could not do on my remote website, as I do not have the authority to do so.

Cheers
Vince Barwinski

How to repeat:
Try and execute an UPDATE query which attempts to correct the corrupted floating point data. You will see that the corrupted data will not be corrected. MySQL says that the query was successful, but when you check it, you will see no change in the value.

Vince, we need a testcase in order to investigate that bug. Could you please write what table structure did you use and what exactly query you submit? And it would be great if you provide some test data.

Hi Aleksey

Here is the table structure and example data. With this bug, the data field value of longitude_degrees (not longitude_minutes) for London and Bucharest will be correctly inserted into the table, as zero for London, and 26 for Bucharest are less than 100. However, for the Australian cities with longitudes greater than 100, eg Sydney with longitude_degrees = 151, it will be incorrectly rounded off to 100.

longitude_degrees is the third field. longitude_minutes is the fourth field --- This field will always be entered by the INSERT query correctly, as it's value will always lie between zero and 60, that is, always less than 100

Note, that this bug appears in any other table which may contain fields that may have floating point values higher than 100.

/////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\

CREATE DATABASE /*!32312 IF NOT EXISTS*/ world_distances;

USE world_distances;

---- Table structure for table 'location_table'----

CREATE TABLE location_table 
(location_id int(10) unsigned NOT NULL auto_increment,
 LOCATION_NAME varchar(50) NOT NULL default '',
 longitude_degrees float(8,6) NOT NULL default '0.000000',
 longitude_minutes float(8,6) NOT NULL default '0.000000',
 EW char(1) NOT NULL default '',  
 latitude_degrees float(8,6) NOT NULL default '0.000000',
 latitude_minutes float(8,6) NOT NULL default '0.000000',
 NS char(1) NOT NULL default '',
 PRIMARY KEY  (location_id)) 
TYPE=ISAM PACK_KEYS=1;

---- Dumping data for table 'location_table'---- 

INSERT INTO location_table VALUES (1,'Sydney NSW Australia',151.000000,10.000000,'E',33.000000,55.000000,'S');
INSERT INTO location_table VALUES (2,'London UK',0.000000,6.000000,'W',51.000000,32.000000,'N');
INSERT INTO location_table VALUES (3,'Brisbane Qld Austalia',153.000000,0.000000,'E',27.000000,30.000000,'S');
INSERT INTO location_table VALUES (69,'Bucharest Romania',26.000000,6.000000,'E',44.000000,25.000000,'N');

/////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\

Cheers

I verified the bug on Linux Suse 9.3 and Windows 2005. I found that longitude_degrees had corrupted value. Instead of 151.000000 and 156.000000 the follow query returns 100.000000

mysql> select longitude_degrees from location_table;
+-------------------+
| longitude_degrees |
+-------------------+
|        100.000000 |
|          0.000000 |
|        100.000000 |
|         26.000000 |
+-------------------+
4 rows in set (0.00 sec)

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

the 'longitude_degrees' field is declared as float(8,6).
This means that 99.999999 is the maximum value to set to that field.
The fact that sometimes MySQL allows bigger values there is rather a
defect.

mysql> create table t1 (ld1 float(8,6),  ld2 float(9,6));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(151.000000, 151.00000);
Query OK, 1 row affected, 1 warning (3.22 sec)

mysql> select * from t1;
+------------+------------+
| ld1        | ld2        |
+------------+------------+
| 100.000000 | 151.000000 |
+------------+------------+
1 row in set (0.00 sec)