Hai,
I work with ZEOS 6.6.3 with MySQL 4.1 and I encountered refresh problems.
When I make a SQL append, update or delete the changes will be visible for other users only after a reconnect or programrestart.
Please advise, this is a serious problem!
Gr. Aad
RefreshProblem
Moderators: gto, EgonHugeist
-
mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
Goeieavond Aad,
I suppose you are using a transactional storage engine in mysql. In that case the visibility of updates in other sessions depend on commits issued by the updates and the transaction isolation level used on both sessions.
Also make sure you're not using cached updates.
To be sure an update is posted to the server before trying to query from an other session you could use a TZSQLMonitor to check if update and commit statements are effectively issued to the server.
Mark
I suppose you are using a transactional storage engine in mysql. In that case the visibility of updates in other sessions depend on commits issued by the updates and the transaction isolation level used on both sessions.
Also make sure you're not using cached updates.
To be sure an update is posted to the server before trying to query from an other session you could use a TZSQLMonitor to check if update and commit statements are effectively issued to the server.
Mark
Goeieavond Mark,
Can you help us with our properties.
I am using the InnoDB and MyISAM database from MySQL 4.1. Which is better concerning our refresh problem?
The TZconnection properties are AutoCommit is True and TransactIsolationLevel is tiNone
The TZQuery properties are CachedUpdate is False.
There is also a property UseSequenceFieldForRefreshSQL from TZUpdateSQL that I don't understand. The value is False.
Kan je ons helpen?
Groetjes, Aad
Can you help us with our properties.
I am using the InnoDB and MyISAM database from MySQL 4.1. Which is better concerning our refresh problem?
The TZconnection properties are AutoCommit is True and TransactIsolationLevel is tiNone
The TZQuery properties are CachedUpdate is False.
There is also a property UseSequenceFieldForRefreshSQL from TZUpdateSQL that I don't understand. The value is False.
Kan je ons helpen?
Groetjes, Aad
-
mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
Hoi Aad,
Thinking about it... With mysql it shouldn't make a difference between MyISAM and Innodb when you're using auocommit and cachedUpdates=False on all clients accessing the database. Also the transaction isolation level may not make much difference.
However, with Innodb it MAY help to use autocommit=false and execute a commit on the reading side before doing Query.refresh. This may avoid transaction isolation issues.
For MyISAM this shouldn't make a difference. When the update is posted it's written immediately and visible for every database client.
When you still have trouble getting this right you definitely should add a TZSQLMonitor to both writing and reading applications and write a log file for each proces. That way it's possible to 'replay' what happens exactly and in what order. (Make sure both processes use the same system time)
This works even better when you can emulate the problem with 2 connection objects in one executable. Then you have 1 log file which is a perfect chronological representation of what happens.
Mark
Thinking about it... With mysql it shouldn't make a difference between MyISAM and Innodb when you're using auocommit and cachedUpdates=False on all clients accessing the database. Also the transaction isolation level may not make much difference.
However, with Innodb it MAY help to use autocommit=false and execute a commit on the reading side before doing Query.refresh. This may avoid transaction isolation issues.
For MyISAM this shouldn't make a difference. When the update is posted it's written immediately and visible for every database client.
When you still have trouble getting this right you definitely should add a TZSQLMonitor to both writing and reading applications and write a log file for each proces. That way it's possible to 'replay' what happens exactly and in what order. (Make sure both processes use the same system time)
This works even better when you can emulate the problem with 2 connection objects in one executable. Then you have 1 log file which is a perfect chronological representation of what happens.
Mark