Database Administrators

Return to Question

3 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link
inline side-by-side side-by-side markdown

There are currently some open bugs about this issue (https://bugs.mysql.com/bug.php?id=69800, https://bugs.mysql.com/bug.php?id=53341), and some questions in the subject (http://stackoverflow.com/questions/25268782/mysql-workbench-and-default-session-isolation-levelhttps://stackoverflow.com/questions/25268782/mysql-workbench-and-default-session-isolation-level, http://stackoverflow.com/questions/26208007/mysql-workbench-session-does-not-see-updates-to-the-databasehttps://stackoverflow.com/questions/26208007/mysql-workbench-session-does-not-see-updates-to-the-database), but none of these have an actual answer to why this happens or suggests a workaround.

There are currently some open bugs about this issue (https://bugs.mysql.com/bug.php?id=69800, https://bugs.mysql.com/bug.php?id=53341), and some questions in the subject (http://stackoverflow.com/questions/25268782/mysql-workbench-and-default-session-isolation-level, http://stackoverflow.com/questions/26208007/mysql-workbench-session-does-not-see-updates-to-the-database), but none of these have an actual answer to why this happens or suggests a workaround.

There are currently some open bugs about this issue (https://bugs.mysql.com/bug.php?id=69800, https://bugs.mysql.com/bug.php?id=53341), and some questions in the subject (https://stackoverflow.com/questions/25268782/mysql-workbench-and-default-session-isolation-level, https://stackoverflow.com/questions/26208007/mysql-workbench-session-does-not-see-updates-to-the-database), but none of these have an actual answer to why this happens or suggests a workaround.
2 added 933 characters in body
source | link
inline side-by-side side-by-side markdown

UPDATE 2017-03-26:

as @RickJames suggested i turned on general log on one instance and found the following commands being executed when i open a new MySQL Workbench connection:

select null #(this command is the value of "init_connect")
set autocommit=1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
SELECT current_user()
SET CHARACTER SET utf8
SET NAMES utf8
SELECT CONNECTION_ID()
SHOW SESSION STATUS LIKE 'Ssl_cipher'
set autocommit=1
SHOW SESSION VARIABLES LIKE 'sql_mode'
SHOW SESSION VARIABLES LIKE 'version_comment'
SHOW SESSION VARIABLES LIKE 'version'
SELECT current_user()
SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
SHOW SESSION VARIABLES LIKE 'version_compile_os'

So it confirms that MySQL workbench does indeed set the transaction isolation level.

UPDATE 2017-03-26:

as @RickJames suggested i turned on general log on one instance and found the following commands being executed when i open a new MySQL Workbench connection:

select null #(this command is the value of "init_connect")
set autocommit=1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
SELECT current_user()
SET CHARACTER SET utf8
SET NAMES utf8
SELECT CONNECTION_ID()
SHOW SESSION STATUS LIKE 'Ssl_cipher'
set autocommit=1
SHOW SESSION VARIABLES LIKE 'sql_mode'
SHOW SESSION VARIABLES LIKE 'version_comment'
SHOW SESSION VARIABLES LIKE 'version'
SELECT current_user()
SHOW SESSION VARIABLES LIKE 'lower_case_table_names'
SHOW SESSION VARIABLES LIKE 'version_compile_os'

So it confirms that MySQL workbench does indeed set the transaction isolation level.
1
source | link

transaction isolation in MySQL workbench

MySQL Workbench has a bug where every new created session receives default session parameter values regardless of the server's definition for that parameter. For example, when the server's "transaction-isolation" parameter is set to "READ-COMMITTED", connecting with workbench and issuing the command "show variables like 'tx_isolation'" will result in "REPEATABLE-READ", which is MySQL's default, but not this server's configuration (connecting with other clients like command-line or python results in the expected behavior).

There are currently some open bugs about this issue (https://bugs.mysql.com/bug.php?id=69800, https://bugs.mysql.com/bug.php?id=53341), and some questions in the subject (http://stackoverflow.com/questions/25268782/mysql-workbench-and-default-session-isolation-level, http://stackoverflow.com/questions/26208007/mysql-workbench-session-does-not-see-updates-to-the-database), but none of these have an actual answer to why this happens or suggests a workaround.

The issues in my organization is that at possibly any moment, a user may connect to one of our databases with MySQL workbench and execute a "SELECT" query that takes a very long time (for the sake of the example, a few hours long). Since the user's session is in "REPEATABLE-READ" mode the query will lock DML statements on the selected tables, regardless of it's auto-commit configuration status.

Which leads to my question:

How can MySQL Workbench "new session parameters" can be configured?

I have already tried the following:

  1. install the latest MySQL workbench version (6.3.5)

  2. Add the following lines to both global and user specific my.cnf files:

    [client]

    transaction-isolation="READ-COMMITTED"

    [mysql]

    transaction-isolation="READ-COMMITTED"

  3. Add the following lines to both global and user specific my.cnf files:

    [client]

    init-command="SET SESSION tx_isolation='READ-COMMITTED'"

    [mysql]

    init-command="SET SESSION tx_isolation='READ-COMMITTED'"

  4. Under workbench's installation directory i found the file modules/opts.py to contain the following line, so i changed the default to 'READ-COMMITTED':

{'caption': 'transaction-isolation', 'versions': [(), ((4, 1), (5, 0), (5, 1), (5, 4), (5, 5), (5, 6), (5, 7), (6, 0)), ()], 'values': [{'default': 'REPEATABLE-READ', 'type': 'enum', 'choice': ['READ-UNCOMMITTED', 'READ-COMMITTED', 'REPEATABLE-READ', 'SERIALIZABLE']}], 'description': 'Default transaction isolation level', 'name': 'transaction-isolation'}

Issues encountered on both windows 7 and ubuntu 14.04. Thanks