I had a problem the last few months that I could not understand why it happened, but because it was almost never raised on the production server, I didn't invest a lot of time to find out the reason for it.
The problem was that on some period in time, an index in a table of MySQL that is declared as Auto Increment lost it's value, and the new indexes started counting as "1" instead of (for example) 32507. Now I know what you are thinking, I might have used a small-sized integer, and there was an integer overflow, and instead of giving negative values, it just started counting with "1". Well, no! I used an unsigned big int type. The data on that table is just keep on changing like a temporary table, and the old data is been stored on different table.
I kept restoring the right index value every time it happens, until I started having deadlocks. The deadlocks were very weird, because only one program opened the connection, and it put itself on a deadlock, so I could not understand why I'm having them. After talking with Oded Arbel, and after he enlighten me regarding MySQL and deadlocks, I started investigating the subject. I found out something really disturbing that you might find as well after googeling or something:
MySQL have several table engines: one of them is InnoDB, another one is MyISAM (that replaced ISAM), and many other engines exists as well, that each of them handles and stores the data differently, and therefor giving different advantages over the others. I believe that this is one of the reasons MySQL became so popular with Open Source projects.
I found out that InnoDB stores indexes only in memory, and it even has a variable inside that I can set to tell MySQL how many bytes to allocate for the task. So now things started to be clear. The above problem happened every time I stopped MySQL from running, because I required to add additional hardware to the server, or because I needed to make maintenance that was needed to make sure that nothing will be written to any table/databases. So MySQL cleared the memory of the indexes, and because most of the time, the table was empty, MySQL assumed that it required to start counting from “1″, but the other table that stored the information, used the value of the “temporary” table index, which collided with unique index values.
I used InnoDB because I’m making a lot of insert update and delete actions on the that table, and InnoDB is supposed to be faster in that area, where MyISAM that stores the index values in an "myi" file, supposed to be faster in reading data, but slower in changing the data itself.
It seems that the biggest advantage of MySQL has become its biggest disadvantage, and now it is not usable for me anymore, but I can’t stop using it on production servers, only for new projects.