Bug #11823 export from version 4 , import to ver 5 corrupts float(7,6) data
Submitted: 8 Jul 2005 18:13 Modified: 8 Jul 2005 18:31
Reporter: Zachary Buckholz Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5 OS:Linux (centos linux)
Assigned to: Geert Vanderkelen CPU Architecture:Any

[8 Jul 2005 18:13] Zachary Buckholz 
Description:
export of longitutde and lattitude data from version 4 mysql to version 5 causes float(7,6) values to appear as 9999.999

CREATE TABLE `zipcodes` (
  `zipcode` varchar(10) NOT NULL default '',
  `lon` float(7,6) NOT NULL default '0.000000',
  `lat` float(7,6) NOT NULL default '0.000000',
  `city` varchar(100) NOT NULL default '',
  `state` char(2) NOT NULL default '',
  `county` varchar(100) NOT NULL default '',
  `type` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`zipcode`),
  KEY `city` (`city`,`state`)
) TYPE=MyISAM;

-- 
-- Dumping data for table `zipcodes`
-- 

INSERT INTO `zipcodes` VALUES ('00501', 40.922325, -72.637077, 'HOLTSVILLE', 'NY', 'SUFFOLK', 'UNIQUE\n');
INSERT INTO `zipcodes` VALUES ('00544', 40.922325, -72.637077, 'HOLTSVILLE', 'NY', 'SUFFOLK', 'UNIQUE\n');

How to repeat:
CREATE TABLE `zipcodes` (
  `zipcode` varchar(10) NOT NULL default '',
  `lon` float(7,6) NOT NULL default '0.000000',
  `lat` float(7,6) NOT NULL default '0.000000',
  `city` varchar(100) NOT NULL default '',
  `state` char(2) NOT NULL default '',
  `county` varchar(100) NOT NULL default '',
  `type` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`zipcode`),
  KEY `city` (`city`,`state`)
) TYPE=MyISAM;

-- 
-- Dumping data for table `zipcodes`
-- 

INSERT INTO `zipcodes` VALUES ('00501', 40.922325, -72.637077, 'HOLTSVILLE', 'NY', 'SUFFOLK', 'UNIQUE\n');
INSERT INTO `zipcodes` VALUES ('00544', 40.922325, -72.637077, 'HOLTSVILLE', 'NY', 'SUFFOLK', 'UNIQUE\n');

Suggested fix:
Change float table definition
[8 Jul 2005 18:31] Geert Vanderkelen 
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:

Hi,

Actually they show as: 9.999999 | -9.999999

Which is expected for float(7,6) (signed)
http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html

Your data will not fit in a float(7,6). SHOW WARNINGS after your INSERT says so.

Geert