| Bug #85480 | @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON | ||
|---|---|---|---|
| Submitted: | 16 Mar 2017 12:01 | Modified: | 26 Mar 2017 19:04 |
| Reporter: | kfpanda kf | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.7.17 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Mar 2017 19:04]
MySQL Verification Team
Thanks for your bug submission, verified as described Best regards Bogdan
[29 Jun 2017 15:15]
Gopinath G
But this affects me when gtid_mode=off can you please suggest .. This stops my replication exec_msater log position is not moving .. this increases seconds behind master .. any suggestion to overcome this
[29 Jun 2017 15:42]
MySQL Verification Team
I cannot reproduce this with gtid off, you might want to contact mysql support team to see what problem you have. I assume you have something miss-configured best regards Bogdan [root@localhost mysql]# mysqlbinlog data/binlog.000005 -v /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170629 17:36:54 server id 1 end_log_pos 123 Start: binlog v 4, server v 5.7.18-log created 170629 17:36:54 BINLOG ' lh5VWQ8BAAAAdwAAAHsAAAAAAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AJ0eq5Y= '/*!*/; # at 123 #170629 17:36:54 server id 1 end_log_pos 190 Previous-GTIDs # 9df9f420-3b79-11e7-a372-000c29c354f6:1-49 # at 190 #170629 17:36:55 server id 1 end_log_pos 230 Rotate to binlog.000006 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost mysql]# mysqlbinlog data/binlog.000005 | mysql ERROR 1782 (HY000) at line 19: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. [root@localhost mysql]# vi /etc/my.cnf [root@localhost mysql]# bin/mysqladmin shutdown 2017-06-29T15:39:16.806622Z mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended [1]+ Done bin/mysqld_safe [root@localhost mysql]# bin/mysqld_safe & [1] 89279 [root@localhost mysql]# 2017-06-29T15:39:23.509471Z mysqld_safe Logging to '/usr/local/mysql/data/localhost.localdomain.err'. 2017-06-29T15:39:23.524791Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data [root@localhost mysql]# bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.18-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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 binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000001 | 10398 | | binlog.000002 | 2150 | | binlog.000003 | 5573 | | binlog.000004 | 230 | | binlog.000005 | 230 | | binlog.000006 | 209 | | binlog.000007 | 190 | +---------------+-----------+ 7 rows in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.03 sec) mysql> flush logs; Query OK, 0 rows affected (0.03 sec) mysql> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000001 | 10398 | | binlog.000002 | 2150 | | binlog.000003 | 5573 | | binlog.000004 | 230 | | binlog.000005 | 230 | | binlog.000006 | 209 | | binlog.000007 | 230 | | binlog.000008 | 230 | | binlog.000009 | 190 | +---------------+-----------+ 9 rows in set (0.00 sec) mysql> \q Bye [root@localhost mysql]# mysqlbinlog data/binlog.000008 -v /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170629 17:39:35 server id 1 end_log_pos 123 Start: binlog v 4, server v 5.7.18-log created 170629 17:39:35 BINLOG ' Nx9VWQ8BAAAAdwAAAHsAAAAAAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AG2reJs= '/*!*/; # at 123 #170629 17:39:35 server id 1 end_log_pos 190 Previous-GTIDs # 9df9f420-3b79-11e7-a372-000c29c354f6:1-49 # at 190 #170629 17:39:35 server id 1 end_log_pos 230 Rotate to binlog.000009 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost mysql]# mysqlbinlog data/binlog.000008 | mysql [root@localhost mysql]# mysql -e "select @@GLOBAL.GTID_MODE" +--------------------+ | @@GLOBAL.GTID_MODE | +--------------------+ | OFF | +--------------------+ [root@localhost mysql]#
[29 Jun 2017 17:34]
Gopinath G
in the binlog event i can see | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' this info if this event came to slave server exec_masterlog position is get stuck it cant moving in slave replication .. after giving sql slave skip counter=1 .. than only its moving ... else second behind master keep on this increasing ..please give any suggestion
Description: mysqlbinlog printed a ROLLBACK at the end of the binary log file, which when played back caused the error -@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON..- This occurred when the binary log file did not include any data related events. How to repeat: Generate a binary log file which did not include any data related events. mysql -uroot -p123456 mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 201 | +------------------+-----------+ mysql> flush logs; mysql> flush logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 201 | | mysql-bin.000003 | 201 | | mysql-bin.000004 | 201 | +------------------+-----------+ # mysqlbinlog mysql-bin.000003|mysql -uroot -p'123456' mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1782 (HY000) at line 19: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. Suggested fix: stops a ROLLBACK from setting gtid_next to ANONYMOUS when gtid_next has not yet been determined by a subsequent event.