MySQL Bugs: #21407: mysqldump uses insufficient precision for DOUBLE columns

Bug #21407	mysqldump uses insufficient precision for DOUBLE columns
Submitted:	2 Aug 2006 0:10	Modified:	2 Aug 2006 18:49
Reporter:	Mark Wadsworth	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	10.10, dist 5.0.20a	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When mysqldump dumps a DOUBLE value, it uses insufficient precision to distinguish between some close values (and, presumably, insufficient precision to recreate the exact values from the original database). If the DOUBLE value is a primary key or part of a unique index, restoring the database from this output fails with a duplicate key error.

How to repeat:
Execute these SQL statements:

create table defloat (n double, primary key (n));
insert into defloat values (1127507246.45464),(1127507246.454641);

Dump the resulting table with mysqldump. The INSERT statement produced is

INSERT INTO `defloat` VALUES (1127507246.45464),(1127507246.45464);

Suggested fix:
Use greater precision, enough to guarantee that single-bit differences result in different decimal representations.

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

Read how to MySQL stores data in FLOAT and DOUBLE columns if total number of digits and number of digeits following by decimal point has omitted in time then table was created here: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

>The database works fine
If you issue statements you provided
create table defloat (n double, primary key (n));
insert into defloat values (1127507246.45464),(1127507246.454641);
and after SELECT * FROM defloat; what do you see?

I see two identical values. But they aren't identical, or they wouldn't be allowed to be keys. If I do
select format(n,6) from defloat; 
I get
+----------------------+
| format(n,6)          |
+----------------------+
| 1,127,507,246.454640 |
| 1,127,507,246.454641 |
+----------------------+
2 rows in set (0.00 sec)
mysqldump is converting two different values into the same value. Is there any way I can get a dump of the database that includes all possible precision? I can't restore the database from the output I get from mysqldump.

> I see two identical values. But they aren't identical, or they wouldn't be
> allowed to be keys.

Yes, it is expected behaviour. mysqldump is client program and knows nothing nor about conversion you want to prepare before making dump, nor about values server really keeps.

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

OK.  But in that case I don't think mysqldump should be promoted as a way to backup and duplicate databases, without at least warning users that there are some simple cases where it will not produce a usable backup.

Thanks for your time.

We have a very large database that we'd like to convert to using single-file-per-table after upgrading mysql. Unfortunately, doing a dump/restore is the only way to do this and there are tables in the database with double type primary keys that fail uniqueness on restore. This has left us with no way to convert the database. This also means that our nightly backups using mysqldump are useless.

We ran into a similar issue trying to set up a replication slave over the same database - no way to restore a dump on the slave.

I don't understand how this cannot be classified as a bug. I DO classify this as a bug - a serious one, as there is no way to do what needs to be done without this being fixed. Any tool touted as a way to do a 'backup' that creates a file that cannot be restored is broken.