1

I know that I can reset the AUTO_INCREMENT value in MySQL using

ALTER TABLE fooo AUTO_INCREMENT = 1;

However, can I avoid changing the value when the table is not empty? Alternatively, can I automatically and atomically set it to the current maximum value + 1?

Background

I have a queue table that is filled by various processes with simple data that is processed by another, single process. The latter deletes items it has processed. It is likely that the table becomes completely empty, although new data will come in quickly.

Currently the auto increment value is over 1,000,000,000 and sooner or later this will lead to nasty integer overflows in the processes. This is why I'm looking for a way to reset the autoinc value for a busy table (within the process that consumes/deletes the rows in the table).

NOTE: As this is a queue, I must absolutely avoid to set the autoinc value to a value lower than currently existing data.

Udo G
  • 11,022
  • 11
  • 47
  • 77
  • 1
    Changing the data type to `bigint` will solve this issue without anything else you need to do, Signed bigint max value is `9.223.372.036.854.775.807` – juergen d Apr 03 '15 at 10:13
  • @juergend, any reason not to use unsigned? – Marcus Adams Apr 03 '15 at 11:41
  • @MarcusAdams: Almost all public APIs are designed using signed INT to be compatible. I would use it if I don't really need unsigned. It is a real pain casting all API calls to `unsigned` in your program. – juergen d Apr 03 '15 at 11:44
  • @juergend: the problem is not within MySQL, but the PHP script consuming the data – Udo G Apr 03 '15 at 12:55
  • @UdoG: http://stackoverflow.com/a/8647177/575376 – juergen d Apr 03 '15 at 13:29
  • @UdoG: From the post I linked before: *64-bit platforms usually have a maximum value of about 9E18.* ...Isn't that enough? – juergen d Apr 03 '15 at 13:59

3 Answers3

0

It sounds a bit that MySQL is not the right tool for your needs. For queues there are better systems like RabitMQ for example. You could use Redis to save data in a very fast way.

It's better to use the right tool for a problem.

Another way for MySQL is to change column type to a format with more possible ids like bigint for example.

René Höhle
  • 24,401
  • 22
  • 66
  • 73
  • RabbitMQ is used for other stuff, but in this case we're talking about >10,000 items per second. To use RabbitMQ for that would be overkill. The data is anyway stored *permanently* in the MySQL database after processing anyway. – Udo G Apr 03 '15 at 12:59
0

How about LOCK TABLES fooo WRITE, then SELECT COUNT(*) FROM fooo to check if the table is empty and reset the AUTO_INCREMENT value if it is, then UNLOCK TABLES. Repeat until you happen to lock the table while it's empty, and thus manage to perform the reset.

Aleksi Torhamo
  • 5,654
  • 2
  • 30
  • 39
  • That could work. It's just that I have had very bad experiences with LOCK TABLES in MySQL (unstable MySQL when something goes wrong during the LOCK). – Udo G Apr 03 '15 at 13:01
0

With MyISAM, simply reset to 1 with ALTER, and it will automatically use the next highest.

For InnoDB, to reset and use the next highest value atomically:

LOCK TABLES table WRITE;
SELECT COALESCE(MAX(id), 1) FROM table;
// Use value from SELECT in the ALTER
ALTER TABLE AUTO_INCREMENT = 99;
UNLOCK TABLES;
Marcus Adams
  • 49,523
  • 8
  • 81
  • 132