MariaDB indices do not start at 1 after removing database and re-creating it I am currently experimenting with a DB-Driven app. It automatically sets up its database-tables if its database does not exist, so it is very easy for me to drop the database and just start from scratch.
During those experiments I noticed that after such a fresh start the db-indices do not start at 1 again, but continue counting as if the DB had never been removed.
So it seems just deleting the DB is not sufficient. Are the last indices cached somewhere or kept in memory - do I need to restart MariaDB to get &quot;truly fresh&quot; indices after a reset?
(It won't affect the software, but I'm concerned I may not be cleaning up well enough...)
Here's an example: table migrations is created using
CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL,
  `exec_ts` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Looking at the current content, there are 4 records with id from 24 to 27.
After dropping the db and going through the creation, I see the same values. Ok, so somehow their base hasn't changed - but why don't the autogenerated indices start at 1?
Browsing around, I came across information_schema.TABLES which has a record for this table and AUTO_INCREMENT indeed shows the value that the next record got when I manually created one. I have verified that dropping the DB also deleted that record, but it must have &quot;hidden&quot; somewhere else...
Looking at the current content, there are 4 records with id from 24 to 27. After dropping the db and going through the creation, I see the same values. Ok, so somehow their base hasn't changed - but why don't the autogenerated indices start at 1?

As the database is recreated by he application, and the application is inserting those rows as part of the recreation, it's completely in the hands of the application. It's possible the application does recreate the tables with auto_increment=1, but then inserts those rows and deliberately specifying the PK values. Or, it's recreating the table, but with auto_increment=24, so when it inserts the rows, they start at that value. You could try to recreate the table manually yourself without any rows and auto_increment=1.

Browsing around, I came across information_schema.TABLES which has a record for this table and AUTO_INCREMENT indeed shows the value that the next record got when I manually created one. I have verified that dropping the DB also deleted that record, but it must have &quot;hidden&quot; somewhere else...

Yes, it seems the start values are hidden inside the application.