MySQL Workbench会话看不到数据库更新 [英] MySQL Workbench session does not see updates to the database
问题描述
我在使用.deb的Ubuntu系统中安装了MySQL Workbench(社区6.2.3).
Workbench会话似乎看不到其他会话(应用程序/命令行客户端)对数据库的更新(DML).
新会话能够在开始时看到数据库的正确状态，但是此后发生的任何变化都不可见.
在工作台中提交后，工作台会话似乎确实与db同步.
当我尝试查询从其他会话创建的表时，得到Error Code: 1412. Table definition has changed, please retry transaction
.
非工作台会话似乎没有这些问题.
我缺少配置或其他内容吗?
更新:
这部分是预期的行为，部分是错误.
我未使用自动提交模式，在这种情况下，使用第一次读取时建立的快照执行SELECT语句.
这是REPEATABLE READ隔离级别的行为，该级别已由MySQL Workbench使用.
是否可以更改或设置MySQL Workbench会话的默认隔离级别?
在工作台中执行时:
SELECT @@Global.tx_isolation, @@tx_isolation, @@session.tx_isolation;
返回:
READ-COMMITTED, REPEATABLE-READ, REPEATABLE-READ
相对于命令行客户端:
READ-COMMITTED, READ-COMMITTED, READ-COMMITTED
相关:
未设置MySQL REPEATABLE-READ Workbench事务级别
MySQL Workbench和默认会话隔离级别
这是一个老问题，但是我仍然遇到相同的错误. OP提到了在另一个线程上在MySQL Workbench上打开的错误( http://bugs.mysql. com/bug.php?id = 69800 ).
根据doc( https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read )，默认隔离级别为REPEATABLE-READ.
这意味着在第一次读取事务时会创建数据库快照.每次对该事务的其他读取都会向您显示快照的数据.
因此，您需要结束事务(提交或回滚)才能在下次读取时获得新的快照.
在AutoCommit上设置MySQL Workbench的同事看不到可重复读取的行为.我们发现这是因为在每次执行SELECT之后，事务都会关闭，并且会创建一个新的快照.
因此，由于该错误仍未得到纠正，因此一种解决方法是:
- 切换到自动提交以自动创建新快照
- 或在每次SELECT之后提交/回滚以创建新快照
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
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
这篇关于MySQL Workbench会话看不到数据库更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
