| Bug #83537 | WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS timeout value handles some inputs badly | ||
|---|---|---|---|
| Submitted: | 25 Oct 2016 22:33 | Modified: | 31 Mar 2017 19:18 |
| Reporter: | Dan Reif | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.6.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 Oct 2016 6:48]
MySQL Verification Team
Hello Dan Reif, Thank you for the report and feedback. Thanks, Umesh
[31 Oct 2016 6:49]
MySQL Verification Team
-- 5.6.32
-- Setup simple gtid enabled master-slave replication
rm -rf master
scripts/mysql_install_db --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.6.34 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.34/master -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.6.34 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.34/master --core-file --socket=/tmp/mysql_master.sock --port=3306 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.6.34/master/log.err --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency --server-id=1 2>&1 &
rm -rf slave
scripts/mysql_install_db --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.6.34 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.34/slave -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.6.34 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.34/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.6.34/slave/log.err --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency --server-id=2 2>&1 &
-- Master
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| hod03-bin.000001 | 1918 | | | 462648d7-9f34-11e6-a100-0010e05f3e06:1-11 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
-- Slave
mysql> SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('462648d7-9f34-11e6-a100-0010e05f3e06:1-11',1.5);
+------------------------------------------------------------------------------------+
| WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('462648d7-9f34-11e6-a100-0010e05f3e06:1-11',1.5) |
+------------------------------------------------------------------------------------+
| 0 |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('462648d7-9f34-11e6-a100-0010e05f3e06:1-12',1.5);
+------------------------------------------------------------------------------------+
| WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('462648d7-9f34-11e6-a100-0010e05f3e06:1-12',1.5) |
+------------------------------------------------------------------------------------+
| -1 |
+------------------------------------------------------------------------------------+
1 row in set (2.00 sec)
mysql> SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('462648d7-9f34-11e6-a100-0010e05f3e06:1-12',0.5);
+------------------------------------------------------------------------------------+
| WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('462648d7-9f34-11e6-a100-0010e05f3e06:1-12',0.5) |
+------------------------------------------------------------------------------------+
| -1 |
+------------------------------------------------------------------------------------+
1 row in set (1.00 sec)
mysql> SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('462648d7-9f34-11e6-a100-0010e05f3e06:1-12',0.01);
^^ waits forever
mysql> SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('462648d7-9f34-11e6-a100-0010e05f3e06:1-13',-1);
^^ waits forever
[2 Nov 2016 8:57]
Sven Sandberg
Posted by developer:
Thank you for the bug report. Yes, it would be nice to handle negative and fractional numbers better.
If you are only after a non-blocking way to check if a GTID or set of GTIDs has been executed, note that there is:
SELECT GTID_SUBSET('gtid_set', @@GLOBAL.GTID_EXECUTED)
[31 Mar 2017 19:18]
Daniel So
Posted by developer: Added the following entry to the MySQL server 5.6.36, 5.7.18, 8.0.1 changelogs: "When a negative or fractional timeout parameter was supplied to WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(), the server behaved in unexpected ways. With this fix: + A fractional timeout value is read as-is, with no round-off. + A negative timeout value is rejected with an error if the server is on a strict SQL mode; if the server is not on a strict SQL mode, the value makes the function return NULL immediately without any waiting and then issue a warning."
Description: WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() takes two parameters: a GTID (or GTID-set) and an optional "timeout" parameter. When supplied with a timeout parameter that is anything other than a positive integer or zero, the server behaves oddly: WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('gtid', 1.5); waits 1.0 seconds. WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('gtid', 0.5); waits forever (by convention, timeout=0 waits forever). WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('gtid', -1); also appears to wait forever, though it might have just underflowed an internal unsigned value; attempts to prove this through carefully chosen constants were not successful, however. In particular, this precludes the usage of the useful trick: WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('gtid', 0.000001); to, in an (almost-) nonblocking way, determine whether a given GTID has been processed yet. How to repeat: Covered above ^ ^ Suggested fix: timeout should support noninteger values, and should error out when supplied with negative ones.