RAGFlow报错：ESConnection.sql got exception

环境：

Ragflowv0.17.2

问题描述：

RAGFlow报错：ESConnection.sql got exception

_ming_cheng_tks, '浙江', 'operator=OR;minimum_should_match=30%') 
2025-04-25 15:55:06,862 INFO     244867 POST http://localhost:1200/_sql?format=json [status:400 duration:0.002s]
2025-04-25 15:55:06,862 ERROR    244867 ESConnection.sql got exception
Traceback (most recent call last):
  File "/home/www/ragflow/ragflow/rag/utils/es_conn.py", line 553, in sql
    res = self.es.sql.query(body={"query": sql, "fetch_size": fetch_size}, format=format,
  File "/home/www/ragflow/ragflow/.venv/lib/python3.10/site-packages/elasticsearch/_sync/client/utils.py", line 446, in wrapped
    return api(*args, **kwargs)
  File "/home/www/ragflow/ragflow/.venv/lib/python3.10/site-packages/elasticsearch/_sync/client/sql.py", line 330, in query
    return self.perform_request(  # type: ignore[return-value]
  File "/home/www/ragflow/ragflow/.venv/lib/python3.10/site-packages/elasticsearch/_sync/client/_base.py", line 389, in perform_request
    return self._client.perform_request(
  File "/home/www/ragflow/ragflow/.venv/lib/python3.10/site-packages/elasticsearch/_sync/client/_base.py", line 320, in perform_request
    raise HTTP_EXCEPTIONS.get(meta.status, ApiError)(

解决方案：

1.主要修改点说明：

1. 正则表达式增强

pattern = r"([a-z_]+_l?tks)\s+(like|=)\s*'([^']+)'"

• 支持大小写混合的字段名
• 允许等号(=)和LIKE操作符
• 更严格捕获值部分

2. 分词处理优化

tokenized = rag_tokenizer.fine_grained_tokenize(rag_tokenizer.tokenize(val))

• 确保中文值被正确分词
• 处理特殊字符转义

3. 参数标准化

'operator=OR, minimum_should_match=30%'

• 使用逗号替代分号作为参数分隔符
• 符合Elasticsearch SQL参数规范

4. 保留大小写

re.sub(re.escape(old), new, sql, flags=re.IGNORECASE)

• 保持原始SQL的大小写格式
• 避免意外修改其他部分

5. 超时时间优化

params={"request_timeout": 30}

• 从2秒延长到30秒
• 适应复杂查询场景

6. 错误日志增强

logger.error(f"ES SQL Error: {str(e)} \nQuery: {sql}")

• 记录完整错误信息
• 保留问题查询语句

典型转换示例：

原始查询：

SELECT * WHERE _ming_cheng_tks LIKE '浙江'

转换后：

SELECT * WHERE MATCH(_ming_cheng_tks, '浙 江', 'operator=OR, minimum_should_match=30%')

验证方法：

# 测试用例
test_sql = "SELECT _id FROM index WHERE name_tks = '杭州' OR addr_ltks LIKE '西湖区'"
expected = "SELECT _id FROM index WHERE MATCH(name_tks, '杭 州', 'operator=OR, minimum_should_match=30%') OR MATCH(addr_ltks, '西 湖 区', 'operator=OR, minimum_should_match=30%')"

2.修改源码es_conn.py文件路径和详情

大概531行

ragflow-main\rag\utils\es_conn.py

    def sql(self, sql: str, fetch_size: int, format: str):
        logger.debug(f"ESConnection.sql get sql: {sql}")
        sql = re.sub(r"[ `]+", " ", sql)
        sql = sql.replace("%", "")
        replaces = []
        # 修改点1：增强正则匹配模式
        pattern = r"([a-z_]+_l?tks)\s+(like|=)\s*'([^']+)'"
        for r in re.finditer(pattern, sql, re.IGNORECASE):
            fld, op, val = r.group(1), r.group(2), r.group(3)
            # 修改点2：正确处理分词和特殊字符
            tokenized = rag_tokenizer.fine_grained_tokenize(rag_tokenizer.tokenize(val))
            # 修改点3：参数格式标准化
            match_expr = f"MATCH({fld}, '{tokenized}', 'operator=OR, minimum_should_match=30%')" 
            replaces.append((f"{fld} {op} '{val}'", match_expr))
        
        # 修改点4：保留原始大小写格式
        for old, new in replaces:
            sql = re.sub(re.escape(old), new, sql, flags=re.IGNORECASE)
        
        logger.debug(f"ESConnection.sql transformed: {sql}")

        # 修改点5：增加超时时间
        for i in range(ATTEMPT_TIME):
            try:
                res = self.es.sql.query(
                    body={"query": sql, "fetch_size": fetch_size},
                    format=format,
                    params={"request_timeout": 30}  # 从2秒增加到30秒
                )
                return res
            except ConnectionTimeout:
                logger.exception(f"ESConnection.sql timeout on: {sql}")
                continue
            except Exception as e:
                # 修改点6：记录完整错误信息
                logger.error(f"ES SQL Error: {str(e)} \nQuery: {sql}")
                return None

3.重启服务后验证正常