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)

[18 Apr 2012 19:10] Tony Edgin
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.
[19 Apr 2012 7:21] Valeriy Kravchuk
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.
[19 Apr 2012 21:50] Tony Edgin
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)
[25 Apr 2012 18:24] Sveta Smirnova
Similar bug #62322
[25 Apr 2012 18:44] Sveta Smirnova
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.
[25 Apr 2012 18:45] Sveta Smirnova
test case

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

[25 Apr 2012 20:55] Tony Edgin
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)
[26 Apr 2012 10:24] Sveta Smirnova
Thank you for the feedback.

Verified as described. Problem is not repeatable with version 5.5
[27 Apr 2012 18:52] Omer Barnir
Issue is addressed n 5.5 and will not be back-ported to 5.1