There are many answers when I googled for reset auto increment in MySQL table. But very few specifically talk about doing it purely using MySQL query and simply set the auto-increment to just +1 of the largest id value.
This article addresses that specifically.
Since I couldn't find exactly what I want here, I have cobbled the answer from various answers and sharing it here.
Few things to note:
create a stored procedure like this:
DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN
SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
PREPARE stmt FROM @get_next_inc;
EXECUTE stmt;
SELECT @next_inc AS result;
DEALLOCATE PREPARE stmt;
set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
PREPARE stmt FROM @alter_statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
Then run it.
This time you can simply use normal MySQL queries.
call reset_autoincrement('products');