Tidb (5.7.10-TiDB-v3.0.0-beta-27-g6398788)sql执行直接 > 2013 - Lost connection to MySQL server during query

最近我执行一个SQL的时候,获取information_schema.columns 中对应的表字段,发现tidb直接断开链接,但同样的SQL在mysql数据库中执行就没问题。


后来我逐级验证,发现在order by 的时候,去掉其中一个字段,就没有任何问题,这个字段是自定义的字段给的值是null, 请问一下,tidb 对空值的排序不支持吗?

附上对应的SQL语句:

SELECT
TABLE_SCHEMA TABLE_CAT,
null TABLE_SCHEM,
TABLE_NAME,
COLUMN_NAME,
CASE data_type WHEN ‘bit’ THEN - 7 WHEN ‘tinyblob’ THEN -3
WHEN ‘mediumblob’ THEN -4
WHEN ‘longblob’ THEN -4
WHEN ‘blob’ THEN -4
WHEN ‘tinytext’ THEN 12
WHEN ‘mediumtext’ THEN -1
WHEN ‘longtext’ THEN -1
WHEN ‘text’ THEN -1
WHEN ‘date’ THEN 91
WHEN ‘datetime’ THEN 93
WHEN ‘decimal’ THEN 3
WHEN ‘double’ THEN 8
WHEN ‘enum’ THEN 12
WHEN ‘float’ THEN 7
WHEN ‘int’ THEN IF ( COLUMN_TYPE LIKE ‘%unsigned%’, 4, 4 )
WHEN ‘bigint’ THEN -5
WHEN ‘mediumint’ THEN 4
WHEN ‘null’ THEN 0
WHEN ‘set’ THEN 12
WHEN ‘smallint’ THEN IF( COLUMN_TYPE LIKE ‘%unsigned%’, 5, 5 )
WHEN ‘varchar’ THEN 12
WHEN ‘varbinary’ THEN -3
WHEN ‘char’ THEN 1
WHEN ‘binary’ THEN - 2
WHEN ‘time’ THEN 92
WHEN ‘timestamp’ THEN 93
WHEN ‘tinyint’ THEN IF ( COLUMN_TYPE LIKE ‘tinyint(1)%’,- 7,- 6 )
WHEN ‘year’ THEN 91 ELSE 1111
END DATA_TYPE,
IF (COLUMN_TYPE LIKE ‘tinyint(1)%’,‘BIT’,UCASE(IF(COLUMN_TYPE LIKE ‘%(%)%’,CONCAT(SUBSTRING( COLUMN_TYPE, 1, LOCATE( ‘(’, COLUMN_TYPE ) - 1 ),SUBSTRING( COLUMN_TYPE, 1+ locate( ‘)’, COLUMN_TYPE ) ) ),COLUMN_TYPE ) ) ) TYPE_NAME,
CASE DATA_TYPE WHEN ‘time’ THEN IF( DATETIME_PRECISION = 0, 10, CAST( 11 + DATETIME_PRECISION AS signed INTeger ) )
WHEN ‘date’ THEN 10
WHEN ‘datetime’ THEN IF( DATETIME_PRECISION = 0, 19, CAST( 20 + DATETIME_PRECISION AS signed INTEGER ) )
WHEN ‘timestamp’ THEN IF( DATETIME_PRECISION = 0, 19, CAST( 20 + DATETIME_PRECISION AS signed INTEGER ) ) ELSE IF( NUMERIC_PRECISION IS NULL, LEAST( CHARACTER_MAXIMUM_LENGTH, 2147483647 ), NUMERIC_PRECISION ) END COLUMN_SIZE,
65535 BUFFER_LENGTH,
CONVERT ( CASE DATA_TYPE WHEN ‘year’ THEN NUMERIC_SCALE WHEN ‘tinyint’ THEN 0 ELSE NUMERIC_SCALE END, UNSIGNED INTEGER ) DECIMAL_DIGITS,
10 NUM_PREC_RADIX,
IF( IS_NULLABLE = ‘yes’, 1, 0 ) NULLABLE,
COLUMN_COMMENT REMARKS,
COLUMN_DEFAULT COLUMN_DEF,
0 SQL_DATA_TYPE,
0 SQL_DATETIME_SUB,
LEAST( CHARACTER_OCTET_LENGTH, 2147483647 ) CHAR_OCTET_LENGTH,
ORDINAL_POSITION,
IS_NULLABLE,
NULL SCOPE_CATALOG,
NULL SCOPE_SCHEMA,
NULL SCOPE_TABLE,
NULL SOURCE_DATA_TYPE,
IF( EXTRA = ‘auto_increment’, ‘YES’, ‘NO’ ) IS_AUTOINCREMENT,
IF( EXTRA IN ( ‘VIRTUAL’, ‘PERSISTENT’, ‘VIRTUAL GENERATED’, ‘STORED GENERATED’ ), ‘YES’, ‘NO’ ) IS_GENERATEDCOLUMN
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
( ISNULL( DATABASE ( ) ) OR ( TABLE_SCHEMA = DATABASE ( ) ) )
and ( TABLE_NAME LIKE ‘m_dim_day’ )
and (1=1)
ORDER BY
TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
ORDINAL_POSITION

