MySQL Bugs: #91419: Workbench 6.3.10 generates incorrect statement for adding fulltext index

Bug #91419	Workbench 6.3.10 generates incorrect statement for adding fulltext index
Submitted:	26 Jun 2018 9:53	Modified:	27 Jun 2018 5:43
Reporter:	David Webb	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.3.10, 8.0.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When adding a fulltext index to a table in Workbench, the generated SQL statement includes the ASC sort-order for the index, causing an error. The user has to manually remove the ASC from the statement before executing it. It is not possible to select a blank (no sort order) before clicking "Apply". Fulltext indices do not have sort orders, so I suggest that when the user selects "FULLTEXT" as the index type, then the sort order is automatically blanked in the index columns.

How to repeat:
Set up a table with a text or varchar column, then use Workbench to try to add a fulltext index.

Synopsis amended.

Hello David,

Thank you for the report and feedback.
Could you please share the screenshot of the error that you are seeing when ASC/DESC included in the ALTER/CREATE TABLE? I quickly checked with mysql CLI, on WB 6.3.10 and 8.0.11. My observation is that it is ignored in CLI, WB 6.3.10(generated SQL statement includes ASC|DESC) and WB 8.0.11 don't include ASC|DESC. 

### CLI

mysql> create table t1(id int not null primary key, story text);
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `test`.`t1`
    -> ADD FULLTEXT INDEX `index2` (`story` ASC);
Query OK, 0 rows affected, 1 warning (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(id int not null primary key, story text);
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `test`.`t1`
    -> ADD FULLTEXT INDEX `index2` (`story` DESC);
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

-- 6.3.10 - ASC/DESC 

ALTER TABLE `test`.`t1`  ADD FULLTEXT INDEX `index2` (`story` ASC|DESC);

-- 8.0.11 - ASC/DESC 

ALTER TABLE `test`.`t1` 
ADD FULLTEXT INDEX `index2` (`story`);
;

But I agree that when FULLTEXT/SPATIAL index selected, it should not allow to chose ASC/DESC option even in 8.0.11 as descending indexes are not supported for FULLTEXT or SPATIAL indexes..

Thanks,
Umesh

I'm using the latest GA Release of Workbench, 6.3.10. I am not aware of a Workbech 8.0.11. It's a 2-column index. The error code with ASC is:

ERROR 1221: Incorrect usage of spatial/fulltext/hash index and explicit index order
SQL Statement:
ALTER TABLE `enigma`.`alias` 
ADD FULLTEXT INDEX `ftname` (`n1` ASC, `n2` ASC)

I will upload a picture.

Thank you for the feedback and requested details.
Strange, still I'm not seeing the reported issue at my end.  May I request you to please provide unaltered WB log, OS/MySQL Server version details and result of show create table <table_name>\G? Thank you.

### On Win7
########## WB->preferences - with/without - default sql_mode set to  STRICT_ALL_TABLES

drop table if exists t1;
create table t1(id int not null auto_increment primary key, story text, story1 text, g GEOMETRY NOT NULL);

-- WB 3.6.10 connected to MySQL Server 5.6.40 - no errors (ASC|DSC ignored)
- From WB - Navigate to table 't1' -> Alter table -> select "Indexes" tab -> chose index type "FULLTEXT" -> select columns from the right side pane and click apply

Executing:
ALTER TABLE `test`.`t1` 
ADD FULLTEXT INDEX `index2` (`story` ASC, `story1` ASC);

SQL script was successfully applied to the database.

-- WB 8.0.11  connected to MySQL Server 5.6.40 - no errors
- From WB - Navigate to table 't1' -> Alter table -> select "Indexes" tab -> chose index type "FULLTEXT" -> select columns from the right side pane and click apply

Executing:
ALTER TABLE `test`.`t1` 
ADD FULLTEXT INDEX `index2` (`story`, `story1`);
;

SQL script was successfully applied to the database.

Also, I suggest you to check with MySQL Workbench 8.0.11 rc - https://dev.mysql.com/downloads/workbench/

Regards,
Umesh

Umesh, you appear to be using MySQL Server 5.6.40. I am using MySQL Server 8.0.11, the latest GA Release. Can you test with MySQL Server 8.0.11?

Thank you, WB 6.3.10 is affected but WB 8.0.11 has fix for this.
Only issue I found in 8.0.11 is that it allows to select ASC/DESC for FULLTEXT indexes which should have been disabled. I would request you to upgrade to WB 8.0.11 in which generated SQL statements are valid(no ASC/DESC ordering mentioned). I'm verifying this for the cosmetic change i.e disable selecting index order for FULLTEXT/SPATIAL columns.

########## WB 8.0.11 - generated SQL has no ASC/DESC ordering

Executing:
ALTER TABLE `test`.`t1` 
ADD FULLTEXT INDEX `index2` (`story`, `story1`) VISIBLE;
;

SQL script was successfully applied to the database.

########## WB 6.3.10 - generated SQL has ASC/DESC ordering, which is fails on MySQL Server 8.0.11

Executing:
ALTER TABLE `test`.`t1` 
ADD FULLTEXT INDEX `index2` (`story` ASC, `story1` ASC);

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1221: Incorrect usage of spatial/fulltext/hash index and explicit index order
SQL Statement:
ALTER TABLE `test`.`t1` 
ADD FULLTEXT INDEX `index2` (`story` ASC, `story1` ASC)

- even with CLI with MySQL Server 8.0.11 - it fails

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table t1(id int not null auto_increment primary key, story text, story1 text, g GEOMETRY NOT NULL);
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE `test`.`t1`
    -> ADD FULLTEXT INDEX `index2` (`story` ASC, `story1` ASC);
ERROR 1221 (HY000): Incorrect usage of spatial/fulltext/hash index and explicit index order