The Downside of MySQL Auto-reconnect
A few days ago I was doing some cleanup on a passive master database using the MySQL client. I didn’t want my commands to be replicated so I executed set sql_log_bin=0
in my session.
One of my queries dropped an unused schema that I knew was corrupt, so I wasn’t too surprised when the drop database
command crashed the MySQL server. After the crash, the server came back up quickly, and my client automatically reconnected, so it was safe to keep running queries right?
Wrong.
When the client reconnected I lost my session state, so sql_log_bin
reverted to 1, and any commands I ran from that point forward would be replicated, which I did not want.
This behavior makes sense, and it’s documented in the manual:
Automatic reconnection can be convenient because you need not implement your own reconnect code, but if a reconnection does occur, several aspects of the connection state are reset on the server side and your application will not know about it. The connection-related state is affected as follows:
- Any active transactions are rolled back and autocommit mode is reset.
- All table locks are released.
- All TEMPORARY tables are closed (and dropped).
- Session variables are reinitialized to the values of the corresponding variables. This also affects variables that are set implicitly by statements such as SET NAMES.
- User variable settings are lost.
- Prepared statements are released.
- HANDLER variables are closed.
- The value of LAST_INSERT_ID() is reset to 0.
- Locks acquired with GET_LOCK() are released.
But it’s easy to overlook such details when working with automatic features like MySQL client auto-reconnect. In this specific case I didn’t execute any other commands in the reconnected session so I didn’t inadvertantly replicate anything, but this incident served as a good reminder to be vigilant about my session state when using the MySQL client.
Here’s a snippet from my session to show the value of sql_log_bin
before and after the crash:
1 |
|
https://mechanics.flite.com/blog/2013/05/03/the-downside-of-mysql-auto-reconnect/
最新文章
- 剑指Offer面试题:12.在O(1)时间删除链表结点
- Nokia 920板砖自救(理论上通用,升级Win10成板砖也可以用这个恢复)
- duapp获取mysql用户名密码等等……
- 实现开启和关闭android移动网络(转)
- sqlserver 出现 因为文件组 'PRIMARY' 已满 的解决办法 有可能是磁盘剩余空间不足 导致的
- e.target与事件委托简例
- MySQL常用命令大全(转)
- map遍历的四种方式
- Python学习笔记整理总结【RabbitMQ队列】
- 用 Javascript 实现的“Dual listbox”(双向选择器)
- 简单Socket网络通信
- Windows2008R2操作系统日志清理
- react表单的一些小例子
- Centos 6.3 安装教程
- 交叉编译zookeeper的C库
- [代码]--db2中left()函数和right()函数对应oracle中的substr()函数
- C/S与B/S区别
- ThreadLocal与Synchronized
- AndroidA——背景选择器selector用法汇总(一)
- window 命令行