tidb替换mysql遇到的问题记录

最新推荐文章于 2024-07-29 00:00:00 发布

舟行于无涯之海

最新推荐文章于 2024-07-29 00:00:00 发布

阅读量2.5k

点赞数 1

本文链接：https://blog.csdn.net/liusf1993/article/details/120116053

版权

tidb 专栏收录该内容

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';
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;
((select 1 from dual)) 多个括号出现语法错误.
这个在4.0.10遇到,后面升级后没有这个错误了,应该是已经修复了.
show full processlist,根据pid作kill时,不成功.
可执行kill tidb pid 或在topo.yaml中配置
```
server_configs:
  tidb:
    compatible-kill-query: true
```
写入数据时报entry too large,原因是单行数据太大,一般很大blob数据会出现此问题,可按下列配置指定单行数据最大大小
```
server_configs:
  tidb:
    performance: 
      txn-entry-size-limit:  100000000
```

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

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

子查询索引在某些情况下不工作,建议减少子查询,或将更具体的条件放到子查询中. 问题详情
如将条件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;

默认执行计划有误,执行计划见,使用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
参考链接
insert into ... on duplicate update,不会返回 last id. 问题详情
临时处理方法
1. mybatis使用useGeneratedKeys
2. 视具体业务看能否替换为replace into
join时显示查询条件尽量作用在小表上,面对复杂sql时可能mysql优化得很好,tidb优化不够,需要手动调整sql
事务传播机制尽量只用PROPAGATION_REQUIRED, 其余可能不支持,只测试了PROPAGATION_MANDATORY,PROPAGATION_NESTED