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: | |
Category: | MySQL Workbench | Severity: | S3 (Non-critical) |
Version: | 6.3.10, 8.0.11 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[26 Jun 2018 9:53]
David Webb
[26 Jun 2018 9:54]
David Webb
Synopsis amended.
[26 Jun 2018 11:39]
MySQL Verification Team
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
[26 Jun 2018 12:54]
David Webb
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.
[27 Jun 2018 5:23]
MySQL Verification Team
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
[27 Jun 2018 5:27]
David Webb
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?
[27 Jun 2018 5:43]
MySQL Verification Team
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