I ran into this the other day, and while the solution is
documented in the manual, it was kind of buried, so I thought I’d
mention it here (and hopefully make it easier for others
searching for this in the future).

In this specific instance, one was running a huge LOAD DATA (10
hours into it) and had to terminate the LOAD DATA command.

Terminating the command is one thing, but preventing InnoDB from
trying to undo the rows that were inserted is another.

In this case, the LOAD DATA will easily re-create the table in
question, so the existing table can be “thrown out”, so to speak.
This, of course, is a *critical* requirement for this operation
(i.e., that you can easily re-create the table – either from a
load, backup, dump, etc.).

Per the manual:

“If you know that a given table is causing a crash on rollback,
you can drop it. You can also use this to stop a runaway rollback
caused by a failing …

So, this is a small blog that I felt necessity to do cause I am
seeing many tweets on twitter (obviously) talking about that “a
rollback query on innodb does not reset the auto_increment
counter”. I quickly went test it and really didn’t understand
what happened with this user environment, due to it I decided
[...]

Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.