MySql losing an auto-increment
Something bizarre happened at a client: a table with lots of daily inserts and deletes (somehow!) lost its auto increment, unique and primary key restrictions.That had some far reaching consequences.
As this table was truncated every night, and then filled from different sources, after losing its auto increment and primary index things where destined to go very very wrong quickly.
And they did indeed.
The newly inserted data relied on the id field being auto-incremented. But as the auto-increment field no longer worked all id fields of all new data had the same value: 0 !
After millions of rows where inserted there was a de-duplication step. This involved finding rows with certain fields containing the same values and from those clusters marking all but the first (cheapest in this case) for deletion. In this query there was a self-join. On the id field. Oh boy.
As you can imagine, joining a table with millions of rows to itself without proper use of an index is a bad idea, and that proved to be a correct assumption indeed. The de-duplication step did not complete before the next day arrived, and the whole process was started again.
So now the system was doing the same hopeless task twice at the same time. Oh boy.
The only solution was to terminate the processes, kill the queries, and re-create the index.
After that the regeneration process could be restarted and finished in the usual time of about 3 hours.
Ellert, November 2020.
