Here's the syntax I've implemented in the parser:

\W

use test

drop table if exists t1;

create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create index idx_col1 on t1 (col1) visible;

show create table t1;

insert into t1 (id, col1) values (1,'abc'), (2, 'def');

alter table t1 change index idx_col1 invisible;

alter table t1 change index idx_col1 visible;

alter table t1 change index idx_id invisible, change index idx_col1 invisible;

select id, col1 from t1 order by id;

drop table if exists t1;

show warnings;

mysql> \W

Show warnings enabled.

mysql> drop table if exists t1;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

Note (Code 1051): Unknown table 'test.t1'

mysql> create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.20 sec)

mysql> create index idx_col1 on t1 (col1) visible;

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;

| Table | Create Table                                                                                                                                                            |

| t1    | CREATE TABLE `t1` (

  `id` int(11) DEFAULT NULL,

  `col1` char(3) DEFAULT NULL,

  KEY `idx_id` (`id`),

  KEY `idx_col1` (`col1`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 

1 row in set (0.00 sec)

mysql> insert into t1 (id, col1) values (1,'abc'), (2, 'def');

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table t1 change index idx_col1 invisible;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 change index idx_col1 visible;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 change index idx_id invisible, change index idx_col1 invisible;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select id, col1 from t1 order by id;

+------+------+

| id   | col1 |

+------+------+

|    1 | abc  | 

|    2 | def  | 

+------+------+

2 rows in set (0.00 sec)

mysql> drop table if exists t1;

Query OK, 0 rows affected, 1 warning (0.03 sec)

The Oracle 11g documentation is fuzzy on whether invisible indexes are session or global or both. If anybody has any thoughts on that, please add a comment.

In MySQL, various index properties are scoped like this:

set foreign_key_checks (session or global)
alter table disable keys (MyISAM-only, global)
create temporary table (session)

mysql has visible keyword now, so even not still broken mariadb 10.1 is incompatible with mysql

Upvoting this issue as it blocks compatibility with recent versions of MySQL Workbench, and as it is a straightforward feature that has already been prototyped.

colm_smyth, you can configure a target mysql version in workbench preferences. Set it to 5.7 to get a compatible dump.

varun, we cannot really call them INVISIBLE, because we already use the concept of invisibility for something completely different. Perhaps, "IGNORED"? That exactly explains what they are, and also matches the IGNORE INDEX syntax in the FROM clause.

While I understand that IGNORE is a better keyword than INVISIBLE, I worry that MariaDB's incompatibility with MySQL will be made worse. It means that transferring some datasets between MariaDB and MySQL, in either direction, will lead to errors fixable only by hand-editing of the dump.

Perhaps a partial solution is for loading the file can accept either keyword. Or a commandline switch can allow for ignoring the conflicting keyword.

Note that Workbench raised the problem by adding [unnecessarily] "VISIBLE" when making dumps. "mysqldump" should not include VISIBLE.

There is no logical conflict between the concept of an invisible column (to SELECT) and an invisible (to the optimizer) index. If there is no syntactic issue, it would be better to use the same INVISIBLE keyword to maximise compatibility with MySQL as it is important for MariaDB to be able to simplify migration and adoption for former MySQL users.

rjasdfiii, VISIBLE is already silently parsed and ignored after MDEV-22199. Unless one actually tries to load a dump that has "invisible" indexes, there is no compatibility issue here. Perhaps we can do this "partial solution" or may them work only in some kind of "mysql" mode.

colm_smyth, unfortunately, there is. Currently invisible indexes are indexes that only index invisible columns. There can be columns so invisible to the user that even SHOW CREATE TABLE doesn't show then, and indexes that index them are not shown either. Although they're perfectly "visible" to the optimizer and it is allowed to use them to optimize the query.

Thanks, Colm and Sergei. In addition to the invisible index on an invisible column, I can think of two others: The hidden 6-byte PK that InnoDB uses as a last resort. And the extra column added for InnoDB FULLTEXT.

That's 3 cases. Is there a list somewhere of all such odd indexes? Perhaps such a list would be made as a checklist on whether all cases are being handled for this feature request and other situations.

The patch is in the branch 10.5-mdev7317

Review input provided:
https://lists.launchpad.net/maria-developers/msg12543.html

Note:

From the Server Team Lead Call: The MySQL syntax to use "INVISIBLE" should be supported as an alias

This hasn't been implemented.

Hi ralf.gebhardt@mariadb.com, right. We did not add an optimizer switch flag for this.