【 TiDB 使用环境`】生产环境 and 测试环境
【 TiDB 版本】 5.2.0
【遇到的问题】
同样版本的tidb，执行同样的查询语句，区分大小的集群报错Can’t find column Column#226 in schema Column:。
报错的sql是
SELECT t.telemarketId,t.orderNo,t.phone, t.remarkPhone,t.backTime,t.taskId
FROM (
SELECT tb.orderNo, ci.phone, ci.remarkPhone,sctc.taskId,tb.telemarketId,
CASE WHEN rr.backTime is not null and rr.backTime != ‘0000-00-00 00:00:00’ THEN rr.backTime
WHEN alirr.backTime is not null and alirr.backTime != ‘0000-00-00 00:00:00’ THEN alirr.backTime
ELSE IF(jdrr.backTime=‘0000-00-00 00:00:00’,null,jdrr.backTime) END as backTime
FROM telemarket_fat2.telemarket_base tb
LEFT JOIN telemarket_fat2.smart_call_task_config sctc on sctc.baseType = tb.baseType
LEFT JOIN telemarket_fat2.customer_info ci on tb.idCard = ci.idCard
LEFT JOIN loverent_aliorder_fat2.rent_record alirr on tb.orderNo = alirr.rentRecordNo
LEFT JOIN loverent_order_fat2.jd_rent_record jdrr on tb.orderNo = jdrr.rentRecordNo
LEFT JOIN loverent_order_fat2.rent_record rr on rr.rentRecordNo = tb.orderNo
where tb.enableFlag=1 and tb.baseType = 5 and tb.changeState in (1,6)
) t where t.backTime >= ‘2020-12-30 23:59:59’ and t.backTime <= ‘2020-12-30 23:59:59’;
报错的信息是
SELECT t.telemarketId,t.orderNo,t.phone, t.remarkPhone,t.backTime,t.taskId
FROM (
SELECT tb.orderNo, ci.phone, ci.remarkPhone,sctc.taskId,tb.telemarketId,
CASE WHEN rr.backTime is not null and rr.backTime != ‘0000-00-00 00:00:00’ THEN rr.backTime
WHEN alirr.backTime is not null and alirr.backTime != ‘0000-00-00 00:00:00’ THEN alirr.backTime
ELSE IF(jdrr.backTime=‘0000-00-00 00:00:00’,null,jdrr.backTime) END as backTime
FROM telemarket_fat2.telemarket_base tb
LEFT JOIN telemarket_fat2.smart_call_task_config sctc on sctc.baseType = tb.baseType
LEFT JOIN telemarket_fat2.customer_info ci on tb.idCard = ci.idCard
LEFT JOIN loverent_aliorder_fat2.rent_record alirr on tb.orderNo = alirr.rentRecordNo
LEFT JOIN loverent_order_fat2.jd_rent_record jdrr on tb.orderNo = jdrr.rentRecordNo
LEFT JOIN loverent_order_fat2.rent_record rr on rr.rentRecordNo = tb.orderNo
where tb.enableFlag=1 and tb.baseType = 5 and tb.changeState in (1,6)
) t where t.backTime >= ‘2020-12-30 23:59:59’ and t.backTime <= ‘2020-12-30 23:59:59’
1105 - Can’t find column Column#226 in schema Column: [telemarket_fat2.telemarket_base.telemarketid,telemarket_fat2.telemarket_base.orderno,telemarket_fat2.smart_call_task_config.taskid,telemarket_fat2.customer_info.phone,telemarket_fat2.customer_info.remarkphone,loverent_aliorder_fat2.rent_record.backtime,loverent_order_fat2.jd_rent_record.backtime,loverent_order_fat2.rent_record.backtime] Unique key:
过渡方案，不能根本解决，增加DATE_FORMAT能正常运行，数据变慢
SELECT t.telemarketId,t.orderNo,t.phone, t.remarkPhone,t.backTime,t.taskId
FROM (
SELECT tb.orderNo, ci.phone, ci.remarkPhone,sctc.taskId,tb.telemarketId,
CASE WHEN rr.backTime is not null and rr.backTime != ‘0000-00-00 00:00:00’ THEN rr.backTime
WHEN alirr.backTime is not null and alirr.backTime != ‘0000-00-00 00:00:00’ THEN alirr.backTime
ELSE IF(jdrr.backTime=‘0000-00-00 00:00:00’,null,jdrr.backTime) END as backTime
FROM telemarket_fat2.telemarket_base tb
LEFT JOIN telemarket_fat2.smart_call_task_config sctc on sctc.baseType = tb.baseType
LEFT JOIN telemarket_fat2.customer_info ci on tb.idCard = ci.idCard
LEFT JOIN loverent_aliorder_fat2.rent_record alirr on tb.orderNo = alirr.rentRecordNo
LEFT JOIN loverent_order_fat2.jd_rent_record jdrr on tb.orderNo = jdrr.rentRecordNo
LEFT JOIN loverent_order_fat2.rent_record rr on rr.rentRecordNo = tb.orderNo
where tb.enableFlag=1 and tb.baseType = 5 and tb.changeState in (1,6)
) t where DATE_FORMAT(t.backTime, ‘%Y-%m-%d %H:%i:%s’ ) >= ‘2020-12-30 23:59:59’ and DATE_FORMAT(t.backTime, ‘%Y-%m-%d %H:%i:%s’ ) <= ‘2020-12-30 23:59:59’;
【复现路径】
1.之前搭建了tidb 5.2.0,做数据对比发现源数据mysql不区分大小写，tidb区分了，造成很多业务数据对比不一致，很多业务不能直接使用tidb，例如 DISTINCT某条数据，a,A tidb 显示2条，mysql显示1条。
2.新搭建同样版本的，增加不区分大小的配置
server_configs:
tidb:
new_collations_enabled_on_first_bootstrap: true
3.业务代码运行在不区分大小写的集群大量报错，其中一个 Can’t find column Column
【问题现象及影响】
如果 升级版本要修改查询语句，会带来大量工作量。怎么在区分大小写的情况下，不修改业务查询语句。
【附件】
Cluster type: tidb
Cluster name: tidb-dev
Cluster version: v5.2.0
Deploy user: tidb
SSH type: system
Dashboard URL: http://192.168.2.162:2381/dashboard
Grafana URL: http://192.168.2.162:13000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir
192.168.2.162:19093 alertmanager 192.168.2.162 19093/19094 linux/x86_64 Up /data/tidb-data/alertmanager-19093 /data/tidb-deploy/alertmanager-19093
192.168.2.162:13000 grafana 192.168.2.162 13000 linux/x86_64 Up - /data/tidb-deploy/grafana-13000
192.168.2.160:2381 pd 192.168.2.160 2381/2382 linux/x86_64 Up /data/tidb-data/pd-2381 /data/tidb-deploy/pd-2381
192.168.2.162:2381 pd 192.168.2.162 2381/2382 linux/x86_64 Up|L|UI /data/tidb-data/pd-2381 /data/tidb-deploy/pd-2381
192.168.2.164:2381 pd 192.168.2.164 2381/2382 linux/x86_64 Up /data/tidb-data/pd-2381 /data/tidb-deploy/pd-2381
192.168.2.162:19090 prometheus 192.168.2.162 19090 linux/x86_64 Up /data/tidb-data/prometheus-19090 /data/tidb-deploy/prometheus-19090
192.168.2.160:4001 tidb 192.168.2.160 4001/10081 linux/x86_64 Up - /data/tidb-deploy/tidb-4001
192.168.2.162:4001 tidb 192.168.2.162 4001/10081 linux/x86_64 Up - /data/tidb-deploy/tidb-4001
192.168.2.164:4001 tidb 192.168.2.164 4001/10081 linux/x86_64 Up - /data/tidb-deploy/tidb-4001
192.168.2.159:20161 tikv 192.168.2.159 20161/20181 linux/x86_64 Up /data/tidb-data/tikv-20161 /data/tidb-deploy/tikv-20161
192.168.2.161:20161 tikv 192.168.2.161 20161/20181 linux/x86_64 Up /data/tidb-data/tikv-20161 /data/tidb-deploy/tikv-20161
192.168.2.163:20161 tikv 192.168.2.163 20161/20181 linux/x86_64 Up /data/tidb-data/tikv-20161 /data/tidb-deploy/tikv-20161
node_exporter_port: 9101
blackbox_exporter_port: 9116
deploy_dir: /data/tidb-deploy/monitor-9101
data_dir: /data/tidb-data/monitor-9101
log_dir: /data/tidb-deploy/monitor-9101/log
server_configs:
tidb:
new_collations_enabled_on_first_bootstrap: true
performance.txn-total-size-limit: 904857600
tikv: {}
pd: {}
tiflash: {}
tiflash-learner: {}
pump: {}
drainer: {}
cdc: {}