MySQL Bugs: #65812: mysqlbinlog -v does not properly decode DECIMAL values in an RBR log

Bug #65812	mysqlbinlog -v does not properly decode DECIMAL values in an RBR log
Submitted:	4 Jul 2012 17:27	Modified:	12 Sep 2012 7:51
Reporter:	Justin Swanhart	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S1 (Critical)
Version:	5.5.24, 5.5.26	OS:	Any (CentOS 6.2 x86_64, Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
mysqlbinlog does not properly decode DECIMAL values in a RBR log which results in wrong values being printed out for DECIMAL columns.  

I have attached a simple one row test case to demonstrate two problems that I have found.  

The value: -0.938582 is decoded as: -938582000.000000000
The value: 4294967296.001 as: 000000004.294967296.001000000.000000000

In case #1 -938582000 is very different from -0.938582
In case #2 there is more than one decimal place which is not valid

How to repeat:
mysql> create table test_case(c1 decimal(10,10), c2 decimal(20,10)) engine=myisam;
Query OK, 0 rows affected (0.20 sec)

mysql> reset master;
Query OK, 0 rows affected (0.49 sec)

mysql> insert into test_case values ('-0.938582',pow(2,32) + .001);
Query OK, 1 row affected (0.01 sec)

$ mysqlbinlog -v /var/lib/mysql/binary-log.000001 |grep "###"
### INSERT INTO geographic.test_case
### SET
###   @1=-938582000.000000000
###   @2=000000004.294967296.001000000.000000000

Suggested fix:
unknown

Thank you for the bug report. Verified just as described on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.26-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'binlog%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_cache_size                       | 32768 |
| binlog_direct_non_transactional_updates | OFF   |
| binlog_format                           | ROW   |
| binlog_stmt_cache_size                  | 32768 |
+-----------------------------------------+-------+
4 rows in set (0.01 sec)

mysql> create table test_case(c1 decimal(10,10), c2 decimal(20,10)) engine=myisam;
Query OK, 0 rows affected (0.33 sec)

mysql> insert into test_case values ('-0.938582',pow(2,32) + .001);
Query OK, 1 row affected (0.39 sec)

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| macbook-pro-bin.000002 |      565 |              |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.02 sec)

mysql> exit
Bye
macbook-pro:5.5 openxs$ bin/mysqlbinlog -v /Users/openxs/dbs/5.5/data/macbook-pro-bin.000002 | grep '###'
### INSERT INTO test.test_case
### SET
###   @1=-938582000.000000000
###   @2=000000004.294967296.001000000.000000000

What mysqlbinlog actually prints is the (useless) internal representation of the decimal value.

This is fixed in trunk. (Currently labelled 5.7.)

request backport to 5.6 and possibly 5.5.  This can't break compatibility with existing tools using this feature as this feature is broken in all versions and thus has never been of use.  If anyone is relying on the feature, they definitely need the fix because in many cases it is giving them wrong data.

This is also fixed in 5.6.16.

5.6$ bzr log -r 5696
------------------------------------------------------------
revno: 5696
committer: Venkatesh Duggirala<venkatesh.duggirala@oracle.com>
branch nick: mysql-5.6
timestamp: Tue 2013-12-17 17:13:02 +0530
message:
  BUG#17544169 MYSQLBINLOG -V DOES NOT PROPERLY DECODE
  DECIMAL VALUES IN AN RBR LOG
        
  Analysis: If verbose (-v) option is used in
  mysqlbinlog tool it decodes a RBR event into
  the query (the same query which was used at
  source to generate) and displays immediately
  after displaying the row event in the output.
  The algorithm used to display the decimal was
  wrongly written which were causing problems
  similar to the ones mentioned below.
  Eg: -0.938582 is decoded as: -938582000.000000000
  4294967296.001 as: 000000004.294967296.001000000.000000000 
        
  Fix: 'decimal2string' is an existing function
  which converts a decimal value into a string
  format. Hence the algorithm used was replaced with
  this existing 'decimal2string' function to
  avoid problems mentioned above.
  After this fix, decimal will be printed exactly
  the same way how they get printed in 'select' command
  output.
  Eg: -0.938582 for decimal(10,10) will be displayed as
  -0.938582000. 4294967296.001 for decimal(20,10) will
  be displayed as 4294967296.0010000000