Bug #22591 Error In Replication of FUNCTIONs vs PROCEDURES
Submitted: 22 Sep 2006 13:55 Modified: 7 Dec 2006 11:42
Reporter: Daniel Fiske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.22 OS:Windows (WIN32)
Assigned to: CPU Architecture:Any
Tags: FUNCTION, replication, Rounding, stored procedure

[22 Sep 2006 13:55] Daniel Fiske 
Description:
Rounding error when replication run functions.

I'll admit part of this may be down to my bad coding. Look and decide.

How to repeat:
1. Set up a replicated database (5.0.22->5.0.22).
2. On the slave enable full logging (log=<filename> in my.ini)
3. Run the following on the master to reproduce.

CREATE TABLE `tbl_bug_test` (
  `dbl_bug_test` double(12,4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE PROCEDURE `sp_bug_test01`(IN dbl_val DOUBLE(12,4))
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
     INSERT INTO `tbl_bug_test` VALUES (dbl_val);
END;

CREATE FUNCTION `fn_bug_test`(dbl_val DOUBLE(12,4))
    RETURNS int(11)
    DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT ''
BEGIN
     INSERT INTO `tbl_bug_test` VALUES (dbl_val);
     RETURN 1;
END;

4. On the master run

CALL sp_bug_test01(0.5);
SELECT fn_bug_test(0.5);
select * from tbl_bug_test;

5. The last should return
+--------------+
| dbl_bug_test |
+--------------+
|       0.5000 |
|       0.5000 |
+--------------+

6. On the slave run

select * from tbl_bug_test;

7. This returns
+--------------+
| dbl_bug_test |
+--------------+
|       0.5000 |
|       1.0000 |
+--------------+

8. On the slave look in the log for the replicated calls you'll see

		     33 Query       INSERT INTO `tbl_bug_test` VALUES ( NAME_CONST('dbl_val',0.5000))
		     33 Query       COMMIT /* implicit, from Xid_log_event */
		     33 Query       SELECT `fn_bug_test`(1)
		     33 Query       COMMIT /* implicit, from Xid_log_event */

9. Seems why is it replicated as SELECT `fn_bug_test`(1)???
[13 Oct 2006 14:33] Valeriy Kravchuk 
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.26, on both master and slave, and inform about the results.
[13 Oct 2006 15:42] Daniel Fiske 
Will test on 5.0.27 when released (waiting for fix to Bug #20028 to be pushed) and will leave comment.
[16 Oct 2006 8:07] Valeriy Kravchuk 
Please, wait for it and inform about any results with 5.0.27-BK.
[17 Nov 2006 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[7 Dec 2006 11:42] Daniel Fiske 
Tested on 5.0.27....and bug appears to be fixed