I just started working on a project dealing with a table called orders. Each order has an order_id field which is a unique incrementing number. Well apparently when this was designed three years ago the person had never heard of an auto incrementing key. Instead there is a separate table called count. When a new order is created the value in count is selected as the order id and then updated to be one higher. After that completes a new order is inserted with the order_id. This has caused conflicts in the past where two people have placed orders close enough that data gets over written or not written at all.
I've pulled out the code that selects the count field and updates it and I've changed the order_id field to be an auto incrementing field. This seems to have worked for the most part except that in certain cases along with my inserted order it inserts a blank row.
The exceptionally weird part about it is that occasionally it will wait a full two to six minutes after the fact to insert the blank row. I reverted my changes and it stopped doing it so whatever is happening is some how directly related to auto incrementing that field.
i can assure you that with several million installations of mysql, if there were a "whoops, accidentally inserted a blank row" problem with the mysql server, then it would've been reported -- and then fixed -- long ago
it's gotta be something that your application code is doing
Indeed you're right. I pulled just the sql out and it inserts one row every time. Playing around with some time stamps and uniquely generated id's it seems that the code is some how calling the function to create an order twice...
Not sure why changes inside a function will make it get called twice but it's certainly not a database error.
After fighting with this for the last two days I've determined my firebug extension to fire fox was causing the form to double post, once with blank data. Apparently this is a firebug bug. I post this here in case anyone else has had similar problems where something seems to be inserting twice, or blank rows, or not at all. Disable your firebug and try again.