MySQL 5.7并发复制隐式bug实例分析

前言

我们MySQL线上环境大部分使用的是5.7.18的版本,这个版本已修复了很多bug,但针对主从复制的bug还是有很多的,尤其是一些组复制、并行复制的bug尤为突出,在5.7.19版本有做相应改善和修复。所以建议5.7.19之前的版本还是不要使用mgr和并发复制的功能,如使用建议升级至5.7.19(含)以后的版本。

我这里遇到的问题主要是莫名其妙的数据同步出现问题,无法执行stop slave,数据不一致等现象,经过查看发现是版本bug所致,所以对已上线的从库关闭并发复制,对未上线的系统实行版本升级。此风险非常非常高,各位务必重视。

具体5.7.19修复的复制bug如下:

参考手册:https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-19.html

References: See also: Bug #84107.

Replication: In the case of delayed initialization of the Group Replication plugin, deployed in single-primary mode, secondaries were able to get writes through an asynchronous replication channel, which is not allowed in normal initialization of the Group Replication plugin. (Bug #26314756)

Replication: With GTIDs generated for incident log events, MySQL error code 1590 (ER_SLAVE_INCIDENT) could not be skipped using the --slave-skip-errors=1590 startup option on a replication slave. (Bug #26266758)

Replication: A USE statement that followed a SET GTID_NEXT statement sometimes had no effect. (Bug #26128931)

Replication: Groups can now contain members running different server versions to enable you to do online upgrades of a replication group. The rules for combining members in a group with different versions are:

If you have a group with 8.0 members, you cannot add a 5.7 member

If you have a group with 5.7 members you can add a 8.0 member, but it remains in read-only mode. Writing to this member is dangerous while the group contains multiple server versions and should be avoided.

In a single-primary group, if the current primary leaves the group and a new primary must be elected, the primary is first chosen from the lower version members. If no lower version member is found, the primary is chosen from newer version members. (Bug #25876807)

Replication: When binlog_checksum=NONE was set on a MySQL server after startup, and then Group Replication was started, if an error occurred, the server remained in RECOVERING state and could not be shut down. (Bug #25793366, Bug #85667)

Replication: In a Group Replication setup where circular asynchronous replication was implemented between members of different replication groups, view change log events were repeatedly replicated between the groups with new generated GTIDs each time. The fix ensures that view change log events are ignored outside the named replication group where they occur, and never generate new GTIDs. (Bug #25674926)

References: See also: Bug #26049695, Bug #25928854, Bug #25721175.

Replication: When first starting the MySQL server following an installation from RPM, passwword validation plugin is activated by default (true only for RPM installations). If binary logging was already enabled at this time, the activation was logged, even though plugin activations should not be recorded in the binary log. (Bug #25672750)

Replication: In a setup where single-primary Group Replication was combined with asynchronous replication, for example with S1 and S2 forming a group and with S2 and S3 functioning as master and slave, secondaries such as S2 were accepting transactions and these could then enter the group. The fix prevents secondaries creating an asynchronous replication channel when belonging to a single-primary group, and Group Replication cannot be started when asynchronous replication is running. (Bug #25574200, Bug #85047)

References: See also: Bug #86325, Bug #26078602.

Replication: In the event that a member failed to join a group the member was not stopping and continued to accept transactions. To avoid this set your members to have super_read_only=1 in the my.cfg file. Group Replication now checks for this setting upon successful start up and sets super_read_only=0. This ensures that members which do not successfully join a group cannot accept transactions. (Bug #25474736, Bug #84728)

Replication: If the binary log on a master server was rotated and a full disk condition occurred on the partition where the binary log file was being stored, the server could stop unexpectedly. The fix adds a check for the existence of the binary log when the dump thread switches to next binary log file. If the binary log is disabled, all binary logs up to the current active log are transmitted to slave and an error is returned to the receiver thread. (Bug #25076007)

Replication: Interleaved transactions could sometimes deadlock the slave applier when the transaction isolation level was set to REPEATABLE-READ. (Bug #25040331)

Replication: If a relay log index file named relay log files that did not exist, RESET SLAVE ALL sometimes did not fully clean up properly. (Bug #24901077)

Replication: The slave_skip_errors system variable did not permit error numbers larger than 3000. Thanks to Tsubasa Tanaka for the patch. (Bug #24748639, Bug #83184)

Replication: mysqlbinlog, if invoked with the --raw option, does not flush the output file until the process terminates. But if also invoked with the --stop-never option, the process never terminates, thus nothing is ever written to the output file. Now the output is flushed after each event. (Bug #24609402)

Replication: A memory leak in mysqlbinlog was fixed. The leak happened when processing fake rotate events, or when using --raw and the destination log file could not be created. The leak only occurred when processing events from a remote server. Thanks to Laurynas Biveinis for his contribution to fixing this bug. (Bug #24323288, Bug #82283)

Replication: A slave server could lose events not yet applied when MASTER_AUTO_POSITION=0, both replication threads were stopped, and the applier delay was changed using CHANGE MASTER TO MASTER_DELAY=N. (Bug #23203678, Bug #81232)

References: See also: Bug #25340185, Bug #84375.

Replication: Transmission of large GCS messages could take so long the sender appeared to have died. (Bug #22671846)

Replication: Multithreaded slaves could not be configured with small queue sizes using slave_pending_jobs_size_max if they ever needed to process transactions larger than that size. Any packet larger than slave_pending_jobs_size_max was rejected with the error ER_MTS_EVENT_BIGGER_PENDING_JOBS_SIZE_MAX, even if the packet was smaller than the limit set by slave_max_allowed_packet.

With this fix, slave_pending_jobs_size_max becomes a soft limit rather than a hard limit. If the size of a packet exceeds slave_pending_jobs_size_max but is less than slave_max_allowed_packet, the transaction is held until all the slave workers have empty queues, and then processed. All subsequent transactions are held until the large transaction has been completed. The queue size for slave workers can therefore be limited while still allowing occasional larger transactions. (Bug #21280753, Bug #77406)

Replication: An incident event that broke replication was not written to the binary log with a GTID, so that it was not possible to skip the event using SET gtid_next=value. Instead, it was necessary to set the relay log file and relay log positions directly; this meant that, when autopositioning was enabled, it was necessary first to disable it, then to set the relay log file and position, and finally to re-enable autopositioning.

Now in such cases MySQL writes the incident event into the statement cache, so that a GTID is generated and written for it prior to flushing, and that the slave applier works with the change. Then users can skip the event using the SQL statement SET gtid_next=value, followed by BEGIN and COMMIT. (Bug #19594845)

Replication: In certain cases, the master could write to the binary log a last_committed value which was smaller than it should have been. This could cause the slave to execute in parallel transactions which should not have been, leading to inconsistencies or other errors. (Bug #84471, Bug #25379659)

Replication: When using group_replication_ip_whitelist=AUTOMATIC, IPs in the private network are permitted automatically, but some class C IP addresses were not being permitted correctly. (Bug #84329, Bug #25503458)

Replication: When an existing GTID_NEXT transaction was assigned a conflicting GTID by the server, Group Replication generated an assert upon detecting two transactions with same GTID. This was because Group Replication generates the GTID after conflict detection, which is later than with master/slave replication. The fix relaxes some conditions to only be called when commit is done and a message has been added to alert you when a GTID has already been used. (Bug #84153, Bug #25232042)

Replication: The replication applier thread returns Error 3002 ER_INCONSISTENT_ERROR when there is a difference between an expected error number and the actual error number. It is now possible to ignore this error by using 3002 with slave_skip_errors. (Bug #83186, Bug #24753281)

Replication: MySQL lost its GTID position following a restart when a dump from mysqldump had been used to load data.

To keep this problem from occurring, the mysql.gtid_executed table is now excluded automatically from dumps made by mysqldump. (Bug #82848, Bug #24590891)

References: See also: Bug #87455, Bug #26643180.

Replication: Corruption of relay logs for one channel in multi-source replication caused good channels not to be initalized during a server restart. In addition, when run with --skip-slave-start=false, the server also failed to start slave threads for those channels which were in good condition, despite the fact that it should have started the slave threads for all good channels.

Now, regardless of any errors on other channels, the server attempts to create and initialize channels that are in good condition, and starts slave threads for the good channels if --skip-slave-start is disabled. As part of this fix, START SLAVE and STOP SLAVE, which are intended to operate on all channels, are also modified such that they continue executing on all good channels even if they find bad channels among them. (Bug #82209, Bug #24285104)

Replication: The SQL thread was unable to GTID skip a partial transaction. (Bug #81119, Bug #25800025)

Debian client packages were missing information about conflicts with akonadi-backend-mysql packages. (Bug #26002288)

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

(0)

相关推荐

  • MySQL并发更新数据时的处理方法

    UPDATE是否会加锁? SQL语句为如下时,是否会加锁? UPDATE table1 SET num = num + 1 WHERE id=1; 答案是不会 实际上MySQL是支持给数据行加锁(InnoDB)的,并且在UPDATE/DELETE等操作时确实会自动加上排它锁.只是并非只要有UPDATE关键字就会全程加锁,针对上面的MySQL语句而言,其实并不只是一条UPDATE语句,而应该类似于两条SQL语句(伪代码): a = SELECT * FROM table1 WHERE id=1;

  • 分析MySQL并发下的问题及解决方法

    1.背景 对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标,既要满足大量并发访问的需求又必须保证在此条件下数据的安全,为了满足这一目标大多数数据库通过锁和事务机制来实现,MySQL数据库也不例外.尽管如此我们仍然会在业务开发过程中遇到各种各样的疑难问题,本文将以案例的方式演示常见的并发问题并分析解决思路. 2.表锁导致的慢查询的问题 首先我们看一个简单案例,根据ID查询一条用户信息: mysql> select * from user where

  • PHP+MySQL高并发加锁事务处理问题解决方法

    本文实例讲述了PHP+MySQL高并发加锁事务处理问题解决方法.分享给大家供大家参考,具体如下: 1.背景: 现在有这样的需求,插入数据时,判断test表有无username为'mraz'的数据,无则插入,有则提示"已插入",目的就是想只插入一条username为'mraz'的记录. 2.一般程序逻辑如下: $conn = mysqli_connect('127.0.0.1', 'root', '111111') or die(mysqli_error()); mysqli_selec

  • MySQL 5.7并发复制隐式bug实例分析

    前言 我们MySQL线上环境大部分使用的是5.7.18的版本,这个版本已修复了很多bug,但针对主从复制的bug还是有很多的,尤其是一些组复制.并行复制的bug尤为突出,在5.7.19版本有做相应改善和修复.所以建议5.7.19之前的版本还是不要使用mgr和并发复制的功能,如使用建议升级至5.7.19(含)以后的版本. 我这里遇到的问题主要是莫名其妙的数据同步出现问题,无法执行stop slave,数据不一致等现象,经过查看发现是版本bug所致,所以对已上线的从库关闭并发复制,对未上线的系统实行

  • JS面试题大坑之隐式类型转换实例代码

    1.1-隐式转换介绍 在js中,当运算符在运算时,如果两边数据不统一,CPU就无法计算,这时我们编译器会自动将运算符两边的数据做一个数据类型转换,转成一样的数据类型再计算 这种无需程序员手动转换,而由编译器自动转换的方式就称为隐式转换 例如1 > "0"这行代码在js中并不会报错,编译器在运算符时会先把右边的"0"转成数字0`然后在比较大小 1.2-隐式转换规则 转成string类型: +(字符串连接符) 2..转成number类型:++/--(自增自减运算符

  • C++隐式转换问题分析及解决办法

    我们先来看下实例代码: #include <iostream> #include <string> using std::cin; using std::cout; using std::endl; using std::string; int main() { unsigned int a; int b = -1; while (cin >> a) { if (a > b) { cout << "a > b" <<

  • MySQL权限控制和用户与角色管理实例分析讲解

    目录 一.MySQL用户登录 二.用户管理 三.权限控制 四.角色管理 一.MySQL用户登录 一般在本机上我们的登录命令:mysql -u root -p+密码 这里介绍命令的作用:-u  指定用户名-h  指定主机地址(默认为localhost)-p  指定用户输入的密码-P  指定服务器的端口(默认为3306) 二.用户管理 什么是用户管理呢?在创建了一个数据库后,数据库的管理员或者是创建者可以创建用户并同时对该用户做出限制(也就是用户的权限),当其他人想要访问该数据库时,可以通过使用该用

  • javascript显式类型转换实例分析

    本文实例讲述了javascript显式类型转换的方法.分享给大家供大家参考.具体分析如下: 尽管js可以做许多自动类型转换,但某些时候仍然需要做显示类型转换或为了代码逻辑清晰易读而做显示类型转换. 做显示类型转换最简单的方法就是用Boolean().Number().String()或Object()函数: Number("3") //3 String(false) //"false" false.toString()//同上 Boolean([]) //true

  • JS声明式函数与赋值式函数实例分析

    本文实例讲述了JS声明式函数与赋值式函数.分享给大家供大家参考,具体如下: 引言 "程序是不会骗人的"我们项目中的一个哥们经常这样说,为什么他会有这样的感叹呢?就是有时候我么程序员会出现的这样的问题,当我们让别人来调试错误的时候,别人什么都没有说,在我们给人家复现错误的时候发现,错误竟然没有了,留下自己在风中凌乱.此处中枪的童鞋们请顶起来......下面说说小编给别人调BUG时候遇到的问题如下: 请听题:说出下面几段js脚本的结果是什么? <script type="t

  • MySQL因大事务导致的Insert慢实例分析

    [问题] INSERT语句是最常见的SQL语句之一,最近有台MySQL服务器不定时的会出现并发线程的告警,从记录信息来看,有大量insert的慢查询,执行几十秒,等待flushing log,状态query end [初步分析] 从等待资源来看,大部分时间消耗在了innodb_log_file阶段,怀疑可能是磁盘问题导致,经过排查没有发现服务器本身存在硬件问题 后面开启线程上升时pstack的自动采集,定位MySQL线程等待的位置. [分析过程] 部署了pstack的自动抓取后,出现过6次thr

  • mysql触发器之创建多个触发器操作实例分析

    本文实例讲述了mysql触发器之创建多个触发器操作.分享给大家供大家参考,具体如下: 这次记录的内容mysql 版本必须得是5.7.2+的哈,之前的会不好使的.废话不多说,咱们开始正文哈. 在mysql 5.7.2+版本之前,我们只能为表中的事件创建一个触发器,例如,只能为BEFORE UPDATE或AFTER UPDATE事件创建一个触发器. mysql 5.7.2+版本解决了这样限制,并允许我们为表中的相同事件和动作时间创建多个触发器.当事件发生时,触发器将依次激活.我们来参考创建第一个触发

  • javascript中的深复制详解及实例分析

     javascript中的深复制 JavaScript深拷贝是初学者甚至有经验的开发着,都会经常遇到问题,并不能很好的理解javascript的深拷贝.        深拷贝(deepClone)是神马,与深拷贝相对应的就是浅拷贝,刚开始我也没弄懂. 在很多情况下,我们都需要给变量赋值,给内存地址赋予一个值,但是在赋值引用值类型的时候,只是共享一个内存区域,导致赋值的时候,还跟之前的值保持一直性. 看一个具体的例子 // 给test赋值了一个对象 var test = { a: 'a', b:

  • jQuery链式操作实例分析

    本文实例讲述了jQuery链式操作.分享给大家供大家参考,具体如下: 从过去的实例中,我们知道jQuery语句可以链接在一起,这不仅可以缩短代码长度,而且很多时候可以实现特殊的效果. <script type="text/javascript"> $(function() { $("div").addClass("css1").filter(function(index) { return index == 1 || $(this).

随机推荐