So as a workaround, we are able to remove the primary key and create a unique key with the required columns. InnoDB seems to treat this the same as the primary key (needs more testing).
Therefore, this feature should be easy to implement?
MariaDB [test]> alter table bad_with_key add primary key (id,id_len); |
ERROR 1903 (HY000): Primary key cannot be defined upon a generated column |
MariaDB [test]> alter table bad_with_key add unique key (id,id_len); |
Query OK, 0 rows affected (0.035 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
The implementation should take care of :
https://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/
The idea was to create an UNSIGNED LONG HASH for primary or unique key but this is blocked by 10.3+ -> https://jira.mariadb.org/browse/MDEV-18319
I'm confused. https://mariadb.com/kb/en/generated-columns/ implies that "persistent" and "virtual" are mutually exclusive. So, is this bug report about a GENERATED column that is VIRTUAL? Or is PERSISTENT (aka STORED)?
(Also, Singh's example does not have enough info to reproduce it.)
Less of on issue as of MDEV-19506 
Hi rjasdfiii,
I'm confused. https://mariadb.com/kb/en/generated-columns/ implies that "persistent" and "virtual" are mutually exclusive. So, is this bug report about a GENERATED column that is VIRTUAL? Or is PERSISTENT (aka STORED)?
MariaDB implemented "virtual generated columns" before it implemented "persistent generated columns". At first, many folks just called the new feature "virtual columns". When MariaDB implemented "persistent generated columns", many folks were used to calling the old feature "virtual columns", so they started calling the new feature "persistent virtual columns", even though "virtual virtual columns" sounds kind of redundant and weird. Hopefully that confusing misnomer stops being used eventually.
When it comes to InnoDB, and presumably any other storage engine that uses index-organized tables, support for defining a primary key on a virtual column would have to be implemented at a very low level. It would also imply that any update of the base columns of that virtual column would be expensive, just like updating the primary key. In an index-organized table, updating the primary key is like a DELETE and INSERT of the entire row, in all indexes.
In a heap-organized table (which InnoDB does not support), every key can be updated independently of each other, so this would just be a DELETE and INSERT of the primary key index, not touching the data heap or other indexes.