MySQL Workbench session does not see updates to the database

Question

I have MySQL Workbench (community-6.2.3) installed in a Ubuntu system using .deb.

Workbench session does not seem to see updates (DML) to the database done by other sessions (applications/command line client).
A new session is able to see correct status of the database at its start but non of the changes that happen afterwards is visible to it.
It seems workbench session does sync up with db after a commit in workbench.

I'm getting Error Code: 1412. Table definition has changed, please retry transaction when I try to query a table that I've created from a different session.

Non workbench sessions does not seem to have any of these issues.

Am I missing a configuration or something?

---

Update:

This is partly the expected behaviour and partly a bug.

I'm not using autocommit mode, in which case SELECT statements are executed using the snapshot established at the first read.
This is the behaviour for REPEATABLE READ isolation level, which gets used by MySQL Workbench.

Is there a way to change or set default isolation level of a MySQL Workbench session?

When executed in Workbench:

SELECT @@Global.tx_isolation, @@tx_isolation, @@session.tx_isolation;

returns:

READ-COMMITTED, REPEATABLE-READ, REPEATABLE-READ

as opposed to, in command line client:

READ-COMMITTED, READ-COMMITTED, READ-COMMITTED

Related:
MySQL REPEATABLE-READ Workbench transaction level not set
MySQL Workbench and default session isolation level

Ignition

It happens with any query? – Bogosian

@Bogosian Yes, inserts/updates/deletes from other sessions are not reflected in workbench. – Ignition

That seems like pretty much what would be expected after you start a transaction, depending on your configured isolation level... can we assume you are not deliberately starting a transaction? Issuing a ROLLBACK; would make the other sessions' changes visible to you, but the question remains, why are you in a transaction, if you didn't start one. – Aeroscope

Answer 1

This is an old question, but still I have the same bug. OP mentionned a bug opened on MySQL Workbench in another thread (http://bugs.mysql.com/bug.php?id=69800).

According to doc (https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read), Default Isolation Level is REPEATABLE-READ.

This mean that a snapshot of the database is made on the FIRST read of the transaction. Every other read of this transaction will show you the data of the snapshot.

So you need to end the transaction (commit or roll-back) to get a new snapshot on the next read.

My colleagues who set MySQL Workbench on AutoCommit don't see the repeatable-read behaviour. We figured out it's because after each SELECT, the transaction is closed and a new snapshot is created.

So, as the bug is still not corrected, a workaround would be :

• switch to autocommit for new snapshots to be automatically created
• or commit/rollback after each SELECT to create a new snapshot

Carryingon