Bug #69469 ERROR 1729 overfills by the value of MASTER_DELAY greater than 2^31
Submitted: 14 Jun 2013 8:53 Modified: 19 Sep 2013 13:27
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: DELAYED, replication

[14 Jun 2013 8:53] tsubasa tanaka
Description:
The range of MASTER_DELAY is between 0 and 2147483647(2^31 - 1) but it's not documented.

http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html

And when I take CHANGE MASTER TO MASTER_DELAY = N, over 4294967296(2^32), it overfills in ERROR 1729 message.

How to repeat:
mysql56> CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_DELAY = 2147483648;
ERROR 1729 (HY000): The requested value 2147483648 for the master delay exceeds the maximum 2147483647

mysql56> CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_DELAY = 4294967296;
ERROR 1729 (HY000): The requested value 0 for the master delay exceeds the maximum 2147483647

Suggested fix:
First, please document MASTER_DELAY limit is 2147483647.

Second, modify sql/sql_yacc.yy

$ diff -c ./sql/sql_yacc.yy ./sql/sql_yacc.yy.org
*** ./sql/sql_yacc.yy   2013-06-14 16:59:05.756760754 +0900
--- ./sql/sql_yacc.yy.org       2013-05-22 00:18:39.000000000 +0900
***************
*** 2225,2231 ****
              if ($3 > MASTER_DELAY_MAX)
              {
                my_error(ER_MASTER_DELAY_VALUE_OUT_OF_RANGE, MYF(0),
!                        static_cast<ulong>($3), MASTER_DELAY_MAX);
              }
              else
                Lex->mi.sql_delay = $3;
--- 2225,2231 ----
              if ($3 > MASTER_DELAY_MAX)
              {
                my_error(ER_MASTER_DELAY_VALUE_OUT_OF_RANGE, MYF(0),
!                        static_cast<uint>($3), MASTER_DELAY_MAX);
              }
              else
                Lex->mi.sql_delay = $3;

But this overfills more than 4294967296..
[14 Jun 2013 9:13] Umesh Shastry
Hello tsubasa,

This has been documented in the manual, please reference - http://dev.mysql.com/doc/refman/5.6/en/change-master-to.html

"MASTER_DELAY specifies how many seconds behind the master the slave must lag. An event received from the master is not executed until at least interval seconds later than its execution on the master. The default is 0. An error occurs if interval is not a nonnegative integer in the range from 0 to 2^31-1. For more information, see Section 16.3.9, http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html This option was added in MySQL 5.6.0. 

Regards,
Umesh
[14 Jun 2013 9:17] tsubasa tanaka
Hello Umesh,

I'm sorry, that is my ignorance..

I changed the synopsis, about overfill in Error 1729.
[14 Jun 2013 10:48] Umesh Shastry
Hello tsubasa,

Thank you for the report.
Verified as described.

Regards,
Umesh
[19 Sep 2013 13:27] Jon Stephens
Fixed in 5.6.14 / 5.7.2 see BUG#69249.