Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.8
-
None
-
10.3.8-MariaDB-1:10.3.8+maria~stretch-log - mariadb.org binary distribution
Description
There is no easy way to check the column information of the auto-generated row_start and row_end columns when a table is created WITH SYSTEM VERSIONING. Due to this, it is causing a bug with phpMyAdmin and other similar tools/libraries (including my own PHP Universal Database Library). These utilities usually do a SELECT * query on their (Browse) tab. In phpMyAdmin, most features, such as editing row values, is disabled because they assume they don't have the full PRIMARY KEY in the results set. We already know that row_end in the PRIMARY KEY is GENERATED, but there is no way to query this so the application software can be aware of this and handle it properly. MDEV-16804 is adding the GENERATED flag to these columns, however if the columns are automatically created rather than manually created, they don't even show up via DESC / SHOW COLUMNS, so there is no way to add the needed conditional code to handle these cases at the application layer.
More details about how this is causing issues with phpMyAdmin can be found over in their GitHub: https://github.com/phpmyadmin/phpmyadmin/issues/14515
The only other work around I can think of at the moment for the application layer would be to assume any column listed in the PRIMARY KEY that is NOT listed in SHOW COLUMNS is a system GENERATED column. But there may be other applications that want to know the data type information of the PRIMARY KEY, so this would still need addressed.
As a note, according to documentation, user created INVISIBLE columns show up in SHOW COLUMNS, so system created INVISIBLE columns probably should too. https://mariadb.com/kb/en/library/invisible-columns/
CREATE TABLE vertest (x INT PRIMARY KEY) WITH SYSTEM VERSIONING; |
|
|
|
|
SHOW COLUMNS FROM vertest; |
+-------+---------+------+-----+---------+-------+ |
| Field | Type | Null | Key | Default | Extra | |
+-------+---------+------+-----+---------+-------+ |
| x | int(11) | NO | PRI | NULL | | |
+-------+---------+------+-----+---------+-------+ |
|
|
|
|
SHOW FULL COLUMNS FROM vertest; |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+ |
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+ |
| x | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | | |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+ |
|
|
|
|
SHOW INDEX FROM vertest; |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
| vertest | 0 | PRIMARY | 1 | x | A | 0 | NULL | NULL | | BTREE | | | |
| vertest | 0 | PRIMARY | 2 | row_end | A | 0 | NULL | NULL | | BTREE | | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
Attachments
Issue Links
- relates to
-
-
- Closed
-