佛系CursorNotReadAllDataError的bug，会被select的col的顺序影响...

[fatpo]

版本： tormysql==0.2.9 tornado==4.2

2018-02-01 17:11:38,443 GetGongxiuList.py-58 [ERROR] If cursor not read all data, the connection next query is error.
Traceback (most recent call last):
  File "/data/www/myapp/biz_handlers/gongxiu/GetGongxiuList.py", line 43, in run
    yield GongxiuService.wrap_info_to_pb(conn, gx_info, pb_gx)
  File "/data/python_venv/chanzai_venv/lib/python2.7/site-packages/tornado/gen.py", line 870, in run
    value = future.result()
  File "/data/python_venv/chanzai_venv/lib/python2.7/site-packages/tornado/concurrent.py", line 215, in result
    raise_exc_info(self._exc_info)
  File "/data/python_venv/chanzai_venv/lib/python2.7/site-packages/tornado/gen.py", line 879, in run
    yielded = self.gen.send(value)
  File "/data/www/myapp/service/gongxiu/Gongxiu.py", line 46, in wrap_info_to_pb
    pb.creator.avatar = settings.QINIU_HTTP_HOST + '/' + settings.QINIU_DEFAULT_HEAD_IMG_KEY
  File "/data/python_venv/chanzai_venv/lib/python2.7/site-packages/tormysql/cursor.py", line 76, in __exit__
    raise CursorNotReadAllDataError("If cursor not read all data, the connection next query is error.")
CursorNotReadAllDataError: If cursor not read all data, the connection next query is error.
2018-02-01 17:11:38,444 web.py-1908 [INFO] 200 POST /api?type=json (127.0.0.1) 62.65ms

我的代码如下： 外层：

            with (yield pool.Connection()) as conn:
                yield conn.commit()

                for gx_info in gx_infos:
                    pb_gx = self.rsp_info.rsp.gongxius.add()
                    yield GongxiuService.wrap_info_to_pb(conn, gx_info, pb_gx)

内层：

    @classmethod
    @tornado.gen.coroutine
    def wrap_info_to_pb(cls, conn, info, pb):
        with conn.cursor() as cursor:
            # 补充用户头像 + 用户名
            sql = "select nick_name, user_name, head_img from %s.user where `status`=0 and id=%s" % (
                settings.db_zizaihome_name, info.creator_id)
            logging.info(sql)
            yield cursor.execute(sql)
            item = cursor.fetchone()
            if item:
                if item['nick_name']:
                    pb.creator.username = item['nick_name']
                elif item['user_name']:
                    pb.creator.username = item['user_name']
                if item['head_img']:
                    pb.creator.avatar = item['head_img']
                else:
                    pb.creator.avatar = settings.QINIU_HTTP_HOST + '/' + settings.QINIU_DEFAULT_HEAD_IMG_KEY

在我上洗手间之前，bug稳定重现，每一次都是执行到以下sql出错：

select user_name, nick_name, head_img from dev.user where `status`=0 and id=100001

郁闷的我出去上了个洗手间，然后回来，把sql改成：

select nick_name, user_name, head_img from dev.user where `status`=0 and id=100001

再也没出现bug。。。 为什么呢...不就是user_name和nick_name的顺序换了一下而已嘛？？？ orz..

[fatpo]

0.2.9升级到0.3.6后，解决了这个问题。。 但是如果再从0.3.6降级到0.2.9后，报错：

    with (yield chanzai_pool.Connection()) as conn:
  File "/Users/ouyang/PycharmProjects/python_venv/myapp_venv/lib/python2.7/site-packages/tornado/gen.py", line 1055, in run
    value = future.result()
  File "/Users/ouyang/PycharmProjects/python_venv/myapp_venv/lib/python2.7/site-packages/tornado/concurrent.py", line 238, in result
    raise_exc_info(self._exc_info)
  File "/Users/ouyang/PycharmProjects/python_venv/myapp_venv/lib/python2.7/site-packages/tormysql/util.py", line 14, in finish
    result = fun(*args, **kwargs)
  File "/Users/ouyang/PycharmProjects/python_venv/myapp_venv/lib/python2.7/site-packages/tormysql/connections.py", line 268, in connect
    raise exc
OperationalError: (2003, 'Can\'t connect to MySQL server on 127.0.0.1:3306 (OperationalError(2006, "MySQL server has gone away (StreamClosedError(\'Stream is closed\',))"))')
2018-02-01 19:26:54,121 140735576007488-MainThread-web.py-2063 [ERROR] 500 POST /api?type=json (127.0.0.1) 1260.81ms

发现是因为tornado被升级到4.5.2，降级之...发现bug竟然修复了... orz... 也就是说，我进行了一次升级

pip install tornado==4.5.2
pip install tormysql==0.3.6

再降级

pip install tornado==4.2
pip install tormysql==0.2.9

就修复了bug...

[snower]

之所以会出现CursorNotReadAllDataError这个错误，是因为在python2中，with不支持异步语法，退出with的时候并不能等待连接完成所有数据读取，如果sql查询了超过1条数据，但你只读取第一条，此时退出cursor并继续进行下一次查询，那么就极有可能这些未读取的数据会出现在下一次的结果当中，导致数据混乱，所以with语法退出的时候会检查是否已经完全读取了返回数据，否则就会raise出CursorNotReadAllDataError这个异常，cursor使用fetchall读取所有数据之后就不会再出现这个问题了

Python3支持异步with语法，所以可以安全的使用异步语法保证连接完成数据读取过程

[fatpo]

使用fetchone()只拿第一条数据，可能返回n条，此时with生命周期结束，就有可能造成结果集混乱？？ 岂不是太过于可怕。。
难道每次使用fetchone()我都要在sql语句中加一个limit 1？

[snower]

fetchone通过连接返回数据是异步的，yield就是标记等待数据返回啊，但是with退出exit并不支持yield等待语法，不能完成等待数据读取完成的过程，所以只能外部完成数据读取，然后再退出with作用域 如果你使用Python2的话，那么要么限制limit1，要么使用fetchall，然后再取第一条数据 如果python3的话，with有异步语法支持，你直接只用异步语法就行