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得库表结构,完美

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