Unable to create an index on Virtual column

webyog / sqlyog-community

Webyog provides monitoring and management tools for open source relational databases. We develop easy-to-use MySQL client tools for performance tuning and database management. Webyog's solutions include SQL Diagnostic Manager for MySQL performance optimization and SQLyog for MySQL administration. More than 35,000 companies (including Amazon, IBM, Salesforce, AT&T, eBay, and GE) and 2.5 million users rely on Webyog's solutions to provide valuable insights into their databases. Webyog is an Idera, Inc. company.

GNU General Public License v2.0

2.16k stars 318 forks source link

With the MariaDB server, it is unable to create an index on a Virtual column in the UI, as the virtual column does not show up as a selectable column in the 'Index' tab.

CREATE TABLE `json_user` (
  `id` int(11) NOT NULL,
  `name` longtext GENERATED ALWAYS AS (`id` + 1) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

On creating the index on the Virtual column by manually executing the SQL, the index is reflected in the UI.

CREATE INDEXname_idxONjson_user(name);

The index then is displayed even on altering it in the UI. (For E.g., modifying the index name).

Server used: MariaDB v10.8.3

The user report can be found in SF 00951974

webyog / sqlyog-community

Unable to create an index on Virtual column #2299