MySQL Bugs: #65025: The maximum floating point value breaks replication.

Bug #65025	The maximum floating point value breaks replication.
Submitted:	18 Apr 2012 19:10	Modified:	27 Apr 2012 18:52
Reporter:	Tony Edgin	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.48, 5.1.62	OS:	Any (CentOS 5, RHEL 6, MacOSx 10.7)
Assigned to:		CPU Architecture:	Any
Tags:	max float double replication
Triage:	Needs Triage: D3 (Medium)

Description:
My software needs to store floating point values from computation where NaN is a meaningful value.  My software connects to the database using the C API, and sends data using prepared statements.  In order to support NaNs, I map them to -FLT_MAX.  The insert succeeds on the master, but it causes the replication on the slave to break.  I get the same problem for FLT_MAX and +/-DBL_MAX.

Inspecting the binary log, the -FLT_MAX is written as -3.4028234663853e+38.  If I edit the log by chopping off the least significant digit, -3.402823466385e+38, the slave will replicate the value.  I suspect the binary log writing routine is using too much precision to represent floating point values.

How to repeat:
Set up two MySQL DBMSs with master/slave replication.
Create a table, t, in a database with a floating point column, c.
Write a simple C program that connects to the master and uses a prepared statement to write the value -FLT_MAX to the c column of table t.
Run the C program.

Suggested fix:
Reduce the precision of the string conversion of float and double values in the binary log writing routine.

Please, check if the same problem still happens with a recent versions, 5.1.61. If it does, please, send CREATE TABLE for the table used and exact INSERT statements used to repeat this.

As requested, I've confirmed the problem still exists in 5.1.62 on Mac OS X 10.7.

To cause this to happen, here's a minimal table create statement.

CREATE TABLE t (c FLOAT);

In a C program, do the following.
#define INSERT_SAMPLE "INSERT INTO t(c) VALUES(?)"
MYSQL_STMT * stmt;
MYSQL_BIND cBind;
float cBuf;
stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, INSERT_SAMPLE, strlen(INSERT_SAMPLE));
memset(&cBind, sizeof(cBind));
bind.buffer_type = MYSQL_TYPE_FLOAT;
bind.buffer = (char *)&cBuf;
bind.is_null = 0;
bind.length = 0;
mysql_stmt_bind_param(stmt, &cBind);
cBuf = -FLT_MAX;
mysql_stmt_execute(stmt);

Run the program.  

Now look do the following on the slave.

SHOW SLAVE STATUS\G

*************************** 1. row ***************************
[SNIP] 
                 Last_Errno: 1264
                   Last_Error: Error 'Out of range value for column 'c' at row 1' on query. Default database: 'test'. Query: 'INSERT INTO t(c) VALUES (-3.40282346638529e+38)
[SNIP]
1 row in set (0.00 sec)

Similar bug #62322

Thank you for the report.

I can not repeat described behavior with test case which will be attached soon.

Please try this test case, check your master and slave and if problem still repeatable provide output of SHOW CREATE TABLE problem_table from both master and slave and configuration files from both servers.

test case

Attachment: bug65025.c (text/plain), 2.20 KiB.

The provided test program replicates the bug on my system.

I forgot to mention that on both the master and slave, the configuration parameter sql-mode has been set to STRICT_ALL_TABLES.

On the master:

mysql> SHOW CREATE TABLE testfloat\G
*************************** 1. row ***************************
       Table: testfloat
Create Table: CREATE TABLE `testfloat` (
  `val` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

On the slave:

mysql> SHOW CREATE TABLE testfloat\G
*************************** 1. row ***************************
       Table: testfloat
Create Table: CREATE TABLE `testfloat` (
  `val` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Thank you for the feedback.

Verified as described. Problem is not repeatable with version 5.5

Issue is addressed n 5.5 and will not be back-ported to 5.1