title: MySQL DROP TABLE and TRUNCATE not fully resetting AUTO-INCREMENT
body: <p>I have a table in a MySQL database called Events that is defined like this:</p>
  <pre><code>CREATE TABLE IF NOT EXISTS `TrackIt`.`Events` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `EventType` INT NULL,
    `timestamp` TIMESTAMP NULL,
    `Comment` TEXT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_Events_EventTypes_idx` (`EventType` ASC) VISIBLE,
    CONSTRAINT `fk_Events_EventTypes`
      FOREIGN KEY (`EventType`)
      REFERENCES `TrackIt`.`EventTypes` (`id`)
      ON DELETE NO ACTION
      ON UPDATE CASCADE)
  ENGINE = InnoDB;
  </code></pre>
  <p>Once in a while, I desire to reset the auto-increment column so that all of the id's are sequential without any gaps. But it seems that no matter which way I accomplish this (either by drop table or truncate), adding another record to the reset table makes the id field much larger than the last id... for example:</p>
  <p>If I reset the id field using this method:</p>
  <pre><code>CREATE TABLE NewEvents LIKE Events;
  INSERT INTO NewEvents (EventType, timestamp, Comment) SELECT EventType, timestamp, Comment FROM Events ORDER BY id;
  DROP TABLE Events;
  CREATE TABLE Events LIKE NewEvents;
  INSERT INTO Events (EventType, timestamp, Comment) SELECT EventType, timestamp, Comment FROM NewEvents ORDER BY id;
  DROP TABLE NewEvents;
  </code></pre>
  <p>Then here is what I get from a SELECT, which is expected:</p>
  <pre><code>SELECT id FROM Events ORDER BY id DESC;
  
  450
  449
  448
  447
  ...
  </code></pre>
  <p>HOWEVER, If I then do this, look at the results:</p>
  <pre><code>INSERT INTO Events(EventType) VALUES(1);
  SELECT id FROM Events ORDER BY id DESC;
  
  512
  450
  449
  448
  447
  ...
  </code></pre>
  <p>So when I do the drop table thing and copy the records over, it correctly re-numbers the id field so that everything is sequential. But when I then add another record, its like it knows there were a bunch of records that were gapped and it somehow just skips that number of records and picks up where it once left off..</p>
  <p>At least this is what it looks like is happening to me.</p>
  <p>Is there some setting in MySQL that will cause this behavior? Or is there some other command that I can execute between the dropping of the table and the re-creation of it that will fully reset the counter? I'm not understanding what's happening here.</p>