mariadb: setting AUTO_INCREMENT using SELECT

Question

I am migrating a MariaDB database from my personal machine to a test server. The database schema has changed so I need to recreate the tables. The source database is also a mix of data that I want to keep and a lot of test junk that I weeded out of the dump text file manually. Since I dropped a lot of data, I want to update the AUTO_INCREMENT value back to the new maximum.

This works:

MariaDB [imok]> alter table accounts AUTO_INCREMENT 114;
Query OK, 11 rows affected (0.04 sec)
Records: 11  Duplicates: 0  Warnings: 0

But when I try to create a generic statement using a select, MariaDB complains:

MariaDB [imok]> alter table accounts AUTO_INCREMENT (select max(AcctID)+1 from accounts);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select max(AcctID)+1 from accounts)' at line 1

I tried using a variable, with the same result:

MariaDB [imok]> set @autoinc=(select max(AcctID)+1 from accounts);
Query OK, 0 rows affected (0.00 sec)

MariaDB [imok]> select @autoinc;
+----------+
| @autoinc |
+----------+
|      114 |
+----------+
1 row in set (0.00 sec)

MariaDB [imok]> alter table accounts AUTO_INCREMENT @autoinc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@autoinc' at line 1

QUESTION: How do I set the AUTO_INCREMENT value to something other than a literal value in MariaDB?

NOTE: I am using MariaDB not MySQL.

NOTE: The '=' in the ALTER statement is optional, MariaDB gives the same complaint with or without it; setting AUTO_INCREMENT using a literal value 113 works without or without the '='.

Answer 1

I think best would be to remove the value of auto_increment from import script/dump - let it start at 1, then insert all the rows with fixed ID and let InnoDB do the work:

"If you insert a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value." - from manual

If this is not possible, then you can use a variable in the ALTER. You might do that using prepared statement:

SET @query = CONCAT('ALTER TABLE accounts AUTO_INCREMENT = ', @autoinc, ';');
PREPARE stmt FROM @query;
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;