Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.20, 10.4.12
-
None
Description
When setting the session value of alter_algorithm to DEFAULT, it will not be set to the literal value DEFAULT, but will instead fetch whatever the current global value of the variable is. So unless the global value of the variable is also DEFAULT it is not possible to set the session value back to DEFAULT which is its default value.
This can lead to rather confusing error messages on ALTER TABLE ...
Same is true for system_versioning_asof where DEFAULT is also a valid literal value.
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';
|
+-----------------+---------+
|
| Variable_name | Value |
|
+-----------------+---------+
|
| alter_algorithm | DEFAULT |
|
+-----------------+---------+
|
|
|
MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm';
|
+-----------------+---------+
|
| Variable_name | Value |
|
+-----------------+---------+
|
| alter_algorithm | DEFAULT |
|
+-----------------+---------+
|
|
|
MariaDB [(none)]> SET GLOBAL alter_algorithm=COPY;
|
|
|
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';
|
+-----------------+-------+
|
| Variable_name | Value |
|
+-----------------+-------+
|
| alter_algorithm | COPY |
|
+-----------------+-------+
|
|
|
MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm';
|
+-----------------+---------+
|
| Variable_name | Value |
|
+-----------------+---------+
|
| alter_algorithm | DEFAULT |
|
+-----------------+---------+
|
|
|
MariaDB [(none)]> SET SESSION alter_algorithm=DEFAULT;
|
|
|
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';
|
+-----------------+-------+
|
| Variable_name | Value |
|
+-----------------+-------+
|
| alter_algorithm | COPY |
|
+-----------------+-------+
|
|
|
MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm';
|
+-----------------+-------+
|
| Variable_name | Value |
|
+-----------------+-------+
|
| alter_algorithm | COPY |
|
+-----------------+-------+
|
|
|
MariaDB [test]> SET SESSION alter_algorithm='DEFAULT';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm';
|
+-----------------+-------+
|
| Variable_name | Value |
|
+-----------------+-------+
|
| alter_algorithm | COPY |
|
+-----------------+-------+
|
1 row in set (0.002 sec)
|
|
|
MariaDB [test]> SHOW SESSION VARIABLES LIKE 'alter_algorithm';
|
+-----------------+---------+
|
| Variable_name | Value |
|
+-----------------+---------+
|
| alter_algorithm | DEFAULT |
|
+-----------------+---------+
|
1 row in set (0.002 sec)
|
|
Attachments
Issue Links
- relates to
-
MDEV-13134 Introduce ALTER TABLE attributes ALGORITHM=NOCOPY and ALGORITHM=INSTANT
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Description |
When setting the session value of {{alter_algorithm}} to {{DEFAULT}}, it will not be set to the literal value {{DEFAULT}}, but will instead fetch whatever the current global value of the variable is. So unless the global value of the variable is also {{DEFAULT}} it is not possible to set the session value back to {{DEFAULT}} which is its default value.
This can lead to rather confusing error messages on ALTER TABLE ... {noformat} MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SET GLOBAL alter_algorithm=COPY; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SET SESSION alter_algorithm=DEFAULT; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ {noformat} |
When setting the session value of {{alter_algorithm}} to {{DEFAULT}}, it will not be set to the literal value {{DEFAULT}}, but will instead fetch whatever the current global value of the variable is. So unless the global value of the variable is also {{DEFAULT}} it is not possible to set the session value back to {{DEFAULT}} which is its default value.
This can lead to rather confusing error messages on ALTER TABLE ... Same is true for {{system_versioning_asof}} where {{DEFAULT}} is also a valid literal value. {noformat} MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SET GLOBAL alter_algorithm=COPY; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SET SESSION alter_algorithm=DEFAULT; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ {noformat} |
| Summary | Setting alter_algorithm to DEFAULT sets it to global value instead | Setting alter_algorithm or system_versioning_asof to DEFAULT sets it to global value instead |
You can still set it to DEFAULT if you quote the value.
MariaDB [test]> set @@global.alter_algorithm=COPY; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> set @@global.alter_algorithm= COPY; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> set @@alter_algorithm= DEFAULT; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select @@alter_algorithm; |
+-------------------+ |
| @@alter_algorithm |
|
+-------------------+ |
| COPY |
|
+-------------------+ |
1 row in set (0.000 sec) |
MariaDB [test]> set @@alter_algorithm= 'DEFAULT'; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [test]> select @@alter_algorithm; |
+-------------------+ |
| @@alter_algorithm |
|
+-------------------+ |
| DEFAULT | |
+-------------------+ |
1 row in set (0.000 sec) |
I don't see what else can be done about it, @@session.var= DEFAULT resetting the value to the global one is the common and documented behavior. Having DEFAULT among valid values is admittedly not a very good design as it causes ambiguity, but since it is already in the GA trees, it is not easy to get rid of, either. If the problem is indeed important, maybe we should add a synonym to it, and start deprecating DEFAULT as enum values?
elenst, I agree that we probably have to introduce a new ENUM value as an alias to the unfortunately named DEFAULT.
For alter_algorithm and MDEV-16288, this is even more unfortunate, because ALGORITHM=DEFAULT was valid syntax already in MySQL 5.6 and MariaDB 10.0. I think that we should allow the new name also in the ALGORITHM clause.
What could be an easy-to-remember but yet distinctive replacement of the name DEFAULT? The name LEGACY might work for alter_algorithm. For system_versioning_asof, I have no idea.
"You can still set it to DEFAULT if you quote the value. "
That's weird ... I remember having tried yet, and it didn't work back when I filed this, but I can't reproduce it now, 'DEFAULT' and "DEFAULT" now working fine in 10.3 and 10.4. Not sure what I did wrong there last week.
This also makes fixing MDEV-21213 in a good way easier 
So we can probably reduce this ones priority, maybe even turn it into a documentation issue only ...
| Component/s | Documentation [ 10903 ] | |
| Component/s | Storage Engine - InnoDB [ 10129 ] |
| Assignee | Geoff Montee [ geoffmontee ] |
We already had this test case in versioning.sysvars since December 2017:
--echo # DEFAULT: value is copied from GLOBAL to SESSION
|
set global system_versioning_asof= timestamp'1911-11-11 11:11:11.111111'; |
set system_versioning_asof= '1900-01-01 00:00:00'; |
select @@global.system_versioning_asof != @@system_versioning_asof as different; |
set system_versioning_asof= default; |
select @@global.system_versioning_asof = @@system_versioning_asof as equal; |
We could claim that this is intentional and should simply be documented.
I will add a test case for alter_algorithm, which we forgot to add in MDEV-13134.
| Fix Version/s | N/A [ 14700 ] |
| Summary | Setting alter_algorithm or system_versioning_asof to DEFAULT sets it to global value instead | Document difference between DEFAULT and 'DEFAULT' when setting alter_algorithm or system_versioning_asof |
This is currently documented for alter_algorithm: https://mariadb.com/docs/reference/mdb/system-variables/alter_algorithm/#disambiguating-default-values
We need similar documentation for system_versioning_asof.
| Epic Link | MENT-51 [ 75581 ] |
| Link | This issue is part of MENT-51 [ MENT-51 ] |
| Epic Link | MENT-51 [ 75581 ] |
| Workflow | MariaDB v3 [ 104472 ] | MariaDB v4 [ 141815 ] |
| Assignee | Geoff Montee [ geoffmontee ] | Joe Cotellese [ JIRAUSER54006 ] |
| Description |
When setting the session value of {{alter_algorithm}} to {{DEFAULT}}, it will not be set to the literal value {{DEFAULT}}, but will instead fetch whatever the current global value of the variable is. So unless the global value of the variable is also {{DEFAULT}} it is not possible to set the session value back to {{DEFAULT}} which is its default value.
This can lead to rather confusing error messages on ALTER TABLE ... Same is true for {{system_versioning_asof}} where {{DEFAULT}} is also a valid literal value. {noformat} MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SET GLOBAL alter_algorithm=COPY; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SET SESSION alter_algorithm=DEFAULT; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ {noformat} |
When setting the session value of {{alter_algorithm}} to {{DEFAULT}}, it will not be set to the literal value {{DEFAULT}}, but will instead fetch whatever the current global value of the variable is. So unless the global value of the variable is also {{DEFAULT}} it is not possible to set the session value back to {{DEFAULT}} which is its default value.
This can lead to rather confusing error messages on ALTER TABLE ... Same is true for {{system_versioning_asof}} where {{DEFAULT}} is also a valid literal value. {noformat} MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SET GLOBAL alter_algorithm=COPY; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ MariaDB [(none)]> SET SESSION alter_algorithm=DEFAULT; MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ MariaDB [test]> SET SESSION alter_algorithm='DEFAULT'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> SHOW GLOBAL VARIABLES LIKE 'alter_algorithm'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | alter_algorithm | COPY | +-----------------+-------+ 1 row in set (0.002 sec) MariaDB [test]> SHOW SESSION VARIABLES LIKE 'alter_algorithm'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | alter_algorithm | DEFAULT | +-----------------+---------+ 1 row in set (0.002 sec) {noformat} |
I fixed it in KB pages on "Server variables" and "System-versioned Tables" myself now.
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Link | This issue is part of MENT-51 [ MENT-51 ] |
I observed something similar in
MDEV-16288.