pdo，mysql 中binlog日志记录的一个 bug

最近发现数据库同步总是出问题，最诡异的时，主从数据库写入的数据不一样，我勒个去。程浩同学看了半天终于找到原因，原来是PDO的一个大坑，加上binlog的一个大坑。

首先声明，这篇文章有很强的攻击性，如果你利用这里面写的东西攻击，所造成的一切后果，自负！

       起因：
       2010/12/15 我的领导，突然要求我们开始折腾一下机器。主要的目的是，没做备份的，做一下备份，单个的数据库做主从，线上的机器要做一个能快速恢复的热备份。经过检查发现机器若干台需要整理，于是开始一一处理，其他的还算顺利，但发现了一个 数据库的同步经常有问题，主要问题表现在做好主从同步以后，经过一段时间就会发现重复的插入，引起同步失效。

 

       排查过程：
       1、首先考虑数据本身就不一致，造成的同步失效。
       处理方法： 

       在一个周五的晚上 12 点，夜深人静的时候，趁大家都熟睡的时候，直接对那台线上的数据库 shutdown ，在回写硬盘缓存之后，打包数据，从新同步。当天夜里、及次日均未发现数据不同步的情况。

       2、本以为问题解决，结果在周一，再次发现数据同步失败。

       处理方法： 

       考虑主从数据库系统及版本不一致的情况。从装系统，及数据库，系统（CentOS 5.5） 数据库 (Mysql-5.8.87) ，之后重新做的数据同步。

       3、 次日，悲剧在次发生，同步又掉了 …… 

       处理方法：

       现在有点茫然了，主从两台机器，从系统到数据库完全一样，数据也保证没有任何问题。为什么就同步不起来呢。现在只能考虑其他因素了。系统我使用 kickstar 装的，数据库是我自己制作的 rpm ， mysql 的数据更是从一个 tar 包里解出来的。为了方便测试，我把主库放到了一个卷上，对从数据库做了只读。清除所有日志，从新同步了数据库。并且求助于我的同事，张文亭、包鹏、李锁住，让他们和我共同观察这个问题，果不出所料，在之后的一段时间内，同步在一次的失败了。 经过数次的测试（因为有卷了，可以做卷影复制），我们发现了一个问题，每次同步失败的原因都是因为同一个错误，就是重复的键值，而且这些错误都是出现在同一个库的几张表内。我把这个库的数据 dump 出来，然后把库删除了，手工重建 库 和 表 ，然后把数据导入，重新同步。

        4、 结果大家应该猜得到，同步依旧失效 ……

        处理方法：
        实在没办法了，只好吧这个库挪到另外的单独的一台机器上，单独观察，其他剩余的库做了同步。

        5、 在那个库挪走以后同步竟然神奇的好了，观察了一周。

        处理方法：

        看来这问题就出在哪个被挪走的库上面了。于是把那个库的结构单独拿出来，做了主从，手工插入若干条，依旧没有出现任何问题，在把那个库的数据也导入了，手工插入更新若干条，也没有出现问题。观察使用了一周，一切正常。换到线上的同步一测试，不出半天同步又失效了 ！～～难道这就是传说中的人品问题？鉴于我最近没干啥亏心事，决定对这个东西出大招，一探究竟， 无奈之下，在网上请教了一下mysql 的大牛人物（叶金融），他也认为这可能是一个 mysql 的 bug ，于是就和我的同事李锁住开始折腾。

       依次查找用到的 apache ,php ,zend,pdo 等。这真是不看不知道，一看吓一跳！我发现这做运维的网管和程序员那就是一对天敌。看这些东西那叫一个郁闷。
       首先查看 apache ，发现是使用 apache 的 proxy 模块代理到另一个机器。
       到了那个机器我怎么也找不到访问的那个路径，我不懂 php 但配置 apache 不菜啊，咋就没有呢，这一通折腾才发现人家在那个目录下写了一个 .htaccess 又重定向了 ！～
       这次总算找到那个 php 了，一搜索光 Insert 函数就有 4、5 个。这玩意太多，还是找 mysql_query() 。找了半天没有～～，仔细一看用的是 PDO
       你用 PDO 你就 $var = new PDO(‘mysql:host=xx;dbname=xx;charset=gbk’,'xx’,”); 用吧，一找 new PDO 还没有。
       一点点的找下去发现 人家用的是 Zend_Db::factory()，还搞了一个超复杂的对象。 $config->db->adapter,$config->db->config->toArray() 这里咬牙、跺脚若干次。
       总算找到正主了，找这个可真不容易： 访问不直接访问，用 proxy 弄跑了，弄到另一个机器上也不老老实实的访问，整一个 302 跳转了，php 链接 mysql 有现成的 mysql_query() 不用，非要调用 PDO ，PDO还不直接调用，要用 Zend 的框架调用。
       接下来的事情就是跟踪 PDO Zend 调用过程，抓包查看交互的数据，根据许许多多的调试信息来看，终于发现了，这个 PDO 处理数据的方式比较特别。
       访问 mysql server 的方式有两种。
       1、 直接访问模式
       2、 预处理模式
       先说说这两个结构的区别，直接访问就像我们用客户端连接进数据那样，标准的 sql 语句插入、更新、删除和查询。这个要求就是每个命令里面都要指明 表、字段、等信息。
       预处理就是：先告诉 mysql 一个表的结构，然后，后面的全都按照这个表结构来，这样就不用每次都发送 表、字段等信息了。这样的优势是大量的插入会快一点。特点是只在第一次发送表结构。而不是每次都发送一遍，问题是 mysql binlog 里面不支持这种格式。
       这两种方式比较起来，第一种 安全，第二种 快速 。第二种因为没有表结构，所以当任何一个字段出现问题，就会造成所有的数据问题，而不像第一种，只影响那一句。那个 PDO 使用的就是第二种方式，而且他错误的认为一切都是字符串，把所有的数据都转换成 16 进制了。
       在第一次插入的时候 mysql 使用第二种方式插入数据，但 binlog 里面因为没有这种结构，所以他自己把语句转换成了 第一种模式，加上了表、及字段信息，但 mysql 不会对 int 形做相应的转换，（这个在字符串表示中是没有错误的），造成了记录的日志是按照字符串的方式记录的。这样在吧一个字符串插入 int 形就出现了插入的数据和日志不一致的情况。要解决这个问题只有 1、给mysql 写一个补丁，解决这个问题。（现在功力不够还写不出来） 2、在我们公司禁用 预处理结构体方式的数据写入。看来目前我们只能使用第二种方法了。

       结论：
       总的来说，pdo 写的有问题，mysql 的 log 记录转换的方式也存在问题。下面是我写的一个能够触发这个 bug 的代码。

 

 

 

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>

#define INSERT_QUERY "INSERT INTO a(a) VALUES(?)"
#if !defined(MC68000) && !defined(DS90)
char *strmov(register char *dst, register const char *src)
{
  while ((*dst++ = *src++)) ;
  return dst-1;
}
#else

char *strmov(dst, src), char *dst, *src;
{
  asm(" movl 4(a7),a1 ");
  asm(" movl 8(a7),a0 ");
  asm(".L4: movb (a0)+,(a1)+ ");
  asm(" jne .L4 ");
  asm(" movl a1,d0 ");
  asm(" subql #1,d0 ");
}

#endif

int main(int argc, char **argv)
{
    if (argc != 2)
    {
       printf("%s digit\n",argv[0]);
       return(1);
    }
    char *server="localhost",*user="root",*password="";
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, server, user, password, "test", 0, NULL, 0))
    {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit(EXIT_FAILURE);
    }
    MYSQL_STMT *stmt = mysql_stmt_init(conn);
    mysql_stmt_prepare(stmt, INSERT_QUERY, strlen(INSERT_QUERY));

    MYSQL_BIND bind[1];
    memset(bind, 0, sizeof(bind));
    unsigned long length;

    char query[100] = {0};

    char *pos = query;
    strcpy(query,argv[1]);

    bind[0].buffer_type= MYSQL_TYPE_BLOB;
    bind[0].buffer= query;
    bind[0].is_null= 0;
    bind[0].length= &length;
  