@xqwbx163 能提供一下 select tidb_version() 的结果吗,我在相应的版本上复现一下问题

Release Version: v3.0.0-beta-27-g6398788

Git Commit Hash: 6398788f6bb722d29d06b754e3d1d61d02a3090e

Git Branch: master

UTC Build Time: 2019-01-31 08:42:28

GoVersion: go version go1.11.2 linux/amd64

Race Enabled: false

TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e

Check Table Before Drop: false

切到相应版本后没有复现。。能提供一下 tidb log 中的 panic 日志吗,看看是什么原因

的确在 6398788f6bb722d29d06b754e3d1d61d02a3090e 这个版本中复现了该问题。

在 3.0.0 版本中这个问题已经修复了,升级集群到 3.0.2 版本看看问题是否还存在呢?

我这边复现所采用的 SQL 为:
untitled.sql (3.2 KB) 。启动 tidb server 后执行该文件中的 SQL:

source untitled.sql

tidb 中 panic 的堆栈为:

github.com/pingcap/tidb/server.(*clientConn).Run.func1(0xc000728340)
        /Users/zhangjian/Code/tidb/server/conn.go:534 +0x104
panic(0x5046a80, 0x635df50)
        /opt/goroot/src/runtime/panic.go:522 +0x1b5
github.com/pingcap/tidb/server.(*clientConn).writeResultset.func1(0x0, 0x54ad1c0, 0xc00a0f94a0, 0xc00a1a5b30, 0xc000728340)
        /Users/zhangjian/Code/tidb/server/conn.go:1084 +0x340
panic(0x5046a80, 0x635df50)
        /opt/goroot/src/runtime/panic.go:522 +0x1b5
github.com/pingcap/tidb/executor.(*SortExec).lessRow(0xc000770280, 0xc00a0557a0, 0x0, 0xc0007570b0, 0x3, 0x9009758)
        /Users/zhangjian/Code/tidb/executor/sort.go:203 +0xda
github.com/pingcap/tidb/executor.(*SortExec).keyColumnsLess(0xc000770280, 0x0, 0x63, 0xc00074a0c0)
        /Users/zhangjian/Code/tidb/executor/sort.go:220 +0x9a
sort.medianOfThree_func(0xc00a1a58b8, 0xc00074a0c0, 0x0, 0x63, 0xc6)
        /opt/goroot/src/sort/zfuncversion.go:53 +0x3e
sort.doPivot_func(0xc00a1a58b8, 0xc00074a0c0, 0x0, 0x31d, 0x4fc37c0, 0x8)
        /opt/goroot/src/sort/zfuncversion.go:76 +0x4f9
