Bug #116879 Unable to configure external locking in MySQL with --skip-external-locking
Submitted: 4 Dec 2024 21:25 Modified: 6 Dec 2024 19:24
Reporter: KOS Team Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:Ver 8.0.40 OS:Ubuntu (Ver 8.0.40-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu)))
Assigned to: CPU Architecture:x86

[4 Dec 2024 21:25] KOS Team
Description:
In this documentation: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_external_...,
the option "--skip-external-locking" can be set to ON or OFF to configure mysqld to use
external locking or not. However, this option takes no effect when specified with the value OFF
in the "/etc/my.cnf" configuration file.

The root cause seems to be that, in https://github.com/mysql/mysql-server/blob/61a3a1d8ef15512396b4c2af46e922a19bf2b174/sql/sy..., "--skip-external-locking" seems to be set as a read-only variable with default value of true, making this option futile. 

How to repeat:
Install MySQL server:
```
sudo apt install mysql-server
```

Shut down the MySQL server:
```
sudo systemctl stop mysql
```

Edit the configuration file "/etc/my/cnf" with the following content:
```
[mysqld]
skip-external-locking=OFF
```

Such that we don't skip external locking, i.e., we use external locking.

Restart MySQL server:
```
sudo systemctl start mysql
```

Export the system variables, and observe that external-locking is still FALSE:
```
mysqld --verbose --help | grep external-locking
```

Suggested fix:
The documentation of mysql server states that the variable can be configured with OFF or ON.
However, the source code defines this option as READONLY with default value of ON. 
A potential fix is to clarify whether this option can be configured with value OFF in the documentation, and rejects the value when MySQL starts with OFF.
[5 Dec 2024 6:47] Daniël van Eeden
I think it is best to use either:
- a key without a value, but with `skip-` prefix to disable.
- a key with a value, but without prefix

Examples:

external_locking        # ON
skip_external_locking   # OFF

external_locking=ON     # ON
external_locking=OFF    # OFF

The docs may be intended for these, but aren't very clear on this.
[5 Dec 2024 6:57] Daniël van Eeden
$ mysqld --help --verbose | grep -E '^external-locking'
external-locking                                             TRUE
$ grep 'external.locking' /etc/my.cnf
external_locking

This is with 9.1.0, but 8.0.40 is probably behaving the same.
[5 Dec 2024 7:30] MySQL Verification Team
Hi,

If you look at the same url:

"This variable is set by the --external-locking or --skip-external-locking option. External locking is disabled by default. "

The documentation could be a bit clearer so I'll pass this to our documentation team for re-check.

Thanks
[5 Dec 2024 18:56] KOS Team
Currently there are two options to configure the external locking: --external-locking and --skip-external-locking. Both of them are READONLY parameters which do not accept ON|OFF values. 

The --skip-external-locking option is problematic because it is a READONLY parameter with default value of ON, which means that this variable cannot affect the system configuration in any case. READONLY parameters should never have the default value of ON.

On the other hand, the --external-locking option itself should be already enough to control the external locking configuration.

Updating the documentation is a sensible quick fix of this issue. In the future, MySQL can perhaps consider removing the --skip-external-locking option as it has no effect anyway. 

Having two options controlling one behavior may be not an optimal configuration interface design, as it introduces the possibility of both parameters being set at the same time and users need to understand the overwrite behavior.
[6 Dec 2024 7:03] Daniël van Eeden
There is only *one* option, called `external-locking`.

See also:
https://github.com/mysql/mysql-server/blob/61a3a1d8ef15512396b4c2af46e922a19bf2b174/sql/my...

This option is disabled by default. You can enable it by either putting `external_locking` or `external_locking` in your my.cnf.

The `skip-` prefix is just an alternative way of disabling an option, so `foobar=OFF` and `skip-foobar` are the same. 

If you use `skip-foobar=ON` then you're mixing two different ways of setting options.

To demonstrate this with an options that is TRUE by default:

$ mysqld --help --verbose | grep -E '^log-slave'
log-slave-updates                                            TRUE
$ mysqld --help --verbose --skip-log-slave-updates| grep -E '^log-slave'
log-slave-updates                                            FALSE
$ mysqld --help --verbose --log-slave-updates=OFF | grep -E '^log-slave'
log-slave-updates                                            FALSE
[6 Dec 2024 7:04] Daniël van Eeden
Fixed message
-------------

There is only *one* option, called `external-locking`.

See also:
https://github.com/mysql/mysql-server/blob/61a3a1d8ef15512396b4c2af46e922a19bf2b174/sql/my...

This option is disabled by default. You can enable it by either putting `external_locking` or `external_locking=ON` in your my.cnf.

The `skip-` prefix is just an alternative way of disabling an option, so `foobar=OFF` and `skip-foobar` are the same. 

If you use `skip-foobar=ON` then you're mixing two different ways of setting options.

To demonstrate this with an options that is TRUE by default:

$ mysqld --help --verbose | grep -E '^log-slave'
log-slave-updates                                            TRUE
$ mysqld --help --verbose --skip-log-slave-updates| grep -E '^log-slave'
log-slave-updates                                            FALSE
$ mysqld --help --verbose --log-slave-updates=OFF | grep -E '^log-slave'
log-slave-updates                                            FALSE
[6 Dec 2024 19:24] KOS Team
Thanks for the detailed explanation! We understand that they are one option now.

The documentation at https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_skip_external_...
misled us to think that the `--skip-external-locking` accepts the {ON|OFF} values.

The reason we suggest to remove the `--skip-external-locking` option is because we don't think this option makes an effect to the system variable in any case.

In the case neither of `--skip-external-locking` and `--external-locking` command line options are present, external-locking is FALSE:
```
mysqld --help --verbose | grep -E '^external-locking'
external-locking                                             FALSE
```

In the case users specify `--skip-external-locking`, the external-locking system variable is still by default FALSE.
```
mysqld --help --verbose --skip-external-locking | grep -E '^external-locking'
external-locking                                             FALSE
```

In the case users specify both `--skip-external-locking` and `--external-locking=ON`, the `--skip-external-locking` does not overwrite the `external-locking` option:
```
mysqld --help --verbose --skip-external-locking --external-locking=ON | grep -E '^external-locking'
external-locking                                             TRUE
```

In all the cases above, the `--skip-external-locking` option does not have any effect. This is why we are confused why we have this option and suggested to remove it.