Hi!
Downloaded and installed successfully ZeosLib 6.6..0beta into Delphi 7.0 for using with MySQL 5.0
Got strange issue: when I run SQL-query on server the query returns 11 actually records
But when i run the same query from delphi program query returned 13 records (11 actually and 2 recently deleted)
Tried restart mysql, start transaction ... commit -the issue is not resolved
What are causes of this issue?
P.S. Sorry for poor English.
SELECT query returned data with deleted records
Moderators: gto, cipto_kh, EgonHugeist
-
mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
First : I don't know your app, of course. So forgive my mistakes.
- What storage engine are you using with mysql? In case you used MyIsam I'm puzzled. There every update is almost physically written to the table. Ifyou use a transactional engine, it could be there was no commit on the server and after rollback (reboot server) the rows are back again.
- Your query does not depend on the environment it's running in? (e.g. User, @-variables, ...)
- Delete was physical (no 'delete' switch for logical delete)
Just guessing a little. I'm absolutely certain mysql does not 'invent' deleted rows, so there must be some logical explanation. Question is : who will find it.
Mark
- What storage engine are you using with mysql? In case you used MyIsam I'm puzzled. There every update is almost physically written to the table. Ifyou use a transactional engine, it could be there was no commit on the server and after rollback (reboot server) the rows are back again.
- Your query does not depend on the environment it's running in? (e.g. User, @-variables, ...)
- Delete was physical (no 'delete' switch for logical delete)
Just guessing a little. I'm absolutely certain mysql does not 'invent' deleted rows, so there must be some logical explanation. Question is : who will find it.
Mark
mdaems, thank you for your reply
The engine is InnoDB.
I've restarted mysql server again.
I don't understand why but transaction really is not being committed.
I've tried again - start transaction; delete ... ; commit on the mysql-server and well done SELECT-query works properly from mysql-server and from delphi app : ) .
One more question
Why transaction was no commit at first time? what are reasons?
Best regards
The engine is InnoDB.
I've restarted mysql server again.
I don't understand why but transaction really is not being committed.
I've tried again - start transaction; delete ... ; commit on the mysql-server and well done SELECT-query works properly from mysql-server and from delphi app : ) .
One more question
Why transaction was no commit at first time? what are reasons?
Best regards
-
mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
Well,
I don't know why commit was not done first.
I think the client doing the delete (I suppose the mysql command line client?) was not in autocommit mode and you forgot the commit statement. At that time your client session on the server knows about the delete and selects OK. The zeoslib client does not know about it. Certainly not when the transaction isolation level is 'high' enough. (Interesting topic when having this kind of transaction problems. So google it...)
When you reboot the server without the server client deletes being comitted that action is rolled back. So every client will now see the rows again.
Mark
I don't know why commit was not done first.
I think the client doing the delete (I suppose the mysql command line client?) was not in autocommit mode and you forgot the commit statement. At that time your client session on the server knows about the delete and selects OK. The zeoslib client does not know about it. Certainly not when the transaction isolation level is 'high' enough. (Interesting topic when having this kind of transaction problems. So google it...)
When you reboot the server without the server client deletes being comitted that action is rolled back. So every client will now see the rows again.
Mark