/* Bind the buffers */
    mysql_stmt_bind_param(stmt, bind);
  
/* Supply data in chunks to server */
    mysql_stmt_send_long_data(stmt,0, pos, strlen(query));
    mysql_stmt_execute(stmt);
    mysql_stmt_close(stmt);
}

 

 

测试过程如下：

 

mysql -uroot -p <<'EOF'
CREATE DATABASE test;
USE test;
DROP TABLE IF EXISTS a;
CREATE TABLE a (
  a int(11) NOT NULL COMMENT 'id',
  UNIQUE KEY a (a)
) ENGINE=MyISAM;
EOF

# 制作同步数据库，省略代码若干条

gcc -g $(mysql_config --cflags --libs) -o mysql_test mysql_test.c
./mysql_test 12345
./mysql_test 23456

# 现在查看你的从数据库已经不同步了。

以上转自：http://blog.chinaunix.net/uid-8746761-id-2015321.html

下面是我们的php测试代码：

<?php
$dsn = "mysql:host=localhost;dbname=wanke";
$db = new PDO($dsn, 'wanke', 'wanke');
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); //必须加
$db->exec('SET NAMES gbk'); //必须加
//$sth = $db->prepare("DELETE FROM `atest` WHERE t2=:t1");
//$sth = $db->prepare("INSERT INTO atest (`t1`,`t2`) VALUES(?,?)");
$sth = $db->prepare("INSERT INTO atest (`t1`,`t2`) VALUES(:a,:b)"); //必须是这种形式，不能是问号的
//$sth->bindValue(':t1','666777',PDO::PARAM_STR);
//$sth->bindValue(1,67890);
//$sth->bindValue(2,'ttttttasdfasttttt');
$sth->bindValue(':a','6784444');
$sth->bindValue(':b','ttttttasdfasttttt');
$count = $sth->execute();
echo $count;
$db = null;
?>

