OceanBase 特殊的 INT 与时间类型隐式转换问题

最新推荐文章于 2025-06-20 09:39:29 发布

原创

最新推荐文章于 2025-06-20 09:39:29 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

本文介绍了在Oceanbase数据库中遇到的一个由于时间与数值类型隐式转换导致的查询异常问题。作者通过分析日志和执行SQL语句，发现UPDATE语句在执行时由于严格模式，当转换失败时会报错，而SELECT查询虽然也有同样的转换问题，但由于SQL_MODE设置，可能返回不正确的结果。解决方案是避免隐式转换，明确指定转换函数。

本文作者分享了Oceanbase时间与数值类型隐式转换导致查询结果不符合预期或“不正确”问题的排查思路。

作者：任仲禹

爱可生 DBA 团队成员，擅长故障分析和性能优化，文章相关技术问题，欢迎大家一起讨论。

本文来源：原创投稿

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

之前在 OceanBase 使用中碰到了一个“令人费解”的数据类型隐式转换问题。结论比较简单，特跟大家分享下排查思路。

问题描述

某客户项目组执行更新 SQL 语句时会偶发失败，报错如下：

脱敏处理后

ERROR bad SQL grammar [update renzy set at=current_timestamp,expire_at=(cast(unix_timestamp(current_timestamp(3) as unsigned) +?)), order_id= ? where id = ? and (expire_at < current_timestamp or order_id = ?)] java.sql.SQLSyntaxErrorException: (conn=1277168) Incorrect value.

查询 OceanBase 版本。

./observer -V

observer (OceanBase 3.2.3.2)
REVISION: 105000092022092216-445151f0edb502e00ae5839dfd92627816b2b822

查看表结构和数据。

MySQL [test]> show create table renzy\G
*************************** 1. row ***************************
       Table: renzy
Create Table: CREATE TABLE `renzy` (
  `id` varchar(64) NOT NULL,
  `at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `order_id` varchar(64) NOT NULL,
  `expire_at` bigint(20) NOT NULL,
  `vt` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

MySQL [test]> select * from renzy;
+----+---------------------------+---------------+------------+---------------------------------+
| id | at                  | order_id      | expire_at  | vt                  |
+----+---------------------------+---------------+------------+---------------------------------+
| 1  | 2023-07-07 14:57:13 | 0:16632@172.1 | 1716040750 | 2023-07-07 14:57:13 |
+----+---------------------------+---------------+------------+---------------------------------+
1 row in set (0.02 sec)

问题排查

问题 1：报错语句

直接执行报错的 SQL 语句。

update renzy  set at=CURRENT_TIMESTAMP, expire_at=(cast(unix_timestamp(current_timestamp(3)) as unsigned) + 30000000), order_id= '0:16632@172.24.64.1'  where id = '1'  and (expire_at < CURRENT_TIMESTAMP  or order_id = '0:16632@172.24.64.1')

ERROR 1292 (22007): Incorrect values.

问题 2

但是，当主键 ID 匹配不到任何数据，UPDATE 语句将不会报错。

为啥？先记录下问题接着看日志。

# 表不存在 id=2 的数据
update renzy  set acquired_at=CURRENT_TIMESTAMP, expire_at=(cast(unix_timestamp(current_timestamp(3)) as unsigned) + 30000000), order_id= '0:16632@172.24.64.1'  where id = '2'  and (expire_at < CURRENT_TIMESTAMP  or order_id = '0:16632@172.24.64.1')

Query OK.

报错 SQL 的 sql_auit 输出和日志

带着问题分析下 sql$gv_audit 输出，得出如下信息：

该语句计划是本地计划，分发到了 0.71 节点。
错误码是 4219 （无效的 datetime 值）。

MySQL [oceanbase]> select trace_id,svr_ip,ret_code,retry_cnt,usec_to_time(request_time),elapsed_time,execute_time,plan_type,query_sql from gv$sql_audit where query_sql like 'update id%' and ret_code != 0 order by request_time desc  limit 5;

+-----------------------------------+-------------+----------+-----------+----------------------------+--------------+--------------+-----------+--------------------------------