Alter AUTO_INCREMENT value by select result

Question

Basically what I want is a working-version of the following code:

ALTER TABLE table_name
AUTO_INCREMENT =
(
    SELECT
        `AUTO_INCREMENT`
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_SCHEMA = 'database_name'
    AND TABLE_NAME = 'another_table_name'
);

The error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO_INCREMENT =

The reason:

According to MySQL Doc:

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

This means that whenever I restart the server, my auto_increment values are set to the minimum possible.

I have a table called ticket and another one called ticket_backup. Both of them have a column id that is shared. Records inside the ticket table are available and can be claimed by customers. When they claim the ticket I insert the record inside ticket_backup and then I erase them from ticket table. As of today, I have 56 thousand tickets already claimed (inside ticket_backup) and 0 tickets available. If I restart the server now and don't perform the ALTER TABLE, the first ticket I make available will have id 1 which is an ID already taken by ticket_backup, thus causing me duplicate key error if I don't fix the auto-increment value. The reason for me to want this in a single query is to be able to easily perform the query on server startup.

There is no `SET` in the query. The MySQL syntax error messages contain the fragment of the query starting with the syntax error was detected. It says: "SET AUTO_INCREMENT = " — axiac, Jan 08 '15 at 15:06
@Strawberry I'll be editing the main question to provide my reason. — Marco Aurélio Deleu, Jan 08 '15 at 15:06
@axiac I already fixed that, one of the answers below was about that, but that didn't fix the problem. — Marco Aurélio Deleu, Jan 08 '15 at 15:07
Isn't it easier to run the two queries separately and use the client code to put the value retrieved using `SELECT` into the `ALTER TABLE`? — axiac, Jan 08 '15 at 15:13
@axiac I edited the question to reflect on why I'm trying to reach this goal. I wanted a single query so I could just automatically execute this script at server startup. — Marco Aurélio Deleu, Jan 08 '15 at 15:17
Don't erase them. And certainly not for numbers as small and insignificant as 56,000. Instead see partitioning. — Strawberry, Jan 08 '15 at 15:22
That said, one wonders why you didn't simply refer to the comments section of that page !?!? — Strawberry, Jan 08 '15 at 15:29
@Strawberry I will take a deeper look at partitioning, but the reason to divide the tables wasn't about performance but rather make the reports easier to handle. The software works perfectly, this was my only known issue that wasn't expected. I didn't refer to that page comment section because their solution only covers triggers and Stackoverflow is a great place for developers to find fast solution. — Marco Aurélio Deleu, Jan 08 '15 at 15:33
Indeed, fixing the AI value only once at server start sounds better than running two queries in a trigger before every insert. I wonder if it's not possible to use a variable as a guard to avoid those selects run after the AI was fixed on the first insert. — axiac, Jan 08 '15 at 15:39

score 12 · Accepted Answer · answered Jan 08 '15 at 06:38

12

Try this:

SELECT `AUTO_INCREMENT` INTO @AutoInc
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'another_table_name';

SET @s:=CONCAT('ALTER TABLE `database_name`.`table_name` AUTO_INCREMENT=', @AutoInc);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Jan 08 '15 at 06:38

Saharsh Shah

28,687
8
48
83

You'll receive the bounty rep tomorrow. Thank you. – Marco Aurélio Deleu Jan 08 '15 at 14:44

score 0 · Answer 2 · answered Jan 08 '15 at 02:00

0

Just upon quick glance, the error says it all. Syntax is incorrect

The syntax should adhere to:

ALTER TABLE table_name AUTO_INCREMENT=<INTEGER_VALUE>;

So looking at your query, remove the word "SET"

ALTER TABLE table_name AUTO_INCREMENT =
(
    SELECT
        `AUTO_INCREMENT`
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_SCHEMA = 'database_name'
    AND TABLE_NAME = 'another_table_name'
);

answered Jan 08 '15 at 02:00

rurouni88

1,165
5
10

Although that does make sense and points out my lack of attention towards the proper way to use ALTER TABLE, your solution did not work. I guess for some reason I can't execute a DML statement inside a DDL statement. My code is a mere explanation to what I would hope to achieve. – Marco Aurélio Deleu Jan 08 '15 at 02:06
Yeah looking at http://stackoverflow.com/questions/2741485/set-auto-increment-value-programmatically, looks like you'll have to do it via application code instead of SQL. Interesting :) So, maybe have a postinst script which does that? – rurouni88 Jan 08 '15 at 02:10
This didn't work for me either but the answer from Saharsh did. – dgundersen May 02 '23 at 17:54

score -1 · Answer 3 · answered Jan 05 '15 at 18:18

-1

What about

ALTER TABLE table_name 
SET AUTO_INCREMENT = (SELECT MAX(a.AUTO_INC_VAL) FROM database_name.table_name a) )

if you want the current increment value or

ALTER TABLE table_name 
SET AUTO_INCREMENT = (SELECT MIN(a.AUTO_INC_VAL) FROM database_name.table_name a) )

if you want the same initial INCREMENT VALUE

answered Jan 05 '15 at 18:18

gbtimmon

4,238
1
21
36

Neither of those options worked. Both of them gives me the same error as my statement. – Marco Aurélio Deleu Jan 05 '15 at 18:41

Alter AUTO_INCREMENT value by select result

3 Answers3

Linked