tidb替换mysql遇到的问题记录

1. SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql.user.Update_priv' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
   sql_mode中去掉only_full_groupby
   set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

2. function get_lock has only noop implementation in tidb now, use tidb_enable_noop_functions to enable these functions
   有些mysql函数还未在tidb中实现,如果可以忽略错误的话,可以执行
   set global tidb_enable_noop_functions=1;

3. ((select 1 from dual)) 多个括号出现语法错误.
   这个在4.0.10遇到,后面升级后没有这个错误了,应该是已经修复了.

4. show full processlist,根据pid作kill时,不成功.
   可执行kill tidb pid 或在topo.yaml中配置

   server_configs:
     tidb:
       compatible-kill-query: true
   

5. 写入数据时报entry too large,原因是单行数据太大,一般很大blob数据会出现此问题,可按下列配置指定单行数据最大大小

   server_configs:
     tidb:
       performance: 
         txn-entry-size-limit:  100000000
   

6. Transaction too large 一个事务写的数据过大,提交失败

   server_configs:
     tidb:
       performance: 
         txn-total-size-limit:  1000000000
     tikv:  
         raftstore: 
           raft-entry-max-size: 64MB
   

7. 子查询索引在某些情况下不工作,建议减少子查询,或将更具体的条件放到子查询中. 问题详情
   如将条件task_id=1放到子查询中

   select name as taskName, (select group_concat(name) from task_participant where task_id = a.id) as participants from task a where a.id=1; 
   
   select name as taskName, (select group_concat(name) from task_participant where task_id = 1) as participants from task a where a.id=1;
   

8. 默认执行计划有误,执行计划见,使用Qury Hint或指定索引或analyze table(场景,索引不同值对应的数据量差别比较大;做了较多更新,表的stats_healthy比较低)
   查看表的stats_healthy where table_name='t1',如果healthy的值较低的话,可执行anlyze table t1
   若analyze table 还是没选择期望的执行计划,可以手动指定:
   query hint: select * from /*+ INL_JOIN(t2, t3) */ from t1 ,t2,t3 where t1.id1=t2.id1 and t2.id2=t3.id2*/
   use index: select * from /*+USE_INDEX(t1,logic),USE_INDEX(t2,t_exam_know_stu)*/ from t1 ,t2,t3 where t1.id1=t2.id1 and t2.id2=t3.id2
   参考链接

9. insert into ... on duplicate update,不会返回 last id. 问题详情
   临时处理方法

   1. mybatis使用useGeneratedKeys
   2. 视具体业务看能否替换为replace into

10. join时显示查询条件尽量作用在小表上,面对复杂sql时可能mysql优化得很好,tidb优化不够,需要手动调整sql

11. 事务传播机制尽量只用PROPAGATION_REQUIRED, 其余可能不支持,只测试了PROPAGATION_MANDATORY,PROPAGATION_NESTED