sort.quickSort_func(0xc00a1a58b8, 0xc00074a0c0, 0x0, 0x31d, 0x14)
        /opt/goroot/src/sort/zfuncversion.go:143 +0x9a
sort.Slice(0x4faa980, 0xc00074a0a0, 0xc00a1a58b8)
        /opt/goroot/src/sort/slice.go:21 +0x129
github.com/pingcap/tidb/executor.(*SortExec).Next(0xc000770280, 0x549cf00, 0xc00a10ed40, 0xc0001a1e60, 0x0, 0x0)
        /Users/zhangjian/Code/tidb/executor/sort.go:96 +0x2a2
github.com/pingcap/tidb/executor.(*recordSet).Next(0xc00a0f9450, 0x549cf00, 0xc00a10ed40, 0xc0001a1e60, 0x0, 0x0)
        /Users/zhangjian/Code/tidb/executor/adapter.go:104 +0xc0
github.com/pingcap/tidb/server.(*tidbResultSet).Next(0xc00a0f94a0, 0x549cf00, 0xc00a10ed40, 0xc0001a1e60, 0xc000075800, 0x90006d0)
        /Users/zhangjian/Code/tidb/server/driver_tidb.go:365 +0x51
github.com/pingcap/tidb/server.(*clientConn).writeChunks(0xc000728340, 0x549cf00, 0xc00a10ed40, 0x54ad1c0, 0xc00a0f94a0, 0xc000005b00, 0xc000728340, 0xc000031d00)
        /Users/zhangjian/Code/tidb/server/conn.go:1133 +0x33f
github.com/pingcap/tidb/server.(*clientConn).writeResultset(0xc000728340, 0x549cf00, 0xc00a10ed40, 0x54ad1c0, 0xc00a0f94a0, 0xc000001d00, 0x0, 0x0, 0x0)
        /Users/zhangjian/Code/tidb/server/conn.go:1097 +0x1c8
github.com/pingcap/tidb/server.(*clientConn).handleQuery(0xc000728340, 0x549cf00, 0xc00a10ed40, 0xc0005c8c81, 0xc42, 0x0, 0x0)
        /Users/zhangjian/Code/tidb/server/conn.go:1014 +0x124
github.com/pingcap/tidb/server.(*clientConn).dispatch(0xc000728340, 0xc0005c8c81, 0xc43, 0xc43, 0x0, 0x0)
        /Users/zhangjian/Code/tidb/server/conn.go:743 +0x674
github.com/pingcap/tidb/server.(*clientConn).Run(0xc000728340)
        /Users/zhangjian/Code/tidb/server/conn.go:582 +0x222
github.com/pingcap/tidb/server.(*Server).onConn(0xc00a016150, 0x54b3040, 0xc0001a0000)
        /Users/zhangjian/Code/tidb/server/server.go:374 +0x240
created by github.com/pingcap/tidb/server.(*Server).Run
        /Users/zhangjian/Code/tidb/server/server.go:309 +0x465

2019/08/21 20:30:17.063 server.go:366: [info] con:1 close connection

和上面报错的 SQL 的 panic 堆栈一致。切换到 tidb 3.0 的版本后,该 panic 没有再复现了。

好的,下个星期我升级一下,看一下到底会是什么情况

@zz-jason-PingCAP 今天我部署了新得集群,集群版本是: Release Version: v3.0.2 Git Commit Hash: 94498e7d06a244196bb41c3a05dd4c1f6903099a Git Branch: HEAD UTC Build Time: 2019-08-07 02:35:52 GoVersion: go version go1.12 linux/amd64 Race Enabled: false TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e Check Table Before Drop: false

目前测试,我们使用得软件可以直接获取tidb得库表结构,完美

如果觉得别人的回答有帮助,可以标记为解决方案✅,这样可以帮助其他有同样问题的人快速找到答案~

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。