MySQL Bugs: #115619: Invalid variable error on restart

Bug #115619	Invalid variable error on restart
Submitted:	17 Jul 2024 6:11	Modified:	1 May 17:38
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	9.0.0	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)

Description:
Run this query:

SET PERSIST_ONLY TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Then restart the server. The following error will be shown in the log:

[ERROR] [MY-011268] [Server] Configuring persisted options failed: "Variable 'transaction_isolation' can't be set to the value of '3'".

From the documentation, PERSIST_ONLY is not mentioned in the transaction isolation level option. Maybe it shouldn't be allowed?

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the steps above.

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh

--
cat my.cnf
[mysqld]

rm -rf 115619/
bin/mysqld --defaults-file=my.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/115619 --log-error-verbosity=3
bin/mysqld_safe --defaults-file=my.cnf --mysqld-version='' --basedir=$PWD --datadir=$PWD/115619 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/115619/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

 bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 9.0.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PERSIST_ONLY TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

-- excerpts from error log

bin/mysqld_safe --defaults-file=my.cnf --mysqld-version='' --basedir=$PWD --datadir=$PWD/115619 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/115619/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

cat 115619/log.err | grep -i 'transaction_isolation'
2024-07-18T05:46:49.498924Z 6 [ERROR] [MY-011268] [Server] Configuring persisted options failed: "Variable 'transaction_isolation' can't be set to the value of '3'".

Documented fix as follows in the MySQL9.4.0 changelog:

    It was possible to use the PERSIST or PERSIST_ONLY keyword with
    SET TRANSACTION ISOLATION LEVEL, even though this should not be
    allowed, and later caused errors on server restart. Now
    attempting to do so causes the statement to be rejected with
    ER_CANNOT_PERSIST_TRANSACTION_ISOLATION.

Closed.