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)
James Briggs
added a comment - - edited 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)
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
added a comment - 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.
Sergei Golubchik
added a comment - 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.
Sergei Golubchik
added a comment - - edited 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.
Rick James
added a comment - 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.
Colm Smyth
added a comment - 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.
Sergei Golubchik
added a comment - 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.
Rick James
added a comment - 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.
From the Server Team Lead Call: The MySQL syntax to use "INVISIBLE" should be supported as an alias
This hasn't been implemented.
Sergei Petrunia
added a comment - Note:
From the Server Team Lead Call: The MySQL syntax to use "INVISIBLE" should be supported as an alias
This hasn't been implemented.
Task
Critical
MDEV-25075 Ignorable index makes the resulting CREATE TABLE invalid
Here's the syntax I've implemented in the parser:
\Wshow warnings;mysql> \WShow 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: 0mysql> 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: 0mysql> alter table t1 change index idx_col1 invisible;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table t1 change index idx_col1 visible;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> 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)