Bug #44995 mysqldump does not properly format MAX DOUBLE values in its output.
Submitted: 20 May 2009 20:33
Reporter: Patrick Crews Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: CPU Architecture:Any
Tags: dump/restore, max double, MySQL

[20 May 2009 20:33] Patrick Crews
The mysql client does not accept MAX DOUBLE (+ or -) values via text file.
While the server accepts INSERT statements with values >= MAX DOUBLE (they get truncated to M D if larger), the mysql client returns the following error when it attempts to load these values via a text file:

ERROR 1367 (22007) at line 36: Illegal double '1.79769313486232e+308' value found during parsing

This should not happen as the server deliberately truncates larger values to this one AND accepts INSERTS that use it.

This was detected on Mac OSX 10.5, but is likely to be applicable on other platforms.

How to repeat:
Place the following into a .test file and try to record:

./mtr --record <name_of_test>

# Embedded server doesn't support external clients
--source include/not_embedded.inc
--source include/have_log_bin.inc

--echo # Cleanup


--echo # Taken from mysqldump.test
--echo # Bug#2055 mysqldump should replace "-inf" numeric field values with "NULL"
--echo #
let $dump_file = $MYSQL_VARDIR/tmp/bug_example.dat;
CREATE TABLE test.t1 (a double);
# truncates
INSERT INTO t1 VALUES ('-9e999999'); 
INSERT INTO t1 VALUES ('1.79769313486232e+308');
--exec $MYSQL_DUMP  test t1 > $dump_file
--echo # Rename original table to allow for mysql client recreation from
--echo # the mysqldump output
ALTER TABLE t1 RENAME to t1_org;

--exec $MYSQL test < $dump_file
DROP TABLE t1, t1_org;

Suggested fix:
Ensure that any value accepted / set by the server is also accepted by our clients.

I am presently unsure of if the problem lies in the mysql client code or is in the server, but this behavior seems inconsistent at present.
[21 May 2009 20:04] Patrick Crews
Recommend adding a test case for this fix to mysqldump_restore.test which will be added for Bug#40465.

Did not include such a test case in this file yet as it would currently fail.
[22 May 2009 14:09] Patrick Crews
After checking out the mysqldump file again, I found that the DOUBLE values in the INSERT statements are not placed in quotes, which is the cause of the rejection on restore.

Altering the dump file to add quotes to the INSERTs results in a successful restore.

I have attached the original, failing mysqldump output here.
[22 May 2009 14:09] Patrick Crews
failing mysqldump output (no quotes in INSERTs)

Attachment: dump.dat (, text), 1.73 KiB.

[22 Jan 2010 6:46] Anestis Sismanidis
I can reproduce this bug with Ubuntu 9.10 (desktop and server editions).

Currently I am using the replace command to workaround the bug e.g:

replace 1.79769313486232e+308 \'1.79769313486232e+308\' -- filename