另外一种解决方案

主库使用：binlog_format="ROW" 模式可以避免这种情况的发生，不用修改PDO属性。mysql 默认的binlog 使用的是  Statement 模式

时间： 2024-10-27 15:57:38

pdo，mysql 中binlog日志记录的一个 bug的相关文章

mysql中bin-log日志操作命令

查看日志是否开启 1).可以通过Mysql配置文件my.cnf来确认(Mysql默认开启二进制日志记录): # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin 刷新日志 flush logs; 查看当前日志位置 show master status; 查看当前所有日志 show master logs; 清空所有的bin-log日志 reset m

MySQL的binlog日志

binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的. 一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版).二进制有两个最重要的使用场景: 其一:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致

THINKPHP项目开发中的日志记录实例分析_php实例

本文实例讲述了THINKPHP项目开发中的日志记录用法.分享给大家供大家参考.具体方法如下: 1.建立日志表 复制代码 代码如下: CREATE TABLE `logs` (    `id` int(11) NOT NULL auto_increment,    `guid` varchar(100) character set utf8 NOT NULL,    `addtime` timestamp NOT NULL default CURRENT_TIMESTAMP,    `accoun

MySQL中二进制日志binlog中的事件类型

MySQL binlog记录的所有操作实际上都有对应的事件类型的,譬如STATEMENT格式中的DML操作对应的是QUERY_EVENT类型,ROW格式下的DML操作对应的是ROWS_EVENT类型. 首先,看看源码中定义的事件类型 源码位置:mysql-5.7.14/libbinlogevents/include/binlog_event.h enum Log_event_type {   /**     Every time you update this enum (when you ad

mysql的binlog日志删除与限制大小

现象:网站访问越来越慢,最后无法访问了,经过检查发现磁盘满了.仔细查询下来确认是由于mysql的binlog太多太大占用了空间. 分析过程及解决方案:通常出现这种问题都应该登录服务器检查磁盘.内存和进程使用的情况,通过top.df –h和free –m来检查,发现磁盘空间满了.再进一步通过du –sh对可以的目录进行检查,发现是mysql的binlog占用空间过大.清理binlog的方法如下: 1) 设置日志保留时长expire_logs_days自动删除 查看当前日志保存天数:  代码如下 复

mysql利用binlog日志恢复数据库的例子

binlog日志用于记录所有更新了数据或者已经潜在更新了数据的所有语句.语句以"事件"的形式保存,它描述数据更改.当我们因为某种原因导致数据库出现故障时,就可以利用binlog日志来挽回(前提是已经配置好了binlog),接下来我们来配置 一.开启mysql-binlog日志 在mysql配置文件my.cnf加上如下配置  代码如下 复制代码 [mysqld] log-bin=mysql-bin 重启mysql  代码如下 复制代码   service mysqld restart 二

MySQL中查询日志与慢查询日志的基本学习教程_Mysql

一.查询日志   查询日志记录MySQL中所有的query,通过"--log[=file_name]"来打开该功能.由于记录了所有的query,包括所有的select,体积比较大,开启后对性能也有比较大的影响,所以请大家慎用该功能.一般只用于跟踪某些特殊的sql性能问题才会短暂打开该功能.默认的查询日志文件名为:hostname.log.  ----默认情况下查看是否启用查询日志: [root@node4 mysql5.5]# service mysql start Starting

关于MySQL count(distinct) 逻辑的另一个bug

背景          上一篇博文(链接)介绍了count distinct的一个bug.解决完以后发现客户的SQL语句仍然返回错误结果(0), 再查原因,发现了另外一个bug.也就是说,这个SQL语句触发了两个bug -_-   这里只说第二个,将问题简化后复现如下,影响已知的所有版本 .   drop table if exists tb; set tmp_table_size=1024; create table tb(id int auto_increment primary key,

linux中利用日志记录用户执行的命令

工作中,需要把用户执行的每一个命令都记录下来,并发送到日志服务器的需求,为此我做了一个简单的解决方案.这个方案会在每个用户退出登录 时把用户所执行的每一个命令都发送给日志守护进程rsyslogd,你也可通过配置"/etc/rsyslog.conf"进一步将日志发送给日志服务器 第一种方法  # vi /etc/profile #设置history格式 export HISTTIMEFORMAT="[%Y-%m-%d %H:%M:%S] [`who am i 2>/dev