Microsoft Access Undelete or Lack Thereof
Recently I had occasion to delete a couple of rows from a MySQL table using the Access interface. This is not something I make a habit of doing. Normally with an Access database you highlight some rows, press Delete, the rows disappear, it asks you do you really want to do that, and if you say "No" the rows magically reappear again.
The first thing that happened was an error. I did not have Delete privilege granted to me on this database. I quickly corrected that, noting that the error messages appeared before it asked me for confirmation that I really meant to do this operation.
So, again selecting and electing to delete these rows, as it prompted me with it's readback of what I am about to do to my database that I cannot undo, and do I really want to do it? message, I elected to say "No" just for grins. My rows came back— sort of. They had the #Deleted text in every column, and I had this feeling that they hadn't really come back. Good thing I really meant to delete them!
This got me to thinking. Obviously Access had actually gone ahead and deleted them before asking if I really wanted to delete them. It was expecting to bring these rows back via some mechanism evidently. I checked the table definition and it was a MyISAM table, not a suprise since I had set it that way on purpose. So I did the following:
It worked.
So my conclusion is that the Access front end is counting on transactions being supported in order to roll back the operation if the user elects not to perform it.
My real concern here is the "You are about to delete 1 record(s)" message, the key words being "about to"— it has already done so! Especially since it has blithely begun a transaction and deleted your rows, confident in it's ability to roll this transaction back, and completly unaware that the underlying storage engine does not support transactions and has already irrevocably deleted them.
This gives rise to a whole slew of further questions. First of all I don't exactly like Access's style of first deleting the records and then telling me I am about delete them and do I really want to, etc. I really feel it should ask me first.
That said, obviously it sends some command to initiate a transaction. At some point deep in the bowels of MySQL this command performs a null action since the underlying storage engine does not support transactions. However, it returns as if it did. The question is, since I am using an engine that is not transaction-safe, should this command return an error? Should there be an option to enable or disabling returning an error? What were the design factors leading to the current way it is implemented?
The first thing that happened was an error. I did not have Delete privilege granted to me on this database. I quickly corrected that, noting that the error messages appeared before it asked me for confirmation that I really meant to do this operation.
So, again selecting and electing to delete these rows, as it prompted me with it's readback of what I am about to do to my database that I cannot undo, and do I really want to do it? message, I elected to say "No" just for grins. My rows came back— sort of. They had the #Deleted text in every column, and I had this feeling that they hadn't really come back. Good thing I really meant to delete them!
This got me to thinking. Obviously Access had actually gone ahead and deleted them before asking if I really wanted to delete them. It was expecting to bring these rows back via some mechanism evidently. I checked the table definition and it was a MyISAM table, not a suprise since I had set it that way on purpose. So I did the following:
CREATE TABLE table2 LIKE table;and linked this table into Access via ODBC just like the first table. I then did the 'delete a couple rows, change my mind' thing again.
ALTER TABLE table2 ENGINE InnoDB;
INSERT INTO table2 SELECT * FROM table;
It worked.
So my conclusion is that the Access front end is counting on transactions being supported in order to roll back the operation if the user elects not to perform it.
My real concern here is the "You are about to delete 1 record(s)" message, the key words being "about to"— it has already done so! Especially since it has blithely begun a transaction and deleted your rows, confident in it's ability to roll this transaction back, and completly unaware that the underlying storage engine does not support transactions and has already irrevocably deleted them.
This gives rise to a whole slew of further questions. First of all I don't exactly like Access's style of first deleting the records and then telling me I am about delete them and do I really want to, etc. I really feel it should ask me first.
That said, obviously it sends some command to initiate a transaction. At some point deep in the bowels of MySQL this command performs a null action since the underlying storage engine does not support transactions. However, it returns as if it did. The question is, since I am using an engine that is not transaction-safe, should this command return an error? Should there be an option to enable or disabling returning an error? What were the design factors leading to the current way it is implemented?
posted by Bob Field at 6:56 AM
1 Comments:
Hi Bob,
interesting observations, gotta remember that.
I don't think access can do much better though. The way I see it, access does send a SEND TRANSACTION command (or an equivalent) to the server.
Now, access knows it will be utilizing the transaction to modify the data in just one table, but mysql does not know this. MySQL knows that transactions are not supported for the MyISAM table, but I don't see how access should know about that.
Ok, Access performs the DELETE statement, and after that performs either a ROLLBACK or a COMMIT in response to the 'are you sure?' dialog.
After the DELETE, all that can happen at this point is that MySQL raises a warning indicating that the last action cannot be rolled back. Well, even if mysql would do that, it wouldnt be very useful as the harm's already been done. Alternatively, MySQL could raise an error but this would make it impossible to mix actions on tables that do and tables that don't support transactions. So, neither a warning nor an error results, and access knows no better than that it will be able to rollback changes.
You might argue that access should not perform a DELETE statement - it could just 'hide' the rows and perform the actual DELETE on the underlying rdbms later on. However, this would mean that access should take the responsibility to maintain an entire transaction itself, forfeiting the benefits of the transaction support of the underlying rdbms if it would be there.
So there really isnt a good solution at this point. The only way out would be if the interface (ODBC I think in this case) would have a method to discover if transactions are supported for a particular table.
Post a Comment
<< Home