MySQL Bugs: #14501: transaction-isolation level is ignored in my.cnf if specified before ansi

Bug #14501	transaction-isolation level is ignored in my.cnf if specified before ansi
Submitted:	31 Oct 2005 4:40	Modified:	3 Nov 2005 13:27
Reporter:	James Dennis	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Linux (Linux 2.6.9-22.ELsmp SMP x86_64)
Assigned to:		CPU Architecture:	Any

Description:
In my.cnf I have:

[mysqld]
transaction-isolation = READ-COMMITTED
default-character-set = utf8
default-storage-engine = InnoDB
default-table-type = InnoDB
ansi

However when querying the value of transaction level I see:

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+

If I change my.cnf so that the transaction isolation level is specified after the ansi option it works correctly:

[mysqld]
default-character-set = utf8
default-storage-engine = InnoDB
default-table-type = InnoDB
ansi
transaction-isolation = READ-COMMITTED

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

How to repeat:
Place transaction-isolation before ansi.

[mysqld]
default-character-set = utf8
default-storage-engine = InnoDB
default-table-type = InnoDB
transaction-isolation = READ-COMMITTED
ansi

Thank you for a problem report. Though, it looks like not a bug, but intended behaviour for me. Options are read line by line, and applied in the same order. The manual clearly states the following (http://dev.mysql.com/doc/refman/5.0/en/ansi-mode.html):

"Running the server in ANSI mode is the same as starting it with these options (specify the --sql_mode value on a single line):

--transaction-isolation=SERIALIZABLE
--sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE"

So, it explicitely changes the isolation level. So, if you place "ansi" after any previous isolation level setting, it will be overloaded. And vice versa, just as you demonstrated.

we don't use "ansi" but try to set transaction-isolation = READ-UNCOMMITTED on a replication slave and it's also ignored

I'm seeing the same in MySQL 5.5, my.cnf has
transaction-isolation=READ-COMMITTED
transaction_isolation=READ-COMMITTED

but after restarting, show variables like '%_isolation' ; still returns
'tx_isolation', 'REPEATABLE-READ'

Turns out that MySQLWorkbench is changing tx_isolation to REPEATABLE-READ when it starts- thus was hiding any changes I made in my.conf. When I queried using the command-line "mysql" client, or using "mysqladmin variables|grep txn_isolation" I see that READ-COMITTED is properly in place.