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
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 ...
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.
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.
I fixed it in KB pages on "Server variables" and "System-versioned Tables" myself now.
I observed something similar in
MDEV-16288.