Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Values cannot be edited in SYSTEM VERSIONING tables with INVISIBLE timestamps #14515

Closed
darkain opened this issue Jul 23, 2018 · 0 comments · Fixed by #14536
Closed

Values cannot be edited in SYSTEM VERSIONING tables with INVISIBLE timestamps #14515

darkain opened this issue Jul 23, 2018 · 0 comments · Fixed by #14536
Assignees
@MauricioFauth @darkain
Labels
Bug A problem or regression with an existing feature has-pr An issue that has a pull request pending that may fix this issue. The pull request may be incomplete
Milestone
4.9.0.1

Comments

@darkain
Copy link
Contributor

darkain commented Jul 23, 2018 •
edited

Reference: https://mariadb.com/kb/en/library/system-versioned-tables/
Reference: https://mariadb.com/kb/en/library/invisible-columns/

Steps to reproduce

1a. Create a table with SYSTEM VERSIONING without manually specifying the TIMESTAMP columns.
1b. Create a table with SYSTEM VERSIONING, set the ts and te columns to INVISIBLE, and add a PRIMARY KEY to any other column
2. Browse the table in phpMyAdmin
3. phpMyAdmin says: "Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available."

Expected behaviour

Normal browsing and editing behavior

Actual behaviour

"Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available."

phpMyAdmin identifies that the primary key is adjusted to have two columns (the one specified, and the te GENERATED column). With te set to INVISIBLE, when doing a SELECT * on the table, the column does not show up in the results. With SYSTEM VERSIONING, te is a read-only GENERATED column and should not be used for UPDATE or DELETE. These queries are fully deterministic using just the initially specified PRIMARY KEY column without the generated te column.

Server configuration

Operating system: Debian Stretch 9.5

Web server: Apache 2.4.25-3+deb9u5

Database: 10.3.8-MariaDB-1:10.3.8+maria~stretch-log - mariadb.org binary distribution

PHP version: 7.0.30-0+deb9u1

phpMyAdmin version: 4.8.2

Client configuration

Browser: Opera 54

Operating system: Windows 10 1803
@darkain darkain mentioned this issue Aug 1, 2018
Merged
4 tasks
@MauricioFauth MauricioFauth added Bug A problem or regression with an existing feature has-pr An issue that has a pull request pending that may fix this issue. The pull request may be incomplete labels Oct 29, 2018
MauricioFauth pushed a commit that referenced this issue Feb 15, 2019
@darkain @MauricioFauth
Fix for SYSTEM VERSIONED tables #14514, #14515, and #14516 (#14536)
3f6e360 
Fixes #14514
Fixes #14515
Possible fix for #14516

#14514 Tables which enable the new MariaDB 10.3 versioning show up as "SYSTEM VERSIONED" instead of "BASE TABLE" inside of information schema. This patch now checks for both, so these new table types don't incorrectly display as views.

#14515 MariaDB 10.3 also supports INVISIBLE columns, which is optionally paired with SYSTEM VERSIONED tabled (or implemented separately). This patch helps handle this new column attribute.

#14516 This patch cannot be fully tested and verified with current MariaDB builds. Version 10.3.9 (currently unreleased) corrects an issue with column information for SYSTEM VERSIONED columns that this patch relies on. https://jira.mariadb.org/browse/MDEV-16804 - the reason for checking with preg_match instead of adding to the list is because there are now combined "extra" information for columns, such as INVISIBLE columns. Just matching entire strings also has a bug with these other "extra" parameters.

Signed-off-by: Vincent Milum Jr <git@darkain.com>
Signed-off-by: Maurício Meneghini Fauth <mauriciofauth@gmail.com>
MauricioFauth pushed a commit that referenced this issue Feb 15, 2019
@darkain @MauricioFauth
Fix for SYSTEM VERSIONED tables #14514, #14515, and #14516 (#14536)
31b40f7 
Fixes #14514
Fixes #14515
Possible fix for #14516

 #14514 Tables which enable the new MariaDB 10.3 versioning show up as "SYSTEM VERSIONED" instead of "BASE TABLE" inside of information schema. This patch now checks for both, so these new table types don't incorrectly display as views.

 #14515 MariaDB 10.3 also supports INVISIBLE columns, which is optionally paired with SYSTEM VERSIONED tabled (or implemented separately). This patch helps handle this new column attribute.

 #14516 This patch cannot be fully tested and verified with current MariaDB builds. Version 10.3.9 (currently unreleased) corrects an issue with column information for SYSTEM VERSIONED columns that this patch relies on. https://jira.mariadb.org/browse/MDEV-16804 - the reason for checking with preg_match instead of adding to the list is because there are now combined "extra" information for columns, such as INVISIBLE columns. Just matching entire strings also has a bug with these other "extra" parameters.

Signed-off-by: Vincent Milum Jr <git@darkain.com>
Signed-off-by: Maurício Meneghini Fauth <mauriciofauth@gmail.com>
(cherry picked from commit 3f6e360)
Signed-off-by: Maurício Meneghini Fauth <mauriciofauth@gmail.com>
MauricioFauth added a commit that referenced this issue Feb 15, 2019
@MauricioFauth
Add ChangeLog entries for #14514 and #14515
11cd119 
[ci skip]

Signed-off-by: Maurício Meneghini Fauth <mauriciofauth@gmail.com>
@MauricioFauth MauricioFauth added this to the 4.8.6 milestone Feb 15, 2019
@github-actions github-actions bot locked as resolved and limited conversation to collaborators Jun 21, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees

@MauricioFauth MauricioFauth

@darkain darkain

Labels
Bug A problem or regression with an existing feature has-pr An issue that has a pull request pending that may fix this issue. The pull request may be incomplete
Projects
None yet
Milestone
4.9.0.1
Development

Successfully merging a pull request may close this issue.

Fix for SYSTEM VERSIONED tables #14514, #14515, and #14516
2 participants
@MauricioFauth @darkain