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 '='.

• jkavalik: Is the table InnoDB? If yes, then the autoinc value is not stored permanently and resets itself automatically on server restart to the value of max(id) - dev.mysql.com/doc/refman/5.5/en/… - using MySQL manual because MariaDB InnoDB/XtraDB is mostly the same.
• TeasingDart: Yes, it is InnoDB. But I am looking for a statement to add to my database creation script, so the variable lifetime and server restarts are not an issue.
• jkavalik: Thats why it is only a comment and not answer. Could you instead remove setting of the autoinc value from the import script/dump file? That way "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." so you end with the max value automatically after inserting all rows from the dump.
• TeasingDart: Except that I want the new maximum to be less than the old counter value. Would it work to just always set auto_increment to '0' before the insert list? I am dropping all the data and reloading it in the script.
• jkavalik: If you remove the old counter, the maximum will be decided by largest id which will be actually imported from your dump.

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;

• TeasingDart: Yes, this works. Using PREPARE also solves another problem as well. How do I print the contents of stmt before executing it?
• jkavalik: You can just do "select @query;" to see results of that concat. I do not know of any way to print the prepared statement itself.