0

In a table in mysql db (version: 5.7.37) I have an auto-increment field (id column).

The table is a huge one, but sometime we delete records (so id of 1 million is not 1 millions records) - still a huge one after the deletion.

Sometime the id reaches the maximum integer value (2147483647).

I won't change to bigint (it is complicated - there is limitation in code also).

I want to do it cyclic - when it reaches 2147483647, the next value will be 1, so I want to do:

alter table mytable AUTO_INCREMENT=1;

That may seems work with no any exception (for a fraction of second), but when I do:

SELECT `AUTO_INCREMENT` 
  FROM  INFORMATION_SCHEMA.TABLES t 
  WHERE TABLE_SCHEMA = 'myschema' 
  AND   TABLE_NAME   = 'mytable';

I see that the AUTO_INCREMENT for above is still of value: 2147483647 and not of value 1.

If I do something like:

SET FOREIGN_KEY_CHECKS = 0;
set unique_checks=0;
alter table mytable
  change column `id` `id` int(11) NOT NULL;
set unique_checks=1;
SET FOREIGN_KEY_CHECKS = 1;  

SET FOREIGN_KEY_CHECKS = 0;
set unique_checks=0;
alter table mytable
  change column `id` `id` int(11) NOT NULL AUTO_INCREMENT;
SET FOREIGN_KEY_CHECKS = 1;  
SET unique_checks=1;


alter table paranoid_zero AUTO_INCREMENT=1;

The above take too long time (few minutes), and still AUTO_INCREMENT value checked by query:

SELECT `AUTO_INCREMENT` 
  FROM  INFORMATION_SCHEMA.TABLES t 
  WHERE TABLE_SCHEMA = 'myschema' 
  AND   TABLE_NAME   = 'mytable';

is equal to 2147483647.

How can I update auto increment value (fast) in a proper way with mysql query.

Thanks.

Eitan
  • 1,286
  • 2
  • 16
  • 55
  • 1
    Autoincrement does not work by this way. If you set it less than current maxvalue+1 then the value to be set is adjusted. If you want to reuse deleted AI values (which is illogical) then assign the value to this column programmatically. – Akina Oct 19 '22 at 13:11
  • I would suggest you to copy all records into a temp table and then empty the actual table. Then copy it back – Indra Kumar S Oct 19 '22 at 13:14
  • I would first suggest to define your auto_increment column as an `UNSIGNED` integer, that would you allow to use the double amount of id's (=4294967295). If that is not enough, then change your auto_increment column to BIGINT UNSIGNED. – Georg Richter Oct 19 '22 at 13:19
  • Also you can create a MySQL event / a cron that will check the next increment id and if it is nearing by 2147483647 then 1) create temp table 2) Copy records to temp table 3) Truncate actual table 4) Copy it back to actual one – Indra Kumar S Oct 19 '22 at 13:20
  • For any solution to change the type of the column - it is too complicated for some programming languages, and also I can encounter a problem again. What is happen when it reach the maximum integer value of 2147483647 - next time it is is stucked on the same id value of 2147483647. I want to run a fast update to update the increment value. I understand why some changes may take long time for full table scan, but I don't understand why this decleration is too `heavy` query. That won't change behavior of each record in of the table (i.e sequences in oracle may be better approach). – Eitan Oct 19 '22 at 13:32
  • Does this AI is used somewhere out of the table (for FK relation, for example)? – Akina Oct 19 '22 at 13:33
  • SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA. KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'myschema' AND REFERENCED_TABLE_NAME = 'mytable' return zero no. of records. – Eitan Oct 19 '22 at 13:39
  • ... Also after checking by: `SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES t WHERE TABLE_SCHEMA = 'myschema' AND TABLE_NAME = 'mytable';` the value autoincrement value is still 2147483647 – Eitan Oct 19 '22 at 13:42
  • If you have a big gap in the ids, I may have another trick. – Rick James Oct 21 '22 at 19:28

1 Answers1

2

An AUTO_INCREMENT will not go below MAX(id). That is, if there are rows still in the table, you cannot change the value all the way down to 1. That is, if your DELETEs did not remove the row with ids near 2147483647, you would gain much breathing room.

You seem to have ``INT SIGNED. Changing to INT UNSIGNEDwould give you about 4 billion limit. But it will be just as slow as changing toBIGINT`.

Use IODKU instead of REPLACE. All flavors of INSERT will assign an id before realizing that it is not needed. Could this be 'burning' ids faster than you realize?

Faster DELETEs: http://mysql.rjweb.org/doc.php/deletebig
Batch Normalization
(And I may have other tips relating to your problem and its cause.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1. I don't understand what is IODKU. 2. The db (maybe years) reaches the maximum integer, so any next value is stucked on the same maximum value, so I get duplicate errors, 3. For changing to bigint - too much changes in code (and not all programming languages can handle bigint values). – Eitan Oct 21 '22 at 08:33
  • IODKU (upsert) (INSERT ... ON DUPLICATE KEY UPDATE...) has benefits over `INSERT IGNORE` and it completely replaces this common multi-step task: Select to see if row exists; if it does not, then Insert a new row; else Update the existing row. – Rick James Oct 21 '22 at 19:27