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

[31 Oct 2005 4:40] James Dennis
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
[3 Nov 2005 13:27] Valeriy Kravchuk
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.
[4 Dec 2017 13:27] Mordechai Danielov
we don't use "ansi" but try to set transaction-isolation = READ-UNCOMMITTED on a replication slave and it's also ignored
[9 Mar 1:54] Yary Lucian
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'
[9 Mar 20:49] Yary Lucian
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.