Can't find column Column#226 in schema Column:

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Can’t find column Column#226 in schema Column:

| username: keyway

【TiDB Usage Environment】Production environment and test environment
【TiDB Version】5.2.0
【Encountered Problem】
The same version of TiDB, executing the same query statement, the case-sensitive cluster reports an error: Can’t find column Column#226 in schema Column:.
The error SQL is
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’;

The error message is
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:

Temporary solution, cannot fundamentally solve, adding DATE_FORMAT can run normally, but data becomes slow
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’;

【Reproduction Path】

  1. Previously set up TiDB 5.2.0, found that the source data MySQL is case-insensitive, TiDB is case-sensitive, causing many business data comparisons to be inconsistent, and many businesses cannot directly use TiDB, such as DISTINCT a certain data, a, A TiDB shows 2 rows, MySQL shows 1 row.
  2. Set up the same version again, adding case-insensitive configuration
    server_configs:
    tidb:
    new_collations_enabled_on_first_bootstrap: true
  3. Business code running on the case-insensitive cluster reports a lot of errors, one of which is Can’t find column Column
    【Problem Phenomenon and Impact】
    If upgrading the version requires modifying the query statement, it will bring a lot of workload. How to avoid modifying business query statements in a case-sensitive situation.
    【Attachment】
    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: {}

2
| username: songxuecheng | Original post link
  1. TiDB should have lower_case_table_names=2
  2. new_collations_enabled_on_first_bootstrap controls the new collations
  3. Check if TiFlash is used and look at the execution plan of this SQL.
3
| username: keyway | Original post link
  1. lower_case_table_names only makes table names case-insensitive, not the query fields.
  2. The official documentation states that you need to use this field to create a new cluster to make it case-insensitive: TiDB 配置文件描述 | PingCAP 文档中心
  3. TiFlash is not installed.
4
| username: songxuecheng | Original post link

Is the version 5.2.0? Try upgrading to the latest 5.2.x.