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:

CREATE TABLE table2 LIKE table;
ALTER TABLE table2 ENGINE InnoDB;
INSERT INTO table2 SELECT * FROM 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.

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?

A Million Tables

The question was asked if one could create a million tables in a MySQL database. So I put together a small php script to do just that. It ran all afternoon, all evening, and into the night. It hung during the creation of the 262,731st table. I was running Windows Server 2003, using the NTFS file system on a mirrored volume. Each table was an InnoDB table with a single tinyint field and no indexes. The ibdata1 file grew by more than 3GB during the process although no rows were ever inserterd. Windows file compression is enabled, however and the 4.4GB ibdata1 file actually occupies only 295 MB.

I posted back to the fellow after my script had run for several minutes my calculation that it would take about 27 hours just to create the tables and he might want to rethink his design. I never heard back from him.

Recently another forum post asked a question in a similar vein, about creating "more than a couple thousand" databases. I suspect a rather similar answer would be obtained.

People ask too, about how many columns they can create. People always want database to be like spreadsheets, and keep thinking in the wrong terms. The power of a well-tuned database lies in the simplicity of the schema; the fewer databases, tables or columns the better, and instead efficiently storing as many millions of rows as necessary.