SurrealDB多参数自定义函数调用失败问题排查

问题背景 #

定义了fn::insert_user函数用于插入用户数据，调用时失败。尽管确认user表为空，仍触发唯一索引冲突报错，同时伴随二进制响应反序列化失败的提示。

表结构定义 #

DEFINE TABLE user SCHEMAFULL;

DEFINE FIELD email ON TABLE user TYPE string
    ASSERT $value != NONE AND is::email($value);

DEFINE INDEX email_unique_index ON TABLE user COLUMNS email UNIQUE;

DEFINE FIELD username ON TABLE user TYPE string;

DEFINE INDEX username_unique_index ON TABLE user COLUMNS username UNIQUE;

DEFINE FIELD name ON TABLE user TYPE string;

DEFINE FIELD hash ON TABLE user TYPE string
    ASSERT $value != NONE;

DEFINE FIELD created_at ON TABLE user TYPE datetime
    ASSERT $value != NONE;

自定义函数定义 #

DEFINE FUNCTION fn::insert_user(
    $email: string,
    $username: string,
    $hash: string,
    $name: string
) {
    RETURN INSERT INTO user {
        email: $email,
        username: $username,
        hash: $hash,
        name: $name,
        created_at: time::now()
    };
};

调用语句 #

RETURN fn::insert_user('user@surrealdb.com', 'user0', 'hash', 'name');

报错信息 #

There was a problem with the database: Failed to deserialize a binary response: unknown variant Database index email_unique_indexalready contains 'user@surrealdb.com', with recorduser:hz34bz2kroymoryx1sld``, expected one of None, Null, False, True, Number, Strand, Duration, Datetime, Uuid, Array, Object, Geometry, Bytes, Param, Idiom, Table, Thing, Model, Regex, Block, Range, Edges, Future, Constant, Function, Subquery, Expression

解决方案 #

1. 确认并清理冲突记录 #

执行查询语句验证表中是否存在报错提到的记录：

SELECT * FROM user WHERE email = 'user@surrealdb.com';

如果存在该记录，执行删除操作：

DELETE FROM user WHERE email = 'user@surrealdb.com';

2. 调整函数返回值格式 #

原函数直接返回INSERT的完整结果，可能导致客户端解析异常。修改函数返回记录ID，简化响应格式：

DEFINE FUNCTION fn::insert_user(
    $email: string,
    $username: string,
    $hash: string,
    $name: string
) {
    LET inserted = INSERT INTO user {
        email: $email,
        username: $username,
        hash: $hash,
        name: $name,
        created_at: time::now()
    };
    RETURN inserted.id;
};

3. 重建索引消除缓存异常 #

删除并重新创建唯一索引，解决可能的索引缓存不一致问题：

DROP INDEX email_unique_index ON TABLE user;
DROP INDEX username_unique_index ON TABLE user;

DEFINE INDEX email_unique_index ON TABLE user COLUMNS email UNIQUE;
DEFINE INDEX username_unique_index ON TABLE user COLUMNS username UNIQUE;

4. 重启SurrealDB服务 #

若上述操作无效，重启服务清除内部缓存，确保索引和表状态一致。

内容的提问来源于stack exchange，提问作者obei