Dave’s Blog

I spoke to a friend recently who had a nightmare story in osCommerce, where when a new customer signs up, another customers details appeared in their address book.  What had happened was the store was copied from another live store, and had the database erased, however entries in the address book table were left.  As new customers signed up, the auto number incremented the customer id and matched up to old entries in the address book table.

This example highlights several problems, of which to be aware.

First of all it shows the importance of not copying stores, but installing them from scratch. It’s not worth the risk of missing any data.

Secondly, there is a very large danger of this sort of thing happening with any non-relational database.   MySQL has the option of using the InnoDB database engine, which supports foreign key constraints, but nearly all open source projects use the MyISAM database engine.  If osCommerce was built using InnoDB with foreign keys, the delete command would have failed for the customers table citing foreign key dependancy to the address book table.

Lastly, it is poor design in MySQL to reuse any auto_increment number in a table.

Why do people continue to use MyISAM? MyISAM allows foreign key statements, and rather than giving an error, ignores them.  Many people may think their database enforces foreign keys, when the truth is they have a ticking time bomb.