MySQL Bugs: #44995: mysqldump does not properly format MAX DOUBLE values in its output.

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:	None
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

Description:
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
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

SET @@GLOBAL.CONCURRENT_INSERT = 0;

--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');
SELECT * FROM t1;
--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
SELECT * FROM t1;
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.

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.

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.

failing mysqldump output (no quotes in INSERTs)

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

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