SQL Statement Triggers Panic When Creating Account in v6.1.4

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

Original topic: v6.1.4创建账号时SQL语句触发panic

| username: Jellybean

【TiDB Usage Environment】
Production Environment

【TiDB Version】
5.7.25-TiDB-v6.1.4

【Reproduction Path】Operations performed that led to the issue
Created user using create user
Assigned privileges using grant privileges

【Encountered Issue: Problem Phenomenon and Impact】
mysql> create user x@10.1.11.111 identified by password ‘xxxx@88888#!’;
ERROR 1827 (HY000): The password hash doesn’t have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
mysql>
mysql> grant select on ABC.* to x@10.1.11.111;
ERROR 1105 (HY000): runtime error: invalid memory address or nil pointer dereference
mysql> flush privileges;
ERROR 2013 (HY000): Lost connection to MySQL server during query

【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】
tidb logs:

[2023/04/10 10:32:11.037 +08:00] [WARN] [session.go:1966] [“run statement failed”] [conn=7866024932374141093] [schemaVersion=38520] [error=“[executor:1827]The password hash doesn’t have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.”]
[2023/04/10 10:32:11.046 +08:00] [ERROR] [conn.go:1056] [“connection running loop panic”] [conn=7866024932374141093] [lastSQL=“grant select on ABC.* to x@10.1.11.111”] [err=“runtime error: invalid memory address or nil pointer dereference”] [stack=“github.com/pingcap/tidb/server.(*clientConn).Run.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1059\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:884\ngithub.com/pingcap/tidb/executor.(*ExecStmt).Exec.func1\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:371\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:884\nruntime.panicmem\n\t/usr/local/go/src/runtime/panic.go:260\nruntime.sigpanic\n\t/usr/local/go/src/runtime/signal_unix.go:835\ngithub.com/pingcap/tidb/executor.(*GrantExec).Next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/grant.go:168\ngithub.com/pingcap/tidb/executor.Next\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:319\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:666\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:517\ngithub.com/pingcap/tidb/executor.(*ExecStmt).Exec\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:465\ngithub.com/pingcap/tidb/session.runStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:2094\ngithub.com/pingcap/tidb/session.(*session).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1963\ngithub.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:230\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmt\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:2022\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1876\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1371\ngithub.com/pingcap/tidb/server.(*clientConn).Run\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1121\ngithub.com/pingcap/tidb/server.(*Server).onConn\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:559”]

【Solution】
Reason: The cause of the TiDB panic, as indicated, is due to an error in the SQL statement. In the statement 【create user x@10.1.11.111 identified by password ‘xxxx@88888#!’;】, there is an extra word “password”. Removing it and re-executing the statement resolves the issue.

Optimization Suggestion: The error returned when executing an incorrect SQL statement needs to be optimized. It should not cause a TiDB panic or lead to a TiDB thread crash.

| username: Jellybean | Original post link

This issue might not have been discovered if the SQL wasn’t written incorrectly.

It should be something that can be optimized to improve user experience.

| username: Billmay表妹 | Original post link

According to the log information, the incorrect password format might be due to using the wrong password algorithm. You can check if the password algorithm being used is correct. The runtime errors and connection loss encountered during authorization might be caused by a null pointer reference or other errors in the program.

| username: Jellybean | Original post link

Wow, your explanation is so professional, definitely a seasoned technical expert :smiley:

The phrase “technically clueless newbie” can be promptly replaced.

| username: Billmay表妹 | Original post link

Recently, I’ve been trying out a moderator assistant tool, and it feels quite useful~

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.