Mysql – How to completely remove MySQL from Windows

1. If undoing the only changes you made doesn't fix http access, you made more than those changes.
2. It doesn't want you to change the password in the phpMyAdmin configuration file. It wants you to change the root user's password in MySQL itself.

The auto-increment counter is stored only in main memory, not on disk.

http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html

Because of this when the service (or server) restarts the following will happen:

After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1.

So in plain English, after the MySQL service starts it has no idea what the auto-increment value for your table should be. So when you first insert a row, it finds the max value of the field that uses auto-increment, adds 1 to this value, and uses the resulting value. If there are no rows, it will start at 1.

This was a problem for us, as we were using the table and mysql's auto-increment feature to neatly manage IDs in a multi-threaded environment where users were getting re-directed to a third-party payment site. So we had to make sure the ID the third party got and sent back to us was unique and would stay that way (and of course there's the possibility the user would cancel the transaction after they had been redirected).

So we were creating a row, obtaining the generated auto-increment value, deleting the row to keep the table clean, and forwarding the value to the payment site. What we ended up doing to to fix the issue of the way InnoDB handles AI values was the following:

$query = "INSERT INTO transactions_counter () VALUES ();";
mysql_query($query);
$transactionId = mysql_insert_id();
$previousId = $transactionId - 1;
$query = "DELETE FROM transactions_counter WHERE transactionId='$previousId';";
mysql_query($query); 

This always keeps the latest transactionId generated as a row in the table, without unnecessarily blowing up the table.

Hope that helps anyone else that might run into this.

Edit (2018-04-18):

As Finesse mentioned below it appears the behavior of this has been modified in MySQL 8.0+.

https://dev.mysql.com/worklog/task/?id=6204

The wording in that worklog is faulty at best, however it appears InnoDB in those newer versions now support persistent autoinc values across reboots.

-Gremio