Bug #4171 using mysqldump and then importing for columns with double does not work

• Submitted: 16 Jun 2004 18:18
• Modified: 22 Jun 2004 19:35
• Reporter: Michael Guo
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.20a
• OS: Windows (Windows XP)
• CPU Architecture: Any

[16 Jun 2004 18:18] Michael Guo

Description:
I exported a database using mysqldump and then imported the database by redirecting the input to the sql file that mysqldump created. I have several columns that are of data type double that contain both doubles and integers. In the original database, all the data was correct. In the sql file mysqldump created, all the data was correct. However, once imported the data in those columns was incorrect. Integers that were inserted into those columns turned into odd floating point numbers that weren't even similar to the original data. Here is an example to make my point clearer:

Here is a description of the table:

--
-- Table structure for table `historicalvalues`
--

CREATE TABLE historicalvalues (
  ID int(11) default NULL,
  primarykey int(11) NOT NULL auto_increment,
  dataType varchar(50) default NULL,
  year int(11) default NULL,
  value double default NULL,
  PRIMARY KEY  (primarykey)
) TYPE=MyISAM;

Here is an example of what mysqldump produced:

INSERT INTO historicalvalues VALUES (702,28328,'mv',2002,13850);

Here is what should have been produced:

INSERT INTO historicalvalues VALUES (702,28328,'mv',2002,'13850');

Notice that the last column is double and that I'm inserting an integer. The integer must be enclosed in single quotes.

How to repeat:
Have a column with the data type double. Insert integers into it. Use mysqldump to export that database. Then, attempt to use that to create a new database. The inserted data is incorrect.

Suggested fix:
Add single quotes around all values to be inserted when exporting with mysqldump.

[17 Jun 2004 21:17] Hartmut Holzgraefe

not repeatable on Linux, maybe the conversion problem
is a windows only issue?

[22 Jun 2004 19:35] MySQL Verification Team

I tested using double columns and populated them with integers values
and in fact the mysqldump not encloses with simple quotes the double
column, but when used for to create a database the values I got were
correct.