Mariadb reset auto increment

Sometimes, after exporting and importing SQL data, depending on your dump method, auto increment counters are lost and struggle to start at 0 again. So you need to set them again to am available value.

Manual method

First method is to do it manually, get the max id and update counter value:

-- Just for information, check current auto_increment value
SELECT `auto_increment`
FROM  information_schema.tables
WHERE TABLE_SCHEMA = 'databaseName'
    AND TABLE_NAME = 'tableName';

-- Get the current max id + 1
SELECT max(id)+1 FROM tableName;

-- Then, update the auto_increment value with max(id)+1
ALTER TABLE tableName AUTO_INCREMENT = 123;

Procedure method

Or you can do that with a more sophisticated way to reset auto increment counter with only one line by calling a procedure.

In the following script, 2 stored procedure are created:

  1. reset_autoincrement: take in argument a table name to reset his id auto increment value.
  2. reset_all_autoincrement: take zero argument and reset id auto increment value for every table in the current database.
-- Declare procedures
DELIMITER //

-- Procedure to reset autoincrement of a table to max(id)+1
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 //

-- Procedure to reset autoincrement of all existing table in the current used database
CREATE PROCEDURE reset_all_autoincrement()
BEGIN
  DECLARE tablename varchar(64);
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE();
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO tablename;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT tablename;
    CALL reset_autoincrement(tablename);
  END LOOP;

  CLOSE cur;
END //

DELIMITER ;

Now, you can call stored procedure to reset auto increment for a chosen table or for every table of a database:

-- Reset auto increment for one table
CALL reset_autoincrement('tableName');

-- Reset auto increment for every table of the current used database
CALL reset_all_autoincrement();

If you want, you can clean stored procedure by deleting them:

DROP PROCEDURE reset_autoincrement;
DROP PROCEDURE reset_all_autoincrement;

These stored procedures have been tested with MariaDB 10.11.

The first procedure is from simkisia, I wrote the second one.