A co-worker just made me aware of a very strange MySQL behavior.

Assuming you have a table with an auto_increment field and another field that is set to unique (e.g. a username-field). When trying to insert a row with a username thats already in the table the insert fails, as expected. Yet the auto_increment value is increased as can be seen when you insert a valid new entry after several failed attempts.

For example, when our last entry looks like this...

ID: 10
Username: myname

...and we try five new entries with the same username value on our next insert we will have created a new row like so:

ID: 16
Username: mynewname

While this is not a big problem in itself it seems like a very silly attack vector to kill a table by flooding it with failed insert requests, as the MySQL Reference Manual states:

"The behavior of the auto-increment mechanism is not defined if [...] the value becomes bigger than the maximum integer that can be stored in the specified integer type."

Is this expected behavior?