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 模式