TiDB parses FULLTEXT KEY incorrectly

[flochman]

Please answer these questions before submitting your issue. Thanks!

1. What did you do? If possible, provide a recipe for reproducing the error.

I am moving MySQL database to TiDB via loader.

I checked TiDB compatibility of MySQL database via TiDB checker with success.

Than I exported MySQL database with mydumper with success.

Now I try to load exported database to TiDB with loader.

It gives an error on this schema:

/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

CREATE TABLE `ossn_users` (
  `guid` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` text NOT NULL,
  `username` text NOT NULL,
  `email` text NOT NULL,
  `password` text NOT NULL,
  `salt` varchar(8) NOT NULL,
  `first_name` text NOT NULL,
  `last_name` text NOT NULL,
  `last_login` int(11) NOT NULL,
  `last_activity` int(11) NOT NULL,
  `activation` text,
  `time_created` int(11) NOT NULL,
  PRIMARY KEY (`guid`),
  KEY `last_login` (`last_login`),
  KEY `last_activity` (`last_activity`),
  KEY `time_created` (`time_created`),
  FULLTEXT KEY `type` (`type`),
  FULLTEXT KEY `email` (`email`),
  FULLTEXT KEY `first_name` (`first_name`),
  FULLTEXT KEY `last_name` (`last_name`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

2. What did you expect to see?

success

3. What did you see instead?

CheckPointSchema:tidb_loader configFile:config.toml SkipConstraintCheck:0 printVersion:false AlternativeDB: SourceDB: RouteRules:[]}, git-hash: bea125f9af348ff049e1e412e92bf5019a73513a utc-build-time:[2017-07-05 09:06:12] go-version:go1.8) 2017/07/07 09:59:13 checkpoint.go:189: [info] calc checkpoint finished. finished tables (map[]) 2017/07/07 09:59:13 loader.go:602: [info] [loader][run db schema]/home/vric/test/ossn-schema-create.sql[start] 2017/07/07 09:59:13 loader.go:611: [info] [loader][run db schema]/home/vric/test/ossn-schema-create.sql[finished] 2017/07/07 09:59:13 loader.go:635: [info] [loader][run table schema]/home/vric/test/ossn.ossn_likes-schema.sql[finished] 2017/07/07 09:59:13 loader.go:135: [info] [loader][restore table data sql]/home/vric/test/ossn.ossn_likes.sql[start] 2017/07/07 09:59:13 db.go:303: [info] checkpoint id:47738f filename ossn.ossn_likes.sql already exists, skip it. 2017/07/07 09:59:13 loader.go:193: [info] data file /home/vric/test/ossn.ossn_likes.sql scanned finished. 2017/07/07 09:59:13 db.go:125: [warning] [exec][sql][use ossn; CREATE TABLE ossn_users (guid bigint(20) NOT NULL AUTO_INCREMENT,type text NOT NULL,username][error]Error 1170: index for BLOB/TEXT column must specificate a key length 2017/07/07 09:59:13 loader.go:632: [fatal] run table schema failed - Error 1170: index for BLOB/TEXT column must specificate a key length /home/jenkins/workspace/build_tidb_enterprise_tools_master/go/src/github.com/pingcap/tidb-enterprise-tools/loader/db.go:84: /home/jenkins/workspace/build_tidb_enterprise_tools_master/go/src/github.com/pingcap/tidb-enterprise-tools/loader/loader.go:520:

4. What version of TiDB are you using (tidb-server -V)?

Git Commit Hash: e356b2663f433aa9538da9f6674330d27b8bea13 UTC Build Time: 2017-07-05 11:39:29

[zimulala]

PTAL @holys

[holys]

@zimulala Sorry for reply so late. As a matter of fact , it's a TiDB compatibility problem. You can verify it by creating table ossn_users in TiDB.

/cc @flochman

[kolbe]

The root of this issue seems to be that TiDB interprets FULLTEXT KEY on a TEXT column as a normal KEY (i.e. it basically just ignores the FULLTEXT keyword). This behavior causes a very confusing error message related to key prefixes on BLOB/TEXT columns.

A better behavior would be to ignore FULLTEXT KEYs entirely, and perhaps issue a warning that they are not supported by TiDB.